UMG0001

Written evidence submitted by Kenneth Whitelaw-Jones

Gridlines is a UK based specialist financial modelling consultancy.

Executive summary

Financial modelling is central to decision making in government, and in business. The risk of error is extremely high in financial modelling. Financial modelling errors can lead to significant financial losses. There are lessons that have been learned in the private sector that could be useful to the Government as it seeks to take a more robust approach to financial modelling. These include

1.      Systematic independent model review,

2.      The use of financial modelling standards,

3.      Making financial modelling training resources available more widely.

Financial modelling is central to decision making in government and in business.

Financial models are used to estimate costs, make investment decisions, forecast future performance and support critical decision making.

The recent National Audit Office Financial Modelling In Government Report highlighted the critical role that financial modelling plays across government.

A 2015 YouGov survey[1] showed that this is true in the private sector also. According to the survey:

●        78% of British businesses use spreadsheet models to support key financial decisions.

●        Financial models are used to analyse £38bn of private sector investment annually in the UK.

The risk of error is extremely high in financial modelling.

Multiple studies have shown that the error rates in financial modelling is consistent with the error rates in other technical fields. When humans do simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities, such as writing software, the error rate rises to about 5%. Financial modelling error rates are consistent with those in software writing.That means one in 20 operations in constructing a model will contain an error.[2]

This leads to more than 90% of spreadsheets containing mistakes[3].

Given the size of projects, investments and decisions underpinned by models, the impact of these mistakes is potentially enormous.

One of the largest ever systematic studies of a collection of spreadsheets was undertaken by Dr Felienne Hermans.[4] As part of the investigation into Enron, a database of some 600,000 emails was released into the public domain. Those emails contained 15,770 spreadsheets. More than 10% of the emails in the database were about spreadsheets. Dr Hermans’ research found that:

●        24% of those spreadsheets contained errors.

●        There were 755 files (5%)  with more than 100 errors.

●        The maximum number of errors in one file was 83,273.

●        59% of the formulas with errors had one or more dependent cells. On average each erroneous cell had 9.6 other formulas depending on it.

●        Of the 68,979 Enron emails discussing spreadsheets, 6% contained words relating to errors.

If this is a reasonably representative sample of models, and emails about models, it’s safe to assume the error rates are high across the economy.

Financial Modelling errors can lead to significant financial losses.

According to the 2015 YouGov survey,

●        17% of large British businesses report having suffered financial loss due to poor spreadsheets

●        57% of large British businesses report that poor spreadsheets have caused significant wasted time.

●        33% of large British businesses report poor decision making due to spreadsheet problems.

These issues around errors in spreadsheet models are common across government and private sector; these are not problems that the private sector has “solved”.

However, they are problems that the private sector have been wrestling with for a number of years. There are lessons learned from the private sector that could be useful to the Government as it seeks to take a more robust approach to financial modelling.

There are three components to reducing the risk of error in financial modelling:

1   Systematic review of models

It is commonplace for financial models that underpin significant private sector transactions to be audited by an independent third party.

Over the past 5 years, in the infrastructure sector alone, 1,068 such third-party audits were undertaken on projects with a combined project value of $610bn.[5]

The UK leads the world in this market. Many of the leading financial model audit teams are based in the UK in firms such as Mazars, BDO, Operis and ourselves.

A third-party audit is undertaken through a combination of software tools to check for structural errors, "shadow modelling" the transaction to highlight commercial or assumption errors, and cell by cell review to uncover formula logic errors.

The National Audit Office's "Framework to review models" good practice guidance provides an excellent reference document that can be used across government to assess the quality of models. We welcome its publication earlier this year and commend Ruth Kelly and her team at the NAO for this work.

Independent review, such as the kind of comprehensive review outlined in the NAO document, whether conducted internally or by a third party, is a crucial component in detecting and correcting errors.

However, there are steps that can be taken to reduce the incidence of errors in the first place.

We are calling on Government to ensure that these measures are undertaken in combination with the excellent work of the NAO.

