The Perils of Excel

An elementary data management fault in an Excel spreadsheet led directly to some erroneous conclusions being drawn in the major economic research paper entitled ‘Growth in a Time of Debt‘. Written by the eminent Harvard academic economists Carmen Reinhart and Kenneth Rogoff, and published in the American Economic Review in 2010, this
paper provided a major input for governments around the world as they evaluated austerity policies to cope with the global economic downturn and debt crisis.

The key finding was that when a country’s indebtedness exceeds 90% of gross domestic product (GDP), economic growth will be substantially slowed; the usual policy response to such a conclusion would be to slash debt and impose powerful
austerity policies.

When attempting to reproduce Reinhart and Rogoff’s results, Thomas Hendon, a graduate student at Amherst College in Massachusetts, US, consistently failed to do so. Eventually, and after much checking and validation, it was established that Hendon was correct, and that the original paper’s results were affected by some major computing errors: the analysis had somehow dropped the economic statistics of five of the 20 countries that were supposed to be used to calculate GDP growth in economies with high levels of debt. The analysis also omitted data from a number of countries such as
Australia, which had managed to combine strong growth with high debt levels, in contrast to the central thrust of the paper’s argument.

Reinhart and Rogoff have now conceded that there were indeed coding errors in their original analysis, which did result in the omission of important data. In effect, copy-and-paste data manipulations had been treated as deletes, so that some
important data was excluded from the analysis. They maintain that these errors do not invalidate their central findings about the relationship of GDP and debt levels. Nonetheless, many national treasury departments will now be swiftly and
anxiously reworking the models they have used to describe their country’s optimum debt levels, to see if they have in fact been over-tightening the screws of austerity through adopting an over-cautious borrowing policy.

Elementary spreadsheet errors have in this case potentially impacted the economic well-being of perhaps billions of people, who have been exposed to austerity policies based upon false data.

Excel – the good news – and the bad>

This rather embarrassing story has immediate relevance for corporate treasurers, because of the ubiquity of the use of Excel spreadsheets in treasury departments. Excel is of course a wonderful, flexible tool for manipulating and reporting data – provided that it is used rigorously, and that the results are properly checked and validated. Its usage does have its appropriate and prudent limits.

Spreadsheet templates are particularly valuable for use by central or regional treasuries collecting data such as cash
flow forecasts, netting invoices and hedge transaction requests. A properly formatted spreadsheet is clear and familiar to finance professionals the world over, and so this kind of spreadsheet usage facilitates the efficient collection
of critical data.

Spreadsheet usage becomes undesirable when it has been adopted as the central technology for managing a treasury of any significant size and complexity. Over time, conclusions of this nature have become common in the audit reports for the treasuries of smaller and smaller companies. Such reports reflect the consensus that spreadsheet systems lack the robustness and security that reflect good practice for managing significant financial operations. It might be theoretically possible to build a secure spreadsheet solution for use in a treasury, but in practice, this never seems to happen. The very convenience and flexibility of spreadsheets is the probable reason for this observation.

Spreadsheet solutions tend to grow organically, over a period of time. They are often developed by technically literate individuals who are primarily treasurers, and so they will often not have been tested and documented in the structured ways required by formal corporate IT policies. When the spreadsheet authors move on to new employment, they carry their knowledge of the spreadsheet system logic with them, making it difficult – or even effectively impossible – for others to repair the errors and breakages that will inevitably occur. There are many kinds of problems that may be encountered,
ranging from data selection issues such as the economists’ copy and paste error, to subtle, conditional faults in formulae and reporting bugs that crop up in apparently unpredictable ways and times. There are so many things that can go wrong with an inadequately constructed spreadsheet solution, and the question that is now asked is that can any such solution be acceptable in a treasury operation?

Treasury Management Systems (TMS) Can Reduce Excel Risk

TMS vendors have of course been hammering away at this point for many years – and the fact of their obvious commercial interest does not mean that they are necessarily wrong. The reason that an established TMS provides such superior robustness and security compared with spreadsheets is that it will have been developed, tested and commissioned in a highly structured, controlled and documented environment. New software will have been coded according to rigorous development standards, and will have been quality assurance tested and validated by experienced clients’ Beta testing before release to general users.

Updated software will have been regression tested to ensure that it works properly in the entire operating environment.
Furthermore, TMS’ are supplied with comprehensive and usually interactive on-line documentation, combined with expert client support services. Ultimately, TMS clients are legally protected by the provision of a suitable service level
agreement (SLA) which binds the vendor to deliver to negotiated quality and performance levels.

Once TMS software has been tested, installed and configured to run a company’s required treasury processes, reports and
interfaces, it is then locked-down using the system’s security functionality. This restricts all authorised users to their permitted functions. It freezes the data selections for all operations. It activates the control processes and audit
functions used in contemporary TMSs that reflect properly secured treasury operations. It delivers treasury operations in compliance with treasury policy.

Today’s TMS’ have stood the test of more than 25 years of history. There is a case to be argued that any company whose operations are sufficiently large or complex enough to warrant the organisation of a distinct treasury department ought, from commercial prudence, deploy a TMS to support treasury operations. The cost of doing so could be reasonably equated to the payment of an insurance premium, to protect the company against reasonable worst case treasury risk scenarios such fraudulent or uncontrolled dealing, lack of accurate cash or risk visibility, or significant hedging errors.

Over the last 20 years, the size threshold for first time TMS adopters has fallen steadily, as the importance of securing dependable treasury operations combined with the generation of fully reliable and timely management, regulatory and
statutory reporting have become generally accepted standard treasury practice. Historically, there has been a sufficient level of competition in the TMS industry to keep prices within the range of most treasury budgets; and entry level SaaS TMS’ are often available at inclusive subscription fee rates that enable the cost to be treated as an operating expense.

There is of course an important place for Excel in support of many of a treasury’s data collection and ad hoc analytical requirements. The essential necessity for doing so at a low level of risk of error is that spreadsheet deployment should be rigorously and transparently integrated with the TMS and other third-party systems – such as bank reporting and enterprise resource planning (ERP) systems – to ensure that treasury operations and reporting are properly
protected from random nasty surprises.

References:

  • BBC website, 21st April 2013,http://www.bbc.co.uk/news/magazine-22213219
    – “The mysterious powers of Microsoft Excel.”
  • The
    Independent
    , 23rd April 2013, “You might be the architects
    of austerity – but you’ve flunked your spreadsheet” – Tim Walker

119 views

Related reading