Error checks for Excel and Financial Modelling

27 February 2020 | Written by Maarten Bessems and Lance Rubin

Introduction to the co-author

After graduating in Mathematical Economics, Maarten worked for 10 years as a quantitative consultant in both the profit and non-profit sectors. Throughout his career he has built and audited hundreds of small and large Excel models for different purposes, which gave him a detailed insight into the possibilities and drawbacks of using Excel.

Maarten provided key decision makers in the banking, real estate and stock market industries with accurate planning and investment decision models built in Excel. These models provided the necessary support for successful multi-million euros transactions.

Because of Maarten’s extensive experience with financial models, he is often approached to consult to companies on this matter or to provide them with very specific training to highly qualified professionals.

Although his mother tongue is Dutch, his English, German and French is impeccable.

In 2011 Maarten decided to take his knowledge and experience to a new level by helping even more professionals leverage the best of Excel (flexibility) whilst removing the draw backs (errors). 

He started a company called Spreadsheetsoftware with a lot of ideas, enthusiasm and a vision to develop, amongst other things, the most advanced error detection software in Excel called ExcelAnalyzer.

ExcelAnalyzer comes as an extra button in Excel and is positioned to be the solution to resolve errors and risks so that Excel users can gain confidence in their spreadsheets again.

Why did Maarten select the topic and why is he passionate about it?

I am no stranger to Excel errors and in fact, like many finance professionals, spreadsheeters and financial modellers, I had to face the reality that humans will naturally create errors without even realising it.

Excel is a development platform for decision making and its flexibility is both its strength and its weakness.

Whilst humans are incredibly creative and we assist in multi-million-euro transactions, there are some easy tips and tricks that we can apply with relative ease to make us even more awesome.

I wanted to help people save time and make more accurate decisions by sharing these tips.

Topic and context in 3 sentences

Here are a few key facts to kick this off:

1. The reality is that anyone who has worked in a spreadsheet is not immune to the possibility of errors due to its flexibility which is both a blessing and a curse at the same time.
2. There are lots of statistics on this but a common one is that more than 90% of spreadsheets contain serious errors, while more than 90% of spreadsheet users are convinced that their models are error-free – ACCA article.
3. It’s not Excel that is the problem, but the way it is used.

If you had to teach this topic in a class to school kids what key tips would you give them to focus on?

Prevention is always better (cheaper) than the cure so be sure you follow best practice and keep your spreadsheets hygienically sound.

Kids generally don’t like to go to the doctor and especially not the dentist. So, stay away from too many sugary drinks, lollies and food that is generally bad for your health.

Errors in spreadsheets can be bad for your health too when a decision is being made based on an error-riddled spreadsheet.

The problem is most people don’t know that they have an error until it’s too late and very expensive to fix retrospectively.

Review recent articles in the series mentioned below to reduce the risk of errors by designing Flexible, Appropriate, well Structured and Transparent models.

Explore some of the tips in this article to reduce your risk of a bad tooth or other health issues from a poor diet in your Excel workbooks.

What practical steps can people take now to learn more?

Spreadsheeters use Excel like a messy teenager’s bedroom with stuff lying everywhere and no structure, whilst a financial modeller takes care in how they build their Excel models, and this includes error detection and trapping.

However, even professional financial modellers know that humans make errors and whilst it’s possible to reduce the error risk significantly it cannot be eliminated without the use of model audit software and another pair of eyes. Even then errors can sneak through after an audit.

Refer to the upcoming article on model audit by Ian Bennett for more on Model audits. This article is going to cover error detection and trapping tips prior to any audit.

To give this article justice it’s important to distinguish between error trapping or prevention and error detection and remediation.

Think of it like eating well and doing exercise that can help prevent you from getting sick without doing anything extra. Hygiene is a factor of life and you avoid problems down the track as the context for prevention and trapping errors in Excel.

Compared to error detection when you feel unwell you try to detect what is causing that issue and stick a thermometer in your mouth. Once you know it’s a temperature you self-medicate by taking a day off work, sleeping, drinking lots of water and perhaps taking a cold and flu tablet if you are a bit sniffly.

If you feel really sick the day after, then you go see a professional doctor (aka model auditor) for some professional help on your problem.

So why don’t we follow this same regime for our most favourite software Excel. For the purpose of this article we are referring to actual errors compared to alerts of potential errors which will be covered in the article relating to alerts.

An alert may not necessarily be an error, but it needs to be investigated as a possible business problem like breaching debt covenants or outside tolerance of certain KPIs.

Error trapping or prevention

●       Data Validation – this is one of the most under-utilised native functions in Excel that define what is acceptable in a particular cell. If anything doesn’t fit that rule then it throws out an error before it can cause damage ie only positive numbers or no text etc.

 The use of data validation is a great way to prevent your spreadsheet from getting sick, like the example below.

 You can even customise your error messages and input message alerts.

