i-nth logo

Authors

Jonathan P. Caulkins, Erika Layne Morrison, & Timothy Weidemann

Abstract

Your spreadsheets may be disasters in the making, as a new study makes clear.

Here's what to do. Strive for spreadsheets that are validated, reliable, and auditable.

Quality control in the real world means:

  • Look at the decision the spreadsheet is designed to inform. What's the impact of the decision? Will inaccuracy damage your credibility?
  • Look at the spreadsheet itself. A large or complex spreadsheet is more likely to have errors.
  • Provide a yardstick. Give employees guidelines to help them decide whether their spreadsheet needs more quality-control steps.
  • Offer training. Find training which focuses on the principles of good design, rather than simply teaching more advanced features.
  • Foster a culture of quality. Encourage good spreadsheet design, offer staff training on error reduction strategies, and insist on a full peer review of high-priority spreadsheets.

Sample

Guidelines for building more reliable spreadsheets:

  • Design separate sections for data and calculations. "Modules" of information should be separated: data in one place, calculations in another. Then data can be easily updated and calculations reviewed for accuracy. Data should be entered only once and then referenced through formulas.
  • Break long formulas into small, digestible pieces. It's easier for reviewers to follow formulas that are short, with inputs located close together on the spreadsheet.
  • Include documentation. Even the developer may not understand the spreadsheet months later! Documentation assures that spreadsheets are organizational resources for shared decision-making.
  • Keep an audit log of changes and scenarios. Identify changes for reliability.
  • Implement version control. Don't overlook the basics, such as standardizing file-naming conventions.

Publication

2006, Nonprofit World, Volume 24, Number 3, May/June, pages 26-28

Full article

Are spreadsheet errors undermining decision-making in your organization?