Build an Error Free Model

What is “Build an Error Free Model”?

A fully-functioning financial model needs to be accurate and error-free. To build an error-free model, construction best practices should be adhered to and integrity checks should be applied throughout the process. These include:

1.     Sense checking – is the output reasonable?

2.     Structure checking – is formula construction consistent and as expected?

3.     Stress testing – does the model react to changes as predicted?

These three checking techniques can be used to audit the constructed model, to ensure that it is accurate and error-free. It can be applied to the balance sheet, income statement, and cash flow statement. Further, there are certain best practices that modelers must adopt in order to effectively tackle the errors in model construction so that the model remains consistent and in good working order.

Build an Error Free Model – Model Integrity and Error Checking

The three components required to build an error-free model, termed error checking techniques, are sense, structure, and stress testing. This involves asking three questions – does the output seem reasonable, is the formula structure consistent and expected, and does the model react predictably to changes?

Sense checking: this focuses on the model’s output. In order to check the reasonableness of the model results, they must usually be benchmarked. This can be done by trend analysis over time i.e. are values within the expected range? Peer comparison is another way to sense check the outputs – are the results consistent with benchmarks? A good sense check would also be analysis of interrelated metrics – do related values move as expected relative to one another?

Structure checking: a logical and consistent model layout allows timely and efficient error diagnosis. A well-built model will use a consistent column structure that allows for copyable formula construction. This also means the column reference used in formulas will be consistent thereby allowing the user to identify any “off column” references with ease.

Stress testing: this process involves making a deliberate change to the model (typically an assumption such as a growth rate) and checking to establish that it has behaved as predicted. Thought should be given to the change that will give the maximum information to the model builder. It is best to make an extreme change so the output effect will be easily seen. The stress testing process involves changing an assumption and predicting the output change as a result of a change in assumption. If the model behaves as predicted, then the analyst must remember to undo the stress test so the model reverts to its correct status!

Best Practices for Modeling to Minimize Error Creation

To minimize error creation, it is a good idea to take on best practices for modeling. The following should be applied where possible:

• Consistency – the more consistent the model, the easier it is to find errors
• Use similar formulas and similar construction in the model – e.g. if your formula is ‘Sales + growth rate assumption = forecast sales’ then it is a good idea to keep that formula-style consistent down the P&L
• Use consistent data sourcing
• Build formulas once only and link thereafter – this can avoid typing errors
• Build copyable formulas – this will facilitate a consistent and easy to error check model
• Calculate all subtotals once and then copy them
• Input historic and hard data once only – avoid duplication and potential error
• Check little and often as you work through the model – you can often correct an error early on
• Build, check, copy – in that order to keep the model consistent

Consistency is the key to minimizing model construction errors. It applies to both data layout and formula construction. The column structure should be consistent throughout. For example forecast year 1 should be in the same column (e.g. Column F) on each sheet used in the workbook. Also, where possible the order of data row by row should be similar. For example, operating current assets should be listed in the same order on the balance sheet as in the operating working capital calculation. The structure of the data in rows and columns is often referred to as the ‘model matrix’.

Another application of the consistency principle is that similar formulas should be constructed in a similar way. The final application of the consistency principles is to link data to the same source where possible. For example, if there is a backup calculation for PP&E, then depreciation on the balance sheet, depreciation, and CAPEX in the cash flow statement can all be sourced from the PP&E BASE analysis.

A fast and efficient modeler will often build formulas only once and then copy them. Rebuilding formulas creates an extra opportunity for a construction error. Further, subtotals can be a notorious source of errors. To minimize this, they should always be calculated and copied. They should never be input or downloaded or linked from another version of the subtotal.

Next, it is important to input historic or hard-coded data only once, and finally, it is helpful to narrow the universe of what is being checked. It is significantly easier to check an income statement rather than an entire model. So best practice is to check little and often, and always check before copying across.

It may be pertinent to be aware that one of the most time-consuming parts of building a 3 statement model is finding mistakes that cause the balance sheet to be out of balance. There are some techniques that should be used to help resolve these issues.

Error Checking Techniques – An Example

In the following example, CAPEX as a percentage (%) of sales has been changed from 3.7% to 50%. PP&E will increase, the business will run out of cash and a revolver drawdown will be necessary. This is a good error check as the increase is significant and should instigate movement across the model. We can see that the model is working as expected.

However, in the next example, given below, something is wrong with cash and revolver. PP&E has increased as expected but cash is negative and the revolver is still zero. The error should be found and fixed.

Finally, any output that is zero or that has been left blank (as an interim measure) should always be stress-tested since sense and structure checking may not work as normal. The dividends payout assumption is changed from 0.0% to 32.0% and the balance sheet is out of balance. The error should be investigated and fixed.

In conclusion, one of the most time-consuming parts of building a 3-statement model is finding mistakes that cause the balance sheet to be out of balance. A combination of sense checks, structure checks, and mathematical checks should be used to ensure that the balance sheet balances.

The balance sheet usually ‘balances’ the entire model should be checked in detail using these three checking techniques. Once this has been completed, stress testing the entire model can be very powerful and will often find subtle errors or those that are not causing an obvious problem.

A final tip is to always stress-test assumptions that are likely to change or which are currently producing a zero amount in the income or cash flow statements, or an amount equal to the historical data in the balance sheet.

Download the accompanying Excel exercise sheets to learn financial modeling and for a full explanation of the correct answer.