Top 5 traps to AVOID when financial modelling!

These are my top 5 things to avoid when developing or commissioning financial models...

1 - Balancing figures

This is a cardinal sin amongst professional model builders - the use of a balancing or "plug" figure on a balance sheet to make it balance.

A balance sheet should balance because all the double entry bookkeeping has been wired up correctly, not because it's forced to by a balancing figure.

The use of balancing figures can disguise problems elsewhere and should be avoided as there are more rigorous approaches available.

2 - Duplicate inputs

This can be a risk particularly for things which the model builder "doesn't think will change" like VAT rates, Corporation Tax rates, or even things like bank interest rates.

If a model has duplicate inputs, then a user might only update in one place, leading to wrong results.

So don't have the same input in two places, link all the calculations to a single input and avoid the risk that one copy is updated, and the other isn't.

3 - Embedded assumptions

It can be tempting to embed assumptions within formulae, e.g. calculate 5% growth using "x1.05" within a formula. However, this means that not only can the assumption not be easily updated, but it is also at risk of not even being seen!

Inputs should live in their own separate cells and be colour coded and labelled as this makes them much easier to review and update.

4 - Redundant inputs

Another common inputs problem is when a model is updated and no longer needs one or more of the inputs - leading them to becoming redundant.

This means that the model will contain 'clutter' which doesn't need to be there, as well as there being a risk that a user updates the now redundant input and of course won't get the effect on outputs they expect, because it's not connected!

If you need to remove calculations, then take the corresponding inputs away too.

5 - Calculations in macros

I've saved the worst for last! Please don't make VBA do any calculations! Macros can be perceived as high risk within models as they have the opportunity to do things which can't easily be understood - and calculations is a dangerous example of that.

Calculations in macros can be almost impossible to identify

As the result will usually be a hardcoded value placed on a sheet. If they can't be identified, they can't be reviewed, and this increases the chances of an undetected error in the model.

Best practise would be to perform the calculations "on the grid" and then use a macro to simply "copy and paste", which means the audit trail on numbers is easier to follow.