Chapter 11: Model Testing for Reasonableness and Accuracy

In Chapter 10, Valuation, we built a complete Discounted Cash Flow (DCF) valuation model and obtained alternative valuations with trading and transaction comparatives. We also prepared a football field chart to interpret our results.

In the process of building our model, we made a number of assumptions based on our experience, historical financials, and discussions with management. We recognize that we could have selected a different set of assumptions from the same information so it is only right that we test our model as it is for reasonableness and accuracy.

In order to reduce the effects of the subjectivity inherent in your model, you will need to adopt certain procedures, some of which we have already mentioned, and carry out certain tests designed to highlight the most volatile assumptions and give direct attention to the inputs to which the model is most sensitive.

By the end of the chapter, you will understand...

Incorporating built-in tests and procedures

A financial model, by its nature, is full of formulas and calculations. Although most of them are simple, their volume and repetitiveness create exposure to errors that could cause the most accomplished modeler nightmares trying to track them down.

The following are some of the procedures to adopt in your model to reduce this exposure:

• Hardcoded cells
• Balance checks
• Cash and cash equivalents
• Entering values only once
• Using one formula per row

Let's look at each of these procedures in detail:

• Hardcoded cells: Hardcoded cells should be distinguished by using a blue font. The significance of hardcoded cells is that they are the primary input number cells, which contain data that is fixed and will not change (unless it is to correct an error or modify an assumption) and should therefore be formatted in such a way that the cells are easily identified. An example of this is historical information....

Troubleshooting

It is unprofessional to share a model that is full of errors. You should always check your model for errors and then take steps to correct them.

The following are guidelines to follow when errors are detected in your model:

• Precedents are those cells that have been referred to in arriving at the value in a particular cell.
• Dependents are those cells that have included the cell in focus in their formula.

The following illustration will be used to explain this further:

Figure 11.03 – Cells connected as dependents or precedents

Look at cell K8. The formula in that cell is =K6*(1-$D$7). The formula relies on the contents of cells K6 and D7, which makes them precedents of cell K8. On the other hand, cell K8 is a dependent of both cells K6 and D7.

On the formula ribbon, in the formula auditing group, selecting trace precedents or trace dependents reveals thin blue arrows linking a cell to either its precedents or...

Understanding sensitivity analysis

In Chapter 10, Valuation, we computed a value for equity share price. As a result of the uncertainty inherent in your model, you should take some steps to mitigate this. One way is to run some tests to see how the share price behaves when you change some of the inputs and drivers utilized in arriving at that value. This process is called Sensitivity Analysis. Apart from the volatility of your target value, it also indicates which inputs or drivers have the greatest effect on the target value.

You will need to identify two inputs or drivers that appear to hold prominence in your model:

• Turnover: We have already mentioned that turnover is the most prominent figure on the income statement. So we can use the revenue growth driver as one of the items to sensitize.
• Terminal Value: Another item of prominence is the terminal value.

We have seen in Chapter 10, Valuation, how much of an impact this has on your share price valuation. You...

Using direct and indirect methods

There are two methods in sensitivity analysis, direct and indirect methods. Both methods make use of data tables that can be found under What If? in the forecast group on the Data ribbon in Excel.

In order to make use of the data table, you must structure your data in a particular way. The cell at the top-left corner of the table layout must be related to the target value share price, whose behavior we wish to observe.

Figure 11.16 – Layout for a data table

That specific position is essential for the data table function to work. However, since it will not be used for anything else, it is shown here in a white font, making it invisible, so as not to cause any distraction. The row input values are entered across the top row of the table. We have selected the terminal growth rate for the row input, with values from 3% with 1% increments up to 7%.

The column input is revenue growth (Compound Annual Growth Rate (CAGR...

Understanding scenario analysis

In sensitivity analysis, we have selected a few inputs/drivers and changed them while keeping all other variables the same. This has shown us the isolated effect that each of the selected inputs has on the share price. However, in practice, this is rarely the case. Variables do not change in isolation. What you generally have is a number of variables changing as a result of a certain set of circumstances or a scenario.

Scenario analysis usually looks at two or three sets of circumstances, most likely, worst-case and best-case scenarios. For each scenario, you would assume alternative values for selected variables. In selecting the variables, you would concentrate on those inputs or drivers that are the most subjective. Scenario analysis involves substituting all the selected variables for a given scenario in your model and examining the effect this has on the share price.

Creating a simple Monte Carlo simulation model

Monte Carlo simulation is a model that calculates probabilities of different results in a process where there is much inherent uncertainty. The model makes use of randomly generated numbers to obtain thousands of possible results from which a most likely outcome can be deduced. We will look at growth in free cash flow, FCFF, as well as the cost of capital and WACC, which are both integral parts of our DCF model.

FCFF Growth rates can be calculated using the following formula:

Here are the steps to create a simple Monte Carlo simulation model:

1. Calculate the FCFF growth rates for the historical years Y02 to Y05.

Figure 11.45 – FCFF growth rates 02 to 05

Usually, a Monte Carlo simulation uses thousands of repetitions. However, for illustration purposes, we will limit the number to 100.

2. Take the average of FCFF historical growth to arrive at the (historical)...

Summary

In this chapter, we have learned how to build a number of tests and procedures into a model, in order to improve the accuracy of the model. We have learned some basic procedures to follow in order to troubleshoot where errors are revealed in a model. We have understood the meaning of sensitivity analysis and learned how to use direct and indirect methods. We have also learned how to display our results on a chart and to interpret them meaningfully. Finally, we learned about scenario analysis, how it differs from sensitivity analysis, and how to use it, as well as creating a simple Monte Carlo simulation model.

In the next chapter, you will learn how to integrate a trial balance into a 3-statement model of balance sheet, profit and loss account, and cash flow statement.