1.5 Exercise: One-Variable Sensitivity Analysis
Introduction: Model for this Section
In this section, we will work with a “clean” copy of the model that you developed during the starter course:
We will create sensitivities of the following forms:
- Capturing the effect on a single output as a single input varies across a range of values.
- Capturing the effect on several outputs as a single input varies across a range of values.
Depending on which direction (horizontal or vertical) is used for the input, and which for the output(s), one has a row-form or column-form sensitivity.
Row-Form Sensitivity with Single Output
In a row-form Data Table, one pre-defines – in consecutive cells along a row – the list of values that one wishes to use for a particular input variable. For example, in a blank area of the model, one could create a structure such as:
The second step is to define which output calculation of the model is to be analysed. This is done by making a cell reference to that output, and which is placed in the cell that is diagonally-left below the list of inputs. Thus, to analyse the effect on Sales Revenue (which is in cell C8) of changes in the value of Price, one would create a reference link as follows:
When this is entered, the cell will show the value of cell C8 (i.e. as would any other regular Excel formula):
To populate the Data Table, one selects the full two-dimensional range associated with the table:
Then, one selects Data>What-If Analysis>DataTable:
This will result in the Data Table dialog being presented as empty. The final step is to inform the dialog that the cell to be treated as the input is cell C3 (containing the input value for Price). This is a “Row input cell”, since the list of values that are to be used have been placed in a row:
On pressing OK, Excel will fill the table with the sensitivity calculations:
Note that these shown values are dynamic, in the sense that there are in fact formulas (known as array formulas), which on the Formula View would look like:
The values would update if another (precedent) model input changes. For example (since we are dealing with Sales Revenue, which has only Price and Volume as its precedents), if the value of the Volume were changed from 800 to 1000, then the Data Table would update:
Row-Form Sensitivity With Several Outputs
One can include other outputs within a sensitivity table. In the case of a row-form Data Table, one simply lists the other outputs as cell references in additional rows. For example, to add Profit to the above table, one would create the cell references as follows (the image using the Formula View for clarity):
The one needs to reselect the full two-dimensional range associated with the table, and reapply the Data>What-If Analysis>Data Tables step as above (setting cell C3 as the row input, as before), to give (after resetting Volume to the original 800):
One may also apply some formatting (such as adding borders) and some labels to make the context clearer, for example:
Finally, since the hiding of columns or rows is generally not to be recommended, one could alter the text colour of the referencing cells, which serve no purpose frm a communication perspective here, for example as:
Column Form Sensitivities with One or Several Outputs
Sensitivity tables can also be created which are presented in which the list of inputs values is contained in a column (i.e. listed in consecutive rows). The choice to do so rather than using the row form is essentially a presentational one. For example, one could list the values to be taken by Price in a column, and in this case the reference to the output cell would be diagonally to the top-right of this list (using the Formula View for the image):
For multiple outputs, it would simply be:
The table would be completed by selecting the full two-dimensional range, and then using the Data>What-If Analysis>Data Table (as before), and then completing the dialog box, noting that the cell to be varied (C3) is represented by the values that are listed in the column of the table:
The completed Data Table would be as follows (when reverting to the normal view, not the Formulas View):
Finally, one may choose to present this with improved formatting and labelling, for example as:
