How Does a CFO Ensure That a Forecast Model Meets the Test?

By Greg Johnstone

As a partner at FLG Partners, I frequently begin new engagements as an interim CFO. When I begin a working relationship with a CEO, I typically put together a brief summary of my top priorities and an action plan for the first 60 days. This both helps me to organize my thoughts and makes sure that the CEO and I are on the same page. Inevitably, one of my top initial priorities is to evaluate the company’s existing financial forecast model (assuming one exists) to rapidly determine whether this important tool is generally sufficient, needs a major overhaul, or should be scrapped altogether and replaced.

Why the Quality of the Forecast Model Matters So Much

It is difficult to overstate the importance of a good forecast model. Regardless of the size or stage of a business, it is critical for its leaders to have a tool that summarizes the expected effects of the company’s planned operations. Just as a company’s financial statements serve as a summary of how it has performed in the past, a financial forecast provides the same but from a forward-looking perspective. Unlike the company’s financial history, which should only have one “version,” there can be many alternative future scenarios depending on choices that management makes as well as assumptions they make about often hard-to-predict external factors. It is the forecast model that helps illustrate the expected outcome (or range of outcomes) of a potential course of action under consideration, and so the quality of the forecast model directly affects the ability of company leaders to make effective planning decisions.

Models, Plans, Budgets, and Forecasts

Models represent an envisioned financial future of a company, and they are all created using some sort of tool (generally a software product or an Excel spreadsheet). Sometimes the model is used as a regularly updated forecast. Sometimes the model is locked to a specific version that becomes a budget or plan. They are all models. Developing a single workable structure and using different versions of inputs to the same model provides major advantages in consistency and ease of use, and the criteria for a good model are largely the same regardless of whether its output is used as a plan, budget or forecast.

Evaluating Forecasts: What Matters?

When evaluating a forecast model, I look at several key factors. In particular, I consider whether the model is:

• Accurate: Has the model projected major company metrics (e.g., revenue, total expenses by department, net cash burn) reasonably accurately in the recent past? If not, were forecast variances to actuals caused by errors in inputs/assumptions, changes in external circumstances, or errors in the model’s logic or algorithms? Are near-term future period metrics fairly similar to those of recent past periods and, if not, are there good reasons for the differences?
• Understandable: Do the model’s summaries provide a clear, easily digestible view of expected performance that provides the reviewer a thorough and useful understanding of how the company is allocating its resources? Are inputs and outputs well-labeled? If one wants to understand the underlying assumptions and key drivers of the model, is it straightforward to do so without having to decipher formulas? Is it obvious which values are inputs, and which are calculated values? Is it easy to drill-down from summaries to lower levels of detail?
• Actionable: Does the model provide information in a way that is actionable by all team members who have budgetary responsibilities? For example, can a department head see the expenses under her control AND what activities and decisions are driving those expenses, so that she can adjust as necessary? Are revenues and expenses grouped in a manner that is consistent with how the executive team thinks about the company’s operations? Are revenues and expenses forecast on the same basis that actuals are recorded in the accounting system, so that forecast vs. actual comparisons can be made on an apples-to-apples basis?
• Robust: Is it hard or easy to “break” the model inadvertently? Are error checks in place to notify the user if the balance sheet doesn’t balance or if subtotals aren’t included in totals?  Are input assumptions well labeled and kept distinct from calculations (rather than buried as constants in formulas)? If the model is dependent upon other company models (e.g., a company budget model that gets revenue figures from a separate revenue model) or data sources, are those linkages resilient? That is, if changes are made to the external model, will the updated results flow into our forecast model accurately?
• Flexible: Here the focus is on speed. Can the model be used to rapidly iterate through and estimate the financial impact of potential changes to company strategy? Can the CEO pose a quick “what-if” question and have it addressed on the spot, or does an analyst have to come back a day or week later with an answer? Can changes to the company’s chart of accounts be accommodated rapidly?
• Appropriate for the Company: Are the cost, level of complexity, and need for support consistent with the stage and complexity of the organization? Is information available to those who need it? Is access to particularly sensitive information (salaries, expected terminations, M&A activity, etc.) kept restricted?
• Comprehensive: Does the model address all of the company’s planning, budgeting and forecasting needs, across all of its operations, for both near- and long-term time periods? If not, are there processes or structures in place to integrate information as needed and keep it consistent (e.g., between a short-term forecast and the overlapping early portion of a long-term forecast)?
• Well-Managed: Who maintains the structure of the model? Who is responsible for making or coordinating changes to input assumptions? Are processes in place to make sure that the model assumptions accurately reflect the most current knowledge and plans that exist within the company? Who handles version control?

These criteria have served me well over my 25 years as a finance professional when evaluating the forecast models in use at numerous companies. Whether they are being used for planning and budgeting, capital expenditure and investment decision making, or cashflow and working capital evaluations, models are crucial tools for all levels of management. As such, it is vital that finance professionals think critically about and address the quality of these tools. If you have questions or concerns about how your business does its planning and forecasting, feel free to reach out to me or one of my partners at FLG.