What If Analysis with Scenario Manager - Excel Data Analysis

What is What If Analysis with Scenario Manager?

Scenario manager is useful within the cases wherein you have more than variables in sensitivity analysis. Scenario manager creates scenarios for every set of the input values for the variables below consideration. Scenarios assist you to explore a set of possible effects, assisting the following −

  • Varying as many as 32 input sets.
  • Merging the scenarios from numerous different worksheets or workbooks.

If you need to analyze extra than 32 input sets, and the values represent only one or two variables, you can use data Tables. Even though it is limited to only one or variables, a data table can include as many different input values as you want. Check with What-If analysis with data Tables on this tutorial.

Scenarios

A scenario is a set of values that Excel saves and can substitute automatically in your worksheet. you can create and save different groups of values as scenarios on a worksheet and then switch among these scenarios to view the different results.

For example, you can have numerous different price range scenarios that examine various possible income levels and expenses. you can also have unique loan scenarios from different sources that compare various possible interest prices and loan tenures.

If the information that you want to apply in scenarios is from different assets, you could collect the information in separate workbooks, and then merge the scenarios from the unique workbooks into one.

After you have all the scenarios you need, you can create a scenario summary file −

  • That consists of data from all the scenarios.
  • That lets you compare the scenarios side-by-side.

Scenario Manager

Scenario Manager is one of the What-if Analysis tools in Excel.

To create an analysis report with Scenario Manager, you have to follow these steps −

Step 1 − Define the set of initial values and identify the input cells that you want to vary, called the changing cells.

Step 2 − Create each scenario, name the scenario and enter the value for each changing input cell for that scenario.

Step 3 − Select the output cells, called the result cells that you want to track. These cells contain formulas in the initial set of values. The formulas use the changing input cells.

The Scenario Manager creates a report containing the input and the output values for each scenario.

Initial Values for Scenarios

Before you create several different scenarios, you need to define a set of initial values on which the scenarios will be based.

The steps for setting up the initial values for Scenarios are −

  • Define the cells that contain the input values.
  • Name the input cells appropriately.
  • Identify the input cells with constant values.
  • Specify the values for the constant inputs.
  • Identify the input cells with changing values.
  • Specify the initial values for the changing inputs.
  • Define the cells that contain the results. The result cells contain formulas.
  • Name the result cells appropriately.
  • Place the formulas in the result cells.

Consider the previous example of loan. Now, proceed as follows –

  • Define a cell for Loan Amount.
    • This input value is constant for all the scenarios.
    • Name the cell Loan_Amount.
    • Specify the value as 5,000,000.
  • Define the cells for Interest Rate, No. of payments and Type (Payment at the beginning or end of the month).
    • These input values will be changing across the scenarios.
    • Name the cells Interest_Rate, NPER and Type.
    • Specify the initial values for the analysis in these cells as 12%, 360 and 0 respectively.
  • Define the cell for the EMI.
    • This is the result value.
    • Name the cell EMI.
    • Place the formula in this cell as −
    • =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Your worksheet looks as shown below –

What-If Analysis with Scenario Manager

As you can see that the input cells and the result cells are in column C with the names as given in column D.

Creating Scenarios

After setting up the initial values for the Scenarios, you can create the scenarios using Scenario Manager as follows −

  • Click the DATA tab on the Ribbon.
  • Click What-if Analysis in the Data Tools group.
  • Select Scenario Manager from the dropdown list.

What-If Analysis with Scenario Manager

The Scenario Manager Dialog box appears. You can observe that it contains a message −

No Scenarios defined. Choose Add to.”

What-If Analysis with Scenario Manager

You want to create scenarios for every set of changing values within the scenario manager. It is good to have the first scenario described with initial values, because it allows you to replace again to initial values each time you need while showing unique scenarios.

Create the first scenario with the initial values as follows −

  • Click the Add button in the Scenario Manager Dialog box.

The Add Scenario dialog box appears.

  • Under Scenario Name, type Scenario 1.
  • Under Changing Cells, enter the references for the cells i.e. C3, C4 and C5 with the Ctrl key pressed.

The name of the dialog box changes to Edit Scenario.

  • Edit the text in the Comment as – Initial Values box.
  • Select the option Prevent changes under Protection and then click OK.

What-If Analysis with Scenario Manager

The Scenario Values dialog box appears. The initial values that you have defined appear in each of the changing cells boxes.

What-If Analysis with Scenario Manager

Scenario 1 with the initial values is created.

Create three more scenarios with varying values in the changing cells as follows −

  • Click the Add button in the Scenario Values dialog box.

Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.

  • In the Scenario Name box, type Scenario 2.
  • Edit the text in the Comment as – Different Interest Rate.
  • Select Prevent changes under Protection and click OK.

What-If Analysis with Scenario Manager

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of Interest_Rate to 0.13 and click Add.

What-If Analysis with Scenario Manager

The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the box under changing cells.

  • In the Scenario Name box, type Scenario 3.
  • Edit the text in the Comment box as – Different no. of Payments.
  • Select Prevent changes under Protection and click OK.

What-If Analysis with Scenario Manager

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of NPER to 300 and click Add.

What-If Analysis with Scenario Manager

