**Overview of the Sensitivity
Toolkit**

** **

The Sensitivity Toolkit was created to bring the most powerful tools of sensitivity analysis to the spreadsheet. The Toolkit supports four different forms of sensitivity analysis:

Data Sensitivity creates a table and chart to show how an output cell
varies with changes in one (or two) inputs. In Tornado Chart, a set of
parameters is varied from low to high and the results for a single output cell
are reported. Solver Sensitivity runs the optimization program *Solver* on
a spreadsheet for a set of values for one (or two) parameters. Similarly,
Crystal Ball Sensitivity runs the simulation program *Crystal Ball* on a
spreadsheet for a set of values for one (or two) parameters.

This document illustrates how each of these tools works. We will
use the spreadsheet *Advertising Budget Model.xls* (shown below) to
illustrate the various options.

**Purpose: **Data Sensitivity is used to trace the effect on an output
of changes in one (or two) inputs.

**Options: **

One-way table: specify one input parameter

Two-way table: specify two input parameters

**Examples:**

**One-way table**

*Question:* How sensitive is Profit to changes in
the Price?

*Procedure:*

Select Sensitivity Toolkit – Data Sensitivity.

Select One-Way Table and specify the cell address for the Result, in this case Profit in cell C21.

Click on Next.

Specify the Cell to Vary, Price, in C7.

Choose the range of prices, from 20 to 60 in steps of 1.

Click on Finish.

The results consist of a table showing the Profit resulting for each value of Price. A line chart is automatically generated from this table.

**Two-way table**

*Question:* How sensitive is Profit to changes in
the Price and in the Cost?

*Procedure:*

Select Sensitivity Toolkit – Data Sensitivity.

Select Two-Way Table and specify the cell address for the Result, in this case Profit in cell C21. (Note: Two-way tables may have only one Result cell specified.)

Click on Next.

Specify the First Cell to vary, Price, in C7.

Choose the range of prices, from 20 to 60 in steps of 1.

Specify the Second Cell to vary, Cost, in C8.

Choose the range of prices, from 20 to 30 in steps of 1.

Click on Finish.

The results consist of a table showing the Profit resulting for each value of Price and Cost. A 3D chart is automatically generated from this table.

**Purpose:** A Tornado Chart ranks input parameters in terms of their impact
on the output. This tool allows the user to create Tornado Charts for a
spreadsheet using one of three alternative approaches. It provides a convenient
way to determine the relative sensitivity of the results to an entire set of
input parameters.

**Options:**

1. Common Percentage Option: varies each parameter up and down by the same percentage of the base case value.

2. Variable Percentage Option: varies each parameter up and down by its own percentage of the base case.

3. Percentile Option: varies each parameter down to the 10th percentile (the value below which the parameter will fall with a probability of 10%), and up to the 90th percentile (the value above which the parameter will fall with a probability of 10%).

**Examples:**

**Common Percentage Option**

*Procedure:*

Select Sensitivity Toolkit – Tornado Chart.

Specify the cell address for the Result, Profit, in cell C21.

Specify a range for the Input Parameters, C7:G15 (blank cells are ignored).

Choose Constant Percentage for Analysis Type.

Click on Next.

Next select the percentage amount to vary each parameter.

Click on Finish.

The results consist of a table of the base case values and the high and low results for each input, and the Tornado Chart.

**Variable Percentage Option**

*Procedure:*

Select Sensitivity Toolkit – Tornado Chart.

Specify the cell address for the Result, Profit, in cell C21.

Specify a range for the Input Parameters, C7:G15 (blank cells are ignored).

Choose Variable Percentage for Analysis Type.

Click on Next.

Specify the percentages for each input parameter. It is best to create a separate range in the spreadsheet for these percentages. For example, we have entered the required percentages in M7:Q15.

Click Finish.

The results consist of a table of the base case values and the high and low results for each input, and the Tornado Chart.

**Percentile Option**

*Procedure:*

Select Sensitivity Toolkit – Tornado Chart.

Specify the cell address for the Result, Profit, in cell C21.

Specify a range for the Input Parameters, C7:G15 (blank cells are ignored).

Choose Percentiles for Analysis Type.

Click on Next.

Specify a range of the 10^{th} and 90^{th}
percentiles for each input parameter. It is best to create two separate ranges
in the spreadsheet for these percentiles. For example, we have entered the
required percentages in M7:Q15 and M18:Q26, respectively.

Click Finish.

The results consist of a table of the base case values and the high and low results for each input, and the Tornado Chart.

