Operations Management: An Integrated Approach, 5th Edition

EVALUATING SPREADSHEET MODELS

After you develop a spreadsheet model, how do you evaluate it? One useful way to think about the quality of a spreadsheet is to assess it along three dimensions. Specifically, a spreadsheet model should be correct, flexible, and documented. A spreadsheet model should produce the correct answer for the information given. Usually we think of this in the context of the base case: for the “given” values for the inputs, does the model calculate the correct results? However, a spreadsheet model needs to do more than simply calculate the correct answer.

A spreadsheet model must be flexible in producing accurate results even if the user changes any of the inputs (controllable or uncontrollable). To provide this flexibility, users should enter each input only once in the model. For example, consider a model that includes a unit cost as an input. This value, say $3.50, would be entered into a single cell, for instance, B8. Any other cells using unit cost in their calculations would then reference cell B8 rather than having the $3.50 value “hard-coded” inside its formula. In this way, the user only needs to change the data item in a single cell to analyze a new problem. Flexibility is often ignored by people developing a spreadsheet they think will only be used once. Most models in the real world are used repeatedly, with different input data. Even if you think yours is a model that will be used only once, your model will be easier to explain to others if every ...