JPMorgan Chase suffered a mammoth trading loss last year as a result of a huge position in credit derivatives that was put on by a trader in its chief investment office. But the bank's report on the $6 billion loss last month laid a portion of the blame on a very common problem: an error in a spreadsheet.
A spreadsheet that calculated the Value at Risk of the chief investment office's portfolio contained an error in a formula that had the effect of lowering the Value at Risk, according to the report.
The risks involved in employing Excel spreadsheets are well-known. Users can make mistakes as they key in data, enter formulas or combine multiple spreadsheets. As spreadsheets circulate through the company, other users can add errors as they make changes. There's also the possibility a user could deliberately introduce inaccuracies.
A recent survey of 3,000 spreadsheet users by Ventana Research shows that about half acknowledge finding errors in spreadsheet data at least occasionally, while a third cite errors in spreadsheet formulas.
Robert Kugel, senior vice president and research director at San Ramon, Calif.-based Ventana, said that is no surprise: "There's more than 30 years of academic research that demonstrates that spreadsheets are error-prone."
And finance departments are among the heaviest users of spreadsheets. The Ventana survey shows 59% of finance employees spend more than half their time working with spreadsheets, versus just 35% of front-office employees.
Michael Juergens, a principal at Deloitte & Touche and leader of its information technology internal audit practice, said that a company's income statement and balance sheet may contain items that are generated in spreadsheets. "It's an enormous issue for external auditors. It circumvents all the process controls, the things we would rely upon as auditors," he said.
In fact, the Public Company Accounting Oversight Board has notified all audit firms that they need to do more to validate information from spreadsheets, Juergens said. "So everybody has really stepped up their games."
Companies are working to improve the accuracy of their spreadsheets and the controls over those spreadsheets, encouraged by Sarbanes-Oxley and "the regulatory environment," Juergens said. Adopting software tools that can inventory a company's spreadsheets, spot errors or enforce controls is an evolving best practice, he said, adding that for a company with fewer than 200 spreadsheets, oftentimes the cost of the software is prohibitive.
"But we have companies that have 10,000, 50,000 spreadsheets they're trying to manage. There's no way they can do that without software to help," Juergens said.
But businesses still have quite a ways to go. "Nobody I have seen or talked to yet is at that optimal place," Juergens said. "Even the folks that are at the leading practices level, with software in place, they are still going through the process of going through all the spreadsheets in the enterprise and enrolling them. There are so many spreadsheets, they do so many different things, they have been built and constructed with so many methods, they're stored all over the place--there isn't a magic button you can push to control and manage all the spreadsheets. That's why companies aren't done yet," he said.
According to Juergens, progress varies by industry, with financial services firms the farthest along because of pressure from their regulators. Effectively managing spreadsheets requires "a solid program that looks at a lot of different criteria--things like software, the regulatory environment, standards and templates, a regulatory model," he said.
"I see a lot of companies falling into the trap of thinking they're just going to buy a software package and it's going to solve all the problems. So they don't do any of the training, they don't build any of the standards, they don't help find templates. Inevitably that fails," Juergens said. "You need a program, you need people focused on this problem, and you can't just throw software at it."
The Ventana research shows that respondents have more confidence in spreadsheets than in a previous survey in 2007. Almost half (49%) said spreadsheets are accurately and timely, up from 35% in 2007.
Kugel said the improved perception may reflect companies' adoption of software solutions that improve spreadsheet accuracy and control. "There are an awful lot of finance-related applications now that have got something that looks like a spreadsheet and acts like Excel, but it's got data storage in a real database, where you've got all kinds of process controls and process management," he said.
Companies could benefit by training employees in how to use spreadsheets. But according to the survey, just 8% of companies offer regular training sessions and 45% offer no training at all, according to Kugel, pictured at left.
"People tend to build on what they know how to do rather than figure out the best way to do things," he said. "The result is it takes longer typically to get something done. If you knew how to do something intelligently in Excel, you could do it in minutes rather than dozens of minutes, and you could do it with more reliable results."
For previous articles on this topic, see Financial Reporting Blues, Taming Spreadsheets and Spreadsheet Sprawl.