The road is littered with mistakes businesses have made in recording details in their spreadsheets. Here are some common pitfalls to avoid when trying to use technology to calculate the bottom line.
Just about every computer in the world comes pre-loaded with spreadsheet software, the king being Microsoft Excel. That doesn’t mean, however, every business user who has it knows how to use it. It’s very easy to make small mistakes within a spreadsheet. And as some businesses have found out the hard way, those little mistakes can translate into devastating results.
Consider the cautionary tale of the Fisher Auction Company based in Pompano Beach, Fla. A couple of years ago, the company was hired to handle Sarasota County’s online public auction of abandoned lots. In a rush to update the minimum bids, overnight some of the lots tripled in price on the county website. Bidders and real estate agents complained, local politicians were blamed, and the auction process broke down into total confusion.
For once, it wasn’t a politician’s fault.
The culprit: an input error in the spreadsheet listing the minimum bids. When revised, the unwitting Fisher Auction employee forgot to sort the value column and all the bids were thrown off from the lot numbers and auction ID numbers.
“These spreadsheets are approaching the complexity of enterprise applications, and yet they are absent the IT controls you would expect to have with those enterprise applications,” points out Eric Perry, vice president of product management at Prodiance, a spreadsheet risk management company based in San Ramon, Calif.
Perry has seen the gamut of typical mistakes from his clients, which primarily fall within the following categories:
The spreadsheet is too complex. Spreadsheets tend to grow virally, passing hand to hand. The more formulas, visual codes and links to other systems and databases, the greater the chance for error. Perry recalls one client that streamlined one of its more unwieldy spreadsheets that was critical to the business by reducing it to two tabs: inputs and outputs.
The spreadsheet is too big. A large file is more difficult to manage. “A five megabyte file is a lot of processing. When a spreadsheet gets to 30, 40 even 50 megabytes, it’s time to downsize,” says Perry.
Little or no access controls. People make mistakes. The more people are involved, the more opportunity there is for mistakes. Companies need to target their most critical spreadsheets and determine who needs certain edit ranges. All others should be assigned read-only status.
Little or no version and change controls. What’s the protocol for updating the spreadsheet? Should users “save” it under the same name or “save as” with a revised new name? How will everyone involved know where to find the latest version? What’s the editing process for making changes? Protocols need to be established and enforced. Change controls, in particular, need to include at least a second pair of eyes to double check all revisions.
Little or no training.Excel, as well as other spreadsheet offerings, is a very complex tool. It’s worth the investment to send users to a workshop or two.
Why errors go undetected
Perhaps one of the most basic mistakes is not realizing there is a mistake in the spreadsheet. “A lot of spreadsheet developers tend to assume that an answer within the spreadsheet is the answer. The system does not naturally alert the user there’s an error. That’s probably the most insidious problem,” says Mark Simkin, a professor of information systems at the University of Nevada, at Reno. In other words, flawed or not, when the data is inputted and calculated, it doesn’t leave any cells blank.
Simkin offers the following tips to catch mistakes:
Create spreadsheets with embedded auditing features and good models. Companies can download a number of tools from Microsoft’s Excel site to get started. Use the “auditing formula” feature under tools on the toolbar. Double check all models.
Enable other self-check features.Error check and auto correct options can be found under “tools”, as well.
“Get out your calculator,”says Simkin. Sometimes nothing beats comparing the spreadsheet’s math to your own.
Require developers to sign the spreadsheet.It should come as no surprise; human errors go down when there is a clear trail to the responsible party.
A splash of color helps
One last tip offered by Perry: “Color coding a spreadsheet has tremendous value. Anything that makes it easier for users to find the areas they are updating, like assigning unique backgrounds, colors or fonts to specific inputs or outputs, makes it a lot easier to avoid confusion. Color can really bring a spreadsheet to life.”
Like all the other applications in the Microsoft Office suite, Excel has a new and improved 2007 edition. Among the upgrades: the capacity for more rows and columns. Specifically, one single document can now build out to as much as a staggering one million rows and sixteen thousand columns.
That’s a lot of room for error.
Last updated: Oct 1, 2007
RENEE ORICCHIO is a technology writer and former supervising news producer for CNN Financial News. She has been covering the computer industry since 1987. @oricchio