Original subtitles

The next what if analysis utility that were going to be looking at is the scenario manager and scenario

manager is a really great way of being able to look at your data in different scenarios, under different

conditions and in turn that can help you make better decisions.

Now, the example that we're going to use is basically just monthly income and monthly expenses.

So what you can see here is that my monthly income is at the top.

We have $3000.

And then I've just listed out all of the expenses that I have each month.

At the bottom, we have total expenses, which is basically just a sum calculation of B five to be 11.

I'm right at the bottom.

I have the amount of spare cash that I have at the end of every month.

And again, that's just a very simple calculation where we're minus saying the total expenses from the

monthly income.

Now it might be that I've been offered a promotion at work and taking that promotion means that I'm

going to get a salary increase, so my monthly income is going to be more.

Now, at the same time as being offered this promotion at my current job, I've also been offered a

brand new job somewhere else.

Again, I'm going to get a salary increase, but this job is a little bit further away, so some of

my other expenses might change.

So I basically have these three different scenarios.

I could stay where I am.

I could take the promotion or I could take the new job.

And what I'm really aiming for here is to work out which one is going to leave me with the most available

cash at the end of the month to go and enjoy myself so we can use scenario manager for this.

Now, one thing I would recommend before you use scenario manager is that you do set up your data as

named ranges, and I'm going to show you why that's so important in a moment.

But for now, I'm going to select all of the cells which contain values, select all of them, and I'm

going to go up to the Formulas tab and we're going to say create form selection.

We're going to use the label in the left column as the name of the range and click on OK.

And now I have all of those ranges in here.

Now you might be thinking to yourself, why on earth are we doing that?

First of all, it will become clear in a couple of moments.

So now that we've done that, let's jump up to the Dates tab.

Let's go into what's for analysis and select scenario manager.

Now this is where we can set up three different scenarios.

So I'm going to click on Add and we need to give our first scenario a name.

Now, one thing I always recommend is that you create an original scenario which shows your data in

its current state so that if it all goes wrong, you can get back to how it was originally.

So we're going to call this original.

It now wants to know what the changing cells are.

So out of all of these values that we have in Column B, which ones might possibly change?

Well, my monthly income could possibly change, and any of these cells could possibly change as well.

The cells at the bottom here, total expenses and about left.

These are basically just some calculations for whatever we have above, but it's possible that any of

these orange cells could change.

So I'm going to select all of them and click on.

OK, now this is why I named the ranges.

Notice that we now have the label and the value.

If I didn't name these ranges, these labels would just say a five eight six eight seven eight so on

and so forth.

So it's pretty hard to know what exactly is your changing if you don't have meaningful labels.

And that's why I always recommend adding named ranges, first of all.

So you'll notice that the values that we have in here directly correspond to what we have on the worksheet

because this is our original scenario.

I don't want anything to change, so we'll just click on.

OK, let's add a second scenario now.

A second scenario is that I could take the promotion at my current company.

The changing cells are the same, so let's click on.

OK, now if I take the promotion at my current job, my monthly income is going to increase to three

thousand five hundred, but pretty much most things are going to stay the same.

My rent's going to stay the same, my travels the same because I'm at the same company.

The only thing that's possibly going to change is because my wages have increased.

I might spend a bit more on shopping each month.

Let's go for three twenty.

And I also might spend a little bit more on entertainment, so I'm going to put both of those.

Goes up.

Let's click on, OK.

I'm going to add my third scenario, which is new job now.

If I take the new job, my wages are going to be slightly less than the promotion.

Still an increase, but not as good as getting the promotion.

But this new job is a little bit further away, so I'm going to be spending more on travel.

And if I was to take this new job, the gym is also a little bit more expensive in that location.

Let's click on.

OK, so now I have my three scenarios and I can basically compare them side by side to see which one

is going to work out the best.

Now, one way that I can compare these is I can select a scenario in the list, for example, promotion

and click show at the bottom to load the figures into the worksheet.

So I can see here this is now updated and this is my promotion scenario.

So I'm going to be quite a bit better off because I'm going to have over a thousand dollars a month

of spare cash.

If I go back to my original, I only had five hundred and eighty now I could carry on going through

these scenarios, comparing them in that way.

But what is sometimes easier to see is having all of your scenarios stacked up side by side.

It's much easier to do a direct comparison that way.

So I'm going to click on the summary button and I'm going to do a scenario summary report.

Let's click on OK.

Now Notice opens a brand new tab and it produces this little report for you.

So again, you can see all of our named range selves.

And again, this is another reason why I like to name the ranges.

If I didn't, this report again would just have cell references in here.

I wouldn't be able to really tell what that cell reference refers to.

I can then see the current values that we have on the worksheet.

The original scenario, the promotion and the new job.

So looking at all of these at the bottom, I can see that taking the promotion is definitely going to

be the most beneficial for me.

So that is how scenario manager can be really useful.

And the beauty of it is it can be used on all different types of data in lots of different situations.