Financial Model Best Practices
In any moment of decision, the best thing you can do is the right thing, the next best thing is the wrong thing, and the worst thing you…
(this piece was written jointly by Chris Bruno and Adi Dehejia)
In any moment of decision, the best thing you can do is the right thing, the next best thing is the wrong thing, and the worst thing you can do is nothing.
Theodore Roosevelt
Amidst the coronavirus pandemic, CEOs and finance leaders at all companies are rethinking their business plans. Under duress, they are rapidly redoing their 2020 budget and longer term forecasts. The short term goals are survival and understanding how much cash their business might require (if not cash flow profitable) for the next 12 to 24 months.
The most effective way to complete this exercise requires having an interactive, flexible financial model.
A good financial model is a useful tool for decision-making. No single answer offered by a financial model will be correct. However, by building out various scenarios, the model offers guidance on possible future outcomes. With a range of potential future outcomes in hand, business leaders can work with their Board and investors to make better decisions. These decisions will improve the chance for the business to survive in the near term and thrive again in the longer term.
We’ve all heard leaders in meetings say “let’s build a model” before taking a decision. That assigns a task to the finance or FP&A team. The finance organization must create an excel spreadsheet laying out the financial assumptions and outputs around a business decision. The decision-making team will review the model. The model outputs will be a key input in the final decision on whether or not to proceed with an investment and, if yes, under what constraints.
Before opening up Microsoft Excel to “build the model”, finance personnel should think through the following “first principles” of financial models.
Define the Objectives:
Models are generally built with one of three goals in mind.
Individual Investment Decision–Sometimes referred to as an RoI (return on investment) model, this is undertaken to justify a single or set of related investments, whether in software, hardware, hiring new employees or consultants etc.
Company Short-term Forecast–This can be an annual budget (broken into a monthly forecast) or a cash flow forecast (typically done on a weekly basis).
Company Long-term Forecast–This is usually a 5-year model that is used for tasks such as fundraising (debt or equity), an M&A process (merging with another company or selling your business) or a 409(a) valuation (which sets the option price for subsequently granted options.)
Who are the End Users:
Is this model going to be manipulated only by the finance team? Or will other people, whether inside the company in other departments or outside the company, want to “test drive” the model? If the actual excel file is going to be shared broadly, then the need for organizational clarity in the model increases tremendously.
Who is the Audience for the Outputs:
Always have your audience in mind. Plan ahead on how to best present the outputs to the audience who has asked for the model. Think through the appropriate mix of numerical outputs (tables) and visual outputs (charts, graphs, trend lines etc.). Visual outputs can really help communicate numerical information to less financially savvy stakeholders. Also, when creating tabular outputs have levels of detail so that you can provide either/both a summary or/and a detailed version when necessary.
Architecting the Model:
This is the design process before the actual build. In a desire to get going, it is easy to skip this step or take too little time thinking it through. This is the process where you create the “drawings” for the “structure” you are building.
Understand the Business–Choose assumptions as inputs that matter to business results (see sensitivities or cases below). Ideally those assumptions should be something that individual performers or teams can both control (i.e. undertake actions to influence) and measure.
Flexibility–Think ahead as to the variables that your investors or CEO may want to change. Design the model with flexibility in mind.
Simplicity vs. Complexity–The level of detail, particularly around expenses (which are often tracked in great detail in the chart of accounts) should be less in the model than in the chart of accounts. Group items together that can be forecast in relation to revenue, employees or other variables you will be forecasting more explicitly.
Ability to Report on KPIs that Matter to The Business–Understanding the business also involves knowing what KPIs investors care about when evaluating the business. Ensure you can create a worksheet with all the relevant KPIs so that investors can compare your performance over time and to other similar companies they have in their portfolio.
Test and Sanity Check:
During the model build process and again, once the build is completed, audit the model for errors. A best practice is to build in error checks within the model which will easily show you if an error exists at any time in the future.
Besides error testing, it is important to “sanity check” the model. A combination of individually reasonable inputs may lead to outputs that are not achievable with any meaningful probability. Sanity checks involve reviewing the outputs to see if you are forecasting business performance wildly out of line with comparable businesses. Comparable public and private company performance metrics exist for almost all business segments. Find that data and use it as a sanity check.
Sensitivities and Scenarios:
When building a model, plan in advance for both sensitivity analysis and multiple cases or scenarios.
Sensitivity analysis helps isolate the inputs/assumptions that have the greatest impact on the output variables that matter to the Leadership Team and Board. Those outputs can be revenue (actual and growth), a metric measuring profitability (EBITDA or pre-tax income) or a metric related to cash (free cash flow or cash burn). Sensitivities can include changes in timings of events as well as changes in the magnitude of the inputs. Sensitivity outputs are often shown using data tables.
Scenario planning or cases relate to having general views of the future environment which will impact a series of assumptions simultaneously. You may hear investors talking about having a “Base” case, “Upside” Case and “Downside” Case. Give some forethought to the key levers you would want to change in each case. And then build a single “switch” that can be changed in one location which will then select the specific different assumptions relevant to each “case”.
Update the Model Regularly:
Models are merely a forecast about the future. Typically, they are based on:
Past performance, and,
The judgment of the Leadership Team on (i) future performance improvements, (ii) changes in competitive dynamics, (iii) changes in regulation, and (iv) changes in the macro-economic environment.
As time passes, the future becomes the past. Update the full model at least once each quarter. For more fast changing data such as revenue, undertake updates after each month-end close is complete. Model updates are not a substitute for providing weekly or real-time updated dashboards on all important bookings, revenue and customer related data to Leadership.
The update for actual results will in turn change the forecast. And, based on actual events, the finance team may choose to change some of the assumptions to incorporate new knowledge. The revised forecast doesn’t mean that the Board will permit you to change the Budget to reflect new knowledge. However, an updated model is more likely to forecast future events with greater accuracy.
With these principles in mind, you are ready to build better quality models. Remember that interactive models are a simplification to help “show” how a complex system works.It is important to understand that models are primarily tools used for planning. However, your audience will also desire accuracy, especially with short-term projections. That is why it is critical to stay in the habit of presenting ranges of outcomes (sensitivities).
The future is unknowable. And as Mahatma Gandhi said, “the future depends on what we do in the present.” Using these best practices in building financial models starting today will empower all businesses to make more informed decisions.