Building High-Quality Financial Models

Firms spend an enormous amount of time, money, and effort in financial forecasting to make strategic decisions that determine their long-run financial performance. In practice, spreadsheet-based financial models are used to forecast or predict a firm’s future financial performance. A financial model is simply a mathematical representation of a financial situation or condition. Financial planning models are generally used in developing business plans, projecting a firm’s future financial needs, valuing a firm, and in developing corporate scenarios to forecast the effect of alternative strategies. A typical financial model consists of three basic elements: (1) model parameters (also called value drivers), (2) financial policy assumptions, and (3) pro forma financial statement.

The quality of the financial models heavily depends on the quality of the underlying assumptions on which the models are built. Assumptions are the model parameters that are used as inputs to the financial planning model and are considered accurate and valid. However, in practice, these assumptions may not be estimated appropriately. Therefore, a wrong input could generate an error in the output (for example, cash flow), resulting in incorrect decisions and financial losses. The total errors in the future cash flows is called foresting risk (or estimation risk). To prevent or minimize the errors in the forecast we can use quantitative analysis tools such as sensitivity analysis (also known as what-if analysis) and scenario analysis to identify and analyze problems in the financial model, and then taking corrective action to solve the problems. Microsoft Excel offers four tools to perform sensitivity analysis: (1) Data Table, (2) Goal Seek, (3) Scenario Manager, and (4) Solver. In this article, we use the Data Table feature of Excel to perform sensitivity analysis to examine the effect of input data (assumptions) on financial model outputs.

In sensitivity analysis, we examine the model outputs with respect to the changes in the model inputs or value drivers. The financial model developed in Program_1 uses “one” and “two” dimensional Data Tables to perform sensitivity analysis, in which inputs are varied. Table_1 of Program_1 analyzes the effect of sales growth (driver parameter) on the share valuation or price per share. The sales growth assumptions in the range from 10% to 18% are generating optimum share prices. However, below and above these ranges, the sales growth parameters are generating lower share values, which are inappropriate conditions.

Table_2 examines the effect cost of goods sold (normalized to total sales) on a firm’s profitability. The linear graph shows that a one percent increase in COGS reduces the profit by $36,400.

Table_3 examines the effect of long-term growth rate (g) and the weighted average cost of capital (WACC) on per-share valuation, using a two-dimensional data table. The results are as follows:

• The rows of Table_3 show that share price declines as WACC increases.
• The columns of Table_3 show that share price increases as the long-term growth rate increases.

After analyzing the above charts, we see that steeply changing (large slope) curves are very sensitive to small changes in the input, and therefore carry high forecasting risk. And relatively flat curves carry low forecasting risk.

Sensitivity analysis gives us the ability to analyze and control the effect of assumptions on model outputs. It is very useful in identifying areas where forecasting risk is severe. If the model output is very sensitive to small changes in input data (or assumptions), then the forecasting risk is very high. It provides a clear picture and understanding of the uncertainties involved in the input/output model relationship. This understanding provides many options for the management team in making quality decisions by selecting the most appropriate assumptions while eliminating or mitigating the impact of other risky inputs. Therefore, the purpose of sensitivity analysis is to remove or minimize the uncertainty from the financial models to reduce forecasting error and to ultimately improve the quality of forecasting.

References and Further Reading

1. B. Render, R. M. Stair, and M. E. Hanna, Quantitative Analysis for Management (New Jersey: Prentice-Hall, 2003), pp. 2-6.
2. S. A. Ross, R. W. Westerfield, and B.D. Jordan, Fundamentals of Corporate Finance (New Delhi: Tata McGraw-Hill Publishing Co., 2002), pp. 349-356.
3. Simon Benninga, Principles of Finance with Excel (New York: Oxford University Press, 2006), pp. 293-296.
4. Wayne L. Winston, Microsoft Office Excel 2007: Data Analysis and Business Modeling (Washington: Microsoft Press, 2007), Chapters 15, 16 and 17.
5. Simon Benninga, Financial Modeling (Cambridge, MA: MIT Press, 2000), pp. 57-80.