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
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
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
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
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 Zippy's flights.
Up to 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 dataset, I like to use pivot tables. Let's start off by returning to our dataset 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 the string.
So I'll first add London, and strip three characters from this word. I'll then use the & sign, open inverted commas and write a dash, because I'd like a dash between London and Paris. I'll then write the & sign again so we can add another part of the string 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 and I'll write an & sign, open inverted commas and then write a comma. Close the inverted commas and write another & sign. And here we 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 OK. And in our pivot table, we'll make sure total additional 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 additional 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 and 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 from different subsections of your data set.