Garbage In – Gospel Out!

Rick Carter and I co-hosted a pretty lively Birds of a Feather session yesterday on best practices in Spreadsheet Modeling to improve productivity and reduce errors and risk. Before going any further, for the participants, here’s the auditing tool I mentioned – www.audinator.com. I think a new version is due out soon.

Among the ideas we discussed:

Data cleaning & validation

• Work from the “edges in” to make sure you’ve captured all necessary data
• Compare relevant “sums” to some external data source to make sure nothing is missing
• Look for outliers — data points that don’t look “normal.  Look for min and max values; sort the data, run histograms and scatter charts

Modeling practice

• Standardize cell colors, e.g., input cells blue, decision variables red, outputs green (or whatever color scheme you prefer)
• Maintain integrity of source data; don’t perform ANY operations on the original data set
• Use revision control.  Tools are available that can capture an audit trail, or to track which users have which version of a model.

Error Prevention

• Use Peer Review (best practice from software development)
• Use an auditing tool
• Keep it simple!  If a non-technical person can understand your model, you probably got it right.  Try your spouse or your children!

We hope the attendees enjoyed the session.  Let Rick or me know if you have any follow-up questions or comments.

Check out the SPRIG – Spreadsheet Productivity Research Interest Group.

Rob