What are your answers to these questions?
- I use a lot of IF functions in my models.
- I am a heavy user of SUMIF and LOOKUPS.
- I am good at writing long complicated formulae.
- I sometimes use hard-wired assumptions in my formulae.
- Sometimes I need to change a formula across a time-based row.
- I agree that it is not possible to write a complex financial model without circular references.
- If I get a circular reference, I switch the calculation method to iteration.
- I am a modelling expert and therefore, if I get a circular reference, I write copy paste iterative macros.
- My models are complex black boxes and therefore running sensitivities takes time.
- I am a good multitasker and can switch rapidly from task to task.
- I rarely have time to think as my life is very busy.
- If I am unsure in my modelling, I turn to the internet for help.
- Modelling is easy to pick up on the job therefore training is pointless.
- Other people write models for me; therefore, I don’t need to be trained myself.
If you replied “yes” to any of these questions, then you are unnecessarily increasing the risk of introducing errors into your models. This is why:
1. I use a lot of IF functions in my models.
Using IF functions may not be one of the biggest modelling sins, but they do make formulae very long-winded and therefore can cause errors. Use more efficient alternatives.
2. I am a heavy user of SUMIF and LOOKUPS.
SUMIFs and certain LOOKUPS have their place, but please don’t overuse these functions. A well structured model will only use them occasionally.
3. I am good at writing long complicated formulae.
If you break formulae down and lay out the logical steps, they will be less prone to error.
4. I sometimes use hard-wired assumptions in my formulae.
That’s one of the cardinal sins of modelling and can cause big problems.
5. Sometimes I need to change a formula across a time-based row.
It should never be necessary to change the formula across a row. It reduces flexibility and increases the risk of errors.
6. I agree that it is not possible to write a complex financial model without circular references.
A modelling myth. Trust me, I have been building models for over 30 years and have not been defeated by a circular reference yet. (That is, if I ignore the ones I included in my early modelling career when I didn’t know any better.)
7. If I get a circular reference, I switch the calculation method to iteration.
Please don’t do this. If you do, you run the risk of introducing more circulars and illogical arguments without being warned. I don’t trust the results from any model that is switched to iteration.
8. I am a modelling expert and therefore, if I get a circular reference, I write copy paste iterative macros.
Another modelling myth is that copy paste iterative macros are needed to solve circular references. This is one of the causes of incomprehensible models.
9. My models are complex black boxes and therefore running sensitivities takes time.
If you can’t run instant sensitivities, this rather defeats the object of building a model in the first place.
10. I am a good multitasker and can switch rapidly from task to task.
Multitasking is very dangerous, especially in financial modelling which requires total concentration. A decent attention span is crucial to good modelling.
11. I rarely have time to think as my life is very busy.
We all need to give ourselves time to think. If you do, good modelling solutions will flow.
12. If I am unsure in my modelling, I turn to the internet for help.
As we all know, we shouldn’t believe everything we read on the internet. That extends to modelling advice too.
13. Modelling is easy to pick up on the job, therefore training is pointless.
As philosopher Karl Popper put it: “True ignorance is not the absence of knowledge, but the refusal to acquire it”.
14. Other people write models for me; therefore, I don’t need to be trained myself.
Financial models are dangerous tools in the wrong hands; modellers therefore need expert support and guidance from their bosses.