Does your project suffer from spreadsheet errors?

There is not a project in this world that does not use spreadsheets from time to time and many are run exclusively by spreadsheets: in numerous surveys, Microsoft Excel is listed as the most common/popular project management tool. And, generally speaking, Excel gets the calculations it is asked to perform 100% correct. Unfortunately the same cannot be said of the users who set up the calculations.

The simple fact is 88% of spreadsheets have errors. While Excel makes it easy for anyone to do the kind of number crunching once reserved for accountants and statisticians, the world’s best-selling spreadsheet software has also contributed to the proliferation of bad mathematics.

Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices points to a 2008 analysis of multiple studies which suggests that “spreadsheets, even after careful development, contain errors in 1% or more of all formula cells”. These mistakes not only lead to miscalculations in family budgets and distorted balance sheets in small businesses, but may also result in questionable rationales for global fiscal policy. The problem is so widespread that there are dedicated groups devoted to stamping out spreadsheet snafus, such as the European Spreadsheet Risks Interest Group.

There’s no question that spreadsheets are a powerful tool, essential to the functioning of the modern world, the European group’s chairman, Patrick O’Beirne, says. “People don’t take safeguards to ensure their work is correct – in fact, in many cases, all it would take to catch these spreadsheet errors is a second set of eyes… Chainsaws are also a very good tool, but who would use one without a chain guard?”

Big errors in little cells

One of the more telling spreadsheet errors in recent times (of particular interest with the Australian budget and federal election looming), is the theory national debt is bad for economic growth. The well-known ‘fact’ trotted out almost any time a debate breaks out about the sustainability of debt, is that once a country gets past 90% on the public debt-to-GDP ratio, the economy will suffer.

This ‘finding’ is based on a study by Harvard economists Carmen Reinhart and Kenneth Rogoff that appears to have overstated the impact that debt burdens have on a nation’s economic growth by failing to include certain spreadsheet cells in the underpinning calculations. Like most well known facts, this assertion is merely a commonly held misconception.

A recent blog by the Roosevelt Institute details a new research paper critiquing Reinhart and Rogoff. According to the blog, the Excel error excluded Australia, Austria, Belgium, Canada, and Denmark from their analysis, which causes the average GDP, once debt-to-GDP exceeds 90%, to be 0.3 percentage points worse than it should be.

The new research paper by Thomas Herndon, Michael Ash and Robert Pollin, also finds other questionable methods in the Reinhart and Rogoff paper. And their new finding? The average growth rate for countries carrying public debt-to-GDP in excess of 90% is actually 2.2%, not -0.1%.

Whether this new paper will change the political debate is another matter. Even if Reinhart and Rogoff are wrong, there’s obviously still a case to be made for keeping debt levels manageable; and Herndon, Ash and Pollin also found lower growth at debt-to-GDP levels above 90% (but not negative growth).

Reinhart and Rogoff are not the only people to have difficulty using Excel. One of the reasons behind the so-called London Whale incident at J.P. Morgan, in which the bank took a $6.2 billion trading loss, was a spreadsheet error in their model.

These errors really hurt! If there is no urgency to keep the debt-to-GDP ratio below 90%, as Reinhart and Rogoff insisted and as policy makers around the world embraced, the double dip recession in the UK, the American budget cuts and the severity of the European contraction may all have been unnecessary (see more at: Prediction is very difficult).

So why does this matter? I would suggest if the ‘top end’ of town can cause recessions and lose billions or dollars due to spreadsheet errors, what are the chances your project has similar nasties lurking in some formula cell? What are you going to do about it?

Peer review is the gold standard in academic research, and the best self-protection for users of spreadsheets in project management—possibly another job for the PMO—alternatively there are many software tools, such as XLTest, to find structural flaws in spreadsheets.

Hope is not a viable policy: now you are aware of the extent of the problem worldwide, basic risk management suggests independent checking of your project’s spreadsheets is essential!