How do you build the perfect error check into a financial model?
Embedding error checks in your spreadsheet design cuts down the workload by shouting out when there’s a figure you need to look into more closely.
In Brief
- Error checks should be prolific in a financial model, as they can be used for both checks and navigation.
- Use a formula that returns either TRUE or FALSE, for example =ABS(x - y) < ErrTol, where x and y are the values that you need to check are the same.
- Add conditional formatting, green for TRUE and red for FALSE, for an instant visual flag.
- Then use a master error check sheet as a navigation tool by hyperlinking to each error check.
Q. How do you build the perfect error check into a financial model?
If you were to build a new rail line, you wouldn’t build all the rails and set trains running before putting all the high-tech safety equipment in place. Your safety systems are installed first and they’re watching every metre of the network. They alert you the second an issue arises. They tell you what’s wrong and they pinpoint the location.
Financial model error checks are the nervous system running the length and breadth of your model, carrying messages of comfort. Crucially, those pathways can also be used for maps and navigation, embedding them at the heart of how you use your model.
We all know we should check if the balance sheet balances, but error checks should be prolific. My rule is: if you can calculate a number in more than one way in the model, put in a check.
Figure 1: Sample work sheet (Click image to enlarge)
1. Insert error checks just as soon as you have typed the labels. Your process should be Labels > Formatting for subtotals etc > Error check formula and formatting > Formula for subtotals etc.
2. You also need the perfect formula: =ABS(x - y) < ErrTol, where x and y are the values, you need to check they are the same (see point 2 in figure 1), and ErrTol is a named range of 0.0001 (to avoid floating point errors). This returns either TRUE or FALSE (or an error such as #REF).
3. At this point, add conditional formatting:
TRUE = green fill and font
FALSE = red fill and font
Add both so that #REF etc show as white and are easy to spot.
4. Then set:
Row Height = 3
You don’t need to see the words TRUE or FALSE, just the colour (see point 3 in figure 1).
“If you were to build a new rail line, you wouldn’t build all the rails and set trains running before putting all the high-tech safety equipment in place.”
5. Once they are correctly set for every time period, then add a row check in one of the label columns (such as column I if you start your timeline in column K), so you can see if there’s an error in far forecast periods that can’t easily be seen on the screen. All these row checks will then be summarised in a worksheet check (see point 1 on figure 1).
Figure 2: Master error check sheet (Click image to enlarge)
6. On a navigation worksheet (aka a master error check sheet), then set:
Column Width = 2
This means you can see all time periods on one screen, and bring every error check into the model.
7. Add a hyperlink to each one to take you to the actual error check (see point 3 on figure 2).
8. Then summarise them all into a master error check (see point 1 in figure 2), which you then display on all other worksheets in the model, and add a hyperlink so it takes you back to the navigation worksheet. Do this and the error checks aren’t only something you check for errors, but are embedded into the model and are the best way to navigate around.
Note, however, that error checks and sense checks are very different. Sense checks, such as “is my gross margin too high?” or “did I breach my covenants” should not be brought into the master check as they will complicate things and may obscure a real error. They can be added as a separate section to the error check sheet.
Your questions answered
Have a financial modelling or spreadsheet question you’d like answered in Acuity?
Send it to [email protected] with ‘Ask Ian’ in the subject line.
Ian Bennett FCA leads PwC Australia’s deals modelling team and has more than 17 years’ experience as a professional financial modeller.