●       Error trapping via flags or custom-built error checks – most financial modellers would build a model which contain specific error flags within it. The reason for doing this is to highlight in an instant when the error has occurred.

 The flag can be custom built with an IFERROR or ISERROR formula if triggered.

 Use conditional formatting for extra effect as it highlights clearly in red that errors exist.

 Better yet, consider adding it to the title of the model for really broadcasting issues straight away, no matter where you are in the model you will see the heading change.

 Here is a simple example to trap any Excel error (not a logic error, example here is #DIV/0!) in any series of precedent formula cells:

One of the best custom-built error checks is whether the Balance Sheet is in balance or not?

If the Balance Sheet is out of balance, it's an Error, NO If, Buts of Maybe’s, its wrong!

This is one of the primary reasons why a 3-way model is more robust than simply a cash flow only model without the Balance Sheet check.

There are different ways to create this error check for the Balance Sheet or any other cross balancing error check, where you expect two numbers from different parts of the model to equal each other.

Another example is revenue by customer and product should equal each other. 

You should setup these error checks as you build the model and ensure that you gain more confidence in the overall accuracy of the calculations.

You would setup this error check where you expect the answer to equal zero when you subtract each number from the other.

Setup this error check formula using the following syntax:

=IF((C3-C5-C7)<>0, (C3-C5-C7),0)

Again, using conditional formatting can be useful to highlighting the errors as seen below:

Error detection

When you feel like there could be an error but not sure where it is then you should grab that thermometer to test whether there is a problem.

Thermometers in the Excel world comes in the form of error detection tools that you can apply to your own models. Some of these are in-built to Excel but are often ignored and sometimes provide too many false positives.

Those tools that do not come with native Excel have been used by model auditors for many years and are well known to professional financial modellers, however most of these are built as an add-in using VBA (which is one of the most inefficient coding languages for heavy Excel processes).

However, this has advanced in the form of more efficient coding languages like VB.Net and C#, which ExcelAnalyzer currently uses.

Once you use your Excel thermometer you might see some interesting indicators of your spreadsheet health like number of hardcoded formulas, wasted space, model flow and complexity, unique formula count, redundant names, Excel error count and highlighting possible logic errors for checking inconsistent formulas.

Breaking the rules mentioned in the prior article like one row, one formula can be seen easily and quickly.

It’s almost like getting a full blood test on your spreadsheet without having to pay a doctor to get it.

But sometimes you might have to visit your doctor if your test results are really bad, they would show up things like this.

Note: that Excel sometimes flags these issues, but many people simply ignore them as sometimes they generate false positives or become annoying so they just get ignored.

Below are some more examples of what these test results would look like in terms of formula issues and errors, including risk areas like external links.

Understanding the flow of formulas and numbers in a spreadsheet or model is also often very difficult without your friendly doctor taking a closer look at it.

Recently, Lance spoke at AccounTech Live 2019 regarding the Roses of Excel and removing those painful Thorns of Errors.

If you want to take a closer look and the slides and this Excel thermometer refer to the links below.

What are good places (links) to find out more on the topic?

Here is a list of links which are useful to those readers wanting to develop a healthy relationship with Excel and avoid those problematic areas of errors:

• AccountechLive 2019 – Mastering Errors in Excel - Keep the Roses, Avoid the Thorns

Other relevant articles in this series that can keep you nice and healthy:

• Financial Model Development– co-authored by David Thompson
• User Friendliness in Financial Modelling – co-authored by David Thompson
• Workbook Structure and Hyperlinks for Financial Models - co-authored with Jennifer Harrison
• Best Practice Formula Construction for Financial Modelling – co-authored with Jennifer Harrison

How important is this skill in the context of learning Financial Modelling?

The fact remains that all professional financial modellers and financial modelling firms recognise that as humans when we write formulas and code up any spreadsheet our creation of errors is inevitable.

In order to master the art of financial modelling we have to master the art of specifically focusing on preventing and trapping these errors and also design formulas and structure the workbook to greatly reduce this risk.

If you want to consider yourself as having robust financial modelling skills, then handling errors is a MUST have skill.

Not just post fact, but before, during and after building any model this must be front of mind.

The skill of error prevention and detection is vital, there is no other way of saying it.

How does all this disruption, AI and automation talk impact this topic?

Whilst there are certainly software packages that can do some really great heavy lifting of error detection and reporting like ExcelAnalyzer and other similar tools, there is still a need for a human to interpret what impact that error might have.

The balance sheet check error seen above will not be detectible unless you specifically force an error creation for an imbalance so that the software can pick up the cell specifically.

If the logic of how a business makes money is out of sync with reality, its only the human that will be able to see this through the outputs or other cross checking over time when the actual results do not reconcile with the forecast.

Our role in error detection needs to change and evolve, like all things when it comes to AI and automation, to being the interpreter of the error vs manually hunting them (unless we are required to do so as part of an audit, but more on that in the next article).

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website.