7. Sensitivity Analysis
Sensitivity tables help you understand the impact that changing various input values can have on the final output - in our case additional profit per plane.
- Sensitivity table re-runs the model multiple times for various input values
- The tables help you understand the relationship between inputs and outcomes
ALT + H , H: Color cells white (remove gridlines)
ALT + H , B , T: Add a thick border around selected cells
ALT + A , W , T: Insert Data Table
ALT + H , L: Open conditional formatting menu
Case study details
Zippy Airways has been operating for 3 months out of Gatwick Airport in London and has 16 flights per day between London and four European cities.
No-overbooking policy... yet
Unlike most other airlines Zippy does not operate an overbooking policy – which allows the airline to sell more tickets than the number of seats (180) available on a flight - thereby increasing revenue. The policy relies on a certain number of customers that book a ticket but do not show up for the flight (no-shows).
If the number of no-shows is greater than the additional tickets sold, no extra cost. If too many passengers show up, however, the airline must pay a ‘bumping cost’ to move passengers to a later flight.
If a customer gets bumped to another Zippy Airways flight later that day, then Zippy will pay them on average £150 in vouchers for the inconvenience.
If a passenger is bumped from a flight that has no Zippy Airways planes later that day, she will fly home with SloMo Airways, which has an agreement with Zippy to look after all bumped passengers at a cost of £175 per passenger. The passenger still receives the voucher from Zippy.
Once you have a sample model built, it's worth conducting some WHATIF analysis to better understand the relationship between your decision variables, projections, and your outcomes, in this case, additional profit. A common modeling tool for insight is sensitivity analysis. A sensitivity analysis reruns your model multiple times for different input values, which are then displayed on a table. To conduct a sensitivity analysis, we'll use the Excel data table, which allows us to conduct a sensitivity on two inputs simultaneously. My two table inputs will be the booking limit, which I control and the number of no-shows, which I don't control. However, I could have chosen any of the projection values from this list, and typically, I would run multiple sensitivities for these values as well. Let's start off by creating a space for our sensitivity analysis. I'll remove the grid lines in this space, which Alt + H, H and I'll also add a thick border with Alt + H, B, T. I'll now add in my values for the booking limit and the no-shows and to save some time I'll do this off-camera. Next, we have to link our table to the desired output value which is additional profit. So in the top left-hand corner cell of our table, I'll write equals and link this to additional profit. We're now ready to create our Excel table. So I'll select all our cells in the table and then I'll press Alt + A to enter the data tab, then W for WHATIF analysis and T for data table. This opens the data table dialogue box and asks for a row input cell. And a row input cell is simply the booking limit. So I'll select the booking limit and move on to column input cell, which is No Shows per Flight. When we press OK, our table is now populated with the additional profit values. To make our table values easier to analyze, let's apply some conditional formatting. So I'll select the cells, and I'll press Alt + H to enter the Home tab and then L for conditional formatting and I'll select the color scale from green to red. And now you can see that our table is much easier to read. Let's see what insights we can now draw from this table. From the first column, we can see that a booking limit of 180 seats makes no difference because no extra bookings would be created. So there will be no additional revenue or additional costs. A lower booking limit, such as 185 or 190 is a lower risk option and in fact, for this set of inputs, we can only gain money by implementing a booking limit of 185. Higher booking limits results in much higher risk for the company with a greater potential for profits if the no-shows are higher, but also a greater potential for losses if there are very few no-shows. It's important to remember that when you're conducting sensitivity analysis, other projections are also feeding into the model as well. For example, let's say we increase the price per ticket to 175. This significantly changes the values in our sensitivity table and makes the higher booking limit a much more attractive option. So by conducting sensitivity analysis always be aware of the other inputs that are not included in the table, but do affect your final outcome. As an exercise, try to create two more sensitivity tables replacing no-shows with price per ticket and demand. With practice, you will soon become very adept at creating sensitivity tables and gaining insights from them.