Is Excel To Blame For Our Economic Pain?
Written by Janet Swift   
Monday, 22 April 2013

Two economists whose work has been used to argue against government spending to revive the US economy have acknowledged some fundamental spreadsheet blunders. This has led to other Excel errors being exposed and blame for the mistakes being heaped on spreadsheet use rather than the users.

The subject of this recent controversy is Growth in a Time of Debt by Harvard professors Carmen Reinhart and Kenneth Rogoff, a 2010 paper that  was cited by Paul Ryan in his proposed 2013 budget “The Path to Prosperity: A Blueprint for American Renewal” .

The findings of this influential paper were only cast into doubt when doctoral student Thomas Herndon and professors Michael Ash and Robert Pollin, at the University of Massachusetts Political Economy Research Institute tried to replicate Reinhart and Rogoff's results. When they could not so so, they obtained the original Excel spreadsheet used for the calculation and discovered an elementary mistake - that data from five of the 20 advanced economies being studied had not been included. The counties were those at the beginning of an alphabetical sequence and so a simple selection error is implicated.

Two other discrepancies involving selectively omitting data and giving undue weight to unrepresentative datapoints in calculating average growth were also identified by Herndon et al, but these could have been errors of judgment rather than slips in spreadsheet manipulation.

When these errors were corrected, the crucial 0.1% decline for countries with the highest post-war growth transformed into a 2.2% increase:

 

 

Following the widespread publicity surrounding the Reinhart-Rogoff Excel problems, commentators have jumped on the anti-Excel bandwagon, dredging up instances of spreadsheet misuse leading to problems.

One frequently quoted example  refers to the London Whale incident. Now instead of blaming the bankers who took the risks that resulted in the $6.2 billion JPMorgan Chase trading loss, the culprit is seen as the spreadsheet that was providing the information on which the riskiness of credit derivative bets were estimated and in which one key factor was aggregated when it should have been averaged - a simple mistake that should have been easy to spot.

All of the blunders quoted are human error and not the spreadsheet doing the sums wrong. 

Another financial blunder - an error in an Excel spreadsheet that made Fannie Mae look $1.3 billion more profitable than it actually was when transitioning to new accounting regulations - could be blamed on the complexity of the financial model, and in some circumstances we might even suspect that the complexity of such models may be used to hide unpalatable figures. 

It is, however, not as easy to hide things in Excel as naive users might imagine. One of the anti-Excel examples presented by CNN Money is about Barclays purchase of Lehman Brothers immediately after the firm's September 2008 collapse.

The offer made by Barclays was presented in spreadsheet, which detailed Lehman's assets and which of them Barclays was prepared to buy. The spreadsheet author had hidden, rather than deleted, cells relating to assets Barclays didn't want. However in the process of converting the Excel file to a PDF to be  e-mailed to the bankruptcy court, the hidden parts of the spreadsheet reappeared. As a result:  Barclays was forced to accept an additional 179 toxic deals it never intended to buy.

This is the type of error you commit only once and you learn to be more careful another time. Another type of error you should learn to avoid is illustrated with an example from bioinformatics, in which genomic researchers became victims of Excel's helpfulness in applying appropriate formats to data as you enter it.

So the short name of a tumor suppressor, Deleted in Esophageal Cancer, DEC1, was displayed in the date format 1-Dec and clone identifiers of the form nnnnnnnEnn are routinely converted by to floating point numbers - so 2310009E13 becomes 2.31E+019. Because this operation happens at the point of input setting formatting general doesn't recover the original text input.

There is an easy remedy,  however - precede the string of text with a single quote - and incorporating data validation into spreadsheet creation would identify errors before it was too late.

Perhaps the lesson to be learned from the latest debacle is that we don't take spreadsheets seriously enough. It is assumed that anyone can use a spreadsheet - and so they can. But to use them properly in critical situations requires that they are valid, reliable, reproducible and transparent.

So should we blame Excel for the spreadsheet mistakes implicated in the current economic plight?  Mission critical spreadsheets should be subject to the same controls and disciplines as any software project. Spreadsheeting is often seen as an alternative to programming, but it needs to be taken seriously. This is a failure of education not of the spreadsheet. 

 

excellogo

 

Banner


Google To Close Freebase and Open Up Knowledge Graph
19/12/2014

Google is shutting down Freebase, its semi-structured database of well-known people, places and things, but plans on offering access to Knowledge Graph.



Feminist Hacker Barbie?
26/11/2014

Can you see Barbie as a computer engineer? No, apparently nor can Mattel, though the online community is giving a much more accurate and amusing view of how things would work out under the hashtag #fe [ ... ]


More News

 

 

Last Updated ( Monday, 22 April 2013 )
 
 

   
RSS feed of news items only
I Programmer News
Copyright © 2014 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.