2   Use of financial modelling standards

Financial modelling standards make it less likely that errors will go unnoticed by the modeller or by internal review.

There are two broad categories of error in building financial models.

Commercial / conceptual errors. These kinds of errors arise when people do not fully understand what they are trying to model. They have perhaps modelled their understanding correctly, but it does not reflect the reality of the business.

Formula / calculation errors. Individuals may have understood how the business works and how it should be modelled, but make formula or structural errors. There are many varieties of these kinds of mistakes.

While standards do not change the error rate we noted earlier (around one in 20 operations), they make it less likely that errors will go unnoticed.

They do this by reducing variability in the modelling approach. If the model is built to a common standard, the modeller knows what to expect from the structure. The easier a model is to review, the more likely errors will be detected.

A focus on making models as simple as possible is common to all standards.

Financial modelling standards increase individual & team productivity, reducing modelling cost.

When we do things the same way, we get better at them. If a financial model is highly structured, additional tools can be introduced to do repetitive tasks and therefore cut down model build time. Code can be easily reused. Model build mechanics can more easily become part of "muscle memory".

Having everybody on a team develop models in the same way makes collaboration easier and safer. One part of the model can be built by one person and continued by another. It's easier for modellers to review and share each's other work.

Financial modelling standards are used in 2/3rds of professional services firms.

In the private sector, the most professional & experienced modelling teams tend to be found in the professional services firms; specialist boutiques such as Gridlines as well as large accounting firms.

In a recent survey of the financial modelling profession6, 95% of modellers responded that standards are important. Overall, 41% of modellers work in teams where standards are used systematically, with this figure rising to 64% in professional services firms.

There are a number of standards. Many of them make similar recommendations.

There are several modelling standards. Overall, they are similar in more ways than they are different. They all emphasise similar elements of good practice; the separation of inputs, calculation and outputs; consistency of structure and layout; clarity of labelling etc.

What’s important here is to pick a standard and then implement it consistently.

●        The ICAEW Financial Modelling Code has been compiled by a broad group of practitioners and so has wide applicability to different user requirements.

●        The FAST Standard is "open-source" and maintained by a not-for-profit company called the FAST Standards Organisation.

6 2021 Global Financial Modelling Profession Survey, published by Full Stack Modeller. 2021 www.fullstackmodeller.com

FAST stands for:

Flexible: To be effective, the structure and style of models require flexibility for both immediate usage and the long term.

Appropriate: Models must reflect key business assumptions directly and faithfully without being cluttered in unnecessary detail.

Structured: Rigorous consistency in layout and organisation is essential in retaining the model's logical integrity over time, particularly as a model's author may change.

Transparent: Effective models are founded upon simple, clear formulas that can be understood by other modellers and non-modellers alike.

●              PWC Global Financial Modeling Guidelines. Another useful free guide to best-practice financial modelling.

There have already been positive moves towards standardisation within Government departments.

OFWAT uses the FAST Standard for the development of its Price Review models. Since this is a model that has to be shared with the wider water industry, adopting a standard has benefited the process. Lessons from OFWATs experience should be shared with other Government departments and entities.

3   Making training resources available more widely

Microsoft Excel appears on every computer as part of the Office suite, and is easy to use for most people. It is tempting therefore to think that spreadsheet training is unnecessary. And indeed many companies fall into this trap. According to the 2015 YouGov survey, 59% of companies had spent nothing on spreadsheet training the previous 12 months. In one-third of businesses, none of the financial decision-makers had ever received any spreadsheet training

However, most people would understand that just knowing how to use some of the features of Microsoft Word does not make somebody a great writer. Similarly, knowing Excel does not make a person a good financial modeller. These are different skill sets.

Financial modelling training does not have to be expensive. Indeed there are many free resources available. We have included a list of useful resources in Appendix 1 that may be useful for government departments looking to improve their financial modelling competence.

APPENDIX 1: USEFUL FINANCIAL MODELLING

RESOURCES

March 2022