We're a Baker Tilly network member
Learn more
Identifying and managing model risk for stronger decision making
Article

Identifying and managing model risk for stronger decision making

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.

This content is general commentary only and does not constitute advice. Before making any decision or taking any action in relation to the content, you should consult your professional advisor. To the maximum extent permitted by law, neither Pitcher Partners or its affiliated entities, nor any of our employees will be liable for any loss, damage, liability or claim whatsoever suffered or incurred arising directly or indirectly out of the use or reliance on the material contained in this content. Pitcher Partners is an association of independent firms. Pitcher Partners is a member of the global network of Baker Tilly International Limited, the members of which are separate and independent legal entities. Liability limited by a scheme approved under professional standards legislation.

Our experts

Kieran Wallis

Kieran Wallis

Partner Brisbane
Rob McKie

Rob McKie

Partner Melbourne
Geoff Thompson

Geoff Thompson

Partner Newcastle and Hunter
Alan Max

Alan Max

Partner Sydney
Pitcher Partners insights Get the latest Pitcher Partners updates direct to your inbox

Thank you for you interest

How can we help you?

Business or personal advice
General information
Career information
Media enquiries
Contact expert
Become a member
Specialist query
Please provide as much detail to ensure appropriate allocation of your query
Please highlight a realistic time frame that will enable us to provide advice within a suitable and timely manner. Please note given conflicting demands with our senior personnel, we will endeavour to respond to you within the nominated time frame. If you require an urgent response, please contact us via [email protected] or 03 8610 5477.
CPN Enquiry
Business Radar report
Student careers 2021-22
Find an expert
Search by industry