Avoiding Black Boxes #1: Structuring Formulae

Not surprisingly, financial models are frequently described as black boxes.

But why are decisions made on the results of black box models when many view this type of financial model as:

1. far too complicated to attempt to understand; and
2. correct?

Modelling should never be complicated and is, unfortunately, rarely correct.

My mission is to demystify financial models. Models should never be black boxes: they are simply made up of many layers, which themselves must always be clear and logical.

In my next few blogs I will discuss the key to accessible models, namely:

• Good formula structure
• Good function choice
• Good layout
• Good logic
• Good basic rules

When faced with a long-winded formula in a model it is time-consuming to work out its purpose. However, I believe that 99% of formulae in a model should be able to be instantly understood. Here are my top tips for formula structure:

1. Keep formulae very short: a long formula is not a sign of modelling skill.
2. The solution is often simpler than you think.
3. Never put a number in a formula (apart from obvious exceptions).
4. Formulae must always be consistent across a time-based row (using a narrow column to the left of the first period helps with this).
5. Use flags and extra rows if needed.
6. Question why any formula needs more than 2 sets of brackets (no 62 bracket nightmares please – see my February 2017 blog).
7. Remember the mathematical order of calculation (BIDMAS/BODMAS/PEMDAS). Simple mathematical errors are common in financial models.
8. Understand which functions are the most efficient for different situations.
9. If creating a formula is proving difficult, use a pen, paper and simple numbers to help work it out.
10. And lastly, don’t assume that your formula is so complex that it doesn’t have to stick to these guidelines.

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” (Antoine de Saint-Exupéry)

Please click here for more blogs