Explain basic spreadsheet approaches for implementing decisi

Explain basic spreadsheet approaches for implementing decision models in Excel.

Solution

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. You can then more easily spot
and correct errors. Research has shown that most spreadsheet model developers
believe their products to be error-free, but this assessment is usually
wrong! Thus, you need to scrutinize formulas and results during and after the
spreadsheet development effort. Use specific text labels, including units of
measure, so that others reading the model can follow your thought process.
The outputs should also be clearly labeled and color-coded. For large models
(loosely speaking, those that do not fit in a window screen), it is often very
helpful to provide a summary of the outputs next to the inputs.
4. Test the spreadsheet model using trial values. Verify the results by hand, if possible.
If you have broken down the intermediate calculations into relatively simple
formulas, this step is much easier.
5. Use the model to perform the needed analysis. This may involve a relatively simple
calculation, preparation of a chart, or more substantial analysis. Two common
types of analysis are scenario analysis and sensitivity analysis. A scenario is
a specific set of conditions that could occur in a real situation. A common practice
is to look at the base-case, best-case, and worst-case scenarios. Scenario
analysis helps a decision manager gain additional insight into a situation. Sensitivity
analysis involves studying the changes to the output of the model (e.g.,
profit) as one or more of the inputs (e.g., demand) change. Sensitivity analysis
helps to identify the inputs that cause the most change in the output. Since the
values for the inputs are often just estimates, it is important to understand this sensitivity. Break-even analysis is one special case of sensitivity analysis. No
matter how simple or complex the analysis, the time invested developing a
high-quality base-case model will pay off when you need to do additional
analysis. Spreadsheet programs contain a number of tools to assist the analyst;
we will provide an overview of some of these later in this supplement.
6. Document the model so that others can easily understand it. Remember, others
may not think of the problem in exactly the way you do, so descriptive labels
and a logical layout are extremely important. Indeed, ideal spreadsheet models
are almost “self-documenting” as a result of the way you organize and label
them. Cell comments can also be used to document and explain key formulas.