Financial models can help to guide very large business decisions. Confidence in financial model outputs is therefore critical in ensuring these decisions are based on strong data.
As part of my work leading a team that builds and reviews financial models, we talk to businesses often about the risk of error in financial modelling. Throughout these conversations and ten years working with businesses on their financial models, we’ve found that many companies underestimate the risk of error in their financial models. Many studies have found that a large majority of financial models have errors. Scary examples of how organisations have made large errors are easy to come by and are not limited to financial errors. An Excel error in October last year, for example, caused the UK Government to misplace nearly 16,000 COVID-19 test results.
In this article, we share how to identify and mitigate model risk to ensure your outputs help your organisation make strong commercial decisions
Admitting you (may) have a problem is the first step
Ray Panko, a computer science professor and expert in human error that financial modellers can be quite sensitive about errors in their models. His observation is that spreadsheet modelling has close ties to software programming, but that programmers and modellers have different attitudes to errors. Programmers will admit to an error rate in their underlying work (say 5 per cent) and are accustomed to peer review processes. However, suggesting to a modeller that their spreadsheet may contain an error is more akin to a personal insult. Financial modellers need to know their limits and embrace good peer review processes.
Think about the likelihood of model risk
In our team, we talk to junior team members about the likelihood of making errors. While we expect to work at a high standard, the message is that if you build a decent size model, it’s difficult not to make a few mistakes. A model with around 2,000 unique formulae, with an average of three operators (variables) per formula, provides at least 6,000 opportunities to make a mistake. Even if the formulas are simple, that is a lot of opportunity for error.
The amount of opportunities for error is why we tell clients that, when completing a review of their models, that it’s more likely than not that we will find an error. In financial modelling, it’s not the error itself that is of most importance, but whether the errors make a material difference to the output of the model or the decision it is informing.
Once you accept the inherent risk of error in spreadsheets, you are in a much better place to start thinking about the processes required to mitigate model risk. Methods to reduce model risk are numerous and include training, following best practice methodologies, using error checks, and performing reconciliations. However, our experience has continually reinforced the importance and value of a structured peer review process, as we undertake with all model builds.
Have a thorough peer review process with no stone left unturned
Our peer review process follows two main streams: a detailed review of each unique formula within the model and a commercial review. The unique formula review utilises spreadsheet review software to identify each unique formula. We then work through each formula to assess that it is calculating values correctly. It’s a no stones unturned approach to reviewing models. It can be a lengthy process for large models, but if there is an error in the spreadsheet, we expect to find it. This is also the process we undertake when clients ask us to review their models.
The commercial review can be considered more of a top-down review and will typically focus on assessing whether the outputs of the financial model make sense. This can be assessed using several methods and will typically include:
- review and reconciliation of the financial statements
- comparison to prior year results (if available)
- comparison of key metrics (financial and non-financial) to prior year results or industry-based benchmarks
- independent recalculation of key items
- review of outputs in the context of the stated assumptions.
The detailed unique formula review and the commercial review complement one another. We expect that they will identify many of the same issues and sometimes identify issues that the other may not.
Proactively identify model risk
Not all models are equal, and some models are riskier than others. It’s important to assess the risk of individual models and scale your review procedures to match. This can be thought of in terms of both likelihood of error and its subsequent impact. To proactively identify model risk, consider the following to be indicators of increased risk:
- modelling projects that have limited ability to compare to prior year results or benchmarks (e.g. new projects)
- models that inform investment decisions (acquisitions, tenders, or new business ventures)
- models that are used to calculate payments to employees
- models required for projects with a higher degree of urgency and therefore time pressure
- poorly structured models
- projects that are significantly different from the organisation’s existing operations, whether it be scale or scope
- models that are only understood in detail by one or two people within the organisation.
Complete a quick assessment at the very least
For clients looking to understand the integrity of their model without undertaking a formula-by-formula review, you could opt to complete a model scan. While not as thorough as the complete peer review process mentioned above, the model scan will consider the key aspects of your model being:
- structure and best practice
- transparency
- complexity
- indication of the potential for error
It provides a rating for each of the above aspects, plus a detailed list of potential issues for further investigation, such as hardcoding, inconsistent formula and overly complex calculations. This can be a good starting point to begin strengthening the integrity of your models.
Moving forward: Accept the probability of error and prepare for it
Accepting the high probability of error in a financial model is a productive place to start, not only because it’s pragmatic, but it can also make your team more open to identifying and mitigating risks and errors. To start, identify those models that are most likely to have errors and in which models the result of an error would be most significant. Further, a thorough peer review process will set up a strong framework to continuously improve your models and reduce the likelihood of error, or at the very least, identify errors quickly.
As outlined earlier in this article, accepting that errors will occur in the modelling process and understanding how to mitigate these is a key mindset shift financial modelling teams and key decision-making stakeholders in a business need. By establishing a systemised process for proactively identifying and mitigating the risk of error in your financial models, you put your team and business in the best position possible to make strong commercial decisions.
If you would like to discuss financial model risk or how to establish a thorough model review process, please contact a specialist below.
