Excel What-If Analysis - Scenario Manager

Data Translates
219 followers
Hello everyone and welcome back to my channel. Today, I thought I would do a quick demonstration
on how to use Scenario Manager in Excel. And the reason why I've chosen to do this today was
because it's something that I've used very recently when planning a trip to Scotland next week. So,
I thought it would be a really good opportunity just to jump on here and show you what I did
and show you how Scenario Manager can work in real life situations. So, I'm thinking of taking
a trip to Scotland next week and I'm trying to decide between three different cities. And what
I'm aiming to find out here is which one is going to leave me with the most spare cash for my trip.
And so, I've used Scenario Manager to help me with this. And if you've never used Scenario
Manager before, you'll find it on the data tab and it's one of the what-if analysis utilities.
And Scenario Manager effectively allows you to test your data against changing inputs. And if
you want to, at the end, you can produce a nice looking summary report that makes it really easy
to see how each of your scenarios stack up against the other and which one is going to be preferable.
So, let's take a look at the data we're going to be using. And it's not a large data set.
What we have at the top here is my budget. So, I don't have any more than £700 to spend
on this trip. Underneath that, I've listed out my inputs or my expenses for this trip.
So, I have my costs related to train travel, accommodation when I'm there,
food, tickets for things like museums, and then some miscellaneous cash at the end.
And at the bottom, I have a total. And if you take a look in the formula bar, you can see this
is a very simple sum calculation where I'm just adding up all of my expenses and then my spare
cash at the bottom there is also a sum calculation where I'm just minusing my expenses from my
budget to give me how much spare cash I'm going to have left over at the end. Now, when you're
setting up your different scenarios, one thing that you need to establish is your input cells.
And these are the cells that are going to change in each scenario. So, when we start to set up our
scenarios for each of these cities, looking at my data, what do you think is going to change here?
Well, my budget isn't going to change. I only have 700 pounds. The total and spare cash at the bottom
were their calculations very much determined by what we have in these cells above. So, the cells
that are going to change are going to be these cells here that I have highlighted in orange.
Depending on the city, the cost of the train to get there is going to be different.
The accommodation costs are going to be different. Food might be cheaper or more expensive in that
particular location, so on and so forth. So, these are going to be my input cells for my scenarios.
These are the cells that are going to change. Now, one thing I always advise people to do when
they're thinking about using Scenario Manager is to create named ranges. And you'll see why this
is so important at the end when we produce a summary report. It's going to make your report
just a lot easier for you to read. So, that's the first thing we're going to do here. I'm going to
select the label and the value. I'm going to hold down control and select the label and the value.
And I'm going to do the same down here as well, label and value. So, let's create ourselves some
named ranges. We're going to jump up to the formulas tab. And in the defined names group,
I'm going to say create from selection. And you can see here that Excel has helpfully picked up
that I have my labels in the left column. So, it's going to use budget, train travel, accommodation,
food as the names for these ranges. So, that's perfect. Let's click on OK. If I want to check
to make sure those have worked, if I click the drop down in the name box, you can see there I
have all of my different ranges. And if I select them, it's going to highlight those specific cells
that relate to that label. So, now that we have those set up, let's build our scenarios. So,
we're going to go to the data tab, across to the forecast group, and let's select what if analysis
scenario manager. So, now we can go in and add our scenarios. And I would recommend that you
always create a scenario called original so that you can easily get back to your original set of
data once these figures start changing. We then need to tell scenario manager what our input cells
are, what are our cells that are going to change. So, for me, the cells that are going to change
are going to be these cells just here, B7 to B11. And let's click on OK. Now, notice here where it
says scenario values, it's now listed out all of those changing cells. And you can see train travel,
accommodation, food, tickets, and miscellaneous. Now, this is why I use named ranges. If I hadn't
have named these ranges, what I would see here would be the cell references as opposed to the
actual labels, which makes it a lot more difficult to know which cells you're actually changing.
Now, because this is my original scenario, I don't want anything to change here.
So, I'm going to click on OK. Let's add another scenario. So, this scenario is going to be for
the first city that I'm thinking about traveling to. And that first city is going to be Edinburgh.
My changing cells are B7 to B11. Let's click on OK. Now, I have the opportunity to go in and
modify these values for Edinburgh. So, maybe the train to Edinburgh is a little bit more expensive.
Maybe it's 80 pounds. Accommodation in Edinburgh is also a little bit more expensive. So, let's
change this to 250 pounds. Food, well, yes, again, that is more expensive. Let's change that to 150.
Tickets for the museum, that's about the same wherever we go in Scotland. So, I'm going to keep
that as 50. And then I want some miscellaneous cash of 70 pounds for bits and bobs that I might
want to buy. So, this is my scenario for Edinburgh. Let's click on OK. You can see the scenario listed
just here. Let's add another one. This time, it's going to be for my second city that I'm thinking
about, and that is Glasgow. My changing cells are still the same, B7 to B11. Now, my train travel to
Glasgow is quite a bit more expensive. It's going to be 150 pounds for me to get there, simply
because there aren't as many direct trains from London to Glasgow. But Glasgow is a little bit
cheaper than Edinburgh. So, my accommodation costs are going to be less. So, let's make this 120.
Food is also a little bit cheaper. So, we're going to say that that's going to be 80.
And tickets are also a little bit cheaper. Let's click on OK. And we have a third scenario.
Let's add our final scenario. And this is going to be for Inverness. Changing cells, B7 to B11.
Now, Inverness is way, way up in the north of Scotland. So, train travel, trying to get there,
is really expensive. So, train travel is 400 pounds. But accommodation in Inverness is really,
really cheap. It's only 75 pounds. Food is also a lot cheaper. So, I'm going to allow 50 pounds for
that. And tickets are also cheaper. So, we're going to say 35 pounds. And I'm not really going
to need as much miscellaneous cash. So, let's say 50 pounds. And click on OK. So, now I have
four different scenarios. My original, so I can get back to my original data, what it's going to
cost me to go to Edinburgh, Glasgow, and Inverness. So, what I can now effectively do is compare all
of these side by side and see which one is going to be best for my budget. Now, there's a couple
different things I can do at this stage. If I want to see what my Inverness data looks like,
I can select Inverness and click on the show button at the bottom. If you look at the spreadsheet,
that is going to update with all of those different values. And it's going to show me
how much spare cash I have at the bottom. So, you can easily switch between your different scenarios
and show them individually. However, that is still quite hard to see. What I would prefer
is to be able to see all of these scenarios side by side in a report to get a true comparison. So,
notice here we have a summary button. I can choose to create a scenario summary or a pivot table
report. Now, in this case, I'm going to do scenario summary. And let's click on OK.
Excel creates another tab at the bottom, and it lists out all of my different scenarios
side by side. And right at the bottom here, I can see how much spare cash I'm going to have
in each of these scenarios. So, it's showing me the current values that I have on the worksheet,
the original values, Edinburgh, Glasgow, and Inverness. So, looking at this, I can very quickly
see that if I go to Glasgow, I'm going to have more money to enjoy myself than if I was to go
to Edinburgh or Inverness. And that is how Scenario Manager works. This is a very simple,
basic example, but hopefully you can think of different ways that you can apply this to your
work. It's great for comparing different financial models or even if you have something like a
personal budget and you want to see if taking a new job or taking a new promotion is going to
leave you with more money at the end of the month, depending on what your variables are.
So, have a little play around with this. It's a really great little utility and really simple
to use. If you enjoyed this video, don't forget to smash that like button and consider subscribing.
And don't forget to turn on notifications so you know the next time that I post a video.
That's it for now. I will see you next time.
