Spreadsheets are deceptively easy to use and incredibly hard to manage. For businesses globally, spreadsheet errors have resulted in loss of cash, profits and customers, reputational damage, and operating failures. Businesses rely on spreadsheets to the point that they are effectively critical to the success of the business. However, many of these businesses are not aware that there is even a risk associated with spreadsheets, let alone know how to manage that risk. This article looks at some classic spreadsheet errors in layman’s terms and suggests some preventative measures.
What Can Possibly Go Wrong?
It’s a sad fact that a great presentation of no substance can fool a lot of people. This applies to spreadsheets too. If a spreadsheet looks good, people are more inclined to believe it. All those lovely sums lend an air of scientific fact to what can in fact be error laden, made-up drivel.
There are many types of possible spreadsheet error, and to give you an idea of the diversity of these errors and their consequences, here’s some real-life examples:
- Drinks manufacturer C & C, lost 15% off of its share price in 2009, due to errors in its published results, when data was incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement.
- A sole trader made significant errors in his VAT, (sales taxes), returns because an error in a simple formula meant that his spreadsheet omitted a portion of his sales.
- A company’s investor forecasts were overstated to the point of ridiculous because, due to an error in the formula, it added in the year, (e.g. 2012), to the sales quantities.
- Company accountants, working late into the night on figures for a company take-over, made a catastrophic mistake in the final formula, and put the currency exchange rate in the wrong way around.
- The London 2012 organising committee, (Locog), confirmed that an error in its ticketing process had led to four synchronised swimming sessions being oversold by 10,000 tickets. Locog said the error occurred when a member of staff made a single keystroke mistake and entered ‘20,000’ into a spreadsheet rather than the correct figure of 10,000 remaining tickets. The error was discovered when Locog reconciled the number of tickets sold against the final layouts and seating configurations for venues, and began contacting ticket holders before Christmas.
Related: Top Ten Tips For Managing Your Books
What’s to be done?
There are lots of things you can do to manage your risk. The most important thing you can do is to accept that spreadsheets are a source of risk if your business uses them. Here are some risk management suggestions:
- Establish if a spreadsheet is desirable at all
Quite often, spreadsheets are used to overcome shortcomings in another system, when the focus should be on either fixing the problems or switching to a more appropriate system. Importing data from another system to a spreadsheet opens up the possibility of error, as in the case of the Olympics. Yet a lot of event organisers end up importing data into spreadsheets because the ticketing system doesn’t provide the reports they need to actually run the event. Neither should spreadsheets be used for your accounts, it’s too easy to make an error, as in the case of the sales tax omission outlined above. Spreadsheets should not be used when there is proprietary software designed to do the job better and faster. - Train Your Staff
If your staff, (or you), are using spreadsheets to prepare important business information, make sure they are trained. Self taught spreadsheet authors are often highly inefficient in the way that they structure their sheets as well as being oblivious to the errors that they create. - Build in Control Totals
Build in as many ways of checking the numbers as possible. For example, the Olympics Committee discovered their overbooking of events, albeit belatedly, when they reconciled the number of tickets sold against the size of the venues. If you are moving data from a software system to a spreadsheet, that is a risk straight away, and you need to build in controls to manage that. - The Sunset Rule
This is a rule I have developed over almost thirty years of spreadsheet use. The authors and testers of important spreadsheets should have a night’s sleep prior to final review of their work, no matter how much pressure employers or clients put on them. The fall-out from big errors isn’t pretty and often leads to resignations and sackings as well as financial loss for the company. - Lock Down
If a spreadsheet is to routinely have data input into it, it should have the data entry cells protected, once it has been tested. That way, users can’t accidentally over-type the formulas. - Audit and Test
Spreadsheet programmes, such as Microsoft Excel, have audit functions built in. You should learn how to audit your formulas and you should be able to devise tests to verify the results generated. - Authors and Auditors
Important spreadsheets should be audited and debugged by someone other than the author. You’re unlikely to find all your own mistakes. - Common Sense
It’s easy to be so enamoured with your spreadsheet that you forget to look at the results with a critical eye. Look over every row and column; do the figures seem possible in the context of your business? For example if there are 300 booked in for a conference, it wouldn’t make sense if 600 delegates had ordered a vegetarian lunch. This might seem madly obvious, but these are the types of errors that routinely get overlooked by the authors and users of “beautiful” spreadsheets.
I love spreadsheets. I really do. I use them for everything, even in my personal life. But I know that if you are relying on the data for an important decision, you’d better test that sheet and not be blinded by the beauty of the numbers.
Related: DIY Management Accounting And Business Performance Review
Have you any spreadsheet horror stories you’d like to share or any tips on reducing spreadsheet risk?
Did you like this article? Sign up for our RSS, like us on Facebook or follow us on Twitter
Image: “lot of numbers on a spreadsheet /Shutterstock“
