Spreadsheets are fun. They allow a layperson to create some very powerful data models. There are limitations for extreme cases, but will suffice for an average analyst.
According to IBM’s Spreadsheet Modeling Best Practice, a spreadsheet model should:
- Easy to use
- Focused on important issues
- Easy to understand
- Reliable
These are my top recommendations based on my experience. There’s no scientific research, just my experience.
- Properly Documented – Whether the documentation is included in the spreadsheet or written as a separate document, the model should have instructions that label the assumptions, methodology, and limitations of the model. It should allow someone that’s reasonably knowledgable in basic spreadsheet usage to pick up the model and make modifications and gain confidence in the reliability of the model.
- Variables Clearly Labeled – The spreadsheet should let the user know where functions are used and where variables should be entered. If necessary, the formula cells should be protected. I like to pull elements of Web 2.0 styles and make large, bright, and visually stimulating data entry fields.
- Traceable – If formulas and macros are used, it should be relatively easy to trace the data and formula and come to the same conclusions.
- Simple – The model should be as simple as possible to achieve the objective. If a simple formula can be used, do so. Using non-traditional formulas can create unnecessary complexities and make it difficult to trace.
Also check out the Spreadsheet Standards Review Board for some spreadsheet-geek standards.