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.

Using the Enter Key

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.

After entering a formula in a cell, press the Enter key. Don’t just click somewhere else.

There are two reasons not to click somewhere else after entering a formula.

• You may accidentally pick up an incorrect cell, which then autocorrects to a completely incorrect formula, as shown in Figure 13-1.
• More important, when you click somewhere else after entering a formula, you’re not checking what you just entered. You need to be deliberate about what you’ve just done — don’t just quickly move on to the next task. When you finish entering a formula, look at the result. Does it look right? Is the number what you expected?

Pressing the Enter key is your number-one line of defense against errors.

Checking Your Work

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.

After you finish entering a formula, and after you’ve pressed the Enter key (see the preceding section), pause for a moment to check the result. Even if you’re in a hurry. Especially if you’re in a hurry. When you have to get a report out by the end of the day and the formulas are flying, that’s when mistakes happen. Use a calculator or, if it’s a simple sum, highlight the range you’re adding, and check the total in the lower-right corner in the status bar, as shown in Figure 13-2. (The green triangle in the upper-left corner of cell B15 alerts you to the fact that something isn’t quite right with this formula.)

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.

Checking It Again

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.

Getting Someone Else to Check Your Work

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.

Documenting Assumptions

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.

Models are only as good as the data they contain, and the answer they produce should most certainly not be taken at face value. When presented with a model, a smart manager or decision maker will query all the assumptions and the way it’s built.

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.

Documenting Methodology with a Flowchart

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.

Stress-Testing with Sensitivity Analysis

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:

1. 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.

2. Go to the Assumptions sheet and change the consumables per cup in cell B17 from $0.45 to $1.
3. Stop for a moment to think about what effect you would expect this change to have on your Income Statement.

4. 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:

1. Set your consumable costs back to $0.45 and make sure the model is set to the base case on the IS Cash Flow page.
2. Change the pricing for large and small cups of coffee from $4 and $3.50, respectively, to $0.

3. Think about what you would expect to happen in the model.

   Revenue should be zero, right?

4. Go back to the IS Cash Flow page and check that that is indeed what has happened.

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.

When checking the logic and formula calculations with a sensitivity analysis, be sure to think through carefully what you expect to happen before looking at the effect your change has had on the outputs of your model. Beware of cognitive bias, where you only notice things that confirm what you already think.

See Chapter 8 for more on sensitivity analysis in financial modeling.

Conducting a Scenario Analysis

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:

• Best case: Set all assumptions to the highest possible value you can conceive as being achievable (even in your wildest dreams).

• 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.

• Worst case: Set all assumptions to the lowest imaginable value that you think might happen. If everything that could possibly go wrong does go wrong, what does our model look like?

Additionally, financial models often include other scenarios to take into account possible fluctuations in inputs due to events, such as the following:

• Legislation: If changes in government legislation will have an effect on the price you can charge for your product, material supply, or additional costs such as labor, then change the inputs in your model to reflect this.
• Foreign exchange: If fluctuation in currency exchange rates will affect pricing or costings, change the inputs affected by foreign exchange in this scenario.
• Competitors: If the introduction of a new competitor to your market would cause margin squeeze (meaning that you’re no longer able to charge the same amount for your product), you could include a scenario that shows a decrease in price.

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?

Compared to formula mistakes, logic errors can be more difficult to spot. Problems with logic may involve incorrect timing, inserting the wrong inputs and source data assumptions, or using pretax instead of post-tax inputs, for example. Sometimes the mistakes can be a combination of both formula and logic errors, and scenario analysis is a good way of identifying if these sorts of mistakes exist and flushing them out.

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.

Taking Note of Excel Error Values

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.

If you’re sure that the error doesn’t need to be fixed, you can always suppress the Excel errors and stop them from showing by wrapping an IFERROR function around the formula. For example, if you have the formula =(B1-A1)/A1, you can suppress a potential error by adding an IFERROR around it like this: =IFERROR((B1-A1)/A1,0). If you do this, make sure that it doesn’t suppress an error value that you need to know about and correct.

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.

If you see ###### (known as railroad tracks) in a cell, you may think there’s an error. Good news! This isn’t really an error at all. It just means the result is too long to fit in the cell. Just make the column wider, and — voilà! — the railroad tracks disappear.

Including Error Checks

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.

Note that error checks are not a substitute for good practices such as checking and auditing your formulas. Error checks are most appropriate for capturing errors a subsequent user has made. They’re less likely to highlight a model-building error.