Forgot your password?
typodupeerror
Software Businesses IT

$10B Annual Tab for Spreadsheet Errors? 305

Posted by Zonk
from the thats-a-lot-of-pennies dept.
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 discussion has been archived. No new comments can be posted.

$10B Annual Tab for Spreadsheet Errors?

Comments Filter:
  • 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?
    • by appleLaserWriter (91994) on Sunday April 24, 2005 @07:33PM (#12332558)
      Spreadsheets aren't costing money, any more than pencil and paper costs money. It is the bad math that costs money.

      I'd bet a LOT of money that fewer mistakes are made with spreadsheets than by people who think they can do perfect math in their head, or perfect long division or multiplication on paper.
      • by Klivian (850755) on Sunday April 24, 2005 @07:55PM (#12332696)
        >It is the bad math that costs money.
        Wrong, it's not bad math it's wrong use of math. It's more the case of using wrong models to solve problems.

        >fewer mistakes are made with spreadsheets
        That should read, more mistakes are made faster with spreadsheets. Take a simple example like a spreadsheet to calculate the cost of some project. Lots of places they use a template, filling in some values and the spreadsheet does the rest. Small mistakes in the template can become seriously expensive when all is accumulated.
      • The article isn't about spreadsheets vs. pencil and paper; it's about improperly constructed spreadsheets and abysmally poor management of them. The plan may be perfectly good, but structural errors (such as a row inserted by somebody who "knows" it needs to be included, but doesn't know that the simple "fix" alters a critical cell address for a formula, and thus doesn't fix the formula) can have an indeterminately large effect throughout a model, since everything dependent upon that value will be in error
    • One little typo and "Okay, so that means you owe me.... 10,000,000 pounds" becomes "Okay, so that means you owe me.... 19,000,000 pounds"
    • by dykofone (787059) on Sunday April 24, 2005 @07:44PM (#12332639) Homepage
      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.

      • by MichaelSmith (789609) on Sunday April 24, 2005 @08:25PM (#12332847) Homepage Journal

        The big problem with spreadsheets is that they are increasingly being used to implement software, but that the architecture they provide (a matrix of expressions) makes it almost impossible to validate the code.

        If the CFO of $COMPANY produces a spreadsheet demonstrating that all is well with the company finances then it is difficult to prove him wrong.

        This may be what went wrong with companies like Worldcom. They could have had one spreadsheet for insiders and another for auditors.

        • by yota (165006) on Sunday April 24, 2005 @08:35PM (#12332891)

          The big problem with spreadsheets is that they are increasingly being used to implement software, but that the architecture they provide (a matrix of expressions) makes it almost impossible to validate the code.

          ... which is something which companies like Pwc and KPMG started, with their quick and dirty approach to consulting, in order to save time and margin! It's the same with the PPT slides, which now have took the place of all reports but with way less informative content.

          Looks like they could have found a new line of business: give professional advice how to solve the problems generated by their professional advice, whoops... this is the old consulting business model!

          Andrea

          • You've got to be young, because if you were old enough to remember VisiCalc, you would never have thought this.

            Most spreadsheets are made by the people on the ground-- secretaries, low-level managers, clerks. That's most of the problem right there; these folks tend to poke around randomly at a problem until they get something that "looks okay," and then forget how they got to a solution and just use it. God forbid that anything should change.
    • by vegaspctech (769513) <vegaspctech@yahoo.com> on Sunday April 24, 2005 @08:01PM (#12332731) Homepage Journal

      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.

      • by 2grhms (653219) on Monday April 25, 2005 @01:59AM (#12334167) Homepage
        This is not bad journalism. It is a serious article about a serious problem. Take people who have never written a program and have never heard of program-design-101, give them a huge collection of poorly documented functions and tell them construct a large complex program to calculate a number. Just how much would you trust that number? It is not hard to imagine what the resulting spreadsheet looks like. Now suppose that number is, say, the value of a mine, or road, or company. If your number is too low, you miss a great opportunity; if your number is too high, you buy the asset and subsequently lose a lot of money. In either case, the loss is serious money- hundreds of millions or more.

        I have spent the last two and a half years auditing spreadsheets for (1) complex financial transactions and (2) models for large public infrastructure projects. I work with a dozen other rocket scientists and actuarial types who specialise in this. My experience is consistent with "some professor from Hawaii", namely Ray Panko [hawaii.edu] who is the world expert in the field. Almost every worksheet of every model I have audited, has been riddled with potential and actual errors- and these spreadsheets are written by professionals and have been already reviewed internally. Auditors like KPMG and PWC are interested in whether an error is "material", i.e. big enough to effect the client's ultimate decision on whether to proceed at a given price. The sample size of 54 is large enough to give overwhelming evidence of the large number of errors, and of the proportion of such errors which are "material".

        All software has bugs when you write it. Reviewing your code, peer review, formal testing, code reviews help you reduce that. Even with this, how much released software is genuinely free of errors? I think perhaps TeX is. With spreadsheets, it is hard to write clearly and simply, it is hard to review, it is hard to test and you have no comments. There are going to be mistakes, and lots of them. If you are not seeing them, it is only because you are not looking. To make a spreadsheet (or any software) without errors you need to approach the problem like NASA. This, of course, requires a budget like NASA or a horde of open source zealots, and so PHBs and accountants need to decide when the cost of detection balances the risk of error.

        john@xq.se
    • by Anonymous Coward on Sunday April 24, 2005 @08:15PM (#12332795)
      Rule1 - Never! Never! Never! put your busness on a speadsheet.

      Rule2 - refer to rule 1

      There is nothing wrong with speadsheets and they are very usefull for analysis but what happens is people try to use them for everything and eventually you have a spreadsheet that is used as a company database.

      Yes I am aware you can lock a spreadsheet but how long before someone (usually a manager) makes a "special" change and before long all sorts of "special" changes occur and things start to get rapidly out of synch.

      A simple analogy is how may people have ever seen simple Unix groups work really well, now take that one step further to ACL's and it starts to get interesting. This is particularly true when you have many people wanting to make changes. The poor Sys Admin can only duck and run for cover.
  • Oh wait (Score:5, Funny)

    by Anonymous Coward on Sunday April 24, 2005 @07:28PM (#12332533)
    That's a $10 Annual Tab for Spreadsheet Errors. Misplaced a decimal!
  • Ummm (Score:5, Insightful)

    by Anonymous Coward on Sunday April 24, 2005 @07:29PM (#12332535)
    Documents have typos. Film at 11.
    • Re:Ummm (Score:4, Interesting)

      by Stevyn (691306) on Sunday April 24, 2005 @07:46PM (#12332651)
      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.
      • Re:Ummm (Score:5, Funny)

        by sphealey (2855) on Sunday April 24, 2005 @07:57PM (#12332703)
        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.
        Not to mention that he probably used a spreadsheet to calculate those statistics...

        sPh

      • 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.

        • Ohh but if they sell in bulk they reduce profits because people shop less and are locked in.

          Knowing them, they'd charge more per unit and count on the customer not to check, Sad thing is, they'd mostly get away with it.

      • Re:Ummm (Score:3, Funny)

        by Moofie (22272)
        Except in this case, they're trying to scare people into paying KPMG to make them more spreadsheets.

        And I guarantee that getting KPMG to make errors for you is going to cost you more than $10 grand.
      • by cgenman (325138)
        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 mi
      • Re:Ummm (Score:3, Insightful)

        by darkonc (47285)
        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.

        I'd put it another way: Loss compared to what? If a spreadsheet saves a company $100K/month, but an error in the spreadsheet costs $10K/month, then the spreadsheet is still doing the company $90K net on the profit side of the ledger.

        Yes, it'd always be nice to kill that $10K bug, but until you do, you can still write it off as a cost of doing business.

    • Re:Ummm (Score:3, Insightful)

      by Baki (72515)
      The difference between a spreadsheet and an ordinary document is that a speadsheet is a kind of programming environment. End-users who think they can do the same job as professional software developers often build monstrous spreadsheets full of formulars, macro's and some VB for excel.

      I work at a large bank, making software to support the investment strategists. Often we find such situations where some strategist has built his own "program" using spreadsheets and sometimes some access "database". And in 99
      • Re:Ummm (Score:3, Insightful)

        by Angostura (703910)
        I'm sorry but this really annoys me, and I'm ever so glad that I'm not a user working a department that you serve.

        I love the implicit sneer in your scare quotes around "program" and "database".

        It's not surprising that investment strategists want to play around with their own models and investigate data on their own. It's part of what they are paid to-do.

        It seems to me that one way forward here would be to provide them with the Excel export that they want, but before it is enabled, the strategist has to a
  • by Anonymous Coward on Sunday April 24, 2005 @07:30PM (#12332538)
    people make mistakes.... it costs money.... next please?
  • GIGO (Score:2, Insightful)

    by OverlordQ (264228)
    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.
    • Re:GIGO (Score:3, Interesting)

      by kaiser423 (828989)
      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.
    • by rokzy (687636)
      we need a "spreadsheets make people stupid" like with powerpoint.

      at my work we use linux, but the retarded admin staff need to use windows and office for their crap.

      need to email people a list of something? they reach for Word. want a 2-column list? Excel!

      ffs. openoffice or my mac can deal with them fine, but it's the same sort of retardedness as HTML-emails. it's like proof that god exists and he doesn't want us to be happy using computers.
    • The point of the article is that spreadsheet errors are a problem that is being overlooked, and that oversight is costing businesses a lot of money. FTA:
      The real problem, of course, is that business managers don't know that there is a problem (actually, lots of problems) with spreadsheets, while IT regards spreadsheets as falling outside its jurisdiction. So spreadsheet management falls down a hole in the middle.
    • Yep. I consult for an area company ... the problem with spreadsheets is they convince people who have no business doing that stuff -- that they can do it.

      The owner of the company had a complex set of spreadsheets that helped him evaluate his employees efficency. Turns out it was vastly underestimating EVERYTHING and making his employees look like dipshits because a bug basically doubled their time off each week (it was added twice). Nobody noticed -- in *TWO YEARS* of using the spreadsheet.

  • Other Losses? (Score:4, Interesting)

    by teh merry reaper (758071) on Sunday April 24, 2005 @07:32PM (#12332555)
    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?
    • Re:Other Losses? (Score:4, Insightful)

      by serutan (259622) <snoopdoug@geekazon . c om> on Sunday April 24, 2005 @11:43PM (#12333680) Homepage
      One thing the article also doesn't consider at all is what the error level was before spreadsheets were computerized. Spreadsheets have beeen around a lot longer than computers. A manual arithmetic mistake early on would propagate all the way to the bottom just like in Excel, but fixing it took a lot more work than changing one number.
  • Primary error (Score:4, Interesting)

    by Cow007 (735705) on Sunday April 24, 2005 @07:33PM (#12332562) Journal
    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:Primary error (Score:4, Insightful)

      by kaiser423 (828989) on Sunday April 24, 2005 @07:39PM (#12332603)
      Well, that matters on how you're using the spreadsheet.

      Also, if you're doing it by hand, how would that stop that error from propogating on down. I remember when I used to do tons of basic math by hand, little errors would still propagate through. At least with a spreadsheet, you can program in some error-checking logic.

      With a spreadsheet it's a lot easier to get the same answer multiple times rather than doing it by hand each time.

      There's nothing inherently evil with electronic spreadsheets. We had been using paper ones forever before then, and they had the same (and in many cases, worse) problems.
    • That type of logic applies to any data error rippling through any data system... not only spreadsheets. The problem in my opinion isn't the fallibility of spreadsheets, it's the misconception that anyone can "handle" them.

    • The whole idea of any algorithm is fundamentally flawed. Any input error into, say, a math equation can be propagated throughout the whole equation, giving a wrong answer!
    • The same can happen in a relational database. What solution do these people pose that is a better solution?
    • 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

    • Re:Primary error (Score:5, Insightful)

      by bcrowell (177657) on Sunday April 24, 2005 @08:11PM (#12332776) Homepage
      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.
      Well, that would be that case no matter how the calculation was done, wouldn't it? I think the issue is more that spreadsheets tend to be impossible to test or debug adequately. It's like spaghetti code without comments. Of course in a simple case, each column has a clear name, and the calculation flows nicely from left to right, and everybody understands what it's doing. But that's like saying that in a simple case, FORTAN programs flow from top to bottom, and everybody understands what they're doing.

      I know at least one not-pointy-haired boss (my mom) who has had major problems with spreadsheets created by employees that are flaky, poorly documented, or poorly understood.

      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.

  • by aendeuryu (844048) on Sunday April 24, 2005 @07:34PM (#12332563)
    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.
    • 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
  • by ScentCone (795499) on Sunday April 24, 2005 @07:35PM (#12332570)
    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.
    • 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
      • 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
  • by geophile (16995) <(jao) (at) (geophile.com)> on Sunday April 24, 2005 @07:35PM (#12332571) Homepage
    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?
  • yoy (Score:3, Interesting)

    by yagu (721525) <yayagu.gmail@com> on Sunday April 24, 2005 @07:35PM (#12332576) Journal

    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... )

    • You should see the process maps that some people in my company make with Excel. Yup, colored boxes and arrows with associated text all cobbled together in a spreadsheet!
  • 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
    • "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"?
      "

      1) because the cost of closing shop is very expensive, and cuts into profits.

      2) this move doesnt benefeit the shareholders.

      2) being the most important, since they control the company.
      • You have no clue what board meetings liek that could be like....

        for example...

        "The law is now harassing us and many of them want for our splitting and destruction. The best solution we forsee is that we literally lay everybody off, seel everything to reimburse our stockholders, and file bankurptcy proceedings. If we continue on this course we're on now, we possibly we split, and perhaps lose everything cause of judgements aginst us"

        It really comes down to a vote and what the 'stockholders could forsee to
        • MS didnt close shop for several reasons. First and foremost, many of the stockholders are MS employees, and they have pride in their company, and a personal investment in the company that gives them reason to not want to just give up. Second, it was clear that Microsoft thought it could win, and in fact, for all practical purposes, it DID win. Third, closing shop would require all outstanding shares to be bought by the company, at enormous cost, this is potentially money they dont HAVE. Fourth, and this
  • Attn: Companies (Score:2, Informative)

    by MrPoopyPants (146504)
    I am an excellent proofreader. Pay me $10K per month to proof all of your spreadsheets. My job will pay for itself!
  • For the software errors, do they mean the problems listed here? [csdassn.org]
  • by Metaphorically (841874) * on Sunday April 24, 2005 @07:43PM (#12332621) Homepage
    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.
    • Today managers can't fund a good solution because their budget doesn't allow for the necessary development.

      Well, that should be easy enough to fix. Lemme just add a coupla zeroes to this cell here, rerun this macro, and voila. The money's now in the budget... : p
    • 'Spreadsheet sprawl' is the problem and OLAP [google.com] (online analytical processing [wikipedia.org]) is the answer.

      Of course the whole 'best tool' addage applies, but the problem with that is that most people don't realize what sort of tools there are out there for centralizing information (ala data warehousing [wikipedia.org] - wikipedia or google directory version [google.com]).

      A bonus to these tools is that of the three that I deal with; SAS, Cognos and Business Objects/Crystal Reports, all have some sort of plugin for Excel whereby it can be linked to a

    • > Not to single out IT departments

      I will single out IT Depts -- On many occassions, I've seen IT actually fight to prevent users from using programming tools (even if it's just VB or Access). If the only programming tool on one's programmable computer is the Excel macro language, people have little other choice but to use it. Usually IT's reasoning is some "enterprise J2EE initiative" or "next years ERP implemenation" or some other phony politics.

      (And even if a poor luser gets his hands on VB, good luc
    • by Baki (72515)
      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 wou
  • Loses 10 Billion? (Score:2, Insightful)

    by tyleroar (614054)
    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.
  • by GileadGreene (539584) on Sunday April 24, 2005 @07:44PM (#12332637) Homepage
    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.

    • I am currently working on an internal project to improve and standardise the trace output from our software.

      Managers and team leaders who I talk to about it keep saying things like "If you do it that way I can import it into excell" as if that is going to solve all their problems.

      Unfortunately, .xls in the new language of management.

  • And how were these data collected?, and the conclusions therein derived?

    A spreadsheet, perhaps?

  • by Reignking (832642)
    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 :)
  • by G4from128k (686170) on Sunday April 24, 2005 @07:47PM (#12332657)
    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?!?!?).
    • by Tony (765) on Monday April 25, 2005 @12:40AM (#12333935) Journal
      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!").
  • by johnnick (188363) on Sunday April 24, 2005 @07:50PM (#12332668)
    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
  • Whoa slow down (Score:4, Interesting)

    by Illserve (56215) on Sunday April 24, 2005 @07:52PM (#12332676)
    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.

  • by SerialHistorian (565638) on Sunday April 24, 2005 @07:53PM (#12332682)
    How do spreadsheets cost companies money?
    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 ... which gets munged or erased due to a
    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.)
  • 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 [spredgar.com] over at Enron and Worldcom. I wonder how many secret sprea [suu.edu]
  • by rewinn (647614) on Sunday April 24, 2005 @08:18PM (#12332821) Homepage

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

  • 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 shi
  • Wont the error just as likely gain a company money?
  • Not surprised (Score:5, Insightful)

    by slamb (119285) * on Sunday April 24, 2005 @09:23PM (#12333077) Homepage
    Most programmers have no little use for spreadsheets, so we don't know how bad they are. We've got a lot of principles that we apply to our own work, but we don't see that the business people are struggling with the same problems without the proper tools.

    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:

    • You can assign names to cells, but not in a way like "the total of the yearly column of the expenses table". Even if it's in the List Manager, there's still a [A-Z]\d+ cell reference between.
    • It has relative and absolute references. Relative ones will basically update correctly when you move the source. Both kinds will basically update correctly when you move the target. They've made some effort to make range references expand and contract, too. But it's a heuristic; it's guessing information it doesn't really have.

    Massive code duplication

    In my spreadsheet today, I ended up with whole columns of formulas like this:

    =VLOOKUP((A4-FedStdDeduction),FedTaxRates,2,TRUE) +((A4-FedStdDeduction)-VLOOKUP((A4-FedStdDeduction ),FedTaxRates,1,TRUE)) * VLOOKUP((A4-FedStdDeduction),FedTaxRates,3,TRUE)

    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:

    • There's no automatic greybarring (alternating light/dark backgrounds for rows). So you can get lost when reading a big table.
    • An entire worksheet column has the same width. If you have two lists on the same page, inevitably one of them will end up with an awkwardly-sized column.

    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 [decisionsciences.org]. 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/

    • Re:Not surprised (Score:3, Interesting)

      by avsed (168886)
      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 s
  • 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!
  • by enjo13 (444114) on Monday April 25, 2005 @12:29AM (#12333880) Homepage
    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.
  • by langoulant (657025) on Monday April 25, 2005 @12:31AM (#12333885)

    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 [xemplex.com] or their product sheet Xemplex Product Overview [xemplex.com].

    p.s. I'm not affiliated with this company.

  • by E Galois (857353) on Monday April 25, 2005 @12:46AM (#12333956)
    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 [lfp.uba.ar] 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
  • Code reviews! (Score:3, Insightful)

    by Calroth (310516) on Monday April 25, 2005 @02:28AM (#12334240)
    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).

"Floggings will continue until morale improves." -- anonymous flyer being distributed at Exxon USA

Working...