The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.

  • In the Scenario Name box, type Scenario 4.
  • Edit the text in the Comment box as – Different Type of Payment.
  • Select Prevent changes under Protection and click OK.

What-If Analysis with Scenario Manager

The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of Type to 1. Click OK as you have added all the scenarios that you wanted to add.

What-If Analysis with Scenario Manager

The Scenario Manager dialog box appears. In the box under Scenarios, You will find the names of all the scenarios that you have created.

  • Click Scenario 1. As you are aware, Scenario 1 contains the initial values.

What-If Analysis with Scenario Manager

Now, click Summary. The Scenario Summary dialog box appears.

Scenario Summary Reports

Excel provides two types of Scenario Summary reports −

  • Scenario summary.
  • Scenario PivotTable report.

In the Scenario Summary dialog box, you can find these two Report types.

Select Scenario summary under Report type.

What-If Analysis with Scenario Manager

Scenario Summary

In the Result cells box, select the cell C6 (Here, we had put the PMT function). Click OK.

Scenario Summary report appears in a new worksheet. The worksheet is named as Scenario Summary.

What-If Analysis with Scenario Manager

You can look at the following in the scenario summary file −

  • Changing Cells − Enlists all the cells used as converting cells. As you have named the cells, Interest_Rate, NPER and type, these appear to make the file meaningful. otherwise, best cell references will be listed.
  • Result Cells − shows the result cell specific, i.e. EMI.
  • Current Values − it is the first column and enlists the values of that scenario which is chosen in the scenario manager dialog box before creating the summary report.
  • For all of the scenarios you have created, the converting cells can be highlighted in gray.
  • In the EMI row, the result values for each situation will be displayed.

You can make the document more meaningful by showing the comments that you added while developing the scenarios.

  • Click the + button to the left of the row containing the situation names. The comments for the situations seem in the row below the scenario names.

What-If Analysis with Scenario Manager

Scenarios From Different Sources

Assume you get the scenarios from three unique assets and you need to prepare the scenario precis record in a master workbook. You can do this through merging the scenarios from different workbooks into the master workbook. Follow the stairs given below −

  • Assume that the scenarios are within the workbooks, Bank1_Scenarios, Bank2_Scenarios and Bank3_Scenarios. Open the three workbooks.
  • Open the master workbook, in which you have the initial values.
  • Click on data > What-if analysis > scenario manager within the master workbook.

The scenario manager dialog box appears.

What-If Analysis with Scenario Manager

As you can observe, there are no scenarios as you have not yet added any. Click Merge.

The Merge Scenarios dialog box appears.

What-If Analysis with Scenario Manager

As you can see, under Merge scenarios from, you have two boxes −

  • Book
  • Sheet

You can select specific worksheet from a particular workbook that carries the scenarios, which you want to add on your results. click the drop-down arrow of book to see the workbooks.

Note − The corresponding workbooks need to be open to appear in this list.

What-If Analysis with Scenario Manager

Select the book – Bank1_Scenarios.

Bank1 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet is displayed. Click OK.

What-If Analysis with Scenario Manager

The Scenario Manager dialog box appears. The two scenarios that were merged into the Master workbook will be listed under Scenarios.

What-If Analysis with Scenario Manager

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank2_Scenarios from the drop-down list in the Book box.
Bank2 sheet is dislayed. At the bottom of the dialog box, the number of scenarios found on source sheet are displayed. Click OK.

What-If Analysis with Scenario Manager

The Scenario Manager Dialog box appears. The four scenarios that were merged into the Master workbook are listed under Scenarios.

What-If Analysis with Scenario Manager

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank3_Scenarios from the drop-down list in the Book box.
Bank3 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet will be displayed. Click OK.

What-If Analysis with Scenario Manager

The Scenario Manager Dialog box appears. The five scenarios that were merged into the Master workbook will be listed under Scenarios.

What-If Analysis with Scenario Manager

Now, you have all the required scenarios to produce the Scenario summary report.

Click the Summary button. The Scenario Summary dialog box appears.

  • Select Scenario summary.
  • In the Result cells box, type C6 and click OK.

What-If Analysis with Scenario Manager

The Scenario summary report appears on a new worksheet in the Master workbook.

What-If Analysis with Scenario Manager

Showing scenarios

Suppose you are providing your scenarios and you would like to dynamically transfer from one scenario to another and show the set of input values and result values of the corresponding scenario.

  • Click on data > What-if analysis > scenario manager from the data tools group. The scenario manager dialog box appears. The list of scenarios appear.
  • Choose the scenario you need to display. click on show.

What-If Analysis with Scenario Manager

The values on the worksheet are updated to that of the selected scenario. The result values are recalculated.

What-If Analysis with Scenario Manager

Scenario PivotTable Report

You can see the Scenario report in the form of a PivotTable also.

  • Click the Summary button in the Scenario Manager Dialog box. The Scenario Summary dialog box appears.
  • Select the Scenario PivotTable report under Report type.
  • Type C6 in the Result cells box.

What-If Analysis with Scenario Manager

Scenario PivotTable report appears on a new worksheet.

What-If Analysis with Scenario Manager

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Data Analysis Topics