Tweak Your Biz

Home » Finance » Lies, Damn Lies And Spreadsheets: How To Prevent Spreadsheets Ruining Your Business

Lies, Damn Lies And Spreadsheets: How To Prevent Spreadsheets Ruining Your Business

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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

Helen Cousins, a chartered accountant by profession, is a business mentor, trainer and consultant for a wide range of Irish SMEs, often working under the auspices of state agencies via her company Xcel Business Solutions. In a successful career spanning more than 25 years, Helen worked in accountancy practice for PricewaterhouseCoopers, and worked in Financial Controller and senior management positions in manufacturing industry, before starting her own consultancy for small businesses. Helen is also a self catering entrepreneur, operating her own self catering holiday home business in Wexford. She is a director and former Chair of the Irish Self Catering Federation, and she works closely with the tourist industry in Ireland.

Similar Articles
  • sitebuilders

    Really enjoyed this article, because I just completed an assignment on analytics competitors. As I was conducting research I ran into the statistics that spreadsheets tend to have a 20 to 40 percent error rate. As much as I enjoy data analysis and manipulation, it is extremely difficult to not make a single mistake when you are working with hundreds if not thousands of variables. These spreadsheets tend to be historical documents for companies. It is deemed to have one error if not a lot in these documents. 

  • What a great post, Helen! Talk about making me see spreadsheets in a new light 🙂

  • Helen – you make me smile. I’ve too many horror stories to share:).  I realized some time ago the necessity to have my formulas checked – and then the input performed by those who love to work on spreadsheets and accounting software.  You have a great talent for this and I applaud you for it – as well as explaining in layman’s terms how to monitor otherwise simple errors.  Thanks.

  • Helen, a super article – thanks for highlighting the vulnerability and volatility of spreadsheets and their users!!
    =20+15*2  – A most common error in spreadsheets is lack of knowledge of simple mathematics. Some would use this simple formula to return an answer of 70, however a spreadsheet will return 50. No amount of auditing/checking will proof something like this.

    I had a client who used Excel for accounting – when moving over to an accounts package, they realised they had not invoiced for 2 contracts!! OK it was only 1000 Euros, but that is a lot of money to be missing out of a small business cash flow!

    Again, thanks for highlighting. Self taught spreadsheet users can be the most dangerous kind, especially when they mentor others!

  • Lewis

     Elaine, I don’t really get your example 🙂 Of course 50 is the right answer to the formula. So that would be an incentive in the use of spreadsheets, or what? 🙂
    We also get LOTS of mistakes with spreadsheets. I mean, mistakes are bound to happen, even with the best of us. Everybody gets distracted or tired sometimes and either you get someone to double check somebody else’s work and someone to triple check that or you have to deal with a margin of error. That said, basic training should be required in every company where Excel or such are largely used (the most of them, actually), which unfortunately doesn’t happen very often.

  • Hi Lewis,
    You are reading the formula one way. Imagine if you wanted to add 20 and 15, and then multiply the result by 2? Basic mathematics and understanding the syntax of a formula would alert us to apply brackets to the aspect of the formula we wish to have calculated together ie. =(20+15)*2 this will return the correct answer of 70. However, if someone does not know or understand these simple principles, they will receive the incorrect result (50) and may not think to check it at all.

    This is such a common yet fundamental issue, I see it in training every day. Precedence in formulae needs to be taught, thankfully this is becoming less of a problem over time. You have hit the nail on the head “basic training should be required in every company…” the very ones to slip through the net are small business owners.

  • Sounds like you have a good delegation plan in place. Thanks Warrren for you kind comments, I’m delighted that I made you smile!
    ~ Helen

  • Thanks Niall 🙂

  • Realising that your spreadsheets are need to be checked for error is half the battle, so many people don’t and place blind faith in them. Glad you enjoyed the article, thank you.
    ~ Helen

  • Thanks Lewis, great to see you having a debate with Elaine here!
    ~ Helen

  • Thanks for your illustration and comments Elaine. Maths does indeed let a lot of people down. However, it should be noted almost all of the mistakes I have cited in my article were made in very large organistions by highly trained people. In fact, one of the mistakes was made by a person holding a degree in Maths and Computer Science and who is a trainer at expert level in MS Office Products including Excel.Scary isn’t it?~ Helen

Featured Author
© Copyright 2009-2018, Bloggertone LLC. All rights reserved.