**Purpose: **Solver Sensitivity is used to trace the effects on the
optimal solution of changes in one (or two) inputs. It calls on Solver to
optimize a spreadsheet model for each of the input values specified by the
user. It reports the value of the objective function and the unit change in the
objective for each value of the input parameter. It also can report any other
values in the spreadsheet, including the values of the decision variables.

**Options:**

One-way table: specify one input parameter

Two-way table: specify two input parameters

**Examples:**

**One-way Solver Sensitivity**

*Procedure:*

*Make sure that Solver has been run at least
once on the model.*

Select Sensitivity Toolkit – Solver Sensitivity.

Specify the cell address for the Objective Function, Profit, in cell C21. (Normally, this selection will have been made automatically.)

Specify any other cells to include in the table; in this case we will include the decision variables in D18:G18.

Choose One-Way Table.

Click on Next.

**Note:** The check box can be used to control the
initial values of the decision variables in nonlinear optimization problems.
When this box is NOT checked, each successive run of Solver will start with the
values of the decision variables in the spreadsheet at the start of the
sensitivity run. If this box IS checked, each successive run will use the final
decision variables from the *previous run* as its starting values.

Select the input parameter, Price, in cell C7.

Specify the range over which to vary Price: 30 to 50 in steps of 1.

Click on Finish.

The results consist of a table with the value of the input parameter, the value of the objective function, the unit change in the objective function per unit change in the input parameter, and the values of the Other Cells.

**Two-way Solver Sensitivity**

*Procedure:*

*Make sure that Solver has been run at least
once on the model.*

Select Sensitivity Toolkit – Solver Sensitivity.

Specify the cell address for the Objective Function, Profit, in cell C21.

(Normally, this selection will have been made automatically.)

Specify any other cells to include in the table; in this case we will include the decision variables in D18:G18.

Choose Two-Way Table.

Click on Next.

**Note:** The check box can be used to control the
initial values of the decision variables in non-linear optimization problems.
When this box is NOT checked, each successive run of Solver will start with the
values of the decision variables in the spreadsheet at the start of the
sensitivity run. If this box IS checked, each successive run will use the final
decision variables from the *previous run* as its starting values.

Specify the first input, Price, in cell C7, and its range of variation (30 to 50 in steps of 1).

Specify the second input, Cost, in cell C8, and its range of variation (20 to 30 in steps of 1).

Click on Finish.

The results consist of a two-way table showing the values of the objective function for all combinations of the two input parameters. A 3D chart is also provided.

**Purpose: **Crystal Ball Sensitivity is used to trace the effects of
changes in one (or two) inputs. It calls on Crystal Ball to run a simulation
for each of the input values specified by the user, and reports the resulting
values for Forecast cells.

**Options:**

One-way table: specify one input parameter

Two-way table: specify two input parameters

**Examples:**

**One-way Crystal Ball Sensitivity**

*Procedure:*

Select Sensitivity Toolkit – Crystal Ball Sensitivity.

Select the Forecast cell(s), here Profit. (Crystal Ball Sensitivity will recognize all the Forecast cells defined in the model.)

Select the Statistics to record, here the Mean. (The available statistics include the Number of Trials, Mean, Standard Deviation, Minimum, Maximum, and Mean Standard Error.)

Select One-way Table.

Click Next.

Enter the Cell to Vary, here Q1 Advertising in D18.

Specify the range over which to vary the input parameter, here 5000 to 15000 in steps of 1000.

Click on Finish.

The results consist of a table with the input parameter Q1 Advertising in the first column and the mean value of Profit as estimated by simulation in the second column.

**Two-way Crystal Ball Sensitivity**

*Procedure:*

Select Sensitivity Toolkit – Crystal Ball Sensitivity.

Select the Forecast cell(s), here Profit. (Crystal Ball Sensitivity will recognize all the Forecast cells defined in the model.)

Select the Statistics to record, here the Mean. (The available statistics include the Number of Trials, Mean, Standard Deviation, Minimum, Maximum, and Mean Standard Error.)

Select Two-way Table.

Click Next.

Enter the First Cell to vary, here Q1 Advertising in D18.

Specify the range over which to vary the first input parameter, here 5000 to 15000 in steps of 1000.

Enter the Second Cell to vary, here Cost in C8.

Specify the range over which to vary the second input parameter, here 20 to 30 in steps of 1.

Click on Finish.

The results consist of a table with the first input parameter Q1 Advertising down the left column, the second input parameter across the top row, and the mean value of Profit as estimated by simulation in the body of the table.