Slashdot Mirror
$10B Annual Tab for Spreadsheet Errors?
theodp writes "According to PWC and KPMG, more than 90% of corporate spreadsheets have material errors in them. With each error costing between $10K and 100K per month, one expert estimates corporate America loses in excess of $10B annually through the misuse and abuse of spreadsheets." From the article: "The key point about spreadsheets is that you need to know which ones are critical to your business, which ones are merely important and which ones you do not have to bother too much about. Once you know that, you can start to apply appropriate policies depending on the criticality of the spreadsheet involved."
This article only brings up losses and problems with the spreadsheet format. I'm sure, however, that there are inaccuracies in company word documents, e-mail, and other forms of communication. How should spreadsheets be any different?
6x9=42
Many people say that the whole idea or spreadsheets is fundamentally flawed because a single error can propagate itself throughout the whole spreadsheet so a miscalculation early on tends to expand exponentially down to the rest.
411 Y0UR 8453 4R3 8310NG 70 U5!! -NSA
ESA has a similar facility, as does NASA Goddard. And from what I've heard contractors like Boeing have experimented with the same kinds of ideas.
I fixed the problem in the spreadsheet, and then dug through all the existing orders that were about to be filled and corrected them. The problem had cost GE about $300,000 and was about to cost them another $120,000 in the next month. The interesting thing, is nobody had really cared to do anything about it until an intern came along, and dumped it on me. They just don't see $10,000 as a whole lot of money in the grand scheme of things, so I'm sure stuff goes on like this all the time.
This is probably just another case where some statistician takes a common problem, makes a few guesses, and comes up with some exorbenant figure to scare people into paying attention. You can support any point with statistics.
Think of this, when you are finished with a tube of toothpaste, there is still a little you can't squeeze out. I'm sure someone could add all that up and claim Americans are throwing out $100 million a year on toothpaste. You could say the same about a lot of products. But what's the point? If you can't do anything about it, why worry yourself over it?
So in this case, you can't eliminate all accounting mistakes and typos, but if some PHB needed to read this to question his spreadsheets, he's useless.
This is money that is "lost" or "gone".
Assuming these errors are uniformly distributed, there are roughly equal numbers of errors in the positive and negative directions. The idea that such money is just vanishing from our economy is flat out wrong.
And even if the errors are heavily biased in one direction, the money is still somewhere, it's just being less efficiently distributed.
As I understand it, it's a slow news day can mean time to post things from the totally unsubstantiated category that's always chock-full of stories thanks to the the only way I'll beat the deadline is to make something up effect.
It's simply bad journalism. The author names PWC as a source of the 'over 90%' figure, but PWC in turn was citing some professor from Hawaii who had looked at 54 spreadsheets and found errors on 49 of them. 54 is a sample so small as to be absolutely meaningless and everyone responsible for the story finding it's way here should hang their heads in shame.
Making the world a better place, one psychotic episode at a time.
The real problem is that Excel (and other such spreadsheets - but mostly Excel) leaves the average person feeling like they're a wiz-kid programmer after they've been using it in their job for only a few months. I've lost track of the number of self-professed "experts" I've interviewed who didn't know the first thing about sheet design and how it affects operational risk. Worse, are consultants and auditors who have no idea about things like calculational complexity and change management. I've worked with spreadsheets for many years, and I've often seen people use volatile functions incorrectly (*please* look them up and understand why they can make a spreadsheet slow if you use Excel) or code a bubble sort in VBA and then wonder why recalculation takes so long. Just to answer some of your points however:
References: There are a couple of (unfortunately) volatile functions you can use to compute refernces dynamically: INDIRECT() and OFFSET(). Alternatively in a large production environment (like a big bank...) you'd do better to resolve refernces once using some form of macro pre-processing (which you'd have to write yourself, but hey).
Code duplication: This is not as big an issue as you make it out to be, as copydowns are quite efficient (all functions and names are just symbols internally, and in R1C1 reference form the copydowns are identical strings). Moreover, you are using the wrong function: You should MATCH() once on (A4-FedStdDeduction), then INDEX() the items required in the FedTaxRates table.
Unreadable code: Use cell comments on the header row (or column) of the table.
Poor layout: Use conditional formatting (with the condition being something like MOD(ROW(),2)=1 to do automatic grey-barring. Use "center across selection" or cell merging for different size table columns.
Charting: Agreed. Don't go there. In fact, for any meaningful statistics anaylsis, use a real system. Besides, the stats functions in Excel have known weaknesses.
Database access / external data: Use RTD() and/or write a COM / VBA addin (even better, use the C API / EXCEL4 interface, which allows for high bandwidth communications).
Overall Excel is a very powerful tool, but most people wildly overestimate their knowledge and abilities in it, leading to the kind of errors hinted at in the liked article. The *real* problems with Excel are:
Unsupported interfaces: MS likes to "innovate" by always adding their latest wizz-bang coding interface to office and leaving previous generations stagnant (EG: strings are limited in length to 255 chars in the C API), then quietly dropping support. This is very bad. I think they will probably not get away with dropping VBA support however.
Calculation complexity limits haven't changed for ten years: Still stuck with the same limits in Excel - 7 nesting levels, 1024 char max formula length etc.
Best regards,
Daniel
PS: I have a comprehensive suite of tools for profiling Excel (internal and external functions, VBA, XLL) that I've been working on for some time, if anybody is interested in such a thing or knows of anything similar (I haven't seen the like yet) please email: desva at btinternet dot com.