Operations Management: An Integrated Approach, 5th Edition

THE SPREADSHEET MODELING PROCESS

Base case

The model containing the “default” or “given” values for the inputs. This is normally the starting point for the analysis.

You need to follow basic steps in order to develop an effective spreadsheet model.

1. Ironically, the first step is to turn off the computer and instead draw a picture to better understand the situation. Identify the uncontrollable inputs, the decision variables, and the outputs. Define the logic necessary to transform the inputs into the outputs.
2. On paper, sketch out an overall plan for the model. In general, group the inputs together. Determine where the inputs, intermediate calculations, and outputs will go. Plan to highlight the key inputs and outputs to make the model easier to use for what-if analysis. Determine the formulas relating the inputs to the intermediate calculations and outputs. This can be very simple for some models (i.e., Profit = revenue – expenses), or it may be quite complicated. In general, the time spent planning a model in this step is normally much less than the time spent debugging an unplanned, completed model.
3. Develop the base case spreadsheet model. Group the inputs together logically. It usually helps to use a color-coding scheme so the user can quickly determine what are the inputs and outputs of the model. Break down the intermediate calculations so that each formula is relatively simple. ...