Modeling 101: Clear, Accurate, and Auditable Spreadsheets

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.

2. Create Inputs and Outputs Sections

A key purpose of many models is to calculate certain outputs based on one or more variables. Therefore, consider incorporating an inputs and outputs section. With the inputs grouped together, a user can easily see the variables that drive the outputs. The main outputs should be located close to the inputs so that the user can see the effect of changes. Figure 1, below, shows an example of an inputs and outputs section.

Figure 1: inputs and outputs example

3. Use Color Coding

Figure 1 also includes another organizational technique – color coding. The input cells are green and the output cells (or in this case, the output section) are blue. This draws the user’s eye to the main parts of the model and helps the user quickly identify different pieces.

4. Write Clear and Precise Labels

One of the most important things a modeler can do to make his or her model clear, accurate, and auditable is to write good labels. Cell labels should have units, as necessary, and use descriptive names. For example, “Customer PPA Rate (¢/kWh)” is clearer than “Price”. In complex models, it becomes critical that both the modeler and the user understand precisely what the value in a cell represents. Don’t confuse “cost” with “price” because of sloppy labeling!

5. Use Explanatory Text

Sometimes a clear label is not enough. Additional explanatory text can help a user understand how the inputs are used and the reasoning behind certain calculations. In a particularly complex model, it may be helpful to explain where the inputs are being used or if there are constraints. Explanatory text can also link to external resources.

Figure 2: example of explanatory text

6. Use Cell Names

Naming cells can be a powerful way to simplify formulas and make auditing easier. Rather than refer to cell “C5” in a formula, you can assign that cell a descriptive name. See Figure 3.

Figure 3: example of naming a cell

Cell names are particularly useful when numerous formulas will refer to a single cell on another worksheet.

7. Incorporate Error Checks

Another way to ensure accuracy is to build in error checks. An error check is a separate calculation that checks if a result is what you expected. For example, if you expect that the sum of certain percentages adds to 100%, then you can incorporate a check to calculate that. Figure 4 shows an example of a “checks” worksheet. Each of the checks in the model are located on other sheets, and then they are combined on the Checks sheet.