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."
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?
That's a $10 Annual Tab for Spreadsheet Errors. Misplaced a decimal!
Documents have typos. Film at 11.
people make mistakes.... it costs money.... next please?
Garbage In, Garbage Out
Just because this deals with spreadsheets makes it news? I think people have had this problem since people started making inventories.
Your hair look like poop, Bob! - Wanker.
A major factor in my switch to Macintosh as my primary platform was that I could run both perl and Excel on the same machine.
Maybe if more geeks played with the spreadsheets we could come up with best practices to hand over to the PHBs.
It has been over a decade since the last innovative new spreadsheet - Lotus Improv. Time for something new.
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
One small problem with spreadsheets is that people sometimes use them instead of databases, I guess because the interface seems simpler than making a properly developed interface to a database from the getgo. Then you get locked into the solution, etc.
This was definitely a problem at my old job. They wanted to create a payroll sheet to keep track of hours, and the easiest way to do it was via a spreadsheet. I was the most programming-savvy person there (heh, you can already smell their doom), at so, not having any database training, I created a really suped-up spreadsheet that handled it for them. It was GREAT, until we had a work situation in which some people worked past 12:00 at night. At that point, people's total shift hours came out negative. We got it fixed eventually, but it involved some really nasty calculation, and it was a problem that could have much more easily fixed if it'd been done by database from the start.
More nonsense brought to you by someone who wants to be paid to solve the "problem". My guess is that these so-called spreadsheet errors are essentially random, and that for every error that leads to a bad decision, there's probably one that mistakenly leads to a good decision.
abuse of spreadsheets
Is it just me, or is that just a wee bit breathless, from an analytical point of view? I doubt that even "misuse" really even has the right connotation, here. More like, misuse of math.
Don't disappoint your bird dog. Go to the range.
If I undercharge due to a spreadsheet error, then I'm out $N, but someone else is $N better off.
If only there were some consulting company, someone who I could call to help me implement some best practicies, to help me avoid these tragic errors. Do PWC and KPMG know anyone who can help?
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... )
Of having people working on these problems?
We address the cost of every little thing in this culture. How much do adverts bring? How much does this job bring in? How much will it cost to clean this chemical spill up? How much does it cost to treat our employees better?
There's a bigger question these businesses could ask: How much would we save if we just shut our doors now?
During the Microsoft DoJ lawsuits, I wondered why MS just didnt close up shop, rescend all the EULA-licenses and just quit. Why would you deal with legal harassment when you can just take the profits you already have, and just shake your hands and say "Too bad"?
Oh well, I guess Karl Marx was right about his looking at Capitalism: He said it would continally be vigilant about 'lost profit' and cut all costs so effectively in that the people would be paupers.
Just think, does 10 Billion actually sound right, or does it sound like every other 'computer number cost' associated...
Costly Compliance: As the full demands of complying with Sarbanes-Oxley become clear, companies are increasingly calling for help to rein in the costs.
"It's only a three-sentence section tucked inside the 64 pages of the Sarbanes-Oxley Act--that sweeping piece of federal legislation aimed at curbing corporate corruption in public companies--but Section 404 is costing corporations at home and abroad billions of dollars in compliance costs and missed business opportunities."
I am an excellent proofreader. Pay me $10K per month to proof all of your spreadsheets. My job will pay for itself!
I hate business-speak so much.
Just say "depending on the importance" or something simple like that.
Plain-speaking will save us all =)
Did they run the numbers through a spreadsheet?
FUCK, you're so fucking DUMB. You're fucking fired. Get out.
For the software errors, do they mean the problems listed here?
A firewall can not protect you from yourself. Turn off what you do not need. Do not use the firewall to do your work.
The scary thing is the suggestion that the IT department should take over spreadsheets. Many people use a spreadsheet for applications that would better be served by a database with the appropriate front end and back end, or a dedicated software application. This article mentions managers specifically, but lots of employees whip up a spreadsheet and throw in some macros then find that the spreadsheet grows to a point of some real usefulness.
It's when the spreadsheet becomes useful that people realize it's not scalable (maybe they don't use that word, but I do) and can be tough to maintain.
Not to single out IT departments in particular, but I think the reason that these spreadsheets start up and grow is specifically that it's often difficult to get someone in another department to understand your needs well enough to make the tool that you really need.
Today managers can't fund a good solution because their budget doesn't allow for the necessary development. Tomorrow they won't be able to afford to get the support they need to get a spreadsheet done.
I don't have a great solution outside of better training for people on how to make spreadsheets that serve their needs.
more of the same on Twitter.
A spreadsheet is really a simple language. Toss in the horrid formulas Microsoft, err, innovated from Lotus 1-2-3, and you have a programming environment.
It's axiomatic that every nontrivial program has at least one bug.
Are 10% of spreadsheets trivial?
Raise your children as if you were teaching them to raise your grandchildren, because you are.
I don't get his point. Of course there are going to be some errors within the spreadsheets. Does this mean people are actually losing money because of it? The benefit that they provide far outweighs its disadvantage.
Portland, North Dakota Puppies
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.
And how were these data collected?, and the conclusions therein derived?
A spreadsheet, perhaps?
I have problems digesting theRegisters style of self-important braggart journalism. Its more than just juvenile sensationalism, its downright irresponsible reporting. Stick to the fucking facts and shove the opinion.
[TheRegister Article Summary]
Rampant KPMG/PWC cronyism blames spreadsheets for mangerial incompetence rather than the managers. Shock Horror!
[/TheRegister Article Summary]
Whosoever put this up should be spanked. This really smacks of a strawman argument. Consider where we would be if we suddenly dumped spreadsheets and went back to calculators. Consider that spreadsheet errors might really just be the poor implementation of lackluster business practices by poorly trained or less than motivated workers? But wait, isn't that just called 'business'? This reminds me of how big a buzzword Business-to-business was some 5 years ago. And then people started to realize how redundant this was. Afterall, B2B is really just business afterall. So why blame spreadsheets? I blame President Bush.
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
One man's Funny is another man's Offtopic.
Although spreadsheets can contain costly errors, so can programs written in any language. I would argue that spreadsheets are a very powerful IDE for a wide class of small problems and can more easily create software with lower rates of errors than other "language oriented" approaches to software development.
The reason spreadsheets provide superior debugging versus language-based software is that they instantly display the intermediate results of the formula every time the inputs or formulae change. Change one number in the inputs and the programmer can instantly see the intermediate and final calculations and do a visual sanity check on the results. In contrast, language-based software creates several impediments such as a manual edit-compile-run cycle, manual/isolated debugging statements, and few easy ways to visually monitor all the values of all the intermediate variables.
Don't get me wrong, spreadsheets have some severe limits. First, they can provide too much power to developers with too little experience/competence. If the developer is an idiot, they are more likely to be able to create a spreadsheet than a program, but just as likely to create (and not find) serious logical error. Programming languages, to some extent, create a barrier that blocks morons (not always). Second, spreadsheets don't scale to large/complex problems very easily. Some of this reflects the monopolist state of the spreadsheet market -- the lack of competition for Excel means that it has not substantively improved in the last decade. (e.g., why is Excel still limited to 256 columns?!?!?).
Two wrongs don't make a right, but three lefts do.
This one has been known for a while, but perhaps the FUD associated with a number like "10 BILLION DOLLARS" (said in appropriately Dr. Evil-ish fashion) could get some attention.
Spreadsheet functionality enables people to bury calculations and they become legacy tools within departments. They are like some of the worst spaghetti code. Someone who may be a serious spreadsheet jock develops a neat tool and it gets implemented in his/her department. The jock leaves, but the tool stays and continues to be used, despite the fact that no one left really knows how it works. Even assuming that there are no errors in it, as circumstances change, the spreadsheet might not produce the "correct" answer, but everyone accepts the answer produced by the legacy spreadsheet because "that's the way we've always done it." And, should someone attempt to modify the spreadsheet, they could get bitten by buried or misunderstood calculations.
Also, spreadsheets enable executives to embed assumptions and play "what ifs" with their forecasts, which is good. But then they use the scenarios they like best to get their pet projects approved using some rather suspect forecasts that "must be true because that's what Excel says the results are."
Spreadsheets are valuable tools, but, like any tool, you can get bitten if you don't really understand what you're using.
John
"The plural of anecdote is not data."
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.
"It has been over a decade since the last innovative new spreadsheet - Lotus Improv. Time for something new."
:-)"
http://www.codecomments.com/message1511409.htm
"- 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
How do spreadsheets cost companies money?
... which gets munged or erased due to a
Just about everything in many companies is tracked on spreadsheets. Expenses, costs, estimates, budgets, projects, etcetera so on so forth.
Often times, employees will use spreadsheets when a database (even Access) should've bene used. As soon as the spreadsheet becomes 'mission-critical' and contains information that is used to run the business and cannot be lost, you'll start to see employees whose sole job is to feed, maintain, and munge that spreadsheet. When data's in a format like Excel that can be shakey, you can see data errors start to build up when one page is dependent on another page which is dependent on another page which is dependent on some figure buried back in cell DA256 on Page 5 of the workbook... which is dependent on some other figure
And the worst part is that it's usually impossible to trace these errors back because there's no way to take a step away from it or a debug tool.
(How do I know this? I write custom software for small businesses that realize that they can't continue doing business the way they're doing it.)
--
Vote for your hopes, not for your fears - Vote Third Party
$1B for calculation errors $9B for cost of pc's thrown out of office window due to Clippy popping up.
And if slow, and mode costly, they did, by and large have some insight into both the data and what they were doing.
Now, less skilled people input the data and the spreadsheets themselves are rarely maintained, debugged and audited; no is the security of and version control of the spreadsheet-code seen as important.
No wonder bad business decisions are made with this, and with Sabannes-Oxley you can be sure internal auditors will start noticing and complaining.
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.
On two occasions I have been asked by members of Parliament, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
Why shouldn't a spreadsheet cause the exact same problems as entering the wrong number into a ledger? What is it about having a computer involved that somehow makes entering a wrong number more insidious?
Bad data propogates throughout any set of calculations, making the ones that come after....wrong. This shouldn't be a surprise to anyone.
Weaselmancer
rediculous.
Has anyone walked through a typical office lately? Doesn't it seem like everyone is working endlessly on spreadsheets? They all seem to be spending more time formatting things than actaully getting useful/profitable information out of them. I'm sure you have all been there, you give your boss an exquisitely formatted sheet and he wnats you to change it anyways. Check any class in Excel, the first thing they teach you is how to format, not the basic math and logic you need to create something useful. The problem with the $10-100K number is that someone probably formatted the number wrong.
"...and yet, I blame society" Duke - Repo Man
I can't believe how many times I've seen data that should be in a database instead of a spreadsheet. At my old company they would do monthly reports, and every month was a new spreadsheet. Because of this performace from month to month could not easily be tracked. If it were in a database, they could have looked at the performace over any time period.
Has anyone done research on how much money is wasted each year on pointless white papers by IT research firms?
I bet corporate America makes a lot more than $10B on spreadsheet "errors."
Sometimes I think Excel should be de-installed from corporate PC's. Excel allows well-meaning employees to create little piles or "silos of information" which aren't shared in any type of sane manner. Since the silos of information are not connected in any centralized manner, people are just guessing at costs, numbers and estimates. Using guesses to make key business decisions and can lead to very poor performance. Just ask the spreadsheet kings over at Enron and Worldcom. I wonder how many secret spreadsheets or silos these guys had lying around? However, thanks to Dick Cheney we may never know.
... I can see how spreadsheets can lead to lost revenue and bad decision making.
To illustrate how spreadsheets can kill, imagine you are working on a project proposal. Usually a typical Excel spreadsheet will get emailed, modified, emailed, modified, emailed, modified for weeks to many, many people. Until... one morning at 2:00AM, the day before the proposal is due, the final cut will need to be made. How do you know which spreadsheet contains the correct numbers? Maybe you needed to merge multiple master spreadsheets together. Maybe you were waiting on numbers from someone else. Have you ever been here? What do you do? What did you do?
Using Excel to manage a corporation reminds me of an old saying, "When the only tool you have is a hammer, every job looks like a nail." Instead of investing time into procedures and systems (to do things correctly and sanely), companies rely on managers with Excel to get numbers for proposals, projects, quotes, etc
There is a quote spreadsheet at my work that I have had the unfortunate task of dealing with. There are 14 tabs of information on it, many dealing with displaying the same information in different forms (cover sheet, billing sheet, materials sheet, build sheet, etc.) and there are multiple ODBC queries and links to other (some non-existant) spreadsheets.
Every quote that is generated is saved as its own file based on the part number being built and the customer. There are approximately 3500 different quotes that have been generated over the years.
My task was to pull certain fields of information off of different tabs in each of these Excel files and combine it all into a master "database" of information - once again in an Excel spreadsheet. The problem is that the data and presentation are not separate and the format of forms has changed over the years, so information is not in the same place on all of the files. I managed to do it in a couple weeks' time using VBA macros in Excel.
My pleas for a database solution went unheard.
One time I forced a database upon my management, and they still refuse to use databases for any new projects even after seeing some of the benefits of keeping the data and presentation separate. Keeping track of all the IT assets has been easier, though. When they asked for labels for all of the computers stating what the system specs of each machine were, they could be printed in less than 5 minutes, which was mostly spent designing the form to print the labels.
KPMG knows a thing or two or threeabout numbers not quite adding up.
>... $165,000 times 9 times 500. That amounts to just shy of three quarters of a billion dollars. And is that anywhere near realistic? No. It is probably safe to say that corporate America, for example, loses in excess of $10bn annually ...
Can You Spot The Spreadsheet Error?
Cell B1 = $165,000
Cell B2 = 9
Cell B3 = 500
Cell B4 = B1*B2*B3
Cell B5 = $10 BILLION
--- Attorneys Assisting Citizen-Soldiers & Families -
this is pure caca.. you know... shit
Remember the old days of Corporate Anthems?
chorus
KPMG, we're strong as can be
A team of power and energy
We go for the gold
Together we hold onto our vision of global strategy.
KPMG, we're strong as can be
A dream of power and energy
We go for the gold
Together we hold onto our vision of global strategy.
verse one
We create, we innovate
We pass the ones that are la-a-ate.
A global team, this is our dream of success that we create.
We'll be number one, with effort and fun
Together each of us will run for gold that shines like the sun in our eyes
chorus
KPMG, we're strong as can be
A team of power and energy
We go for the gold
Together we hold onto our vision of global strategy.
KPMG, we're strong as can be
A dream of power and energy
We go for the gold
Together we hold onto our vision of global strategy.
verse two
The time is now to lead the way
We share the same idea that may win by the end of the day
Our strength is here to stay
Identity, one energy, one strategy, with sympathy
These are the words that will lead us into our new world.
chorus, repeat ad nauseam
"You see, it's not us, it's the damn spreadsheets"
Never play chicken with a passive aggressive.
Spreadsheets and spreadsheet usage have "features" which we dropped from programming languages decades ago.
Unnamed variables. No comments. Uncontrolled side effects. No locality of function. Global variables (every variable is global). No obvious flow of control. No review.
Is anyone suprised they have mistakes?
If someone in my team wrote a program that had all the problems a typical spreadsheet did, he'd have his locker emptied and be standing on the pavement with a pink slip before he knew what was happening.
Spreadsheets are a great tool, but they are one of the easiest things to make mistakes with. Between absolute/non absolute references and the autofill tool, I can see how many mistakes would be made. Many people have probably gotten into the habit that dragging this down to their will perform the same calculation on the data using info from cell D5, while it really shifts location relative to the result location. I know I've almost messed up on a few physics reports w/ that, and if you did spreadsheets all day, I can see how you might not go over everything with a fine tooth comb to find problems.
In undeveloped countries, the consumer controls the market. In capitalist America, the market controls you.
Wont the error just as likely gain a company money?
From the article, 8th paragraph:
It is a simple sum: $165,000 times 9 times 500.
Not sure it's worth reading the rest.
Use 1000ml toothpaste dispensors, not the tiny 3oz ones.
Ohh but if they sell in bulk they reduce profits because people shop less and are locked in.
Id rather buy one toothpaste container lasting 6months thanks.
Either a big ass 20oz tube, or a push soap style dispensor.
Liberty freedom are no1, not dicks in suits.
Max Henrion What's Wrong with Spreadsheets and Raymond R. Panko What We Know About Spreadsheet Errors.
Test 1 2 3 4
Well this HAS TO explain where all of RIAA's profits are going....
Excel's habit of restructuring numbers as it sees fit has to be its worst feature. Particularly when it comes to how it handles opening delimited text files (read: database dumps).
The first hurdle is that you have to open the file the correct way (using File...Open) in order to have it even give you the option to open the file in a sane and controlled manner. But after you've cleared that hurdle, you need to remember to select every column in the spreadsheet and tell Excel to read it as "text" in order to keep it from, say, turning your part numbers into scientific notation, or mangling half the serial numbers in the list.
Wouldn't be such a problem except that not everyone who has to work on this kind of data is completely clueful or careful when it comes to such matters.
Even without accounting errors, I could see where simple UI hassles like this cost the business world many millions of dollars. Having to re-do an entire process because of a few truncated numbers is not an inexpensive thing.
On average, I get to fix up at least one project every year that has been fscked up because people have decided that spreadsheets are the only tool for storing and manipulating numbers or data.\
Most recently, we got to spend over a month repeating work because it had been fscked up in a spreadsheet. The biggest problem with spreadsheets is that the same flexibility that lets you drag numbers around anywhere you want, also lets you drag numbers into to wrong places with little or no warning.
Normalisation? 1st, 2nd and 3rd normal form? People need to know when to use FileMaker Pro instead, at least it warns you. Spreadsheets don't.
The problem with a spreadsheet is that the numbers never have any context, they are always just numbers. I'd love Open Office to offer something like FileMaker Pro, and tools to migrate spreadsheets into it.
"Everything is adjustable, provided you have the right tools"
At one company that I worked for about 3 years ago, a close friend and coworker had a "mistake" on his salary calculation spreadsheet. The error gave him an extra day's pay each week. It was if it was working a 48 hour week, when he only put in about 40 hours.
This $8000 a year pay increase went unnoticed for quite some time.... Lucky guy!
I made a couple spreadsheets recently, and here's what I found:
Fragile references
They're still referencing virtually everything by [A-Z]\d+. This is beyond GOTO considered harmful - when Dijkstra made that claim, we at least could do "goto blah" instead of "GOTO 2050".
Excel has a couple "solutions", neither of which are good:
Massive code duplication
In my spreadsheet today, I ended up with whole columns of formulas like this:
I would have much rather made a function FedIncomeTax(AdjustedGrossIncome) that applied that same bracket logic. Once. And called it the N times necessary. You can define VBA functions, but I didn't see a way to reference cells from them. (Probably because it doesn't have a reliable way to do the dependency/error tracking seamlessly. I can think of how I'd accomplish that in Python...but Python is a very flexible language.)
Unreadable code
There's no way to put longer bits of properly-indented, commented code in there. Certainly related to the above; you're trying to cram way too much stuff into a cell (or group of cells) that's massively repeated, so no one even thinks of doing this.
Poor layout
The result looks poor in a couple ways:
Poor charting abilities
It didn't have much support for charts with confidence intervals. (Don't tell me there's no use for these in finance! They may write everything out to the nearest cent, but that doesn't mean they don't made wild-ass estimates when talking about the future.) If you want to do something like a box-and-whiskers graph, you have to do elaborate tricks. Even basic error bars have weird defaults; to get a meaningful confidence interval, you have to do custom stuff with ranges. The friendlier check boxes end up with the same-sized error bar for every point, which is worthless.
Overall
Just using Excel for my small needs was frustrating, and it's not because I don't know how to use it. (I can read Help files.) I can easily see how people would screw up badly with them and not notice.
It'd be so much better if there were a more free-form document (no overal grid) you could throw 1-dimensional lists and 2-dimensional tables into. With support for formatting, referencing, and summarizing them well. (There shouldn't be [A-Z]\d+ references at all; the concept shouldn't exist.) Including the PivotTable stuff, of course. (Excel's one good point, though it could be better.)
It also should have support for referencing external data easily - a RDBMS or CSV/
We need to have a little talk about the TPS reports. If people were filing their TPS reports on time this wouldn't be a problem, mmmmm'k? So, I am going to go ahead and ask everyone to make their best effort getting their uh, TPS reports in on time, including the new coversheets. It would be great if everyone could that from now on.
Also, Friday is "kick the accountant in the nuts day". So, go ahead and kick Frank here in the nuts anytime Friday. All right. Any questions? Great!
Ken Olsen, founder of Digital Equipment Corporation, made this rant about 15 years ago.
http://www.csdassn.org/software_reports.cfm But of course nobody will use Gnumeric...
One that hath name thou can not otter
it's clueless fucking managers who think they can do the same thing in a bunch of spread sheets that an experience IT software engineer does in a real database.
If you mod me down, I will become more powerful than you can imagine....
trying to survive...
watch them die!
That has got to stop. According to both PricewaterhouseCoopers and KPMG, more than 90 per cent of corporate spreadsheets have material errors in them.
I'm not surprised they would say that, since PART OF THEIR BUSINESS IS GETTING PAID TO PREVENT/CORRECT SUCH ERRORS. In other words, it is in their best interests to tell businesses that their spreadsheets have errors in them, because they want you to pay them to fix them and thus "save" your company money.
Worse, estimates suggest that such errors costs between $10,000 and $100,000 per error per month.
While I have not read the original research, this seems like a misrepresentation. I suspect what they found was that was the SIZE of the average financial spreadsheet error. But it's completely wrong to conclude therefor that such an error translates into a cost for the company of the same amount. In most cases that $ figure is going to be buried in one of many metrics; it's unlikely to actually be in the revenue or profit line. Even if it were, it didn't really COST you that much -- it just mean you under-reported your earnings/revenues/etc. What is going to happen; you overpay a bill from a vendor? They are likely to catch that and it won't cost you anything.
Finally, the errors are going to be distributed fairly randomly -- they are just as likely to "make" your company money as "cost" your company money. Yes, every error is a problem that needs to be corrected, but any estimate of "cost" to the company is going to be very haphazard. Business that fails to be transacted because a number is too low is offset by business that gets transacted successfully because a number is too high.
Bruce
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.
A major factor in my switch to Macintosh as my primary platform was that I could run both perl and Excel on the same machine.
So you're able to install Excel, but being unable to download and install Perl drove you to the Mac? What business do you have evangelizing that platform? Choose your poison:
* Activestate Perl
* Cygwin
Two completely different ways of getting Perl on Windows.
Maybe if more geeks played with the spreadsheets we could come up with best practices to hand over to the PHBs.
This is entering the realm of a technical artist - somebody who knows something about the underlying math, but also has a good eye for presentation.
It has been over a decade since the last innovative new spreadsheet - Lotus Improv. Time for something new.
I agree that Excel is ancient, but there are still modern newcomers like Gnumeric or OpenOffice?
I hope you don't create spreadsheets for your company.
... so much it boggles my mind ... it's the worst meme to have ever inflicteed on mankind
Oh, "tab" for spreadsheet errors. I get it! Yuk yuk!
Quick - make a graph and a piechart , and you will be on your way to the top
...I obey the laws of physics....
.. it is about people using spreadsheet to run their business INSTEAD of properly designed application.
Sometimes it is stupidity but most of the time this is about general failure of today's IT to provide users with everything they need as far as application development.
So on average, one would expect the errors to largely offset, both within a single corporation and within the economy as a whole. I apologize for the anonymous tag; just starting to use this system and I don't have time to sign in.
I was going to do this set of reports for this client in Crystal Reports. They initially said "Yes" but changed their minds when they found out they couldn't change the values. The client then asked for the reports to be in an Excel spreadsheet so they could "correct" the values.
Some errors aren't accidental.
What are the annual losses due to flipped bits? Ones that no one notices? Do these losses cost more than the apparent savings of buying cheap PCs?
-- Microsoft is the most expensive commodity operating system and office suite vendor in the marketplace.
Has anyone already calculated how much money is lost every year on taco-related restroom breaks?
...
Some statistics are better left unchecked
I was going to write a post like yours, but didn't have the energy. Thank goodness someone gets it! Yes, bad math skills, bad software skills, and human error are partly responsible for the mistakes, but most of the posters seem blind to the major lossage of spreadsheets. Basically, making spreadsheets is a simple form of programming--in an awful programming language. To your list, I would add that there's no way to get an overview of the program, because it's composed of many little programs, each in its own box; and there's no way to debug. I also think the solution is along the lines you describe, which I've often thought of writing....
The evaluation of an action as 'practical' . . . depends on what it is that one wishes to practice.
when I supported conglomerations of nested spreadsheets for Bank of America's Microstar cash management software customers. We had customers with fifty linked spreadsheets - totally idiotic.
Spreadsheets should be used for one thing - adding up your personal expenses.
Everything else needs a database and a good reporting tool.
Companies who rely on employees using spreadsheets to manipulate mission critical numbers are just asking to end up like Enron and WorldCom - guilty or not.
Richard Steven Hack - This sig is TOO GODDAMN SHORT TO DO ANYTHING USEFUL WITH! MORONS!
The problem with any automation is that you can effortlessly repeat the same task, including repeating the same error.
I used to work IT for a financial firm that, on their heaviest portfolio "rebalancing" days, could move NASDAQ almost 1 percent... Some of the internal workflow and accounting was done with spreadsheets... The problem was that the staff would occasionally end up breaking the spreadsheets during normal use, but not realize what they have done... And since they're simply acting as "pencil/paper pushers" during that part of the day, the errors don't get caught until much later (if ever) when the clients call and notices an error...
And then there was the guy working there who double checked in excel spreadsheet... with a calculator!
It's worse than that. The statistician takes a common problem, makes a few guesses, comes up with an exorbetant figure, and throws it out the window to use a bigger guess by a factor of 10
So how much money is the Fortune 500 wasting annually? It is a simple sum: $165,000 times 9 times 500. That amounts to just shy of three quarters of a billion dollars. And is that anywhere near realistic? No. It is probably safe to say that corporate America, for example, loses in excess of $10bn annually through the misuse and abuse of spreadsheets. That's a big number
A very big number. I wonder how such a large number was pulled out of such a small opening.
The ______ Agenda
http://shit.slashdot.org/article.pl?sid=05/04/24/1 218239
Just boggles the mind.
"So how much money is the Fortune 500 wasting annually? It is a simple sum: $165,000 times 9 times 500. That amounts to just shy of three quarters of a billion dollars. And is that anywhere near realistic? No. It is probably safe to say that corporate America, for example, loses in excess of $10bn annually through the misuse and abuse of spreadsheets. That's a big number: it suggests a problem worth managing."
Translated: If I take the actual numbers I have, it's a $750mil problem. By some magical feat, however, we can just assume that it's a $10bn problem because it makes my article seem MUCH more important.
How can you possibly just increase the number THIRTEEN TIMES just to suit your needs. Show some integrity.. seriously.
Turn s60 photos into awesome videos with mScrapbook for all S60 3rd edition phones!
A programmer would be chastised for: cutting and pasting code; for not using revision control; and for obscured or hidden behaviour. Yet spreadsheet users commoly have all 3 problems.
The problem is that spreadsheets have become increasingly complex without a corresponding adjustment to formal specification. Imagine if you were using a programming language that doesn't have formal type declarations.
The are tools that assist businesses using visual modelling (much like scientists use) that can help detect and prevent errors in spreadsheets. Have a look at...
XempleX or their product sheet Xemplex Product Overview.
p.s. I'm not affiliated with this company.
The ease of spreadsheet creation is the problem, not the solution. Yes, it allows non-computer-literate managers to create an analysis of a particular problem... but that analysis is often flawed, and it is nearly impossible (for any non-trivial spreadsheet) to figure out where the problem really lies.
I have met several people who claimed to be "computer experts" based solely on their Lotus 1-2-3 / Quattro Pro / Excel expertise. It's all well-and-good to create a spreadsheet; but just like computer programming, you need some sort of development and quality control methodology. Too often (like, in say 99.999% of the cases) there is a single user creating a single spreadsheet that eventually controls some aspect of the way a business is run. There is no quality review; there is merely a, "yeah, that number looks right" phase.
I've seen it too many times. It's endemic in business. I'm not surprised with the results of the study ("Spreadsheets considered bad"), though I'd rank the monetary valuation right up with the report I read 2 years ago, "Slow modems cost US businesses $4B yearly!").
Microsoft is to software what Budweiser is to beer.
Anyone employing Excel for any statistical calculations should get a nice chill from reading any of B.D McCullough's papers on Microsoft's egregious (and mostly uncorrected or corrected badly) errors in this area.
Click here for a link to one of his recent critiques entitled:
On the accuracy of statistical procedures in Microsoft Excel 2003
Here is a nice quote from the above paper:
"...persons who wish to use Excel for statistical purposes should exercise extreme caution...Persons desiring to conduct statistical analyses of data are advised not to use Excel 2003."
"Excel is like a bikini. What it reveals is suggestive, but what it conceals is vital." -- apologies to Aaron Levenstein
I work in a fortune 500 company and recently submitted a capital project for arround 2 million dollars.
The company has an excell form to fill out so corp can evaluated the projects equally.
I input costs and benefits and the spreadsheet spits out depriciation and return on investment.
I can double and triple check my costs and benefits, but corp's calculations of deprication and ROI are a black hole to me and one I can not change. If there is an error, I can not catch it.
If I make a mistake on quoting an item and the cost rises 2 percent does that equal a calculation that favors cost savings over increased profits?
Figures DO NOT lie. Figurers DO.
This cost is nothing compared to the price of human suffering from the misuse and abuse of Powerpoint presentations.
...$10 billion dollars is wasted every year in the IT industry by people reading brain dead slashdot headlines.
America loses in excess of $10B
this should be excel.
The way to avoid it is to insist upon proper training for all "managers" who's function includes whipping up a spreadsheet...
The problem is most of them actually consider themselves to be competent and that training is beneath them. The other problem is the assumption that because it's a computer doing the calculations, then it can't make mistakes... but mistakes in the programming in the spreadsheet itself can only be found the hard way by inspection or the use of test data for which the results are already known.
Donald 'Duck' Dunn: We had a band powerful enough to turn goat piss into gasoline.
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.
Spreadsheets that don't calculate. Powerpoint presentations that dont communicate. Word documents that delete whole directories of company reports.
What next? I get locked out of Access? My Briefcase falls apart and spills My Documents on the floor?
I hate this kind of reasoning. It comes up in poliics. "This measure will create thousands of new jobs in our area". "No, this measure will cost the regional economy hundreds of millions of dollars!" Yeah, like those aren't exactly the same statement. Even if spreadsheet errors really do cost real money (which is unsupported), you can't just add them up.
As far as spreadsheets go, no serious company of any consequence makes serious decisions on the basis of spreadsheets as used by the average spreadsheet jockey. Spreadsheets are ok for back-of-the-envelope calculations and quick graphics. They're definitely the very oppposite of a proper software develpoment environment, though, and any business that uses them in the decision making process can't expect more than they get.
Please feel free to send me 10% of what you were going to spend on that "spreadsheet consultant" (smirk) for this advice. I suggest you use the rest to hire a good coder and replace any mission critical spreadsheets (snort) you might have, like soon, okay?
mt
It seems to me that everyone else can pick up a few basic software engineering practices to tighten up the quality of their spreadsheets. Things like:
Code reviews - give your spreadsheet to a buddy to check over. Have him or her review all the calculations. If you haven't explained them well enough, add comments.
Change control - if you update your spreadsheet, keep old versions around and make a note of what you change. Find all numbers that total up different in this new version and explain why.
Etc. etc. etc. I'm not saying that we should treat everyone else like programmers, or spreadsheets like programs. But a few simple practices like that and your PHB gets to claim that he personally saved the company $10B (and fixed the Internet).
A company should have some kind of unification to both its information policies and procedures. When John Q. Manager/Employee can just whip up anything on a whim, I could see this easily ending up in a big mess. What if, at some point, all of the data have to be consolidated, but the means to collect and manage them have been implemented in 10 different ways? Could be a real problem.
Major in math, not business.
How did the writer come to the conclusion that all the errors cause LOSS ?!!
From the same data we could say that America is GAINING billions of $ each month because of worksheet errors...
And the value of 10k$...100k$ per error is ridiculous.
Hey, I'm trying to find the most arrogant post ever, and I can't. Where is it?
Yes! I have the proof that 90% of spread sheets
:-)
have errors. I have a spread sheet that proves it!
Also, 87.6% of all statistics are made up!
return 0; }
"Not to single out IT departments in particular, but I think the reason that these spreadsheets start up and grow is specifically that it's often difficult to get someone in another department to understand your needs well enough to make the tool that you really need. "
http://web.media.mit.edu/~lieber/PBE/
"What is Programming by Example?
Often, in computer interfaces, users wind up doing the same or similar sequences of operations over and over again in different situations. But if computers are so good at repetition, why is it that the users are the ones who keep repeating things?
Programming by example [or "programming by demonstration"*] is a technique for teaching the computer new behavior by demonstrating actions on concrete examples. The system records user actions and generalizes a program that can be used in new examples. "
Accountants do things differently however, but I suspect they are all taught to do some sort of bounds checking, they just don't all do it.
"The good news is that spreadsheets let people who aren't programmers do all kinds of fancy calculations on a computer. The bad news is that spreadsheets let people who aren't programmers do all kinds of fancy calculations on a computer."
So by that logic. Then PHB's have to hire a mathamatician every time they want to use their pocket calculators.
'nuff respect to all junglists: KPMG song - jungle mix
Backups are for wimps. Real men post their data in comments and have slashdot mirror it
how about configuring the spreadsheets (if using excel) to use pivot tables as it will reduce your chances of getting errors as source data is already in your main database.
Live your life each day as if it was your last.
There is a family of genes called the septins the gene symbols for these are SEPT1 SEPT2 to SEPT11 which gets converted to dates. Some Accession numbers get converted to floating point. here is a link to the paper
suppose i make a mistake of 100 EURO. Say that I overpay a bill for that amount. Would you not agree that somebody else is about to receive 100 extra? In other words. The 10B amount is only correct if all that money flows away from the US or at least from US companies...
It is the same kind of c**P that you hear on the coverage of the economy. Investors lost a gazillion dollars today when stock X closed lower. Bullocks! some other investors got their stocks a lot cheaper.
Funny guys them beancounters...
maybe the American lunar expedition did not leave Hollywood at all.
Its wierd how the word "fraud" appears in the title, but the article has only one passing mention of fraud.
It makes me want to dismiss the article as management baloney-- as though some editor thought the article would attract more attention if they suggested it was about FRAUD.
This isn't a small problem. It's a massive problem where the use of spreadsheets, rather than a well designed database and ui, that's become part of the culture in many of the companies/institutions that I've worked with/for.
These thing end up as huge monstrosities which are shared across an enterprise over the network and which have been cobbled together by folks with little or no experience to make their jobs easier and have then become inherited by others and then spread viral like around the organisation. In many cases the reasons are not just because excel is easy to use to create a small app, but also because the organisation prevents the use of other apps which might be relevant for the development of small shared databases (Access cough cough!!!).
It has driven me to despair to see the way some of these spreadsheets are being used and managed.
Absolutely! Perhaps spreadsheets are like Flash, a good technology except for the fact that it lets too many idiots inflict stuff on the rest of us.
But this study is deeply flawed because all it does is document some of the costs of some of bad spreadsheets. If we are to do a full accounting of spreadsheets (both benefits and costs), then we need for consider more than just numbers of "bad" spreadsheets out there.
I would contend that for a broad class of software-addressable problems, a spreadsheet provides the fastest development time and the least total number of logical errors. Without spreadsheets (or some novice-usable software development environment that creates this problem of logic errors), then we face the problem of delegation of a complex programming task. If a manager needs a piece of software and they are not allowed to use a spreadsheet, then they must explain what they want to a professional programmer. This process is also fraught with error and would also let us write one of these "Businesses lose $X billion to errors" papers.
In fact, i'd wager that that error rates in a non-spreadsheet world would be higher for four reasons.
I do agree that spreadsheets and spreadsheet-creating processes could and should be better. Quality control, testing, and better review processes should be used to ensure that a simple little spreadsheet does not cause thousands or millions of dollars in errors.
Two wrongs don't make a right, but three lefts do.
Heh, it doesn't matter what you do to the song, it never sounds remotely cool!
I've been a temp for almost a year now, and I've seen a lot of offices and a lot of spreadsheets. The thing I've seen in common with most of them is that they usually are things that would be better served by databases. The problem is that databases have no obvious real-world analog that the average computer user can understand and implement properly. Spreadsheets, though, they get.
So when it comes time to do a report that they do every quarter and which have the same formulas, they just duplicate the worksheet on their excel spreadsheet and enter the data, or just throw in another column.
They're not shared easily, they're not protected easily, and it usually involves retyping information that's already in some computer format into excel (good thing too, because they usually hire a temp to do it!).
Perhaps the author plugged a few numbers into a spereadsheet to come up with these results :-)
(1) One easy mistake to make with spreadsheets is to use an incorrect range of cells (maybe the range was correct in a previous version). For example, a cell computing total profit may have the formula
"=sum(a1:a100)"
which works until transactions spill beyond cell a100. A properly written program in C++/Java/whatever would be written to work for any reasonable number of transactions.
(2) To speed up parts of a spreadsheet one sometimes turns calculations off. Forgetting to turn them on at the right time can cause subtle errors.
(3) Excel LINEST (linear regression) outputs the regression coefficients in the REVERSE order of the columns. This insane decision trips up many users. Does the spreadsheet in OpenOffice get this right?
(4) The row and column limits cause people to break up their data to fit into worksheets, which is awkward and error-prone.
(5) A common defect of spreadsheets I see is that the someone doesn't know or doesn't use VBA, and instead constructs indecipherable formulas.
(6) Big spreadsheets tend to get unstable and crash mysteriously, in ways that are difficult to debug.
(7) Spreadsheets tend to be copied and modified many times, leading to much duplication of any VBA code contained inside. When using Python, for example, my code is in a directory, and I don't duplicate code in order to reuse it.
Probably some of the problems above can be avoided by using "best practices" with Excel, such as creating Excel add-ins, but my colleagues and I often don't. They are not programmers and do not seem to realize that there are problems with how they use spreadsheets.
People can use stretch spreadsheets beyond their limits, but they are very useful for small and medium-size problems.
Must be something deep in the mindset of an engineer because it seems to be their default format for all interaction with a computer. And it drives me fscking nuts.
No, in my experience, its usually an engineer, manager, or marketing person who wants to pretend that they are statisticians and the basis of the one (or two (whoo-whoo)) courses they have had in statistics and research methods.
It's amazing how PWC and KPMG manage to create a market for themselves - the validation of spreadsheets. And it's amazing how this rather huge (and in my opinion concocted) figure is now quoted in many news outlets. There's no way the costs for spreadsheet errors are in this order of magnitude.
What we're seeing here is a really well executed PR maneuver by consulting companies.
figured out a way to explicitly blame this on Microsoft, and then proclaim that the Linux is the solution.
It has been thousands of years since the last innovative new load carrying device - the wheel. Doesn't mean it's time for something new.
No need to get personal, Bob; it adds no force to your argument.
The last cell's value is completely unrelated to the preceding calculation. That is an error.
To fill a spreadsheet with fancy calculations and then, at the end, simply stick in the desired answer, is to create phoney data that is all the more pernicious for pretending to be supported by the previous calculations.
Thank you for your concern about my business, but I am quite profitable in part because of high scepticism about unsupported calculations. Remember the silly estimates about Y2K disasters ... another real problem that was grossly exaggerated? This article uses similar tactics.
--- Attorneys Assisting Citizen-Soldiers & Families -
Btw, my opinion of the Y2K incident, based on 39 years in the field, is that it wasn't "silly." We anticipated the problem, made a roughly-accurate estimate of the consequences of not fixing it, and then we fixed it. That's why the predictions didn't come true.
It shouldn't. Being unable to edit posts is one of the charms of slashdot. You can no more edit a post than you can edit a sentence you said in Real Life.
Besides, imagine the confusion, and bring karma and moderation into that mess.
For large sets, this will be our guide even unto death, for the LORD will work for each type of data it is applied to...
Its not math you are talking about - its arithmetic
I've wasted a lot of money in my life, the rest I spent on motorcycles and women.