Buggy spreadsheets: Russian roulette for the corporation
The chasm between common sense and computer programming
Pryor takes this line of reasoning further. "If we assume a rate of one per cent of unique formulae having errors, and look at spreadsheets containing from 150 to 350 unique formulae, we find that the probability of an individual spreadsheet containing an error is between 78 per cent and 97 per cent. This is (obviously) a high number, but is reasonably consistent with the field audit results discussed above."
Most spreadsheets afford little leeway for "non-critical" errors. Spreadsheet formulae contain little or no redundancy, and any mistake in a formula is likely to cause the results to be wrong. Errors quickly multiply when spreadsheets are linked together. To make matters worse, the amateur "developers" who write spreadsheets tend to be dangerously overconfident.
The consequences can be dire. Among the cases cited by Pryor are those of RedEnvelope, whose shares suddenly lost 25 per cent of their value; Westpac, which actually had to halt trading of its shares; and TransAlta, which lost $24m due to a cut-and-paste error. She also notes that spreadsheets were implicated in the John Rusnak scandal at Allfirst/AIB. Last but not least, Ray Butler of HMCE has written a paper entitled "Is This Spreadsheet a Tax Evader?". It is extremely doubtful if any of us wishes to be associated with scofflaw software.
Pryor talks of seeing "junior actuaries spending 70 to 90 per cent of their time doing Excel - it's basically their job. But they don't have any books about it, and they haven't gone on any training courses. They are just expected to pick it up as they go".
Even if they did attend training courses, she warns, they would only learn how to use spreadsheet features and techniques - not why or when. In this respect, spreadsheet training courses are analogous to driving lessons: they train you to control a car, but not necessarily how to drive it safely and defensively. "You can drive down the M1 at 120mph, but you probably shouldn't."
Part of the problem is that spreadsheets lie squarely across the well-camouflaged dividing line between end-user tools and professional programming. Such tools encourage us to overlook the gaping chasm that lies between common sense and computer programming. "I see many extremely bright financial types assuming that they can 'pick things up', and that 'it's all just common sense'," says Pryor. "But you can't have common sense if you have no experience to draw it from."
What should organisations do to make sure that they don't suffer from what we might call "incorrect spreadsheet syndrome"?
According to Pryor, it is essentially about focus. "They need to take what they do seriously. If you spend a lot of time on something, take it seriously and learn how to use the tools well." By applying the well-known disciplines of software engineering to spreadsheet authoring and maintenance, error rates could be substantially reduced. Perhaps it is time for corporate governance to lay down hard-and-fast rules for evaluating and testing business-critical spreadsheets. ®
Sponsored: Becoming a Pragmatic Security Leader