(c) Chartered Accountants Australia and New Zealand. Contact Chartered Accountants Australia and New Zealand for permission to reproduce this article., Management Accounting

Embed error checks in the design

We all know we should check that our balance sheets actually balance. This technique cuts down the workload by shouting out when there’s a figure we need to look into more closely.

IAN BENNETT FCA leads PwC Australia’s deals modelling team and has morethan 17 years’ experience as a professional financial modeller.

In the past few editions of Acuity, I have covered a number of topics relating to best practice in creating spreadsheets and financial models. Hopefully these articles have started to build a picture of how to approach building a great model, the benefits of new modelling technologies, what good habits to form, and what high-risk things to avoid.

It has been a pleasant surprise to find so many readers filling the inbox with financial modelling related questions too pressing to ignore. In response, I have introduced a new question-and-answer format. I look forward to receiving more of your questions and the opportunity to administer vital spreadsheet first aid.

Q. What is ‘citizen tech’ and how does it relate to financial modelling?

I’m guessing you didn’t hear this term from your IT department. ‘Citizen tech’ is the new trendy phrase for ‘shadow IT’, which was coined by IT departments as a scary new term for UDAs (User Developed Applications) or EUC (End User Computing).

Ten years ago this meant Excel spreadsheets and Access databases (remember those days?). Now, it means any technology that is created and owned by the business and not the IT department, for example Excel, VBA, Power Query, Power Pivot, Power BI, Google Sheets, Google Data Studio, Tableau, R, SQL, Anaplan, Adaptive Insights, Jedox.

This used to be a thin layer of Excel analytics wrapped around your IT systems, but now Excel has company, and the potential of all these tools means the business (citizens) are taking over the technology.

In 2017, Gartner research predicted that 38% of technology purchases in that year would be managed, defined and controlled by business leaders. IDC (International Data Corporation) predicts that by 2020, in the majority of the industries it surveys, line-of-business technology spend by non-IT sections of organisations will be greater than that of IT departments.

With tech-enabled analysts arriving in finance functions every day, the use of powerful citizen tech to quickly solve problems is inevitable and should be wholeheartedly embraced.

These people with their new technologies will open your eyes to new potential. They have an agile mindset and they’re prepared to experiment with multiple technologies to find the best approach. Without realising it, they create platforms and tools that sit within the platforms.

Embrace it yourself, because you’d be surprised how much your own thinking is constrained by the limitations of Excel. If you can free yourself from questions such as: “is that more data than Excel can handle?”, “will Excel calculate too slowly?”, “will I have inputs multiple times?”, then you create better solutions.

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: Master error spread sheet

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

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

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.

This article was originally published in the December 2019 issue of Acuity.