What have you done to reduce spreadsheet errors?
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.
Industry insightsView more
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.
AccountingWEB’s Head of Insight has been with the site since 1999 and likes to spend his time studying accountants’ technology habits. When not nerding out, you can find him exploring obscure indie music and searching for the perfect organic sourdough loaf from his base in Brighton, UK.