Philip L. Bewig
You know it's true: Spreadsheets have errors like dogs have fleas. It is generally accepted that nine out of every ten spreadsheets suffer some error, and consequences can be severe:
- A cut-and-paste error cost TransAlta $24 million when it underbid an electricity-supply contract.
- A missing minus sign caused Fidelity’s Magellan Fund to overstate projected earnings by $2.6 billion (yes, billion) and miss a promised dividend.
- Falsely-linked spreadsheets permitted fraud totaling $700 million at the Allied Irish Bank.
- Voting officials reported spreadsheet irregularities in New Mexico and South Africa.
- A new drug introduction was delayed several months by an untested macro, costing the pharmaceutical company profits and its patients misery.
Have a colleague check your work before you publish it.
He should review your documentation, analyze your design, critique your test plan, and test for reasonableness.
Cell-by-cell inspection is tedious, but is the only method of spreadsheet testing known to be effective.
A traffic light tool, such as shown in the figure, can help:
- The auditor has already checked green cells, finding no errors.
- Yellow cells are ready to check because, as the arrows show, all their precedents are green.
- Red cells are not ready to check because they have unchecked precedents.