$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."

Does anyone understand this?

[sllim]

I certainly don't. How can a spreadsheet cost money?
What are businesses overpaying bills? Or keeping projects up that are not needed cause of this?

Other Losses?

[teh merry reaper]

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?

Primary error

[Cow007]

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.

Re:GIGO

[kaiser423]

Exactly. There were most likely more errors of this type before the spreadsheet came to be. You read the article, and this guy is on some weird, stupid war against spreadsheets. The article, and the paper he wrote about spreadsheets is pure drivel, and horribly slanted. Not a study at all. He can't even suggest a viable alternative.

yoy

[yagu]

I never ceased to be amazed at "projects" handed to me from which the management of the assets, funding, etc. were all contained in a spreadsheet, typically in that person's "Documents and Settings" directory somewhere (the "My [insert the item du jour here]" sometimes, sometimes not.

And the spreadsheet often as not was written by someone not familiar with how spreadsheets worked, and were not of programming ilk.

Once (and I'm NOT making this up) I watched as one of the afore-referenced changed a value in a cell, added the values of a small range and entered that number in a "totals" cell. Said person was very surprised when shown the "sum" function.

And this was an incident in a very large corporation... with lots o' money at stake. I was never very popular for taking my stance, but I would always refuse to allow any spreadsheets be a part of my projects for managing info.... (and don't EVEN get me started about using spreadsheets for documentation... )

Spreadsheets get used in weird places

[GileadGreene]

Spreadsheets get used in the weirdest, most unexpected places. For example, both JPL's Project Design Center [nasa.gov], and the Aerospace Corporation's Concept Design Center [aero.org], use multiple Excel spreadsheets to design spacecraft. Not to the "nuts and bolts" level, but a preliminary design concept that can be used for rapid feasibility and trade studies, and rough cost estimates. Note that most JPL missions pass through the PDC during their development. And the bulk of the new generation of USAF spacecraft get their requirements, cost, and payload complements hammered out in Aerospace's CDC.

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.

Re:Does anyone understand this?

[dykofone]

I was on a co-op with GE Power Systems and was working on their spreadsheets that determined necessary pipe sizes and lengths for certain operating characteristics. There was an error in there that was causing two mismatched pipe diameters to be ordered and sent to the site, at which point it cost somewhere around $10,000 to correct the problem (mainly due to delays).

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.

Re:Ummm

[Stevyn]

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 will cost you...

[Reignking]

This happened to a friend of mine. A consultant decided to send a spreadsheet around to all of the employees, about 25, with some HR data, such as hours worked, etc. However, some of it had been copied and pasted as an object, from another worksheet.

What was in that other worksheet? Oh, everyone's salaries :)

Whoa slow down

[Illserve]

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.

spreadsheets are insanely useful-XSIS

[Anonymous Coward]

"It has been over a decade since the last innovative new spreadsheet - Lotus Improv. Time for something new."

http://www.codecomments.com/message1511409.htm [codecomments.com]

"- Spreadsheets. The Analyst Spreadsheet (which was also sold as a
separate package) was simply the best. Cells could contain arbitrary
Smalltalk objects, and forula were arbitrary Smalltalk code. When we
showed people things like image manipulation within spreadsheet cells
or computing inverses of matrices containing fractions and/or complex
numbers, they often could not believe what they saw :-)"

Great Example

[Anonymous Coward]

An electronics engineer friend of mine was using Excel to calculate the durations to keep dam gates open based on sensor inputs.

During debugging and testing his misplaced decimal on the spreadhseet ended up leading to dumping tens of thousands of gallons down what was a mild stream bed.

Small figures early on can lead to dangerous figures in other places.

Re:Does anyone understand this?

[vegaspctech]

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.

Re:Adopt a spreadsheet today, for the children.

[TopShelf]

I would agree that "misuse" is a better word, but misuse of spreadsheets themselves can wreak havoc within an organization. Here's just one recent example from my experience.

We're loading tens of thousands of items into an ERP from spreadsheets put together by users. Many items have leading zeroes which are to be preserved, and spaces which are not. For example, part number 0032330 189 5 should be loaded into the ERP as 00323301895. When using Excel's Find/Replace function (replacing " " with ""), the leading zeroes were lopped off of some records. If a string had letter contained in it, the leading zeroes were left alone - if it was all numbers, the leading zeroes were lopped even though the cells were formatted for text. This led to wrong information getting loaded in and subsequent rework to correct the errors.

Spreadsheets can be a powerful tool in the hands of a strong analytical user, but their sloppy data handling leaves a gap compared to more structured business systems.

Re:Does anyone understand this?

[Sinus0idal]

Yup, and this idea isn't purely with spreadsheets. I used to work for a large supermarkets logistics dept. We used to key in, by hand, orders for stores from suppliers etc all the time. Not a week went by without a store receiving thousands of cases of a product due to a typo..

But how do you check these things? In a business which might be shifting millions of cases of product a day, how do you flag up a couple of thousand, which for any other order, might be quite a reasonable number. And well, the ground staff just did as they were told without questioning.. which is maybe the worrying thing.

Re:1 small problem with spreadsheets

[Klivian]

It can get worse. Try having one of the PHB drop by when you're working/debugging on one, in table view(Used Access in this case). And then having him on later project meetings constantly referring to it as "excel sheets". It gets rather hard do convince the management to give resources for further development/bug fixing for those excel sheets, as they never need such for their own.

no visibility

[menem]

The problem with spreadsheets is that there is no visibility. Understanding a spreadsheet that someone else wrote is very tough. It is like trying to read a C program with only one function. And all of the C variables names are 'a14' and 'b59'. I would much rather see the code used to produce the math. A program like Matlab has any easy to understand scripting language. It is much easier to see how the calculations were made. Excel only works when you have simple calculations. For complex math, spreadsheets are a huge mistake.

if you count the costs

[Baki]

The costs of time lost due to end-users fiddling with spreadsheets, and even add train people to do that, I don't think this is a good idea.

Once a spreadsheet has grown beyond the trivial, and it starts using macro's and pieces of VB, it has become a software program.

Why do so many people assume that anyone with a bit of brains can write decent professions software (i.e. with certain quality standards)? Who don't they think that anyone with a bit of brains can design a building or a bridge?

Instead it would be better to give in and make sure that there is enough budget to let the real software people develop. It is fooling yourself to save money in the IT department, then throw it out of the window by letting amateurs make their own software, both in terms of lost time and in terms of errors and bad quality costing loads of money.

Re:Not surprised

[avsed]

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.