Download File Watch Video – Scenario Manager in Excel
One Variable Data Table in Excel. Two Variable Data Table in Excel. Goal Seek in Excel. Excel Solver.
Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change. Suppose you have a dataset as shown below and you want to calculate the profit value:
The Profit value is dependent on 3 variables – Sale Quantity, Price per Unit, and the Variable Cost per Unit. Here is the formula I have used to calculate the profit: The idea is to see how this final result changes when we change these dependent variables. As shown in the first 2 articles of this series, if you only have one or two variables changing, you can create one variable or two-variable data table. But if you have 3 or variables that can change then scenario manager is the way to go.
Setting up Scenario Manager in Excel
Go to Data Tab –> Data Tools –> What-If Analysis –> Scenario Manager. In the Scenario Manager dialogue box, click on Add. In the Add Scenario dialogue box, fill in the following details: Scenario name: Worst Case Changing cells: $B$2,$B$3,$B$5 (you can also select it by pressing the CONTROL button and using mouse left-click). Comment: Any comment you wish you add. You can also leave this blank. Click OK. This opens the Scenario Values dialogue box. In the Scenario Values dialogue box, fill in the following values (since this is the worst case scenario, enter the values accordingly). If you create names for each cell, that name is visible instead of the cell address: $B$2: 50 $B$3: 30 $B$4: 30 Click OK (Click on Add if you want to add another scenario).
This creates the Worst Case scenario for this data set. You can similarly follow these steps and create multiple scenarios (for example, Worst Case, Realistic, Best Case). Once you have created all the scenarios, you can view the result from each of the scenarios by simply double-clicking on any of the scenarios. As you double click, the values would change based on that scenario. Additionally, you can also create a summary of all the scenarios.
Create a Summary of all the Scenarios
Click on the Summary button in the Scenario Manager dialogue box. In the Scenario Summary dialogue box, select Scenario Summary or Pivot Table (these are the 2 ways to show summary). Also specify the Result cells (the cell where you have the output of this calculation; B6 in this example) Click OK. Instantly a new tab is created with the summary of all the three scenarios.
Scenario manager in Excel is a great tool when you need to do sensitivity analysis. Simply create scenarios and a summary can be generated instantly, giving you a complete comparative overview. Download File… Try it yourself
Calculating Standard Deviation in Excel Making Histogram in Excel Calculating Weighted Average in Excel Calculating CAGR in Excel Calculate Correlation Coefficient in Excel How to Get Descriptive Statistics in Excel?
I’m trying to link and automate the scenario values. Instead of feeding it generic values, I’m using a cell reference, it works (instead of “50”, I’m using “=A1”). However, the values from that cell reference are being fixed and will not update when I change the scenario values in those cells. The problem is once I give it the cell reference as input, e.g. = A1, it takes the generic value inside A1 and fixes it as the scenario figure. When I manually modify this value in A1, the scenario doesn’t update because it’s no longer referring to the cell A1. Any ideas? Thanks. I am Fredrick from Ghana
