Sidebar: Oops! Many spreadsheets have errors

Computerworld - Washington-based Fannie Mae made a $1.2 billion accounting error last year because of what it called "honest mistakes made in a spreadsheet" used in the implementation of a new accounting standard. Toronto-based TransAlta Corp. took a $24 million charge last year after a bidding snafu caused by a cut-and-paste error in an Excel spreadsheet.
These are spine-chilling mistakes, but research indicates that many company spreadsheets have errors. Anecdotal evidence suggests that 20% to 40% of spreadsheets have errors, but recent audits of 54 spreadsheets found that 49 (or 91%) had errors, according to research by Raymond R. Panko, a professor at the University of Hawaii.
Either way, the amateur programmers who set up and use spreadsheet models are making a lot of mistakes, experts agree, in part because of a lack of training in model development and testing.
The mistakes range from mechanical errors (such as pointing to the wrong cell when entering a formula) to logic errors (such as entering the wrong formula) when setting up the spreadsheet, Panko says in a research paper. "Developers are overconfident, and policies are lax," he adds.
"Most casual enterprise users perceive data as movable from cell to cell with little unforeseen consequences," says Ed Chen, IT director at broadcaster KQED in San Francisco. "They need to be made aware of the nature of the data they are dealing with and what might happen to it when they manipulate it."
Microsoft provides a basic auditing tool in Excel, but the high risk of spreadsheet errors has spawned a tiny industry of spreadsheet auditors, add-in auditing tools and researchers. (Just type "spreadsheet errors" or "spreadsheet audit" into a Web search engine to find these resources.)
Longtime database consultant Shaku Atre cautions, "I have seen people using spreadsheets for anything and everything under the sun -- and many times they use them wrongly. Provide good training."

Read more about BI and Analytics in Computerworld's BI and Analytics Topic Center.