Avoiding Spreadsheet Error: Cut and paste at your peril

Seemingly minor spreadsheet errors could cost your organisation dearly.

In the case of the London 2012 Olympic Games, it would be that spreadsheet typo which resulted in 10,000 more tickets offered to synchronised swimming events than were actually available, and then the embarrassing backtrack that followed. Or how about the debacle with TransAlta, the Canadian power generator company that spectacularly overspent by $24 million thanks to a spreadsheet cut-and-paste mistake.

Yes, you read that correctly. Twenty-four million dollars lost because of a simple cut-and-paste error in Excel.

If you’re thinking right now that you would never be so careless with your own calculations or record keeping, the unfortunate reality is that your documents probably contain mistakes too. An in-depth analysis of decades’ worth of studies found that nearly 90 per cent of spreadsheets contained errors. What’s more, it’s been estimated that one in five businesses have suffered financial losses from spreadsheet blunders.

The culprit is not usually technology failures, but rather the operators of that technology: humans, who are known to make mistakes fairly frequently.

Here are 5 common spreadsheet errors and how you can safeguard against them:

Being overconfident

We are a self-assured bunch. A well-known phenomenon, known as the overconfidence effect, shows that people’s judgement of their own abilities is greater than their actual performance. For example, most people tend to believe they are an above-average driver, even when the evidence says otherwise.

The effect can also be seen in decision-making. Quiz-goers who were surveyed about their answers were strongly convinced their choices would be revealed to be correct. In this case, it was shown 40 per cent of their answers were incorrect.

How is this relevant to spreadsheet data input? Well, the same phenomenon has been demonstrated in spreadsheet developers and users. A group of experienced spreadsheet developers created data pages and all believed in the accuracy of their product and even gave themselves a median self-rating of “very confident”. However, every developer was found to have made a minimum of one mistake.

The aesthetics of a spreadsheet can also prove to be a powerful influence in convincing users of data accuracy. Large, well-formatted spreadsheets are often seen as more accurate than equally large, but plainly formatted, spreadsheets. Spreadsheet users also record a high confidence score in favour of the large, well-formatted data, as opposed to small spreadsheets – regardless of their formatting. This goes against common sense – the more data entered, the higher the probability of error.

It would help to remember these trickeries of the mind when working on your own spreadsheet, to make sure you don’t create problems for your organisation.

Only looking at it with one pair of eyes

Have you ever finished a piece of work and thought it was great, only to read back over it a week later and see obvious mistakes? You need time away from the document to see errors but even better is having another person take a look. A step even further on this is to have two or more people working on a spreadsheet. Groups of two can reduce mistakes by a third, while groups of three can reduce them by two-thirds.

Patrick O’Beirne, Chairperson of the European Spreadsheet Risks Interest Group, said that peer reviewing in the publishing and research world was the gold standard and readily accepted, but he noted that alarmingly, the opposite is true of spreadsheets. Data is frequently used publicly – and sometimes on a very large scale – in its rough draft form.

At a business level, it would be worth developing a comprehensive policy for spreadsheets. That should include a policy in which a final review of all data is conducted before using it for reporting.

Forgetting to document your processes

Philip Bewig in his paper “How do you know your spreadsheet is right?  Principles, Techniques and Practice of Spreadsheet Style”, said it is a good strategy to make use of the in-cell comments section, which will save you pain and time down the track.

Perhaps you could describe precisely the source of all data input or explain complex formulas you have used, for example. Bewig also stressed the importance of documenting as you go so all decisions are fresh in your mind.

Not planning your design

You may have heard the acronym of the 7 P’s in planning. You should be just as vigilant when starting a new spreadsheet. IBM’s instruction on spreadsheet modelling best practice outlines excellent tips that can be applied to small business when firing up a new spreadsheet.

One of the initial steps is to determine the scope of the spreadsheet. The scope defines the boundaries and objectives and can include things like:

  • What the spreadsheet will (and will not) do
  • The appropriate level of complexity and what assumptions you’re making
  • Data requirements for the spreadsheet and when you need to produce the data

Your accountant, bookkeeper or other financial service providers are good people to assist you in this project, especially if you’re providing financial information to them.

Not using specific programs for your needs

Excel is a program that relies on manual entry. A better option might be a program that eliminates the reliance on this, and could even suit your organisation’s needs better. For example, Reep is a wise choice for those who would like a budgeting service that integrates MYOB, Agrimaster or Xero. It’s an Australian software company using cloud software to provide a better picture of cash flow, budgeting and forecasts. It also eliminates the need to produce and maintain cumbersome spreadsheets. Even better, Reep offers a 30-day free trial.