Philosopher of science Karl Popper believed that science’s attitude to failure sparked the scientific revolution.
Matthew Syed’s book Black Box Thinking is influenced by Popper and provides a compelling argument for the benefits of admitting errors and learning from mistakes. He contrasts the airline industry (where, with the help of black boxes, every near miss and accident is logged and lessons are learned) to healthcare (where the blame culture ensures that some will never admit to making mistakes and therefore progress is hindered).
The book contains much of relevance to economic forecasting and financial modelling. Any model has a huge potential for errors, which can be broadly divided into two categories: random errors caused by incorrect assumptions and systematic errors caused by mistakes in calculation.
As a financial modelling trainer, part of my role is to increase modelling efficiency and reduce systematic errors. On my courses, participants realise how easy it is to make mistakes: working together to check, correct and improve the models is an integral part of the course and develops both modelling and error checking skills.
My recommendations for day-to-day modelling clarity and error detection are as follows:
- Make models comprehensible and keep to the basic rules
Too many models are over-complicated and therefore hard to understand and check. Keep formulae short, use appropriate functions and make sure the models are logical (i.e. without circular references or iterative macros).
See these blogs for more information:
On unnecessary complexity: http://camillaculley.co.uk/2017/02/
On top financial modelling tips: http://camillaculley.co.uk/2017/05/
- Perform basic checks
For example:
- Check that formulae across the rows are consistent (F5 ALT SW or Ctrl \)
- Check which cells contain assumptions (F5 ALT O XGE)
- Check assumptions for dependent cells (ALT MD)
- Check for references to empty cells (ALT FTF ALT U)
- Set up an error checking area
For example:
- Does the balance sheet balance?
- Are loans fully repaid?
- Is there an overdraft?
- Have default ratios been breached?
Conditional formatting and a check of checks are also useful here.
- Run a Stress Case
Most models focus on a Base Case, which can mask systematic errors.
Different scenarios should be set up, but the most useful scenario to run for checking purposes is a severe downside (Stress Case).
Analyse the Stress Case in detail. It will often pick up basic errors (for example, what happens when there are overdrafts).
- Set up Data Tables
Data Tables are a powerful tool and can instantly analyse results and checks for every case. They are underused, mainly because they cannot be used effectively in models with iterative (copy paste) macros.
It is my mission in life to convince people that models never need circular references or iterative macros. All they need to be is logical.
- Question all results for all cases
Data Tables give instant information for every case. Therefore, if any of the results or checks for any of the cases do not make sense it should be immediately obvious. Find out why.
- Use a process of elimination
To try and identify a cause of error, sometimes a process of elimination helps. Therefore, remove independent variables one by one to check their effect. For example, set the tax rate to zero.
- Keep a clear brain
Take breaks. Unsurprisingly Balance Sheets are always easiest to balance first thing in the morning.
- Model Review
In addition to these day-to-day methods, models need to be reviewed regularly by somebody else with a comprehensive check list. Rather than being a tick box exercise, reviews should also focus on improvements to the model.
One of the themes in Black Box Thinking is “cognitive dissonance”, or our tendency to manipulate evidence which questions our fundamental beliefs. This is a deeply ingrained human trait and why we sometimes find it so hard to change our habits and methods. Black Box Thinking offers much food for thought for financial modellers and I recommend it as a good read. Matthew Syed has also recently presented a series of programmes on the subject which are worth listening to: