Jonathan P. Caulkins, Erika Layne Morrison, & Timothy Weidemann
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.
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.
2006, Nonprofit World, Volume 24, Number 3, May/June, pages 26-28