Spreadsheet screwups cost companies big money

Harvard economists Reinhart and Rogoff are hardly alone. Users everywhere make mistakes every day that have major consequences.

By Jonathan Berr Jul 31, 2013 7:31AM
Woman looking at computer screen (© Corbis)Earlier this year, Harvard economists Carmen Reinhart and Kenneth Rogoff were ridiculed in the financial media after researchers found an error in one of the spreadsheets they used in a key paper that got used to justify economic austerity. It turns out that they're not alone.

An astounding 88% of all spreadsheets have errors in them, and 50% of those used by large companies have "material defects," according to a CNBC.com report that cited research from U.K.-based financial modeling company F1F9.

F1F9 managing director Kenny Whitelaw-Jones blamed most of these errors on a lack of industrywide standards for financial modeling, the website said. Another problem, Whitelaw-Jones noted, is that many spreadsheets are "ridiculously complex and impossible to read."

Experts, though, have been sounding the alarm bells about spreadsheets for years. In 2007, CIO noted that "many public companies and government organizations are trying to wean themselves off their reliance on spreadsheets for complex and critical financial transactions." The problem, of course, is that's easier said than done.

Spreadsheets are as big a part of modern corporate life as the coffee machine in the break room. To call them "ubiquitous" doesn't do justice to do the endless rows of neatly arranged numbers that are the bane of existence for many employees. No one is quite sure how many spreadsheets are in existence, but it's a huge number for sure.

Microsoft (MSFT), which owns and publishes moneyNOW, an MSN Money site, estimates that about 1 billion people use Excel, the most popular spreadsheet program. Google's free spreadsheet program has plenty of users as well. So, the numbers of error-filed documents have to be massive, given that some spreadsheets have thousands of calculations in them.

One of the more noteworthy recent spreadsheet blunders was uncovered by JPMorgan (JPM) during its recent investigation into the so-called London Whale scandal, for the trader whose outsized bet landed the venerable Wall Street firm in hot water. Fortune noted that the financial model the traders were using was operated through a series of Excel spreadsheets that added a key measure that should have been averaged instead.

"The result: Risk officers at JPMorgan believed the credit derivatives bets were half as risky as they actually were," the magazine says.

The outcome at MF Global was even worse. Fortune noted that a year before the commodities broker collapsed, "Consultants hired by the firm determined it needed to improve" its Excel spreadsheets. Those improvements were never made.

Former CEO Jon Corzine is now facing civil charges from the Commodities Future Trading Commission of keeping the company going during its final days by illegally using customer funds.

Jonathan Berr does not own shares of the listed stocks. Follow him on Twitter @jdberr.

More on moneyNOW
36Comments
Jul 31, 2013 9:49AM
avatar

Dollars to donuts Corzine just gets the perfunctory slap on the wrist as the unknowing customers take the hit.

The new American mantra is "Privatize the profits and socialize the losses".

And We The People reap the losses.

Jul 31, 2013 9:39AM
avatar
The blame game once again!  Last time I checked spreadsheets were created by man.  Call it what it is,  incompetence on the part of humans.
Jul 31, 2013 11:05AM
avatar

one problem with spread-sheets and other computerized documents is that those educated in the last 30 years or so never learned how to do math without the aid of computers. when I graduated from high school, I knew from memory the product of squared numbers through 25 and most of the numbers ending with zeros and fives to well over one-hundred.

 

my first full time employer (1970) could divide numbers in hundreds of thousands by up to four digits and write down the first three digits of the dividend before starting the long divisions on paper. this was before computers and calculators with simple division features. he seldom had to change the third number in his dividend (the original number was typically close enough for government work!)

usually adding or subtracting one number from the third digit and never the first two.

 

I did 'spreadsheets' with calculators and my brains for about 15 years before we could afford computers that could do the spreadsheets electronically. errors appeared quickly when the ups and downs didn't balance with rights to lefts. it was a problem to find the cause of the errors most times, but we didn't stop until we had the right answers.

 

the young future competitors I trained had no idea whether their products had the correct answers or not when the work was submitted to me for review. all they knew was they had so much info and you punched it into the computer and it spit out the results. the final answer(s) typically showed me that a problem or problems existed when I started my reviews. I then relied on my old skills to find where the young folks went astray.  

 

don't get me wrong. I use spreadsheets every day here on the point. but I know that I have to check the rational of the final answers mentally before I put the spreadsheets in stone.

Jul 31, 2013 10:03AM
avatar

There is an old acronym that you seldom see anymore.  GIGO, for those who don't know, stands for garbage in - garbage out.  We have come to believe in the saying that "figures don't lie" but we seldom remember the rest of that saying that continues "but liars figure."  The thing about statistics data is that it can be made to say anything that you want it to say.  There is no substitute for using your own head and gut when making decisions about anything financial or otherwise.  Information is ok as long as you know and trust the source but even then mistakes can be fatal.

 

The famous body count numbers used in the Vietnam conflict to indicate that we were winning gave a lot of decision and policy makers a false sense of our progress that lasted up until we got our butts kicked out of that nation by the North Vietnamese army.  The video of our last ditch evacuation by helicopter from the roof of the U.S. Embassy was an eye opener for a lot of people and was a lesson that we still haven't learned because we still get stuck in the political tar babies all over the world based upon flawed information that often comes from (guess what) bad spreadsheets along with erroneous or phony intelligence information (oxymoron?) 

