The crisis in the financial markets may not have anything to do directly with spreadsheets but if our sources are correct there are many worried executives running around financial institutions reviewing whether spreadsheets used to value some exotic financial instrument or another really hold up under scrutiny. The truth is that all of us know that spreadsheets are prone to error but few of us are prepared to desert our favourite accounting tool. But how risky have spreadsheets become and is there anything we can do to reduce our exposure to loss? Gary Simon, FSN’s managing editor reviews the latest.
The notion that spreadsheets are risky is not entirely new. Spreadsheet audit tools have been around for almost two decades and everyone has a horror story to recount about a spreadsheet error. Overwriting a formula with data or inadvertently keying a number into the wrong cell can give rise to enormous error.
The problem in a nutshell is that many spreadsheets are in essence powerful applications in their own right, yet very few, if any, go through the rigour of proper design, user testing and documentation. As a result, companies are playing Russian roulette with business critical spreadsheets – a position exacerbated by staff changes and continuing maintenance.
To add insult to injury, there is no sign that spreadsheets are in retreat. The recent announcement of project Kilimanjaro from Microsoft is designed to increase the power, reach and capabilities of spreadsheets removing some of the present physical limits. As one commentator put it we are facing a new era of spreadsheets on steroids.
According to Grenville Croll, spreadsheet guru and chairman of EuSpRIG (European Spreadsheet Risk Interest Group) we are heading towards spreadsheet catastrophe. He told FSN, “Spreadsheets have been shown to be fallible, yet they underpin the operation of the financial system. If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before another “Black Swan” event occurs [Taleb, 2001], causing catastrophic loss. It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution, significantly damaging the City of London’s reputation.”
Spreadsheet errors are probably more prevalent than most users realise. A 2005 paper by Jonathan P. Caulkins, Carnegie Mellon University, refers to earlier studies which suggest that the (simple) average proportion of spreadsheets with errors was 46%. A further analysis of this literature by Panko suggested that even those numbers might understate the problem, since not all audits find all errors. Out of 8 studies auditing a total of 421 spreadsheets using better methodologies, only one study had an error rate below 21%. According to Panko, a weighted average of published spreadsheet audits since 1995 suggests that 94% of spreadsheets had errors. Even if these stunning rates overstate the problem in practice, it is hard to avoid the conclusion that errors in spreadsheets are common.
The message may be getting through. Croll, who now works for Trintech a vendor of spreadsheet management systems says that the concerns have reached the board room but this isn’t necessarily matched by action. “Even though every other spreadsheet is materially defective people are overconfident about their use. People are in denial – they simply don’t want to hear about the problem”, he told FSN.
Ralph Baxter, a director of rival vendor, ClusterSeven, says that professional services firms are beginning to change perceptions. “People are now more realistic about the risks and we are seeing a steady growth in interest, quarter by quarter. Professional services firms have a key role to play in alerting businesses to the risks and seem increasingly willing to pass the message on”, he says.
Finding somebody to take responsibility is part of the concern, after all nobody wants to be saddled with the problem. “In practice business users own the spreadsheet and should therefore assume the risk. You can’t foist spreadsheets on the IT department because they didn’t create them. The issue is that businesses should not be able to create business critical spreadsheets without testing them”, adds Croll.
So what do you do if you want to remedy the situation and like other organisations you have literally thousands of spreadsheets already deployed? Well there are clever automated tools available from the likes of Trintech and ClusterSeven. XLNET Spreadsheet Management Software from Trintech, for example, will automatically scan your network to compile an inventory of spreadsheets and rate their riskiness (need for review) based on user defined parameters about complexity and size. Once the high risk spreadsheets are “remediated” (industry jargon for repaired and tested) they can be kept under surveillance for changes and supervisory controls implemented (backed by email notifications) to ensure that specified changes are authorised.
Sophisticated tools such as these are gaining ground, but progress relative to market size is relatively slow. Automation seems to be the way forward but ClusterSeven’s Baxter told FSN that businesses do not always want the same thing. “The type of monitoring often depends on the process. With some spreadsheets, management wants to know about any changes in real time – for other processes it is simply a matter of repairing the spreadsheet and a much lower level of surveillance”, he said. The key thing according to Baxter is that management understands the need to complete the whole process if they are going to make an enduring impact. “Broadly speaking they need to find out where the spreadsheets are, identify the high risk items and, following remediation, put in the appropriate control processes. It is important to realise that the skills needed during each phase are very different.”
Trintech’s Croll points to a thriving new trade in spreadsheet remediation. “It can be very boring and time consuming to repair spreadsheets and many professional services firms are seeing this as an attractive fee earning opportunity. Some are outsourcing the work to India”
With ongoing market turmoil both Trintech and ClusterSeven are expecting a busy 2009. Spreadsheet management is once again bubbling to the top of the corporate priority list but advances in technology mean that solutions are at hand.
Related FSN Articles:
Trintech to Acquire Movaris, Inc.
