Spreadsheets are important tools for all manner of organizational analysis and decision making. Considering their importance, it is critical they are clear, accurate, and auditable. We’ve built hundreds of models and in the process we’ve developed a set of guidelines that help us ensure our models reach these goals. Here are our top seven recommendations:
1. Focus on Organization
A fundamental question for any model is how to organize it. Starting with a well-organized structure can save many headaches down the road.
First, ask yourself:
- How can I break the model into logical sections?
- How should I label sections?
- Who is my audience for this model? Would this make sense to him or her?
- Would the user understand if I moved certain sections to different sheets?
Once you have the basic outline, work your way through the calculations linearly. We typically build models with logic that starts at the top of a sheet and works linearly downward. Enforcing a structure like this ensures the modeler’s logic is sound and makes it easier for the user to see that A leads to B leads to C and so on. Models with logic moving higgledy-piggledy over a worksheet or workbook are easy to foul up and hard to understand.
