Save content
Have you found this content useful? Use the button above to save it to your profile.
Hard times in the workplace
iStock_Stressed_PeopleImages

What have you done to reduce spreadsheet errors?

by
11th Apr 2019
Save content
Have you found this content useful? Use the button above to save it to your profile.

Ever since spreadsheets were introduced, people have made mistakes as well as calculations with them. The problems are unlikely to go away as analysis systems get more complex, so the only feasible antidote is eternal vigilance and effective training.

Way back in 2006, Revenue audit consultant Ray Butler reported that VAT inspectors found material errors in 70-80% of the high-risk spreadsheets they tested. Reducing those errors is one of the main planks behind the Making Tax Digital project, which HMRC officials claim will reduce the volume of errors from small businesses that contribute to the multi-billion pound tax gap.

Back in the real world, it has been hard not to notice some of the recent outbreaks of spreadsheet ineptitude, including:

  • Canopy Growth – the Canadian weed grower’s $100m misstated loss arising from “a formula error in the spreadsheet supporting the year to date adjusted EBITDA loss calculation”
  • Convivality’s 5.2m spreadsheet arithmetic error that culminated in administration and acquisition by C&C last year.
  • And in 2016, Marks & Spencer was hit by a double-counting error in a spreadsheet used to compile its quarterly statement.

We could go on and on with this catalogue of spreadsheet shame, right back to the cautionary tale of a £30,000 hole in school accounts one AccountingWEB member discovered in 2001 as a result of right-aligned text figures that were omitted from a Budget spreadsheet calculation.

The cost of some of these errors can run into the millions, even billions, and one that is acknowledged to be one of the all-time kings was recently highlighted in an Industry Update from accountingcpd.net.

A series of programming and data errors in the ‘Growth in a Time of Debt’ report by economists Carmen Reinhart and Kenneth Rogoff undermined growth predictions based on levels of government indebtedness – but by the time they were discovered many governments, including the UK, had built the assumptions into their austerity policies.

Spreadsheet errors are such a hardy perennial that they have their own internet archive: the EuSpRIG spreadsheet horror story page. Such mistakes are addressed over and over by AccountingWEB’s ExcelZone, training organisations like accountingcpd.net and accountancy bodies.

20 principles for good practice

With input from AccountingWEB contributors Simon Hurst, Glenn Feechan, the ICAEW condensed many of the problem areas into a handy guide. The full Twenty principles for good spreadsheet practice paper can be downloaded from the IT Faculty website.

The 20 principles go into detail about different phases of spreadsheet development and use, but at the heart of the whole document is the advice: “Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.”

Simon Hurst has previously acknowledged problems with the presumption of competence on AccountingWEB: you need to know enough about the subject to judge your own competence. For anyone designing or maintaining a financial spreadsheet, this could extend to adequate knowledge of Excel functions, the need for checksums and other validation tests and Hurst’s favourite test, whether the formula writer understands the difference between relative and absolute cell references.

One of the biggest problems with Excel is the lack of consistency in spreadsheets prepared by people within an organisation, due to a lack of formal guidelines and controls, as accountingcpd.net pointed out in one of its topical “bites” on spreadsheet risks. Workbooks are often duplicated and shared within the organisation, where regular copying and cutting and pasting can undermine the internal logic and disrupt important formula links.

This point matches some of the key principles in the ICAEW list, which includes putting in place common standards for layout and formatting, and collaborative reviews to ensure preparation and calculation requirements are met.

Moving goal posts

But technology never stands still. In the past year, AccountingWEB has tracked an increase in traffic to our PowerBI tutorials, which rely on database-like extensions and analytic tools that evolved from Excel’s Power Query feature – now PowerBI is an analysis and visualisation suite in its own right that is filling a lot of the reporting and analytic gaps in finance departments and accountancy firms.

PowerBI and data management introduce new complexities and challenges for the unsuspecting user, and require even more diligence, as Hurst explained recently: Hurst predicts that in the near future some of the principles may be affected by the way Excel is changing to more of a hybrid database application.

“Having Get & Transform right in the heart of Excel may mean certain principles, particularly those involving storing and manipulating data, could change in the future as people stop using traditional cell-based functions and start using the hybrid database tools,” Hurst explained.

As an Excel trainer, Hurst’s dream is to see broader use of Excel competency tests to gauge professional knowledge, backed up by comprehensive training and exams. In the absence of strict syllabus and CPD requirements, however, the responsibility falls to individual accountants to ensure their own Excel competence.

You can do worse than immersing yourself in Hurst’s Excel tutorials on AccountingWEB or the courses available from excel@acpd.

Useful links

Tags:

Replies (2)

Please login or register to join the discussion.

avatar
By dgilmour51
11th Apr 2019 11:57

Interesting article - thanks.

I am surethat there are a host of training suppliers out there who would kill to have "Excel competency tests to gauge professional knowledge, backed up by comprehensive training and exams" as a potential revenue stream.
But there are all sorts of different 'styles' of usage of spreadsheets and many different 'attitudes' towards them.
Some folks just use them as basically a datastore, some for relatively mundane analysis, some for very sophisticated usages but using only 'in-cell formulae', others with extensive VBA to various degrees of sophistication.
There will be increasing usage as an intermediate to feed to HMRC - resulting from the imposition of MTD - and mostly this will be real simple stuff.
All of these require different separable competencies - and no system of examining competency is going to eliminate occurrences such as mentioned.

However, I do agree that the 'dangers' of Spreadsheets should be harped on about and people understand that like all tools, they are dangerous if misused and/or in underskiled hands.

Thanks (1)
avatar
By philrob
26th Apr 2019 15:54

For those interested in learning more, a 'big brother' of the 20 principles is the FAST standard http://www.fast-standard.org/wp-content/uploads/2016/06/FAST-Standard-02... - this still has gaps in it, so not sure if it is actively being developed. But is worth a read

ICAEW also has a wider code for financial modelling https://www.icaew.com/-/media/corporate/files/technical/information-tech...

The most useful tip that I have applied in my own models (included in the principles) is clear colour coding/styles:
- Blue text = User Inputs/hard coded data
- Black Text = calculations and references to the same sheet
- Green = calculations and references to other sheets in the same workbook
- Red = references to separate files or external links.

Creating styles (exp. for the blue, which includes unprotecting the cell) makes 'marking up' the workbook to show source and usage relatively painless. Having black as the default colour minimises the frequency of applying a special style.

Thanks (0)