Spreadsheets considered harmful
The folks over at Computerworld have noticed that spreadsheets are a security risk. Sadly, the article focuses entirely on disclosure issues: users keeping spreadsheets with sensitive data on their desktops and laptops which then are lost or compromised. A far more serious risk of spreadsheet use is that they are highly trusted in making business decisions but at the same time are extremely prone to inadvertent errors.
Way back in May, Reg Developer published an excellent article on the damages done to businesses through spreadsheet calculation errors. The bigger risk, it seems, is not disclosure of information, but calculation errors.
This is blind spot of risk we’ve advised clients about for years and it’s great to see the issue gaining attention through articles and the efforts of groups like euprig.org. While spreadsheet calculation errors have caused the most notable business disasters, the risk also extends to every form of ad hoc data tool including desktop databases like MS Access and reporting tools like Crystal Reports.
The nature of a spreadsheet is that calculations are built on previous ones, almost never with any form of error checking. An off-by-one input error or inadvertent mistake in a formula can cascade through a spreadsheet to become a monumental error. Anyone who’s studied accounting and the tedious requirements of double-entry bookkeeping should understand the need for validating financial calculations, but sadly when it comes to spreadsheets that’s not the case.
For most users, testing of spreadsheets and ad hoc databases is a simple visual process. If a calculation is noticeably out of whack the user will go back and find the mistake. But if it “seems right” there’s usually no further testing. Who takes the time to verify spreadsheet calculations by performing them manually on paper with a variety of inputs? Who bothers to perform the calculations a second way and compares the two results?
Subtle errors (such as from rounding) don’t stand out at a glance. Throw in a few macros that inadvertently change key cells or skips rows and it’s easy to get results that look plausible but are incorrect. Since most spreadsheets are used to process financial data, the consequences can be disastrous.
Any spreadsheet used to make business decisions needs to be recognized as a critical business application. It may have started life as a quick and dirty “what if?” exploration tool, but once decisions are made from the calculations, it needs to be treated with the same care as every other “business intelligence” tool.
Redeveloping a spreadsheet into a database application can be costly, but weigh that cost against the potential losses due to reliance on bogus information from a minimally tested spreadsheet.
Update: There is at least one book available on this topic:
- Spreadsheet check and control by Patrick O’Beirne (Systems Publishing, September 2005)
Related posts: