Beware the perils of spreadsheets

Do you believe in spreadsheets? Of course you do. They are incontrovertible pieces of scripture to be printed out, flattened on the desk and jabbed with a forefinger to justify the next avenue – or cul-de-sac – of corporate policy.

But the trouble is, most of them are wrong. To quote recent research from the University of Hawaii, between 78 and 97 per cent of spreadsheets contain “serious material errors” with a potential to devastate the bottom line.

Consider the evidence:

● A number misrecorded in just one cell of a spreadsheet meant one company had to reduce drastically its fourth quarter outlook. Its shares lost more than 25 per cent of their value.

● A cut-and-paste error led to another company underbidding for an electricity supply contract.

● A missing minus sign caused a fund’s projected earnings to be overstated by $2.6bn.

● Falsely linked spreadsheets covered up a fraud totalling $700m at one bank.

● A faulty macro delayed the introduction of a drug, savaging a pharmaceutical company’s profits.

● A wrongly named spreadsheet led to the inflation of natural gas prices in the US when one company submitted erroneous gas storage figures.

Ask most people who are dependent on spreadsheets to explain how they work, and they will happily own up to ignorance.

Ask them if they have considered the possibility that human error – or human malice – is costing them thousands of pounds each day via the mysterious veil of the spreadsheet, and, so profound is their dependence, they may walk away, refusing to hear.

There is hardly a company in the civilised world that does not rely on spreadsheets to develop complex financial models, organise financial reporting, analyse data, present results, or replace the back of an envelope for simple one-off calculations.

But most spreadsheets are developed without the discipline of traditional programming, with no formal quality assurance to ensure they are built to specified requirements and will work correctly.

A single bug-ridden spreadsheet application in an accounting system can nullify every control that’s been put in place.

Companies need to conduct an inventory of all spreadsheets to identify which are critical and where problems might arise.

At a bare minimum, those developing spreadsheets should design them meticulously, and once they are completed should test them using known results according to a written plan, test them again using a commercial auditing tool and then attack them for a third time by inviting peers or a trusted security adviser to examine them.

The underlying problem is that business managers do not know that a problem exists, and IT managers tend not to see spreadsheets as their responsibility. Thus spreadsheet management slips silently into a gap between corporate divisions.

Prudence suggests the IT department should be responsible for spreadsheets so that experts can bring them under the same development, testing and control procedures as other computer applications.

And so beware: when making that next investment just remember – the figures that look so good may have come from an untested spreadsheet, full of errors …

Ian Cook works for Pentest, an IT security company providing consultancy services across the UK, Europe and North America. He is also vice chair of first.org. (ian.cook@pentest.co.uk).