Business Surgery: Chris Kelly, of Bury St Edmunds accountants Jacobs Allen, warns against putting all your trust in spreadsheets

These days spreadsheets are used by almost everyone to collate and manipulate data.

However, the headlines about monumental errors and catastrophic decisions arising from spreadsheet errors keep coming.

The most serious recent error occurred during the pandemic which resulted in many thousands of Covid cases being under-reported by Public Health and lives put at risk.

Two famous historical cases were the multimillion-dollar losses at Barclays Capital and TransAlta caused by simple spreadsheet mistakes.

This isn’t really surprising as studies have shown that almost 90% of spreadsheets contain errors. This isn’t due to inherent problems with the software but rather because each file is created by a person who can make mistakes.

We use specialist software to create complex financial models rather than rely on spreadsheets. These contain in-built error checking facilities. They also avoid the potential ‘single point of failure’ issue caused when a spreadsheet model is changed by someone other than the originator and its integrity is then compromised.

Technical error-checking procedures can be applied to spreadsheets to identify errors. However, the application of good old fashioned common sense can quickly highlight fundamental errors. Applying common sense most definitely isn’t the preserve of those with particular training in maths or accounting.

Artificial Intelligence and machine learning may be the future but stepping back and applying common sense currently still has a place.

If the results from a spreadsheet don’t accord with your expectations or if the relationships between key numbers don’t make sense, the chances are that something is wrong with the spreadsheet rather than there is a lack of understanding on your part.

Understanding your numbers and the key relationships between them is essential for any organisation, whichever way the figures have been prepared.

Chris Kelly is a director at Jacobs Allen Chartered Accountants & Chartered Tax Advisers