Jul 31, 2013 11:10AM
avatar
This is what happens when you don't lock formula cells, and when you have so few staff you don't have time to do even a cursory double-check of your work, let alone a pair of "fresh eyes" do it.
Jul 31, 2013 9:47AM
avatar
To err is human. So is failing to check the formulas and believing the economists whose predictions fitted our political philosophy.
Jul 31, 2013 12:18PM
avatar

that's why you always test your formulae. Every good engineer and scientist knows this. You test it on a known set of conditions and you always do back of the envelope estimations to see if the program jives. A program is only as smart as the person who wrote it.

 

 

Jul 31, 2013 9:59AM
avatar

There's is an assumption throughout this article and much thinking throughout society, I think.  I've not seen so much evidence that corporate management suffers the great consternation these losses might suggest to many of us.  I rarely see genuine internal auditing or serious modernization of technologies and methods to explore and reduce (permanently !) these "errors."  On the contrary, I have found many public and private entities very relaxed when pursuing corrections/improvements, at their best, and very agile at "creative accounting" that can send almost anything under the radar, at their worst.

A bigger problem may be how many of these organizations stupidly use and share simple EXEL sheets that can even be pulled up via a Google search.

Jul 31, 2013 1:04PM
avatar

Sthptwes,

 

I know EXACTLY what you're talking about. I graduated over 40 years ago, and believe me, if you couldn't at least add and subtract in your head, you didn't graduate. As an example, about 20 years ago I was working as a part-time cashier in a supermarket, and the computer went down. EVERYBODY under 30 that was in the front cashiering was totally helpless on how give change back. The cash registers could be worked mechanically, so that wasn't a problem. We had to take ALL of those under 30 and SHOW them how to count out the change back to the customer. I was shocked on the decline of basic education in this country. And from what I can see, it's still going on.

Jul 31, 2013 11:10AM
avatar
To make matters worse, spreadsheets are subject to manipulation. You can change the results, even if the underlying data has not changed. I've often pointed out potential problems to my colleagues and customers over my 25+ years of working in IT/MIS, but many people prefer convenience over accuracy. Of course, compared with the false information we're continually fed by our elected officials, a few unintended errors seem trivial.
Jul 31, 2013 12:52PM
avatar
In a article listing the problems of little errors of spreadsheets it's fitting that the little error or writing would pop up: the last sentence of paragraph 6 refers to "error-filed documents."  This should read "error-filled documents".
Jul 31, 2013 11:57AM
avatar
When I went from college to the work place I found that there was an outstanding number of employers who don't understand how to make Excel spreadsheets. I've been paid up to $120 to create Excel spreadsheets from certain companies.

They should've taught us that Microsoft Excel would be the most important thing in college :P
Jul 31, 2013 1:07PM
avatar
Without these spreadsheets we would not be able to analyze data to the level that is possible with spreadsheet.  Plenty of good things come from spreadsheets.  They are, after all, a tool.
Jul 31, 2013 4:40PM
avatar

ah.. ha.. and we were wondering how the US got so far into debt.... maybe dems and the potus aren't idiots, maybe congress really does something besides give themselves raises; and duck out of forced healthcare. maybe the repubs aren't riding the short bus, maybe the IRS didn't spent 8M on line dancing and making movies.  maybe.. its Excel..

 

 

Jul 31, 2013 5:44PM
avatar
Spreadsheets are a blessing. Trying doing it the old way.
Aug 6, 2013 12:36PM
avatar
Spreadsheets can be great, but they have to be properly written.  They are only as good as the person who created them.
Aug 2, 2013 10:51PM
avatar
information is only as good as the person who enters it.
Aug 1, 2013 11:19PM
avatar

And this is new because.... Spreadsheets are only as good as the person who created it and all the people who review and approve it.

Jul 31, 2013 5:40PM
avatar
Sometimes it's not the spreadsheet or incompetence it may be they use imaginary number to calculate the GDP.
Jul 31, 2013 1:21PM
avatar
In the 70's my firm employed programmers that I guess we now call them software engineers. Over home brought lunches in the shipping department I asked the question of a programmer. "When your working on a program, when do you know it's finally done"? Answer was "after an amount of time of sampling the program I can't make it fail, however in real use it will fail and when it does we have to patch it, try to make it fail, give it back for use and sooner or later it will fail again". Simply put, no program is foolproof however we use them anyway. 
Report
Please help us to maintain a healthy and vibrant community by reporting any illegal or inappropriate behavior. If you believe a message violates theCode of Conductplease use this form to notify the moderators. They will investigate your report and take appropriate action. If necessary, they report all illegal activity to the proper authorities.
Categories
100 character limit
Are you sure you want to delete this comment?

DATA PROVIDERS

Copyright © 2014 Microsoft. All rights reserved.

Fundamental company data and historical chart data provided by Morningstar Inc. Real-time index quotes and delayed quotes supplied by Morningstar Inc. Quotes delayed by up to 15 minutes, except where indicated otherwise. Fund summary, fund performance and dividend data provided by Morningstar Inc. Analyst recommendations provided by Zacks Investment Research. StockScouter data provided by Verus Analytics. IPO data provided by Hoover's Inc. Index membership data provided by Morningstar Inc.

Trending NOW

What’s this?

MARKET UPDATE

[BRIEFING.COM] S&P futures vs fair value: +3.20. Nasdaq futures vs fair value: +5.20. U.S. equity futures trade near their pre-market highs amid upbeat action overseas. The S&P 500 futures hover three points above fair value, which puts the benchmark index on track to challenge its all-time closing high of 1987.98 and the intraday high of 1991.39. Participants have received several quarterly reports since yesterday's close with results coming in somewhat mixed.

On the ... More

MSN MONEY'S