Welcome to this tutorial on how to use scenario manager in Excel. In this Excel spreadsheet, we have some input variables here at the top, a profit and loss forecast right below it, and the target variable of Operating Margin at the bottom. This is a very simplified forecasting spreadsheet for a new product introduction. And as is the case with NPIs, there is a lot of uncertainty around key assumptions. Using scenario manager in Excel is one of the ways to quantify the relationship between input variables and target variables.

⏱️TIMESTAMPS⏱️
0:00 Scenario manager introduction
0:37 Scenario manager vs goal seek
1:16 Scenario manager vs data table
1:56 How to use Excel scenario manager
3:15 Adding a scenario
4:13 Showing a scenario in the spreadsheet
5:24 Scenario summary report

Scenario manager is found on the Data tab – What-if analysis. There are actually three options listed under What-if analysis: scenario manager, goal seek, and data table. I covered goal seek and data table in earlier videos:
Goal Seek in Excel [ Ссылка ]
Sensitivity analysis in Excel (using data tables) [ Ссылка ]

Using a data table is great when you want to see the effect on target variable Operating Margin of changing two input variables simultaneously. However, we cannot accommodate more than two variables into a data table in Excel. If we want to change all three input variables at the same time, we can use What-If Analysis – Scenario Manager.

Here’s how Excel #scenariomanager works. Select the input variables. In this spreadsheet, there are only three of them. If you need to, a scenario can contain up to 32 cells! Go to Data – #whatifanalysis – Scenario Manager: “Create different groups of values or scenarios, and switch between them”. When you start off, no scenarios are defined yet. Let’s add our first scenario called “Base Case”, where we simply store the current values of the input variables, so we can always go back to the base case if we need to.

Let’s go back to Scenario Manager, and add a scenario called “Low-Low-High” (low volume, low price, high variable cost per unit). For volume, we input 80 thousand units (lower than the base case). For price, $9 (lower than the base case). For variable cost per unit, $7 (higher than the base case).
Next, we add a scenario called “High-High-High”. Volume of 120 thousand units, price of $12, variable cost per unit $7.
Then, a scenario called “High-Low-Low”. Volume of 120 thousand units, price of $8, variable cost per unit of $5.

Now we can apply the magic of the Excel scenario manager. Click on the “Low-Low-High” scenario, and then click “Show”. The input variables from that scenario are now shown in the actual spreadsheet, and we see a negative Operating Margin of $40K in cell B11. The cool thing is that the data tables on the right have also been updated. If we don’t like the negative Operating Margin, then we need to get to at least a volume of 100 thousand units at a price of $9, or a price of $10 with 80 thousand units.

We can switch between showing and editing the various scenarios. Or we can restore the base case scenario, if that turns out to be the one we want to pursue after all.

At some point during the analysis, it might be useful to show what each scenario contains, side-by-side. That’s where the scenario manager summary comes in. We would like report type “scenario summary”, based on result cell B11. If we click OK, then a new tab is added to our workbook. It has the current spreadsheet input variable values in column D, and then the scenarios in column E through H. Row 6 puts the volumes side-by-side, row 7 price, row 8 variable cost per unit. Row 10 has the expected Operating Margin levels for each scenario, showing the large variations in Operating Margins when assumptions change. The note at the bottom is very important: values are represented at the time when the scenario summary report was created. Scenario summary reports do not automatically recalculate, so if you change something on the main worksheet, you will need to create a new summary report to see the latest data side-by-side.

I hope #Excel scenario manager will be useful for you for planning, analyzing and forecasting!

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, classroom sessions, webinars, and business simulations. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through [ Ссылка ]

свернуть