Original URL: https://www.theregister.com/2006/05/03/buggy_spreadsheet/

Buggy spreadsheets: Russian roulette for the corporation

The chasm between common sense and computer programming

By Tom Welsh

Posted in Software, 3rd May 2006 08:37 GMT

How many scenarios can you imagine where a momentary loss of concentration could cost over $1bn? Perhaps a nuclear power station meltdown...or if a currency trader hit a few wrong keys? Well, another possibility is a simple spreadsheet error.

In October 2003, soon after announcing third quarter earnings, Fannie Mae had to restate its unrealised gains, increasing them by $1.2bn. This highly unwelcome outcome was said to stem from "honest mistakes made in a spreadsheet used in the implementation of a new accounting standard".

The really, really bad news is that millions of similar errors are almost certainly being made every year, many of them in business-critical financial spreadsheets. Although they are the quintessential end-user tool, spreadsheets of any complexity are just as hard to write and maintain as any other kind of software - if they are to yield consistently accurate results, anyway.

At Cutter Consortium's inaugural European Summit in March, I took part in a spirited panel discussion about SOA and related matters. At one point the redoubtable Oliver Sims suggested that Excel was the world's most widely-used software development tool - a statement that struck me as probably correct. But if so, how grave are the implications? After all, the overwhelming majority of spreadsheet users are not professional programmers, and it is doubtful whether most of them have even been formally trained to write spreadsheets.

If only organisations realised the harm they are laying themselves open to, they might be inclined to seek advice from a suitably qualified expert. Such as Dr Louise Pryor, an independent software risk consultant who specialises in complex financial models and spreadsheets.

As a Fellow of the Institute of Actuaries, with a PhD in Computer Science and extensive hands-on experience in commercial software development, Pryor is ideally equipped to explain how spreadsheets should be designed, written, and tested. Her website provides detailed and convincing evidence of prevailing spreadsheet error rates, some hair-raising war stories, good advice, and loads of other useful information.

Other valuable resources include the spreadsheet research (SSR) site maintained by Professor Ray Panko of the University of Hawaii, and the European Spreadsheet Risks Interest Group (EuSpRIG). Several books have been written on the subject; for instance Patrick O'Beirne's Spreadsheet Check and Control.

Panko has collected the best available evidence for spreadsheet error rates, based on field audits by organisations such as Coopers and Lybrand, KPMG, and HMCE (the UK's Customs and Excise department). Of 54 spreadsheets audited between 1997 and 2000, no fewer than 49 were found to contain significant errors - a defect rate of 91 per cent. In a more recent exercise, every single one of 30 project financing spreadsheets scrutinised had at least one mistake.

This is not to suggest that spreadsheets are uniquely bug-ridden; there is convincing evidence that virtually all non-trivial software contains defects. According to Panko's Human Error website, "spreadsheet error rates look like error rates in normal program development and in code inspection". He estimates typical human error rates at about 0.5 per cent for simple mechanical tasks like typing, and five per cent for more complex logical activities such as 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. ®