Sign in or start a free trial to avail of this feature.
15. Run Scenarios Using Pivot Tables
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
Overview of the Problem (00:04)
The Zippy Airways problem statement allowed for the possibility of applying an overbooking policy only on certain flights. We want to analyze whether this is a good idea, and which flights should be included or excluded from the overbooking policy. We do this by creating a Pivot Table showing the additional profit for each of Zippy Airways flights.
Creating a Flight Column (00:47)
In the dataset, we create a new column that identifies each flight. We identify a flight by the first three letters of the origin, the first three letters of the destination, and the flight time. For example LONPAR10:15 represents the 10:15 flight from London to Paris. To create the identifier, we use the text function LEFT to identify the first three letters of the origin and destination, and UPPER to convert to uppercase. We use the TEXT function on the departure time to identify the time part of the identifier.
Creating a Pivot Table (03:02)
Once we have an identifier for each flight, we create a Pivot Table showing the total profit for each flight. We can use this table to make decisions on our overbooking policy. In our case, no flight has reduced profit with overbooking in place, so we decide to implement the policy on all flights. If we adjust inputs on the control panel and refresh the Pivot Table, we can see how each flight responds to a change in the model.
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.