1.3 Sensitivity Analysis in Model Testing: Exercise Using Manual Sensitivities
The use of “sensitivity analysis as a thought-process” (in model design) does not involve producing any numerical results, since the model is not yet built. When a model is either built (or is being built), the simplest way to create numerical sensitivity analysis results is to do so manually. That is, one simply changes the value of an input variable and observes the effect on the output(s). (Of course, whilst this is simple to do, it may not always be the most efficient way, for reasons discussed later).
There are two variations of how to run this manual analysis:
- Observation only. One makes a change and observes the effect on the model’s calculations. This is a useful way to check the basic integrity of the model, or of individual formulas, especially when building a model. One sense-checks the results, asking questions such as: Do all the items in the model change in the way one would expect? Do some not change, even though one would have expected it?
- Recording of values. Here, one makes a table in order to keep a record of the values of the inputs and the output(s) produced.
Exercise for Self-Working
For the following exercise, please work with a copy of the model that you built previously (in the Starter course). For reference, this initially should resemble the items and calculations in the following image (excluding column E, which you do not need to build, at is shows the formulas used in column C, and is provided for additional reference and clarity):
Then (referring to the image below for the completed results):
- Place the values of Price that you wish to use (say 8, 10, and 12) in an appropriate area of the worksheet.
- In turn copy each value into the input cell.
- First, notice the change in the values of the items that are affect by Price. Note also that if the workbook Calculation is set to “manual”, then one will need to press F9 each time and input value is changed (see earlier materials).
- Record the values for relevant items (in this case, Sales Revenue and Variable Cost were recorded) by copying the values into a relevant area of the workbook. (In this example, the values can be entered directly as they are simple whole numbers. In a more general case, one would use the Copy/PasteValues menu [an operation which is likely familiar to many readers, but is discussed in the next chapter]).
In general, this manual approach to sensitivity analysis gives valid results, it is not recommended as a standard approach: The resulting numbers are not linked to the model, and will not update if the values of other model inputs are altered or if other changes made to the model. Thus, the recorded values are likely to become outdated and make be used in an incorrect way in subsequent analysis or presentation of results. Most often, the better approach is to automate the processing using a DataTable, as discussed in the next sections of this Chapter.