Intro to Financial Modelling part 12: Error reduction - Constructing error checks

All models have errors

While it is true that a model is never fully right (but is sometimes useful), there are degrees of not rightness and those degrees matter to the users of the model.  To borrow another cliché, there are known unknowns and unknown unknowns and any proud modeller should always aim to at least correct for the known unknowns.

Errors are the hidden icebergs of model building.  Consider how you use Excel: when you write a formula, how often do you get it right first time?  95%, 50%, or 25% are all reasonable answers! Now multiply that by 3,000 unique formulas that might be in a big financial model and it is easy to see why the rule of thumb is that 100% of models have errors.

How do you solve a problem like models?

What can one do to fight back?  The long-winded approach is to review, review, review.  A formal review takes the form of a model audit, which is often requested by banks who want to make sure that their new loan isn’t derailed by an errant minus sign buried in row 600 of the passenger numbers calculation sheet.  A model audit is very thorough and very expensive.

What else?  Get someone else to review.  This is always a good idea.  A separation of build and review helps ensure that the modeller doesn’t get lost in the detail and miss obvious errors.  And to be honest, the reviewer might just be less tired than the modeller.

While a great idea, it is not really feasible for a reviewer to look at every part of the model every time the model is changed.  We live in a world of little spare time, so we need another approach.

This is where error checks come in.  Instead of a manager spending hours poring over the model, a bunch of automated checks will quickly indicate when and where problems arise.  The Financial Modelling Code includes a major section about error reduction and error checks.  Let’s discuss details.

If, when, why, what: thinking about errors

When you should be thinking about errors?  The answer is almost all the time.  At every step of building a model you think to be thinking: what could go wrong here? And: how can this be checked automatically? 

Some of the riskiest places in models are interfaces, such as between two sections, sheets, timelines, or segments. Checks are often included to ensure that values transferred across are complete and accurate, such as the total fixed assets arranged by accounting class equals total fixed assets arranged by tax class.  Be mindful of the flow of logic and values through the model and check every step of the way.

Show and tell when creating error checks

When creating error checks we need two things to happen:

• The check needs to identify any error, but not show an error when there isn’t one
• The user needs to be clearly informed of an error and be able to trace the error quickly

Getting error checks right 

An error check should return two possible values:  say okay or ALERT!  We want to know when something is wrong, and we want to know now.   So first, we need the check to be dynamic – it needs to be an Excel formula, not a special feature like a macro or add-in.  And, crucially, it needs to work with the foibles of Excel.

If you need proof that Excel has foibles try this formula (including brackets):  =(0.5-0.4-0.1).  Exactly as typed this formula does not yield zero but something slightly different.  Excel is imprecise.  Your error checks should account for that.  That usually means using an error tolerance, or – in the example below – a rounding tolerance (number of decimal places that is good enough; in this case rounding to 2 decimal places).

Telling the user

To communicate the error quickly to the user a few points are critical:

• Conditional formatting (or coloured number formats) to highlight an error in a strong colour, such as red.  This colour should not be used for anything else in the model.
• Error check calculations should be listed out in one place (per model or per sheet) and a summary of checks created.  Often both a sheet check summary and a whole model check summary sits at the top of each sheet so that the user sees that something is wrong immediately.  They can then scan the list to find the specific check that is failing.

A good error summary might look something like this – note the red colour for errors.

The summaries at the top of the sheet might be like this. Note that this example shows two types of check:  integrity errors, but also “signals” - see below.

When an error isn’t an error

So far we have talked about model errors, problems with the model logic itself:  bad links, wayward formulas, misunderstood functions, missing data.  Examples include an unbalanced balance sheet or sources and uses not matching.

What about problems with the project being modelled, such as negative cash and repayments not made?  To the user this could be just as problematic as errors in the model, even though the logic is completely sound.  To this end, models often utilise the same error notification system to notify the user of project problems as they arise.  These are sometimes called “signals”, “warnings” or “alerts”.

What could possibly go wrong?

That is enough for today. Hopefully this has put you in the right state of mind to think about risk and checks when building model. The next blog in the series will help you apply checks in practice through examples of some of the best and most useful checks commonly found in real world financial models.  But my lesson is that errors can occur almost anywhere – so if in doubt just add a check…