Spreadsheets

The Right Tools For The Job?

Spreadsheets are one of the most useful software tools ever created. Their wide acceptance and flexibility make them an excellent office productivity tool for many businesses. But businesses need to improve their use of spreadsheets and know when to start using a more solid database approach.

  Watch Out For Errors

Terrible drawbacks accompany the flexibility of a spreadsheet. Put simply, nearly all spreadsheets have errors in them. An authority in this field, Raymond Panko of the University of Hawaii, explains that for large spreadsheets, the issue is how many errors there are, not whether an error exists. His paper, What We Know About Spreadsheet Errors, summarises the research, giving statistics of 90% and above for the percentage with mistakes. And it is getting worse. As new techniques are developed for auditing spreadsheets, more mistakes are found while there is no corresponding improvement from the spreadsheet users.

  It Just Doesn't Add Up

Any competent, humble user of a spreadsheet can list many ways in which errors are made. An incorrect cell is referenced in a calculation. A specific value is typed into a formula, not into a cell, and effectively becomes invisible. Totals add up most of the figures above them, but not all of them. Sub-totals are double counted. Formulae are simply incorrect, for instance in the calculation of a net from a gross figure.

  Is It the Users' Fault?

Again, the user must be humble to recognise these faults. A key contribution to the problem is over-confidence. In one study, Panko found that on average users thought there was a one-in-five chance of their spreadsheet having an error in that particular case 86% of them had made a mistake. If you think you haven’t made a mistake, then think again.

Typical advice is often to separate the input of figures from the calculations on them. Unfortunately this practice introduces the need for formulae to be implemented far away from the inputs, which leads to a particularly high level of error.

  Test, Test, and Test Again

Many businesses use spreadsheets to prepare their business plans. Some apply rigorous methods, such as data testing and line-by-line code checking, which are the disciplines of software development. But statistics insist that some companies will not be rigorous.

  Getting The Correct Results

Spreadsheets do not introduce significant new problems, but neither do they actively reduce error or promote good practice. Where innovation or extreme flexibility is required then they are a good choice.

However, the evidence suggests there is a great danger in planning and decision-making based on incorrectly calculated figures from spreadsheets. For such critical business information, a well-built database is a better solution.

For more information please call Whitespace marketing on 020 7240 0208 Alternatively, you can email us with your request at info@whitespace.co.uk.