Scenario Analysis in Excel

With Option Buttons and

the What-If Scenario

Manager

Two excellent tools in Excel to conveniently perform scenario analysis are Option Buttons and the What-If Scenario Manager. Scenario analysis in Excel involves switching different sets of input values into the same set of formulas to compare the differences in outcome. The classic example is the Best Case-Expected Case-Worst Case set of scenarios. This article will demonstrate in detail how to implement this set of scenarios with both the options buttons and with the What-If Scenario Manager. Here is a brief description of each:

 

Scenario Analysis With Option Buttons – Formerly called a Radio Button, the Option Button to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article. Switching between different scenarios is as simple as selecting a different option button. An example using options buttons to implement scenario selection is shown as follows:

(Click On Image To See a Larger Version)

 

Scenario Analysis With the What-If Scenario Manager -

The Scenario Manager is a tool found under the What-If Analysis section which is located under the Data tab. The Scenarios are created and stored within the Scenario Manager. When an individual scenario is selected within the Scenario Manager, the value in the output cell is changed to the value specified by the scenario as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The Scenario Manager must be brought up every time that a scenario change is desired. Option buttons are always visible making them a more convenient option to switch between scenarios.

 

Turning On the Developer Tab in the Excel Ribbon

Form controls are accessed from the Developer tab. By default the Developer tab does not appear in the ribbon. The Developer tab must be configured to be one of the tabs permanently displayed in the ribbon. This can be quickly implemented with the following steps:

File / Options / Customize Ribbon

This will produce the following dialogue box. The checkbox next to Developer will initially be unchecked. Simply check that checkbox and the Developer tab will become a permanently visible part of the ribbon as long as that checkbox remains checked. This is shown in the following images.

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The Developer tab now appears as a permanent part of the ribbon as long as the checkbox next to Developer remains checked. This is shown as follows:

(Click On Image To See a Larger Version)

All form controls are made available clicking Insert in the Developer tab.

(Click On Image To See a Larger Version)

 

Scenario Analysis With Option Buttons

Formerly called a Radio Button, the Option Button is used to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article.

The option button is creating by clicking the option button icon in the Form Controls menu as follows:

(Click On Image To See a Larger Version)

When the option button icon is clicked, the cursor become the tool to place the option button on the Excel worksheet. The outline of the option button is created by clicking and then dragging the cursor. The location and dimensions of the option button can be changed at any time after the initial option button has been created. The initial option button will appear as follows:

(Click On Image To See a Larger Version)

The option button must now be formatted. This is accomplished by right-clicking anywhere in the option button to bring up the following short-cut menu. Format Control is selected from the short-cut menu. This brings up the Format Control dialogue box. The cell which will hold the output of the option button must now be specified. In this case that cell is A4.

Each new option button that is created will be part of the initial group of option buttons. All option buttons in the same group will collectively send their output to the same cell. Only one option button in a group can selected at any time. The output cell, A4 in this case, will contain the number that is associated with and unique to the specific option button that has been selected.

(Click On Image To See a Larger Version)

The first completed options button and output cell appear as follows

(Click On Image To See a Larger Version)

A second options button can now be created. This second options button will be part of the same group that contains the first options button. The Format Control dialogue box that will appear when the second dialogue box is right-clicked as follows. By default the value of the second options buttons will initially be Unchecked and the output will be linked to the same output cell of the first options box, which is A4.

Changing the output cell in the Format Control of an options button will change the location of the output cells for all options buttons in the same group.

(Click On Image To See a Larger Version)

A third options button can be added to the group as follows:

(Click On Image To See a Larger Version)

The labels associated with each options button can be renamed by right-clicking on the original label and then typing in the new label. The result might be as follows:

(Click On Image To See a Larger Version)

These three options buttons can be assigned to a single group by selecting the Group Box from the list of Format Controls.

(Click On Image To See a Larger Version)

When the group box icon is clicked, the cursor become the tool to place the group box on the Excel worksheet. The outline of the group box is created by clicking and then dragging the cursor. The location and dimensions of the check box can be changed at any time after the initial check box has been created. Any options buttons that are inside the group box are part of one group. Any options buttons that are outside of the group box are part of a different group. The initial group box will appear as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The Option Button is designed to create the ability to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are straightforward methods to implement scenario selection. An example of each of the two methods are shown as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

Conditional formatting was applied to display negative values in red instead of black as shown as follows:

(Click On Image To See a Larger Version)

 

Scenario Analysis With the What-If Analysis Scenario Manager

The Scenario Manager is a tool found under the What-If Analysis section which is located under the Data tab. The Scenarios are created and stored within the Scenario Manager. When an individual scenario is selected within the Scenario Manager, the value in the output cell is changed to the value specified by the scenario.

Creating a Scenario in the What-If Scenario Manager

The Scenario Manager is one of the tools in the drop-down menu under What-If Analysis, which is under the Data tab.

(Click On Image To See a Larger Version)

The following empty dialogue box appears when Scenario Manager has been selected.

(Click On Image To See a Larger Version)

Add a scenario by selecting Add. This brings up the following Edit Scenario dialogue box, which has been filled in with the basic information about the first scenario titled Best Case. The output cells for this scenario is A4.

(Click On Image To See a Larger Version)

The value that will appear in this output cell when the scenario is set in the Scenario Values dialogue box, which appears after OK is clicked in the Edit Scenario dialogue box.

(Click On Image To See a Larger Version)

A second scenario titled Most Expected Case is created in a very similar fashion. In this case, the output cell, A4, will display a 2 when this scenario is selected.

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

Finally a third scenario is added titled Worst Case. This will also be linked to cell A4 and will produce the value of 3.

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The output cell A4 can control which set of input values are applied to the analysis through the use of If-Then-Else statements or CHOOSE() commands as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The scenario is selected from the What-If Scenario Manager as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

This sets the value of cell A4 to 2.

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!