15. Run Scenarios Using Pivot Tables

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Excel offers many ways to run scenarios. The method you choose depends on the type of scenario you wish to run. In this example, I use Pivot Tables to select which flights should have an overbooking policy

Lesson Notes

Many ways to run scenarios

- Many ways exist in Excel to run scenarios
- The method you pick is determined by the type of scenario you want to run
- Pivot Tables work really well for scenarios that extract groups of entries from a dataset

Functions

UPPER( text ): Converts text to uppercase
LEFT( text, 3): Strips the first 3 characters from left of the text string
&: Chains strings of text together
TEXT( time, "hh:mm"): Converts time to 24 hour format

Keyboard shortcuts

F2 : Jump into formula
ALT + I , C: Insert column
ALT + O , C, A: Autofit selected columns
ALT + N , V, T: Insert Pivot Table
CTRL + ALT + F5: Refresh Pivot Table input data
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region

Transcript

There are many different ways of creating scenarios in Excel and the method you use depends on the type of scenario you need to create. In fact, you could argue we've already run scenarios using Goal Seek and Solver along with our Sensitivity Tables. In this lesson, I'm going to focus on the last part of our problem statement which asks us to see if an overbooking policy should be implemented on some or all of these flights.

Up till now, we've assumed that all the flights will implement the booking policy. But this does not necessarily have to be the case. When running scenarios of this nature where we extract or add in parts of a data set I like to use Pivot Tables. Let's start off by returning to our data set and creating a new column which I'll entitle Flight.

This column will be used to identify each data record with one of our 16 flights. To identify a flight, I'll take the first three letters of the departure city, the first three letters of the destination and also the departure time.

So let's start by writing equals and upper because I'd like the text to be in caps.

And then within upper, I'll use the left function which strips characters from the left of a strain.

So I'll first add London and strip three characters from this word.

I'll then use the ampersand sign, open inverted commas and write a dash because I'd like a dash between London and Paris. I'll then write the ampersand sign again so we can add another part of a strain and write left and apply this to the word Paris. Again, I'll take three letters.

If I close the bracket at this stage and press enter, you can see that I've identified both the departure city and the destination. Unfortunately, there are two flights every day from London to Paris so we'll need to include the departure time as well.

So let's jump back into the formula with F2.

I'll write an ampersand sign, open inverted commas and then write a comma. Close the inverted commas and write another ampersand sign. And here we'll put our departure time. Let's create a text function and the text function will allow us to choose the format we'd like for this time. Let's then select the departure time which is Cell C7 and the format will be HH:MM.

Close the inverted commas, close the bracket and press enter. And then if I use Alt, O, C, A you can see that the flight has now been correctly identified.

Let's now copy for the remaining cells.

With this column in place, we can now create our Pivot Table.

So I'll select all of the columns, select all of the rows and then Alt, N, V to insert a Pivot Table.

I'll make sure it's on a new worksheet and then press okay.

And in our Pivot Table, we'll make sure total addition of profit is our values and our row labels will be flight.

I'll do some quick formatting off camera to make these entries more readable. The Pivot Table now shows us the total addition of profit broken down by each flight.

Under the current set of inputs, no flight is providing an additional cost so we'd probably recommend implementing an overbooking policy across the board. However, if we go to our control panel and increase our voucher costs to say 210 and our booking limit to the same value, then return to our Pivot Table then update with Alt, F5, you can see that both flights from London to Berlin now contribute an additional cost. So we wouldn't recommend an overbooking policy on these two flights. Although Pivot Tables are not usually thought of as scenario analysis tools, hopefully this lesson has shown you how effective they can be when testing the output in different cell sections of your data set.