Tapping numbers and formulae into fiddly spreadsheet cells is a process carried out by millions of financial service professionals every day. But what many firms often overlook is that this fundamental task is fraught with risk.
According to a YouGov
study commissioned by financial modelling company F1F9, spreadsheets are used for the preparation of company accounts worth up to £1.9tn in the UK alone; alarmingly, the same study reveals that one in five large companies have suffered a loss due to a spreadsheet error. It is easy to comprehend how a slip of the finger can, and all too often does, cost businesses millions, sometimes billions, of dollars, not to mention land a hefty reputational blow.
Fat finger syndromeChief executives have been made joltingly aware of so-called ‘fat finger syndrome’ and other pitfalls that can make shuffling numbers around in a spreadsheet a chancy game. The infamous tales of woe have been widely covered in the press.
JPMorgan Chase’s London Whale fiasco, which cost the firm over $6 billion, is often solely attributed to intentional spreadsheet manipulation by the eponymous rogue trader (Mr L. Whale is also known as Bruno Iksil). It was later revealed that a
spreadsheet error – value at risk calculated by dividing the sum of old and new rates, and not the average – was largely to blame.
The well-known tale of respected Harvard economists Carmen Reinhart and Kenneth Rogoff accidentally leaving out five rows of data in their 2010
paper is another example, and perhaps the holy grail, of spreadsheet errors. The paper posits that when sovereign debt is high, GDP falls, a premise that has been widely used to warrant government budget cuts. When UMass economists corrected the error, they concluded that GDP still grows when debt levels are high.
The mistakes picked up by the press are only the tip of the iceberg, says Tom Scott, Principal Consultant at Jerts Productivity Consulting. Scott explains: “Spreadsheet-based errors were made not infrequently at financial institutions I’ve worked at, but they would get covered up or resolved one way or another. They certainly never got to the point where they would make the news.”
Management teams should therefore be well aware of the hazards, but Scott postulates: “Generally, I don’t think spreadsheet risk is on their radar screens.”
88%
of all spreadsheets have errors in them (source: MarketWatch)
35%
of all spreadsheet errors are data-related, while formula errors account for 26%
(source: Ventana Research)
No boundariesThere are many reasons why large financial firms continue to rely so heavily on the common spreadsheet, and many of these reasons are sound. As James Kwak, Associate professor at the University of Connecticut School of Law, says: “For some tasks, Excel is the ideal tool. It can accept data from any source. It is powerful and easy to use. It is completely visual … and it’s flexible: you can make any modification to the data that you want.”
Ian Willis, Technology Risk Manager at PwC, goes further: “It seems there are no boundaries if you have the will. Spreadsheets can interlink with other applications to create a full picture that can evidentially support whatever business decisions you need to make.”
He muses: “With such power at your fingertips, is it any wonder why so many companies place so much reliance on spreadsheets?”
Excel is essentially freeThen there are the more practical reasons of time and cost. Ralph Baxter, CEO at ClusterSeven, which provides software to manage the integrity of Excel in businesses, explains: “Spreadsheets are part of the ‘do it now, get it done’ culture. They allow one person to solve a question without having to explain the problem to an IT specialist and wait months for an answer.”
Baxter adds: “As a source of rapid financial innovation there is nothing else so pervasive ... so fast and so cheap.”
Excel is taught to students at almost every school (business or otherwise) in the developed world, meaning companies need provide little, if any, spreadsheet training to employees. Scott explains: “The issue is, Excel is essentially free for businesses. It’s part of the Microsoft Office package that every computer has and therefore everyone knows.”
This lack of training is based on the assumption that finance professionals are inherently well versed in all things spreadsheet, which is probably true. But it also means that creators of incredibly complex and fragile spreadsheets often neglect to explain the idiosyncrasies in them to their colleagues. And that is where the errors creep in.
What are the alternatives to the traditional spreadsheet?Quantrix, launched a decade ago, and the more recent Anaplan both use multi-dimensional modelling to provide solutions to ‘what if?’ scenarios, and cloud technology to provide instant solutions. They have met with mixed success: Of the available non-traditional programs, Tableau has perhaps permeated the market best. But Scott explains that unless you’re selling to a company that has already suffered materially from an error, businesses tend to stick to what they know. “You would think, given the statistics on errors, that these non-traditional programs would be an easy sell. But, actually, they are a tough sell,” he says.
Best practice for building and maintaining spreadsheets
Do:
• Document why the spreadsheet exists in the business – what role does it perform and why?
• Separate data from calculations and presentation to avoid hiding cells that may get forgotten about.
• Build in error checking, cross-checks and balance checks, and use programs’ error checking tools.
• Have someone else check your work and test the results.
• Assume something will go wrong on your spreadsheet.
Don’t
• Be embarrassed about using spreadsheets or brush them under the carpet.
• Keep important spreadsheets on local storage (such as laptops).
• Use free text passwords and user names in your spreadsheets that could be used to bypass corporate security.
• Forget that ‘good’ spreadsheets can go ‘bad’ when people make inadvertent changes…
• …But also don’t forget that this can occur when people forget to update data.
Two major flawsAccording to Kwak, there are two main flaws in Excel that undermine its clear strengths. “One,” he says, “is that you can do immense damage to your data without even realising it. Excel allows you to select ranges of cells in multiple worksheets, but if you forget that you have multiple worksheets selected, you can type over data and not realise it.”
The second major shortcoming, says Kwak, is the absence of an effective audit trail. “Excel doesn’t generate a record of all the changes that were made to your data, which means that if something goes wrong, you can’t go back and figure out what happened.”
Scott cites several repeat offenders when listing the common errors he encounters in businesses’ spreadsheets, but the main one? “A failure to separate data calculations from data presentation.”
It seems that the ‘Hide rows/columns’ buttons are a data officer’s biggest foes: “The end result might look presentable, but one small change can lead to errors, and it’s incredibly difficult to maintain,” says Scott.
Spreadsheets aren’t making an exit yetThere will always be an important role for spreadsheets to play in finance: Willis deems Excel to be “arguably the best thing Microsoft created…” Although, with statistics alarming enough to keep CEOs awake at night, it is no surprise that in the same breath Willis adds, “…but undoubtedly also the worst.”
Firms would be wise to heed the advice from risk managers. After all: “There is no corporate destination that is spreadsheet free,” says Baxter.