Our top 3 tips to improve your financial modelling

Our top 3 tips to improve your financial modelling

Author:

John Dimberline

Published:

27 Jul 2017

Comments:

0 Comments

Category:

It may seem a little odd that one of the key foundations of business over the last 30 years has been the humble spreadsheet. Yet it is hard to find a key financial decision that has not been based on calculations made in this ubiquitous software tool.

As our modelling horror stories e-book shows, nightmares lurk amongst the innocent rows and columns of figures for those that don’t take care with how they process their figures. Research has shown that 88% of spreadsheets contain errors of some sort and approximately 50% of financial models in use operationally in large businesses have material defects.

Many different organisations have been hit by spreadsheet problems in a variety of ways. The root causes of many are surprisingly simple errors. MI5 admitted that they had bugged 134 of the wrong phones because of a spreadsheet formatting problem. Oxford University candidates experienced interview upheaval when administrators muddled up spreadsheet numbers for registration and examination marks.

Unsurprisingly, spreadsheet problems are seen most in the world of finance where the values involved can be terrifying and the financial calculations can become extremely complicated.

An eagle-eyed reader of a US Federal Reserve statement spotted a hitherto unnoticed error in a spreadsheet worth up to 4 billion dollars. Who knows the value of the financial impact of the spreadsheet error found in the published research papers of leading Harvard economists, Carmen Reinhart and Kenneth Rogoff? Their flawed research has been claimed as a basis of several countries’ current economic austerity programmes.

The problems highlighted are not only embarrassing for those involved, they can be costly both in terms of money wasted and in jobs that are lost. At least one CEO has had to resign as a result of errors being discovered in figures that they had previously announced.

The stories which have attracted uncomfortable publicity are only the tip of the iceberg. There must be many more unpublicised spreadsheet skeletons lurking in the cupboards of many an accounting department.

What 3 things should managers responsible for building and operating spreadsheets do?

1. Adopt a standard for creating spreadsheets that can be followed by people in your organisation.

The FAST standard is the only internationally recognised standard for building spreadsheets. It is managed by an independent standards organisation, which is backed by F1F9 as well as international accounting firms, Grant Thornton and Mazars.

FAST defines a Flexible, Appropriate, Structured and Transparent approach for creating understandable spreadsheets and reducing errors. It still gives users the freedom they need to tailor their calculations to particular business needs but it also  imposes simple disciplines that mean that their work is understandable. Most importantly its adoption means that errors can be spotted.

2. Ensure that procedures are in place for managing spreadsheets and the IT environment where they are saved and shared.

Such procedures should include:

– Simple version control and file numbering processes which identify clear responsibility for who is in charge of spreadsheet updates. Many errors are caused because the wrong version of a spreadsheet is updated or viewed.
– Review of spreadsheet work ranging from sense-checks of model outputs to independent examination of the spreadsheet coding for more complicated applications.
– Routines for regular communication between those making and managing commercial decisions and those who are building spreadsheets to ensure that correct assumptions are used.
– Control and audit procedures to identify and track who can access and has updated spreadsheet files.

3. Pay attention to the human-side of spreadsheet control.

Make people aware of the risks of working with spreadsheets and provide training in how to build and manage them safely and efficiently. A culture of positive collaboration and improvement should be encouraged where good design and error elimination is promoted with clearly defined responsibilities.

Given that spreadsheets have been around for over 25 years, it seems surprising that businesses have not mastered ways to manage them yet. But the truth is that high profile errors keep appearing. Part of the problem seems to be a philosophical one. Because the nature of Excel use is everyday and accessible, many managers just don’t look on it as something that needs managed in the same controlled way as other computer systems. Some have already become enlightened. Others have not, to their cost.


Download the Dirty Dozen ebook




John Dimberline
John Dimberline