Cash Flow Model: The Steps to Success (Part II)

In last week’s post we looked at the main components of a cash flow model and today we’ll build on that discussion and identify the need-to-know phases of the build. You may find it useful to reread Part I of this two-post series before diving in.

As discussed in the prior post, the equity markets have become hyper-focused on what the debt markets continuously test for: high-quality cash flow. Building a cash flow model is an integral tool for analyzing a company’s or project’s ability to cover fixed and variable operating costs, make debt payments, and invest in the future.

The 6 Phases of building a Cash Flow Model

1. Discovery

During this phase, you are trying to learn as much about the potential transaction and industry. As a potential stakeholder, you are identifying “fit” or the suitability of the opportunity. In this process, you are asking:

• Do I have enough information to understand the opportunity?

• Do I have the right resources or capabilities to manage the opportunity?

Through this discovery process, the core fixed and variable inputs are identified. If you are unable to confidently answer the two questions above, then the opportunity might not be a good “fit” and developing a complex cash flow model could be an inefficient use of time. Other more suitable opportunities may be lost in the interim. However, if you’ve determined that the opportunity is generally a “fit” you can move on to the Plan and Design Phase.

2. Plan and Design

A large portion of your time should be spent in the planning phase. Designing a model with the goal in mind and a firm grasp of the main attributes, “Inputs”, that contribute to cash in/out flow will substantially reduce headaches later. The goal and requirements, of the analysis, should be discussed with stakeholders, documented, and agreed to ahead of time. As an enthusiastic cash flow modeler, you may spend valuable time creating a model, that is robust and dare I say beautiful but doesn’t fully satisfy the core objectives. Confirm ahead of time:

• Fixed Inputs (contract terms and agreements)

• Variable Inputs (high-leverage assumptions)

• Output Reports

  • Information – Cash flow frequency, IRR, NPV, break-evens, and benchmarks

  • Format – How the outputs are to be displayed to create effective visual insights

Adjustments to the cash flow model that are required after the fact can be extremely time-consuming and retooling can be prone to errors. The best way to challenge your understanding of the requirements is to mockup, for approval from stakeholders the information the output reports will include and how they will look.

3. Collect Information

Identify the source of the Inputs and accumulate each piece of data prior to beginning. Determine how the data is to be extracted, transformed, and loaded (ETL process) into the model. If critical pieces of data are not going to be available there are mitigating actions that can be taken at this stage. Data should include both the Inputs to the model and any information you may want to benchmark your Outputs against. It is important to consider where and in what format data will be available during both the initial evaluation period and on an ongoing basis. Often once a deal “closes” the party responsible for collecting and disseminating information changes which may mean making significant changes to the way your model digests data.

4. Construct and Test the Model

A great amount of attention to detail is required to execute your design. Creating your model structure, formulas that pull together Inputs from different sources to create a seamless, easy-to-understand financial model can be daunting. To help make it less daunting creating a list of each anticipated build component and prioritizing each feature by must-haves and nice-to-haves can help you stay focused on the most critical components and potentially cut down on the time to build. To reiterate building an overly complex model when something more basic will fit your needs is a poor use of time and can lead to missed opportunities. To ensure the quality of calculations and flexibility of the desired functionality test each major build component as you complete them.

5. Report and Decide

Reports should pull from your model, mission-critical, goal-orientated outputs that were identified in the Planning and Design Phase. Like the construction of the model, reports should be created in an order of priority. Valuable insights can be missed due to over development of competing reports. Always keep in mind that a model that is both easy to understand and confidently relied on can pay dividends in making key decisions affecting your business.

6. Monitor and Adjust

If you are creating a model for a single point in time, then you may not need to go any further. However, if the opportunity is executed at the end of the initial evaluation process, having a live model that can be updated with either updated assumptions or actual performance numbers will be invaluable. Creating high-quality feedback loops can help confirm the original assumptions or reveal critical changes that are needed to steer you back toward your desired path.

The creation of a high-quality cash flow model is somewhere between art and science. I have rarely seen two identical. However, ensure the core components are included and phases followed to create an intelligible and usable model.

Author: Benjamin Armenti, President