Errors in Spreadsheets are Pandemic

G Roper writes "Studies show that most spreadsheets have critical errors in one percent of their cells, well beyond a permissible level. Here are some news stories about spreadsheet errors. Spreadsheets won't protect a firm from liability when they are audited and spreadsheet errors found: spreadsheets are not secure, provide no audit trail and won't pass HIPAA or Sarbanes-Oxley auditing. How are Slashdotters coping with the proliferation of spreadsheets in the face of greater legal accountability and auditing?"

spreadsheet errors are hard to fix

[yagu]

From the abstract: "Although spreadsheet programs are used for small "scratchpad" applications, they are also used to develop many large applications. In recent years, we have learned a good deal about the errors that people make when they develop spreadsheets. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists. "

I think "how many errors, not whether an error exists" is just as true for applications and programs written in any language or using any technology. What's so insidious about spreadsheets is their integrity and the difficulty to maintain that.

Once you start changing any complex spreadsheet you risk and almost guarantee corrupting other parts of the spreadsheet ostensibly okay. The spreadsheet is so inextricably integrated to itself, you pull one string, and some widget a million miles away suddenly misbehaves, though, you're unlikely to notice until later, if at all.

IT should be strict about policy around spreadsheets... spreadsheets are great powerful tools, but they shouldn't be anointed as applications.

I worked on a team that created a large software development workbench. A critical piece of this workbench included a suite of spreadsheets with amazingly complex macros and formulae hidden way out of the casual users' sight. Immediately upon release (and much aligned with my warning and prediction) the workbench fell apart on a daily, even hourly basis, among many teams out in the field. Turns out users were deleting rows in the template spreadsheets deemed irrelevant and unnecessary to their work. Guess what got deleted along with the "unnecessary rows"? Yep, chunks of macros critical to the proper function of the workbench.

Auditing in Excel

[everphilski]

provide no audit trail

You can provide an audit trail in Excel:

Tools->Share Workbook->click "Multiple Users"->click "Advanced"->select how many days you want to keep a history for.

(It might not be good enough for HIPAA or SA but there is an audit trail ... )

Re:and the error rate before the computer age....

[PlusFiveTroll]

I'm not sure where you got the %1 idea from, but in one of the linked articles there was a a $50 million dollar spreadsheet error (spending bugeted money that did not exist). There was also an error in a spreadsheet that miscalculated natural gas reserves that causes a BILLION DOLLAR rise in the commidity value (aka speculators) which was not real.

and lastly, who cares? Think Sarbanes-Oxley, if your a CEO, you care, alot.

Re:spreadsheet errors are hard to fix

[Red Flayer]

I've developed some spreadsheet mini-applications for various employers, and there are two basic rules I swear by about both distributed and centralized spreadsheet apps:
1. Black box. Users should see input and output, that's it. Especially wth Excel, a user with a little bit of knowledge is VERY dangerous.
2. Lock it down. Every cell that's not an input should be password-protected. This would have prevented the deletion problem your team experienced.

Re:and the error rate before the computer age....

[Fatchap]

Here's why: Section 404 of The Sarbanes Oxley Act.

Requires each annual report of an issuer to contain an "internal control report", which shall:

(1) state the responsibility of management for establishing and maintaining an adequate internal control structure and procedures for financial reporting; and

(2) contain an assessment, as of the end of the issuer's fiscal year, of the effectiveness of the internal control structure and procedures of the issuer for financial reporting.

Each issuer's auditor shall attest to, and report on, the assessment made by the management of the issuer. An attestation made under this section shall be in accordance with standards for attestation engagements issued or adopted by the Board. An attestation engagement shall not be the subject of a separate engagement.

In a nutshell if you are covered by the Act (basically you have any debt raised in the US or are listed on a US Exchange), you will need to have an external audit sign off on your internal controls around your finacial statement. This means you are asking an auditor (noramlly very risk adverse people) to say that you have a good set of internal control, including that your all your IT applications, including any spreadsheets you use. With a large ERP such as SAP you can create good controls, such as access controls like segregating duties, relatively easily. With a speadsheet this can be very hard. How do you have an good, testable control in this area? If you don't have a testable control how can you expect your auditor to sign off on it? If your auditor can't sign off on it then you are really in trouble!

Re:Hardware?

[Darkman, Walkin Dude]

I also demand to put the scores near the comment title.

I think the thinking there is to cut back on the karma whoring and make comments stand on their own merits. Also it should help keep groupthink under control, and is more indicative of the fact that moderation really only represents the opinions of one, two or maybe five basically random people out of all the thousands that read slashdot. To whit, its not terribly important.

Re:I hear we need:

[smokeslikeapoet]

No kidding. I had to compute 5 year straight-line depreciation for accounting records going back to 1992, to insure our estimates were correct. I wasn't going to compute 10 different formulas for each year that also omitted equipment sold during those years. The result was a 20 line excel formula that required a little programming prowess. I'm the IT guy here and don't normally do accounting stuff, but it wouldn't have gotten done by our deadline without me. Most accountants do not have the programming knowledge to use spreadsheets effectively. And most schools do not teach spreadsheets at the level that is needed in the real world. It really is a forgotten realm in the industry. Oh yeah, and F1 really helps when you get stuck.

you CAN "compile" excel spreadsheets

[Anonymous Coward]

Another thing that would make spreadsheets more useful/powerful would be the ability to COMPILE them into another form.

You can already do this using a program called Turboexcel, which converts excel "programs" into C++ (which can still be used within excel, but run 100 times faster). The banking industry uses this quite a bit.

http://www.turboexcel.com/ [turboexcel.com]

-R

Re:spreadsheet errors are hard to fix

[Ray Panko]

Spreadsheet errors look very much like software errors in both frequency and type. Spreadsheet errors are hard to fix in the same way that software errors are hard to fix. Cell protection is one good practice, but spreadsheet development needs good practice across the entire life cycle. Most significantly, spreadsheet developers need to spend about 30% of their time doing testing. The average is closer to 0% now. For research on spreadsheet errors, consider my website, http://panko.cba.hawaii.edu/ssr [hawaii.edu]. I also have a companion website on human error, http://panko.cba.hawaii.edu/HumanErr [hawaii.edu]. The human error website shows that human error is pretty much constant across application domains with comparable complexity and doesn't vary widely across people. Alexander Pope's dictim, "To err is human" is not only true. Today, we can quantify it. And, unfortunately, we can also ignore it.

Use Excel Audit Tools

[Anonymous Coward]

There are third party tools that do a good job at finding spreadsheet errors. The common, hard to find ones such as incorrectly copied formulas and incorrect A1 references.

The Spreadsheet Detective is one of the oldest and most established, http://www.spreadsheetdetective.com/ [spreadsheetdetective.com]

Like all software development, peer review is the key. But for spreadsheets, this is infeasible without tools.

Anthony