Share

Spreadsheet horror stories

Spreadsheet horror stories
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:

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 

Richard Blaustein
Analytic Solutions, Inc.
10 Nov 2011

Another important tool for the fight against spreadsheet error and risk is automated spreadsheet audit software. One example can be found at www.audinator.com but there are several other good ones out there. Combine automated audit with manual audit and implementation of best practices in spreadsheet development, and spreadsheet error risk will be significantly reduced.

Richard Blaustein
Analytic Solutions, Inc.
10 Nov 2011

Great article John. Always amazes me the lack of effort and resources applied to spreadsheet risk control. Some useful tips for your readers on signficantly reducing spreadsheet risk...http://www.theiia.org/intAuditor/itaudit/2009-articles/eliminating-spreadsheet-risks/

Patrick O'Beirne
EuSpRIG
2 Nov 2011

Thanks for the mention of Eusprig, John.

People with success stories to tell about spreadsheet control could consider a submission to the European Spreadsheet Risk Interest Group (EuSpRIG) conference next July 2012 in Manchester, England. See http://www.eusprig.org

Comments

[Cancel] | Reply to:

Close »

Community Standards

The civilsociety.co.uk community and comments board is intended as a platform for informed and civilised debate.

We hope to encourage a broad range of views, however, there are standards that we expect commentators to uphold. We reserve the right to delete or amend any comments that do not adhere to these standards.

We welcome:

  • Robust but respectful debate
  • Strongly held opinions
  • Intelligent relevant discussion
  • The sharing of relevant experiences
  • New participants

We will not publish:

  • Rude, threatening, offensive, obscene or abusive language, or links to such material
  • Links to commercial organisations or spam postings. The comments board is not an advertising platform
  • The posting of contact details for yourself or others
  • Comments intended for malicious purpose or mindless abuse
  • Comments purporting to be from another person or organisation under false pretences
  • Gratuitous criticism, commentary or self-promotion
  • Any material which breaches copyright or privacy laws, or could be considered libellous
  • The use of the comments board for the pursuit or extension of personal disputes

Be aware:

  • Views expressed on the comments board are left at users’ discretion and are in no way views held or supported by Civil Society Media
  • Comments left by others may not be accurate, do not rely on them as fact
  • You may be misunderstood - sarcasm and humour can easily be taken out of context, try to be clear

Please:

  • Enjoy the opportunity to express your opinion and respect the right of others to express theirs
  • Confine your remarks to issues rather than personalities

Together we can keep our community a polite, respectful and intelligent platform for discussion.

John Tate

John Tate is a qualified accountant and has over 20 years working in the IT industry. He is also a columnist for Charity Finance, IT advisor to CFG and a lecturer at Cass.

Ian Allsop (60) John Tate (53) David Davison (49) Robert Ashton (41) Tania Mason (24) Andrew Hind CB (17) Gordon Hunter (17) Daniel Phelan (14) Vibeka Mair (12) David Ainsworth (11)
David Philpott (10) Gareth Jones (8) Celina Ribeiro (8) Niki May Young (8) Rui Domingues (8) Andrew Chaggar (5) James Brooke Turner (4) Sir Stuart Etherington (4) Kate Sayer (3) Jeremy Swain (3) Garreth Spillane (3) Alistair Gibbons (3) Ian Clark (3) Claris D'cruz (2) Stephen Lloyd (2) Richard Maitland (2) Adrian Beney (2) Iain Pritchard (2) Pauline Broomhead (2) Daniel Fletcher (2) Martin Brookes (2) Tesse Akpeki (2) Nick Brooks (2) Stephen Hammersley (2) June O'Sullivan (2) Dan Corry (2) Peter Holbrook (2) Belinda Pratten (2) Simon Steeden (2) Jonathan Bruck (2) Dan Gregory (2) Mark Astarita (1) Don Bawtree (1) Sir Stephen Bubb (1) Victoria Cook (1) Lindsay Gray (1) Rachel Holmes (1) Nick Ivey (1) Iona Joy (1) John Kelly (1) Michael King (1) Heather Lamont (1) Lucy McLynn (1) Chris Oulton (1) Julian Rathbone (1) Socrates Socratous (1) Richard Weaver (1) Karl Wilding (1) Richard Williams (1) Roger Chester (1) Matthew Bowcock (1) Joe Saxton (1) Reuben Turner (1) Martin Farrell (1) Paul Gibson (1) Becky Slack (1) Jonathon Grapsas (1) Andrew Scadding (1) Simon Hebditch (1) Su Sayer (1) Debra Allcock Tyler (1) Martin Birch (1) Mark Hallam (1) Jonathan Lewis (1) Sara Llewellin (1) John Low (1) Dame Mary Marsh (1) Ruth Murphy (1) Colin Nee (1) Julia Unwin (1) Kate Rogers (1) Malcolm Hayday (1) Filippo Addarii (1) Kimberley Scharf (1) Jakes Ferguson (1) Jessica Sklair (1) Joe Turner (1) John May (1) Julian Blake (1) Rosie Chapman (1) Andy Williamson (1) Malcolm Hurlston (1) Andrew Samuel (1) Chester Mojay-Sinclare (1) Paul Amadi (1) Kirsty Weakley (1) Luke Fletcher (1) Peter Mitchell (1) Billy Dove (1) Andrew Ketteringham (1) Jackie Turpin (1) Lynne Robb (1) Jonathan Crown (1) Paul Emery (1) Ruchir Shah (1) Pesh Framjee (1) Sukhvinder Kaur-Stubbs (1) Moira Protani (1) Vicki Prout (1) Michael O'Toole (1) Dawn Austwick (1) Lisa Clavering (1) Paul Farmer (1) Neelam Makhijani (1) Logan Anderson (1) Andy Rich (1) Sharon Martin (1) Asheem Singh (1) Leigh Daynes (1) Abdurahman Sharif (1) Lynne McMahon (1) Richard Caulfield (1)
Less +++ More +++

Popular Tags

Embedding digital in a traditional organisation

29 Jul 2014

The Children's Society has been creating a digital culture. It hasn't been easy, but it has been worth...

Look before you leap when it comes to cloud technology

7 Jul 2014

If you are going to invest in cloud technology, John Tate has some words of advice – take time first...

How combining international aid and social media can transform Africa

18 Jun 2014

Social media is impacting life in Africa at breakneck speed, and it is not just the usual suspects Facebook...

Too much focus on reputational risk?

28 Aug 2014

Risks must always be weighed up against potential benefits, says Dorothy Dalton.

It's time for the sector to front up and prove it has nothing to hide

19 Aug 2014

The sector's representative bodies must be bolder in telling the Charity Commission what they think of...

Film of the week: OneMinuteJr by Unicef Ukraine

15 Aug 2014

Unicef Ukraine has released a series of one minute videos created by children caught up in the conflict...