1.6 Exercise: Two-Variable Sensitivity Analysis
It is possible to use a Data Table to show the effect of changing two model inputs at the same time. To do so, one creates a table structure with the list of values for one variable in a row, and the other in a column. Also, the cell reference to the output must be placed in the top-left corner of the table.
For example, one could create the table so that the possible values of Price are shown in a column and the values for Volume are along the top row, with the cell reference to the profit in the top-left. The following image shows the formula view of this (using the Formulas/Show Formulas):
The values in the table are filled by selecting the full table area, and then completing the dialog box under the Formulas>What-If Analysis> Data Table, as earlier. In this case, the row input is the reference to the Volume input cell and that column input to the Price input cell:
The completed table will appear on pressing OK, and can then be formatted with labels, to give a result such as:
In a two-way DataTable, it is therefore in principle possible to show only the sensitivity analysis for one output item (as the two inputs vary). Of course, it is possible to construct multiple DataTables (i.e. one for each output) or to “reuse” a DataTable by changing the output reference cell (C11 in the above example) to another cell. For example, by changing the reference to C8 (from C11), one could run a sensitivity of the Sales Revenue as Price and Volume are varied.