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 −
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.
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 −
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.
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 −
Consider the previous example of loan. Now, proceed as follows –
Your worksheet looks as shown below –
As you can see that the input cells and the result cells are in column C with the names as given in column D.
After setting up the initial values for the Scenarios, you can create the scenarios using Scenario Manager as follows −
The Scenario Manager Dialog box appears. You can observe that it contains a message −
“No Scenarios defined. Choose Add to.”
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 −
The Add Scenario dialog box appears.
The name of the dialog box changes to Edit Scenario.
The Scenario Values dialog box appears. The initial values that you have defined appear in each of the changing cells boxes.
Scenario 1 with the initial values is created.
Create three more scenarios with varying values in the changing cells as follows −
Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.
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.
The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the box under changing cells.
The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of NPER to 300 and click Add.
The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box.
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.
The Scenario Manager dialog box appears. In the box under Scenarios, You will find the names of all the scenarios that you have created.
Now, click Summary. The Scenario Summary dialog box appears.
Scenario Summary Reports
Excel provides two types of Scenario Summary reports −
In the Scenario Summary dialog box, you can find these two Report types.
Select Scenario summary under Report type.
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.
You can look at the following in the scenario summary file −
You can make the document more meaningful by showing the comments that you added while developing the scenarios.
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 −
The scenario manager dialog box appears.
As you can observe, there are no scenarios as you have not yet added any. Click Merge.
The Merge Scenarios dialog box appears.
As you can see, under Merge scenarios from, you have two boxes −
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.
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.
The Scenario Manager dialog box appears. The two scenarios that were merged into the Master workbook will be listed under Scenarios.
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.
The Scenario Manager Dialog box appears. The four scenarios that were merged into the Master workbook are listed under Scenarios.
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.
The Scenario Manager Dialog box appears. The five scenarios that were merged into the Master workbook will be listed under Scenarios.
Now, you have all the required scenarios to produce the Scenario summary report.
Click the Summary button. The Scenario Summary dialog box appears.
The Scenario summary report appears on a new worksheet in the Master workbook.
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.
The values on the worksheet are updated to that of the selected scenario. The result values are recalculated.
You can see the Scenario report in the form of a PivotTable also.
Scenario PivotTable report appears on a new worksheet.
Excel Data Analysis Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Data Analysis Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.