Between the sheets

Earlier this year I spoke at a conference in Madrid where Ventana research unveiled research that showed, amongst other things, that companies that rely heavily on spreadsheets take several days longer to close their financial books each month than those that do not.  This makes sense.  Excel is the analyst tool of choice, but its very ease of use presents issues.  It is not easy to document well, and when I was at Shell we had a whole team providing spreadsheet auditing and design services.  For some of the very complex financial or other models that end up being developed it turns out to be very difficult to make sense of a model when someone moves on.  In the IT world we are used to dealing with support issues and at least in theory have plenty of experience with documentation standards and debugging tools.  As we all know, even in mature systems the documentation can be a nightmare, but imagine how much worse it is in Excel when you are asked to take over a thousand line Excel spreadsheet where all the cell formulae use the default grid references e.g. “=Sum(c3:c27)”.  Instead you can use the facilities in Excel to assign meangingful names to cells e.g. this would become something like “=Sum(expenses)”, which is a lot easier to figure out, but how many peopel do this?   Indeed when audits of spreadsheet models were carried out then errors were frequently found, which is worrying given the kinds of decisions being taken that rely on these models. An article this week explains how eXtensible Business Reporting Language (XBRL) offers the prospect of some relief since it defines tags which are independent of cell location. By separating the definition of the data from its cell-specific information it becomes easier to keep track of things, and easier to combine worksheets from multiple sources.  Whilst this is a welcome development I suspect that there is a long, uphill climb involved since the problem comes down to people rather than technology.  It took a long hard struggle to get programmers to (sometimes) document things properly, and I cannot see most finance or other end-users really caring enough.  It is always quicker to use things like cell references rather than proper names, for example, and so it will always be tempting to do so and not worry too much that your pretty model is almost incomprehensible to anyone else.  

It will be interesting to see whether regulators take a firmer view of things over time, since in my experience the quality of spreadsheet models is distinctly patchy.  Insisting on proper audits of spreadsheets used for serious purposes (e.g. statutory accounts, investment decisions) would be a start.  I suspect that most companies have little idea of just what a can of worms they are relying on for the numbers on which they make their decisions.