What-If Analysis: Scenario Manager

Quick reference

• 00:04 In this lesson, we're going to take a look at another one of Excel's amazing
• 00:08 WhatIf-Analysis Utilities, and that is the Scenario Manager.
• 00:12 Now, if you're not sure what the Scenario Manager is, if you've never used it,
• 00:17 then it's a way to look at the values in our spreadsheet in multiple different
• 00:21 scenarios.
• 00:22 Now the best way for me to explain this is just simply to dive into an exam.
• 00:26 Now if you take a look at the information that we have on the worksheets so
• 00:30 far, this is a, let's say, a scenario for an event that we're planning to put on.
• 00:34 And you can see at the top we have the venue,
• 00:37 the number of seats, so the venue holds 300 people.
• 00:41 And then underneath that,
• 00:43 we have some of the associated costs of putting on this event.
• 00:46 So things like food and drink, audio visual setup, staff, travel venue,
• 00:51 rental, marketing and promotion.
• 00:53 And underneath, we just have a very simple sum calculation which adds up
• 00:58 all of those costs so that we can see our total.
• 01:01 Then on the right-hand side,
• 01:03 we have the revenue that this event is going to pull in.
• 01:06 So you can see that the ticket price is going to be $30.
• 01:11 So underneath, when we're trying to calculate ticket sales,
• 01:14 take a look in the formula bar.
• 01:16 We're doing a very simple calculation,
• 01:18 multiplying the ticket price by the number of seats in the venue.
• 01:22 So we're assuming here that the venue is going to sell out, and
• 01:25 we're going to sell 300 tickets.
• 01:27 We then have some estimated costs underneath.
• 01:29 So for merch, again, if you take a look in the formula bar,
• 01:33 we're working off the principle that on average,
• 01:37 every single person who attends this event is going to spend $5 on match.
• 01:42 So we're multiplying the number of seats in the venue, or
• 01:46 the number of tickets by 5 to get our total.
• 01:49 And the same thing here for food and drink.
• 01:51 We're working off of the principle that most people will spend, on average,
• 01:56 about $10 each.
• 01:57 And then underneath we have a very simple sum calculation just to work out what
• 02:01 the total revenue is going to be.
• 02:02 Then underneath that, we have profit or loss.
• 02:05 And as you might expect, this is just a very simple subtraction.
• 02:08 We're subtracting the total costs from the total revenue.
• 02:12 And I can see here that currently, this event is going to make us $2,700.
• 02:17 So maybe I send this to my manager, and he takes a look at it and
• 02:21 he says, you know what?
• 02:22 We want to be making quite a bit more money from this event.
• 02:25 So can you maybe take a look at booking venues of bigger sizes to see how that
• 02:30 changes the amount of money we're going to make at the end of the event?
• 02:35 So let's say that this venue that has 300 seats is a small venue.
• 02:40 Maybe I go away and research larger venues, but
• 02:43 with a larger venue comes additional costs.
• 02:46 So we might have to pay a little bit more for
• 02:48 the audio visual setup in a larger venue.
• 02:51 We might have to pay much more simply to rent a larger venue.
• 02:55 Maybe security will cost more, maybe insurance will cost more.
• 02:59 But with a larger venue, we can sell more tickets.
• 03:03 So what scenario manager can help us with is we can set up our
• 03:07 data underneath different conditions.
• 03:10 And then we can see a comparison side by side as to what the final result is going
• 03:14 to be if we were to go with one of the different scenarios.
• 03:17 Now, when you're thinking about doing this, this scenario that you currently
• 03:21 have on the worksheet, this is always the original scenario.
• 03:24 So we want to make sure that we set up a scenario called original, first of all.
• 03:28 So what I want to be able to do here is I want to be able to do a comparison side by
• 03:32 side of all of the different scenarios depending on the size of venue that
• 03:35 we choose.
• 03:36 Now the first thing I'm going to do before we start to set up these scenarios is I'm
• 03:40 going to create some named ranges.
• 03:42 Now you might wonder why I'm doing this, first of all, but it will become clearer
• 03:46 a little bit later on, so just bear with me for the time being.
• 03:49 So what I'm going to do is I'm going to select all of these.
• 03:52 I'm going to hold down Ctrl I select the costs and the value.
• 03:58 And I want to select the values which might possibly change.
• 04:01 So all of the revenue over here,
• 04:03 these calculations will be changed automatically,
• 04:06 because they're using formulas when other things change in the worksheet.
• 04:10 And one thing my manager has stipulated is that we only want to charge $30 per
• 04:15 ticket.
• 04:16 We don't want to start charging people more to improve our profit.
• 04:19 So I've currently selected just the cells where the values could possibly change.
• 04:24 And what we're going to do is we're going to jump up to the Formulas tab and
• 04:28 we're going to say Create from Selection in the Defined Names group.
• 04:32 Now, because I've selected the labels and the value,
• 04:35 Excel has recognized that we have labels in the left column.
• 04:39 And it's asking me,
• 04:40 do I want to name my ranges based on what we have in the left column?
• 04:43 So I'm going to say OK.
• 04:45 And we can check that that's been completed correctly by
• 04:48 clicking the name manager, and we can see all of those different labels just here.
• 04:52 And you'll notice if I select them,
• 04:54 it highlights the correct cell in the worksheet.
• 04:56 So let's jump up and create our first scenario.
• 04:59 We're going to go to the Data tab > What If Analysis > Scenario Manager.
• 05:04 Now we're going to add our first scenario.
• 05:06 And for this one, we're just going to keep this as it is so
• 05:08 that we can always get back to our original scenario.
• 05:11 Now I'm going to call this small venue, and
• 05:13 then we need to specify the cells that might change.
• 05:16 So they're basically the cells we just selected.
• 05:18 So I'm going to choose the number of seats that might change.
• 05:21 I'm going to hold down Ctrl, and any of these values might also change.
• 05:25 Let's click on OK.
• 05:27 Now, this is why I always name the ranges first,
• 05:31 because now we can see the values and also those labels.
• 05:35 If we hadn't named the ranges, it would just have the cell reference in here,
• 05:39 which makes it a bit difficult to know which values you're changing.
• 05:43 Now because this is our original scenario, we're not going to change anything here.
• 05:46 We're just going to click on OK.
• 05:47 Let's add another scenario, but this one is going to be called Medium_Venue.
• 05:52 Our changing cells are going to be the same.
• 05:54 Let's click on OK.
• 05:56 And now I can input the values if we have a medium sized venue.
• 06:00 So maybe the medium venue that I found holds 800 people.
• 06:04 So let's change that.
• 06:05 Food and drink is going to be a little bit more.
• 06:07 Let's take that up to 1800.
• 06:10 Maybe it costs a little bit more for Audio_Visual, so we'll go for $750.
• 06:15 Talent's going to be the same, some of these are going to stay the same.
• 06:18 It's a lot closer to where everybody lives, so
• 06:20 let's take this all the way down to $250 for Staff_Travel.
• 06:24 Venue_Rental is going to be a little bit more, let's say $3,000.
• 06:27 And Security is also going to be a little bit more, so let's say $600.
• 06:31 So when I click on OK, I now have a Medium_Venue scenario.
• 06:36 Let's add another scenario, and this one is going to be Large_Venue.
• 06:44 Let's click on OK.
• 06:45 Now maybe the large venue holds 1500 people,
• 06:49 but food and drink is going to be 2,000 in a larger venue.
• 06:53 Audio_Visual is going to be, let's say, 750.
• 06:56 Talent's going to be the same.
• 06:57 Let's say Decor is going to be 1500, because it's a larger room.
• 07:02 Venue_Rental is going to be 4,000.
• 07:05 Insurance, let's say that'll be 300.
• 07:08 Security is going to be 700 in this venue.
• 07:11 And maybe the admin costs surrounding ticketing are going to be a little bit
• 07:15 more as well.
• 07:16 So let's click on OK.
• 07:18 So now we have our three different scenarios.
• 07:21 And what we can do is we can switch between them to see what our profit is
• 07:25 going to be with the new values.
• 07:27 So if we choose Medium_Venue and click on the Show button,
• 07:30 it's going to load those values into the spreadsheet.
• 07:34 So you can now see the profit is a lot more if we go with a medium venue.
• 07:38 What about Large_Venue, let's click on Show.
• 07:41 Again, the profit is even more,
• 07:43 even though we have increased costs with a larger venue.
• 07:47 The number of seats and the ticket price is giving us that bigger profit.
• 07:51 And if we want to compare all scenarios side by side if we click the Summary
• 07:56 button just here, we can choose to create ourselves a Scenario Summary report.
• 08:01 And this is going to show the current values on the worksheet,
• 08:04 the small venue values, the medium, and the large.
• 08:07 So we can see the actual profit at the bottom and do a direct comparison.
• 08:12 So I know simply by looking at this, that we're going to make a lot
• 08:16 more profit if we go for a large venue as opposed to a small venue.
• 08:20 But that is generally how Scenario Manager works.
• 08:23 And of course, you can compare more than the three scenarios that we've done here.

Lesson notes are only available for subscribers.