Spreadsheets and the Under Toad

In The World According to Garp, John Irving describes how Garp’s son mishears the word “undertow” as a source of danger at the seaside as a child, and spends the rest of his life in fear of the “Under Toad”.  This word now appears in dictionaries as referring to a general fear and anxiety about the unknown and mortality. It sounds like a word almost designed for actuaries, and never more so than when dealing with spreadsheets.

Spreadsheets are of course currently in the news because Thomas Herndon, a graduate student at the University of Massachusetts, was set an exercise to choose an economics paper and replicate its results. He chose Growth in a Time of Debt, a paper by Professors Reinhart and Rogoff, which had been cited by George Osborne more than any other in defence of his policy of austerity.

He couldn’t replicate any of it, and when the professors sent him the spreadsheet they had used, the reasons why became apparent. Only 15 of the 20 countries with high public debt in the analysis had been included in the calculation of average GDP growth. The As to Ds had been missed off. The paper had not been peer reviewed.

This particular error, when combined with other criticisms Herndon and his professors had of the methodology used in the paper, provided considerable challenge to the original conclusions of the analysis and was therefore widely reported due to its implications for UK economic policy in particular. However errors of this kind in Excel spreadsheets are very common.

The European Spreadsheet Risks Interest Group, or EuSpRIG (“yewsprig”) for short, is an organisation sponsored by a group of heavy spreadsheet users which runs conferences and forums designed to pool users’ experiences and suggest best practice in spreadsheet use. EuSpRIG are therefore connoisseurs of the spreadsheet error. They helpfully include a list of spreadsheet horror stories on their website, including the GDP growth one.

Perhaps the most significant spreadsheet foul up on their list is described in the Report of JP Morgan’s Management Task Force regarding billions of losses in 2012 in its chief investment office, which cited a number of spreadsheet errors. However, my personal favourite is the one involving the London 2012 organising committee (Locog) confirming in January 2012 that an error in its ticketing process had led to four synchronised swimming sessions being oversold by 10,000 tickets. Locog said the error occurred when a member of staff made a single keystroke mistake and entered “20,000” into a spreadsheet rather than the correct figure of 10,000 remaining tickets.

It is tempting to think that our technological advancement and exponentially increasing computer power have made some kind of computational HD within our grasp, with every wart and blemish of the object of investigation now detectable by our ever more sophisticated tools. But EuSpRIG estimate that over 90% of spreadsheets contain errors. Most of these will never be found, but lurk beneath the surface threatening the accuracy of any calculations carried out by the spreadsheets concerned. In other words, the Under Toad.

Carveth Read once said (although only famously when it got attributed to Keynes): “It is better to be vaguely right than exactly wrong.” However, when most spreadsheets contain Under Toads, it is clear that a lot of the supposed precision with which information is provided to us is illusory. That exponential increase in computer power has made even the very measurement of precision in the more complicated spreadsheets virtually unknowable. We may never be more than vaguely right, but often have no real idea how wrong we are.

So we check, to ensure that the numbers coming out of the spreadsheets and other models we use are within a tolerable distance of what we would expect. Some of us use pen and paper. The GDP growth Under Toad might for instance happen when a formula which adds up a column in one worksheet is copied to another worksheet where the columns have a different number of rows in them, and the formula is not adjusted. I have certainly done that before now, and only found it when I checked it against a number of other sources. For this reason, I am always a bit nervous about model results being checked by spreadsheet. It doesn’t seem sufficiently unlikely to me that the two could be acceptably close to each other but, perhaps due to entirely different errors, many miles from the truth.

There is a generation of actuaries, of which I am one, that experience almost physical pain when we see students carrying out even the simplest calculations using spreadsheets, knowing that each new one on the block is almost certainly adding to the unknown unknowns of the Under Toad. I know there are just as many mistakes in my biro scrawls, but I also know it will be a lot easier to find them later.

A GDP increase of 0.3% on Thursday was greeted with relief at a triple dip averted, when a fall of just 0.1% would have been met with anguish. Tiny movements in the FTSE 100 are described as “up” and “down”, as if the direction were more important than the amount and when “broadly unchanged” would be a more informative description.

We are obsessed with tiny movements which contain no information and which, thanks to the Under Toad, we cannot meaningfully calculate. This obsession distracts us from seeing the bigger picture, the fuzzy connections that only become apparent when we look up from our HD sharp tiny piece of detail. And our spreadsheets won’t help us with that.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.