Abstract representations of real world problems have been used as a means to aid decision making since man first scratched the likeness of a bison onto a cave wall. These useful abstractions provide a “sandpit” if you like, in which a variety of scenarios can be safely tested.
MS-Excel is one of the most commonly used tools for developing financial models (FM) and there are currently some 1bn MS-Office Users worldwide but according to Ray Panko – Professor of IT Management at the University of Hawaii and an authority on bad spreadsheet practices – close to 90% of all spreadsheet documents contain errors.
“Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” Ray writes. He goes on to point out that, “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.” Similarly, recent Gartner research indicates that 30 – 90% of all financial models contain material error.
Given the nature and consequence of decisions that are made using such models, and the potentially catastrophic consequences of basing action on flawed conclusions these statistics should give rise to very real concern. It is only sensible to consider “what makes a great financial model?”
Use the guideline below to perform a short assessment of your own FM practices.
- Outputs talk to the needs of the user – Understand what these are by ensuring that requirements are gathered in a structured way.
- Inputs are of a high quality – Use data ranges if the inputs are likely to be materially variable.
- There are no logical flaws – Avoid faulty connection making by building error checks in deliberately, by documenting your methodology, and by ensuring logical structure and reasoning.
- Assumptions are reasonable and tested – It is worth stress testing assumptions so ask around and complete subjective research if necessary.
- The model is tested before being released into production – Even a simple peer review is better than nothing. Ideally you should test widely and don’t forget to focus in on the detail to limit the likelihood of those formulaic errors.
- The model is continuously tested so it does not degrade over time – If a model is used regularly, test it regularly; without testing it will degrade.
- Understand where the sensitivities reside – Focus your reasonability and error checks on these.
- Outputs are presented in a way that the user can consume – Users are unlikely to care about the model itself but rather the outputs so avoid too much focus on the models and pay attention to the presentation of outputs. BI and data visualisation are becoming very important – Usability rules!
- Pay attention to technicalities – Avoid hard-coding, use consistent formats, and triple check all formulae.