Chapter 13
IN THIS CHAPTER
Minimizing the errors in your financial models
Focusing on the details that matter
If you aren’t absolutely paranoid about making a mistake in your financial model, you should be! Even people with a little experience using Microsoft Excel know how easy it is to get something wrong. Both formula and logic errors are easy to make — and they’re prevalent in corporate financial models. As a financial modeler, you have to be vigilant about errors as you build the model. In this chapter, I offer up ten strategies you can employ to reduce errors when building your financial models.
The most common errors in financial models are silly formula mistakes — for example, picking up the wrong cell or missing a dollar sign in the cell referencing. Because these mistakes are the easiest ones to make, they’re also the easiest to avoid.
There are two reasons not to click somewhere else after entering a formula.
A lot of modeling is trial and error. Making mistakes is okay — you just want to make sure you find your mistakes before someone else does! Silly formula mistakes are the easiest ones to make — and the most embarrassing. Fortunately, by employing good error-checking techniques as you build the model, formula errors are the easiest ones to detect and correct.
Highlighting cells and checking totals using this method is a good way of checking for errors. By checking your work as you build, you’re less likely to let errors slip through the cracks.
After you’ve checked your formula and perhaps copied it down a block of calculations, check it again! Another way to check the formula visually is to press the F2 shortcut key, which shows exactly which cells are being used in the formula (see Figure 13-3). This technique is most useful if the source cells are nearby and on the same sheet. When you’ve copied down all the cells in a block of data, do a spot-check with the F2 key to make sure it’s picking up the correct cells.
Once you’ve entered the formulas, checked them, copied down the block, checked the totals, finished your model, and checked again, it’s time to have someone else look at it. I highly recommend having an agreement in place with one of your colleagues where you check each other’s work before it gets sent to senior management. There’s no faster way to lose credibility than having your mistakes pointed out in public.
Why have someone else review your model? Because that other person will bring a fresh perspective. Sometimes you’ve looked at your model for so long that you just can’t see the glaring error right in front of you. Just explaining the model’s logic to someone else can also help you to see holes in the logic and ways to improve the methodology.
If the model is high profile or very important, you may want to get it audited by a model audit firm. Many organizations specialize in conducting professional model audits. This is really the only way to be absolutely confident that there are no errors and your model is working correctly.
A model is only as good as the accuracy of the assumptions. The phrase “garbage in, garbage out” has never been more relevant than in the context of financial modeling. Even the most beautifully built, best-designed model will be completely worthless if the input assumptions that go into it are incorrect.
Important decisions are made based on the outputs of the model, so it’s absolutely critical to list clearly (and sometimes in mind-numbing detail) the assumptions that have gone into the model.
As a financial modeler, you must ensure that all the assumptions have been validated to the best of your ability. Document clearly where the numbers came from so that there can be no possible misinterpretation of the assumptions you’ve used. That way, the assumptions can be revisited and possibly revised at a later date.
When you’re explaining a complex process, such as one represented by your financial model, it can sometimes help to have a diagrammatic flowchart that explains the way that the numbers have been calculated in your model.
Excel doesn’t create a flowchart of the model very easily, although there are add-ins that can help you do this. Without the help of add-ins, you can use SmartArt or simply cells and arrows to create a flowchart to document the inflows and outflows of your data within the model.
A flowchart isn’t always necessary, but if your model contains a large number of sheets and calculations and is difficult for others to follow, a flowchart can be helpful for checking the logic. It’s also useful in explaining the methodology of your model to other people.
After you’ve finished the model, you can test to see if the calculations are working correctly by changing an input and seeing what impact that change has on the numbers. This technique of changing one single input in isolation is called sensitivity analysis (as opposed to scenario analysis, which involves changing several variables at once; see the next section).
For example, after you’ve finished the cafe model in Chapter 12, you can test that the model is working correctly by changing one of the input assumptions and seeing what effect that change have on the output of the model. Follow these steps:
Set your model to the base case on the IS Cash Flow page.
Note that Other Costs is $29,192 and Net Income is $7,791.
Go back to the IS Cash Flow page.
You’ll notice that Other Costs has jumped to $45,804 and Net Income has dropped to a loss of $3,838!
You can see that the model is working correctly, because the costs are increasing, based on the consumables cost increase, and the profit has reduced, which is what you would expect. It also shows that the model is very sensitive to changes in input costs, which is an interesting insight.
Let’s try another one, a little more drastic this time:
Think about what you would expect to happen in the model.
Revenue should be zero, right?
Although you don’t actually expect the consumable costs to increase to a dollar, and you would never charge nothing for your coffee, stress-testing using sensitivity analysis checks both that the formulas are working correctly and that the logic is sound.
See Chapter 8 for more on sensitivity analysis in financial modeling.
After you’ve finished all the calculations in your model, do lots and lots of sensitivity and scenario analyses. Stress-testing with sensitivity analysis (see the preceding section) will check that the inner workings of the formulas and logic of the model are correct, but how realistic are the assumptions? If the absolute worst happens, what happens to your bottom line? How sensitive is your model to changes in key assumptions? This information will help to test the accuracy and robustness of your model, as well as the soundness of the business, product, or project the model is representing.
At a minimum, a financial model should include at least the following three scenarios, or at least some version of them:
Base case: Set all assumptions to what you actually think is going to happen.
Be realistic! This is not the place to be conservative in your estimates — that’s for the worst-case scenario.
Additionally, financial models often include other scenarios to take into account possible fluctuations in inputs due to events, such as the following:
These are just a few generic examples of model scenarios you might use. Scenarios can often flush out anomalies in the model. Look carefully at the results of your scenario analysis. Is it what you would expect to see? Compare the output results side by side. If you increase the inflation amount from 2 percent to 3 percent, do costs increase by the same margin as if you increase it from 3 percent to 4 percent?
Thorough stress-testing, along with scenario and sensitivity analyses, will provide your financial model the rigor and robustness to cope with the variety of fluctuations in assumptions that are possible in the real world.
See Chapter 8 for more on scenario analysis in financial modeling.
As irritating as they can be, I actually like seeing Excel errors in my formulas, because it means that something isn’t working and I can fix it. I’d much rather see an error value than a number that looks as though it’s correct when it’s actually completely wrong — that’ll only cause problems later on in the model.
Common sources of errors are parentheses that don’t match or missing fields for functions. Table 13-1 lists some error values you may get and how to fix them.
TABLE 13-1 Common Error Values in Microsoft Excel
Error Value |
Description |
#DIV/0! |
You’re trying to divide by zero. If the divisor is a cell reference, make sure that it isn’t empty and that it doesn’t have a formula with the resulting value of zero. |
#NAME? |
There’s a name in the formula that Excel doesn’t recognize. If you used a name you defined, check its spelling. You can avoid this error by using the F3 shortcut or selecting a name in the Name Box instead of typing it in. |
#REF! |
Your formula refers to a cell that no longer exists, due to a change in the worksheet. This happens if you deleted cells referred to in the formula or pasted cells onto cells referred to in the formula. You need to rebuild the formula. |
#VALUE! |
The function you’re using is expecting a numerical value, and you’ve entered text, or vice versa. You can avoid this error by using the Function Arguments dialog box. |
Circular Reference |
You’re trying to link a formula to itself somehow. You see Circular followed by a cell reference in the area below the worksheet. Trace back the logic of the formula to correct the error. See Chapter 5 for more information on how to do this. |
Instead of cringing at a horrid #VALUE! error value, or suppressing it with an IFERROR function, take notice of it instead. Figure out what’s wrong, and get to the bottom of it. An Excel error value is far more helpful than the alternative, which is to have deceivingly innocent number values that are, in fact, incorrect.
A good financial modeler is always looking for opportunities to include error checks in the financial model. If you know that the sum total in the CapEx schedule should be equal to the sum of each individual item, add an error check to your calculations to check it automatically, so that the user or modeler can see at a glance if the formulas are calculating correctly. You can do this very simply by deducting one value from the other or inserting an IF function. For detailed instructions on how to build error checks, see Chapter 6.