Tech at Work: Spreadsheet Slip-ups Cause Financial Errors

Software programmers in your business could be responsible for embarrassing errors in your financial statements or coding mistakes that could cost your business money.

You don't employ any software coders, you say?

If anyone in your business creates spreadsheets, they're working as programmers, whether or not you know it.

A spreadsheet combines an application's calculation logic with financial data. Elements of spreadsheet logic, such as formulas and macros, automate the financial calculations in almost all businesses. Microsoft's Excel is probably the most common application development environment in business use today.

Most small businesses use accounting software packages created by experienced financial software developers such as Intuit, Microsoft, or Sage. A business manager wouldn't think of handing off the creation of a custom-made accounting application to a junior bookkeeping clerk with no training in programming. Yet while many businesses use spreadsheet software to supplement the capabilities of their accounting program, how many train their spreadsheet creators as programmers? How many employees are taught to build accurate spreadsheets and test them before use?

Deceptively Easy

It's easy to create a spreadsheet, perhaps deceptively easy. All too often, spreadsheet users learn as they work and receive no special training. After all, spreadsheet development just means setting down a little simple financial logic, right?

Unfortunately, the job isn't as simple as it appears. It isn't difficult to find evidence of financial screw-ups resulting from faulty spreadsheets.

Back in 2003, Fannie Mae, which finances home mortgages, stated in a news release of third-quarter financials that it had discovered a $1.136 billion error in total shareholder equity. Jayne Shontell, Fannie Mae senior vice president for investor relations, explained in a written statement, "There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard."

Eastman Kodak Company restated financial results for the first and second quarters of 2005 by $2 million and $13 million, respectively, due to an erroneous spreadsheet that calculated severance and special pension-related termination benefits.

A Common Problem

While public companies rarely announce these sorts of high-profile errors, the problem of spreadsheet errors in businesses is pervasive.

Professor Ray Panko of the University of Hawaii has performed extensive research on spreadsheet errors, highlighted on his Spreadsheet Research Web site. According to Panko, most firms use spreadsheets in their financial reporting, and most spreadsheets contain errors. His summary of field audits involving real-world spreadsheets shows errors in 94 percent of the 88 spreadsheets audited in seven studies since 1995.

Spreadsheet software isn't necessarily any worse in the error department than other applications. It's just that the logic of the calculations and their output aren't reviewed as rigorously as those of other software. Spreadsheet development is treated very casually--but the larger and more complex a spreadsheet gets, the more likely it is to contain errors.

How can spreadsheet errors be reduced? It all starts by treating the development of spreadsheets more seriously, to reflect their importance in financial reporting. Businesses that rely on spreadsheets should establish a policy on how business-critical spreadsheets will be developed, tested, and documented.

Strategies for Checking Spreadsheets

Spreadsheet developers should be trained to create spreadsheets with self-checking mechanisms. These can include cross-totals and predictive results, as a check on reasonableness. Some free spreadsheet audit training video clips are available at Miricle Solutions.

Spreadsheets should be checked for different ranges of numeric input that can uncover otherwise hidden errors. For example, increase an amount to ten times its typical value, then reduce it to one-tenth of its usual amount. See if the spreadsheet results still make sense in both instances. And spreadsheet software itself can help: For instance, Excel has a Formula Auditing toolbar that can help uncover some errors in formulas.

A spreadsheet should be documented so that its purpose and use are clear. I've seen errors creep into a spreadsheet when modifications to it are made without the involvement of the original developer, who may have left the company.

Important spreadsheets should be audited, a routine practice in the UK, where it's promoted by an active European Spreadsheet Risks Interest Group. In the U.S., large, public companies that are subject to Sarbanes-Oxley requirements are more likely to audit important spreadsheets, but smaller companies seem to ignore this risk.

XLSior for Microsoft Excel helps audit spreadsheets to reduce the risk of financial errors.
XLSior for Microsoft Excel helps audit spreadsheets to reduce the risk of financial errors.
Automated spreadsheet audit tools, such as XLSior, can speed the process. However, I believe nothing is better than a thorough audit by an experienced developer who was not involved in the creation of the spreadsheet under review. If there's no one in your business with the appropriate skills, I recommend you contact your outside accounting firm for a reference.

Richard Morochove is an IT consultant and writer who can be reached via e-mail.
To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.
  
Shop Tech Products at Amazon