JPMorgan Chase suffered a mammoth trading loss last yearas a result of a huge position in credit derivatives that was puton by a trader in its chief investment office. But the bank'sreport on the $6 billion loss last month laid a portion of theblame on a very common problem: an error in a spreadsheet.

A spreadsheet that calculated the Value at Risk of the chiefinvestment office's portfolio contained an error in a formula thathad the effect of lowering the Value at Risk, according to thereport.

The risks involved in employing Excel spreadsheets arewell-known. Users can make mistakes as they key in data, enterformulas or combine multiple spreadsheets. As spreadsheetscirculate through the company, other users can add errors as theymake changes. There's also the possibility a user coulddeliberately introduce inaccuracies.

A recent surveyof 3,000 spreadsheet users by Ventana Research shows thatabout half acknowledge finding errors in spreadsheet data at leastoccasionally, while a third cite errors in spreadsheetformulas.

Robert Kugel, senior vice president and research director at SanRamon, Calif.-based Ventana, said that is no surprise: “There'smore than 30 years of academic research that demonstrates thatspreadsheets are error-prone.”

And finance departments are among the heaviest users ofspreadsheets. The Ventana survey shows 59% of finance employeesspend more than half their time working with spreadsheets, versusjust 35% of front-office employees.

Michael Juergens, a principal at Deloitte & Touche andleader of its information technology internal audit practice, saidthat a company's income statement and balance sheet may containitems that are generated in spreadsheets. “It's an enormous issuefor external auditors. It circumvents all the process controls, thethings we would rely upon as auditors,” he said.

In fact, the Public Company Accounting Oversight Board hasnotified all audit firms that they need to do more to validateinformation from spreadsheets, Juergens said. “So everybody hasreally stepped up their games.”

Companies are working to improve the accuracy of theirspreadsheets and the controls over those spreadsheets, encouragedby Sarbanes-Oxley and “the regulatory environment,” Juergens said.Adopting software tools that can inventory a company'sspreadsheets, spot errors or enforce controls is an evolving bestpractice, he said, adding that for a company with fewer than 200spreadsheets, oftentimes the cost of the software isprohibitive.

“But we have companies that have 10,000, 50,000 spreadsheetsthey're trying to manage. There's no way they can do that withoutsoftware 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 practiceslevel, with software in place, they are still going through theprocess of going through all the spreadsheets in the enterprise andenrolling them. There are so many spreadsheets, they do so manydifferent things, they have been built and constructed with so manymethods, they're stored all over the place–there isn't a magicbutton 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, withfinancial services firms the farthest along because of pressurefrom their regulators.

Effectively managing spreadsheets requires “a solid programthat looks at a lot of different criteria–things like software, theregulatory environment, standards and templates, a regulatorymodel,” he said.

“I see a lot of companies falling into the trap of thinkingthey're just going to buy a software package and it's going tosolve all the problems. So they don't do any of the training, theydon't build any of the standards, they don't help find templates.Inevitably that fails,” Juergens said. “You need a program, youneed people focused on this problem, and you can't just throwsoftware at it.”

The Ventana research shows that respondents have more confidencein spreadsheets than in a previous survey in 2007. Almost half(49%) said spreadsheets are accurately and timely, up from 35% in2007.

Kugel said the improvedperception may reflect companies' adoption of software solutionsthat improve spreadsheet accuracy and control. “There are an awfullot of finance-related applications now that have got somethingthat looks like a spreadsheet and acts like Excel, but it's gotdata storage in a real database, where you've got all kinds ofprocess controls and process management,” he said.

Companies could benefit by training employees in how to usespreadsheets. But according to the survey, just 8% of companiesoffer 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 thanfigure out the best way to do things,” he said. “The result is ittakes longer typically to get something done. If you knew how to dosomething intelligently in Excel, you could do it in minutes ratherthan dozens of minutes, and you could do it with more reliableresults.”

For previous articles on this topic, see Financial Reporting Blues, TamingSpreadsheets and SpreadsheetSprawl.