13. Analyse Risks in Our Model
To gauge risks effectively, we can run some sensitivity analysis for the most risky assumptions in our model.
Risk in our model
- Risks tend to arise from input assumptions and projections that may be incorrect
- To quantify the impact of these risks, I run sensitivity tables for various input combinations
- These tables tell me how sensitive the outcome is to changes in an input variable
ALT + I , R: Insert row
ALT + A , W , T: Insert Data Table
ALT + H , K: Add comma separators
ALT + H , 9: Remove a decimal place
ALT + H , L: Open conditional formatting menu
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
CTRL + B: Bold text
CTRL + I: Add italics
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.
Now that our model is built, we need to conduct some risk analysis to see where our output numbers may differ from reality. My favorite way of conducting this type of risk analysis is using sensitivity tables which I introduced back in lesson 7 of this course.
The main sources of risking your model should be present in the input section.
The first source of risk I'm going to analyses will be demand in the form of conversion rate.
Off camera, I'll create a table with booking limits written across the top, and conversion rates in a column on the left-hand side.
Next, I'll link our output, total additional profit to the table by putting it in the top left-hand corner.
And then I'll select the full table and Alt + A, W, T for data table.
Our row input cell will simply be the booking limit and our column input cell will be the conversion rate. I'll press OK to complete the table.
I'll then select the cells inside the table and do some quick formatting.
Alt + H, K to add comma separators and Alt + H, 9 to remove the decimal places.
I'll also add some conditional formatting with Alt + H, L and then select the green and red option.
This serves to make our table much more readable. From the table, some interesting insights emerge.
Firstly, we noticed that changes in the conversion rate don't actually affect our choice of booking limit because 200 offers the best additional profit at every conversion rate level.
The table also tells us that while a lower conversion rate reduces additional profit from most booking limits, it doesn't reduce additional profit by a huge amount.
And Zippy can still make a healthy profit with a conversion rate as low as 15 percent.
These insights make a strong case for implementing an overbooking policy.
Now let's test some of our other assumptions particularly the compensation paid to bumped customers.
From the case, we were told that a 150 pounds worth of vouchers would be given to each bumped customer.
When we question the CEO about this further he says that this is the industry norm for short haul flights.
But that it may well be higher up to 200 pounds depending on how willing customers are to wait for a later flight.
We also question the CEO about the agreement with SlowMo Airways for the high cost bumped customers, but he says that this is a fixed assumption that will be in place for at least 18 months.
So let's run a sensitivity for increased voucher costs.
First, we'd have to make some changes to our input panel.
I'll start by categorizing the two prices as intermediate variables.
I'll insert a row, and I'll insert another row with Alt + I, R.
So I'll simply type, intermediate variables.
We can format this with Ctrl + B and Ctrl + I.
Under fixed assumptions, I'll put the SlowMo cost.
This will equal to 175 pounds. Under variable assumptions, I'll create a new row and include voucher costs.
I'll do some formatting and set this equal to 150 pounds as well. Now I'll change the formulas for the low cost bumped price and the high cost bumped price.
The low cost bumped price will simply be equal to voucher costs and the high cost bumped price will equal to the voucher costs plus the SlowMo cost per passenger.
And off camera, I'll do some quick formatting of our numbers to make sure we abide by our legend.
These changes to the input panel don't immediately affect the bumped prices of 150 and 325 pounds, however, they do correctly connect the voucher cost assumption to these two values allowing us to conduct our sensitivity.
Below the first risk analysis, I've created another blank table with voucher costs ranging from 200 to 140 pounds.
In the top left-hand corner, I'll again link the table to the additional profit and then I'll select the table with Alt + A, W, T to set up a data table.
The row input cell will be booking limit as before and the column input cell will be voucher costs.
I'll then press OK to complete the table.
The table tells us that a higher voucher cost will have an impact on additional profit.
At large booking limits, big swings of over 800,000 pounds represent a large risk.
Overall, this quick risk analysis has identified a booking limit range between 195 and 205 that could deliver sizable additional profits even allowing for lower conversion rates and higher voucher costs.
In the next lesson, we'll try to find this specific booking limit value that will maximize additional profit using another Excel tool called Solver.