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.
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 is great when you want to play around
with 1-to-1 relationships between variables: if we want the Operating Margin in cell B11
to be $500K, then what should be my volume in cell B2? Goal seek comes up with a solution
for that question, which you can then accept into your data modeling spreadsheet.
Using a data table is great when you want to see the effect on target variable Operating
Margin of changing two input variables simultaneously, like volume and price (100 thousand units
at a price of $10 each should provide an Operating Margin of $200K, while 110 thousand units
at $11 provide $350K of Operating Margin). 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 scenario manager 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 – What-if analysis – 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. Here in the “Changing cells” field, the range of cells that we want to
work on is already selected. At the moment, the cells that we want to run
scenarios on are listed in the grey area right next to the values: B2, B3, B4. Personally,
I like to name those cells so I know what I am looking at. Click OK, and Close, and
then go up here on the top left to name cell B2 Volume, B3 Price, and B4 VCperUnit. This
will be very helpful later on when we put our scenarios side-by-side for comparison
in a scenario summary report. Let’s go back to Scenario Manager, and add
a scenario called “Low-Low-High” (low volume, low price, high variable cost per
unit). Check that we have the right range of cells selected. When we click OK, we can
input the values for this scenario. 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. You can click Edit, and OK, to change that scenario straight away if you
want to. If we click on the “High-High-High” scenario,
and show that one, we get a positive Operating Margin of $400K.
If we click on “High-Low-Low”, and show this scenario, the Operating Margin is expected
to be $160K. 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.
Let’s make one such change. Data – What-if analysis – Scenario Manager. We will edit
the High-Low-Low scenario to a volume of 140 thousand units, and show that scenario on
the main tab. Then we also assign a name to cell B11: Operating Margin. We run a new scenario
summary, which generates a worksheet called “Scenario Summary 2”. The current values
column (D) now has the updated High-Low-Low scenario, and the result cell is called “Operating
Margin” rather than B11. I hope Excel scenario manager will be useful
for you for planning, analyzing and forecasting!