Blogs

Spreadsheet horror stories 3

Finance | John Tate | 31 Oct 2011

Our old friend – the spreadsheet – must be handled with care, warns John Tate. 

Last month it was reported that the UK support-services group Mouchel had discovered an accounting error that led to a £8.6m downgrade of its profits. An independent firm of actuaries found that they had made a spreadsheet error, which meant the Mouchel pension-fund deficit had been wrongly valued. Around half of the profit write-down was due to this mistake.

This resulted in Mouchel’s share price dropping by a third, and its chief executive resigning. In case you have not come across Mouchel before, it manages highways and local authority backoffices, including processing over one million payslips for schools, universities and other public bodies.

Risk of error

What a wake-up call. After email, spreadsheets are probably the most common software tool used by accountants. If a well-respected firm of actuaries can make this mistake we are all at risk of spreadsheet errors.

In case you think you are safe from these problems, I’d refer you to a survey at a recent ICAEW faculty event. This showed that nearly a quarter of accountants have experienced financial or reputational damage due to spreadsheet errors and poor controls. More information at here. 

The accepted practice suggests you should use your core accounting software to produce key management information – relying on the rigours of an off-the-shelf application to generate the correct results. However, charities can find this a real challenge, or sometimes an impossibility, as the ‘functionality’ in the products they are using may not be able to do the job required.

For smaller organisations using, say, a Sage or QuickBooks product, common challenges are tracking budget and actual costs by department/project/funding source. Depending on the precise requirements, these products may not report at the right level of detail. Hence spreadsheet analysis becomes a necessity.

Suddenly it becomes all too easy to, for example, miss out some costs on a project and under-report expenses to a funder. FDs can of course consider implementing a more powerful system than, say, Sage or QuickBooks. However, this can be prohibitively expensive to buy and then operate. In any case, a more powerful system may still not meet all the charity’s needs.

Common problems include fund accounting across financial years – including commitments, tracking investments and pension obligations. There are third-party specialist products to address these needs, but this can mean organisations using half-a-dozen or more different applications. For example, on top of the accounting software: payroll, time-recording, commitment accounting/procurement, investment-management, fundraising and membership systems, and a more advanced reporting tool.

Challenges then arise to ensure period-end cuts-offs are properly managed and reconciliations between control accounts are managed with great care. Finally there is the problem of making sure information from third-parties, based on spreadsheets, is accurate – something Mouchel failed to do.

Practical tips

So with the likelihood that you are making extensive use of spreadsheets, how do you manage the risk of serious error?

The internet can come to your aid. There are a host of sites with practical tips and tricks. Some examples include:

• An interesting paper entitled What we know about spreadsheet errors
• Linked to this is the spreadsheet research (SSR) website covering research on spreadsheet development, testing, use, and technology
• Then there is the European spreadsheet risk interest group.  This site contains a wealth of information, including an unpleasant list of horror stories relating to spreadsheet errors.

At the end of the day, there is no substitute for checking and re-checking the information. If your systems are complex you need the resource to manage them. CEOs and boards of trustees are often reluctant to invest the necessary money in this. But, hopefully, this article will help you put your case for such expenditure.

John Tate is MD of Changebase, IT adviser to the CFDG and a visiting lecturer at Cass Business School 

Who's Who: John Tate