The best thing about Excel is that it’s so flexible. The worst thing about Excel is that it’s so flexible. With enough effort and programming, people can bend and twist Excel into doing almost anything. The challenge with that, as many people know, is that lurking inside every spreadsheet are errors that elude the creator. Some errors can be minor, but many errors are much greater in magnitude and can lead to executives making critical decisions based on bad math.
Numerous studies on spreadsheet errors have been undertaken over the past 5 years which indicate that an alarming rate of errors exist in business-critical spreadsheets. Consider this blurb from the 2009 University of Hawaii paper on “What we know about spreadsheet errors”:
In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists. These error rates, although troubling, are in line with those in programming and other human cognitive domains. In programming, we have learned to follow strict development disciplines to eliminate most errors. Surveys of spreadsheet developers indicate that spreadsheet creation, in contrast, is informal, and few organizations have comprehensive policies for spreadsheet development. Although prescriptive articles have focused on such disciplines as modularization and having assumptions sections, these may be far less important than other innovations, especially cell-by-cell code inspection after the development phase.
To put it another way, on average, a spreadsheet will contain 1 error for every 20 cells that contain data. Cell errors are bad enough. What about the errors that are introduced from copying data from one spreadsheet to the next? It’s pretty common to have multiple spreadsheets – each with multiple workbooks – to manage a complex project for example. Bringing together key data into one summary spreadsheet to create a status report is a common undertaking. The re-keying of information is notorious for mistakes and missed data; not to mention the potential for internal cell errors. Consider this bid error that Emerson Construction Company made:
“Emerson Construction Company made a cell reference spreadsheet error that caused a bid submission that was $3 million lower than intended. According to a whitepaper of the U.S. Government Accountability Office, Emerson Construction Company submitted a bid where “[t]he price at spreadsheet cell number D159 (for electrical work) was not included in the subtotal at cell number D160. Based on the format of the spreadsheet, it is clear that the $3,702,025 price at cell number D159 was intended to be included in the firm’s subtotal price.” While Emerson Construction Company was allowed to correct its proposal and resubmit its bid, an incorrect bid submission may result in the loss of contract or, worse, the legal obligation to preform work at price where the project is no longer profitable.” Taken from Spreadsheet Horror Stories.
In a more recent article by Jeremy Olshan, a study of studies shows us, once again, that 88% of spreadsheets have errors. In this article, he discusses that appropriate safeguards should be undertaken to minimize the impact of spreadsheet errors. This sounds okay in theory, but the reality is, the creation and management of spreadsheets is already time-consuming enough without always having to have someone looking over your shoulder. Few people or organizations are going to go to those lengths to run spreadsheet error checks and proof-reads.
“The problem is so widespread that there are now whole groups devoted to stamping out spreadsheet snafus, such as the European Spreadsheet Risks Interest Group. There’s no question that spreadsheets are a powerful tool, essential to the functioning of the modern world, the group’s chairman, Patrick O’Beirne, tells MarketWatch. “Chainsaws are also a very good tool, but who would use one without a chain guard,” he says.”
A client recently told me several stories about the spreadsheet “Defining Moments” that led him to abandon Excel and move into a dedicated software solution. One example was this, “On a number of occasions, after putting together a status report in Excel, I’d hit print and pick-up the copies on my way to a client status meeting. Then, while sitting in the meeting, my heart would sink when skimming over my report; because I’d realize that I’d used an old version of one of my colleagues spreadsheets to create the report. I’d hand-wave and detract their attention during the meeting so that I wouldn’t get caught. It was humiliating. I had to make a change.”
He’s not alone in experiencing these “red-faced” situations,
“These spreadsheet errors have caused many business owners, executives, and others to be red faced when they discovered these errors, especially when these errors result in errors so substantial that they get picked up in the media. Studying the spreadsheet horror stories of other businesses and organizations should not serve as entertainment, but should be viewed with the understanding that spreadsheet error risk permeates every organization, large and small.” Taken from Spreadsheet Horror Stories.