13. Analyze Risks in Our Model

Overview

To gauge risks effectively, we can run some sensitivity analysis for the most risky assumptions in our model.

Summary

  1. Sensitivity Analysis for Conversion Rate (00:04)

    When creating a model, the main sources of risk should be included in the input section. You can then use sensitivity analysis to study the impact of these risk sources on the model output. In our model, we want to analyze the impact on additional profit of different levels of demand, which is represented by the conversion rate.

    We create a sensitivity analysis of different conversion rates and booking limits using a data table. This table shows us that the optimal booking limit does not depend on the conversion rate. It also shows that Zippy Airways can make a substantial additional profit even if the conversion rate is considerably below the rate observed in the data set.

  2. Refining Bumping Costs (02:08)

    We also want to study the impact of changed bumping costs. To do this, we obtain a greater understanding of bumped costs from Zippy Airways. Low cost bumped passengers must be given vouchers, and the voucher amount can vary in certain circumstances. High cost bumped passengers are given the same vouchers, and Zippy must also pay a fixed fee to send them on SloMo Airways. We update our control panel to reflect these changes.

  3. Sensitivity Analysis for Bumped Costs (04:01)

    We create a sensitivity analysis of voucher costs and booking limits using a data table. The table tells us that higher voucher costs will reduce additional profits. The impact of adjusting voucher costs is greater with a higher booking limit.

Transcript

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.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model