10. Complex Model formulas

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Complex formulas often require additional auxiliary variables to be calculated beforehand. It can also help to draw them out on paper before going to Excel

Summary

  1. Calculating the Price per Ticket (00:04)

    In this lesson, we focus on calculating the various inputs and intermediate values of our model. The price per ticket is the ticket price that would be paid by additional passengers after the flight reaches capacity. We calculate the average price of the tickets to find this price. This is a conservative assumption, as the last few tickets for a flight would probably be more expensive than average.

  2. Additional Calculations (01:10)

    Next, we calculate a few simple values in our model:

    • Total bookings: This is either the booking limit we set, or the demand for the flight, whichever is smaller.
    • Extra bookings: This is the difference between total bookings and capacity, or zero if total bookings is less than capacity
    • Bumped Customers: This is the number of extra bookings minus the number of no shows, or zero if no customers are bumped.
  3. Understanding Bumped Customers (01:57)

    Unit bumping costs are a trickier calculation, so we take time to visualize it before we calculate it. If a customer is bumped from a Zippy Airways flight, they can either be moved to a later Zippy Airways flight, at a low cost, or moved to a SloMo Airways flight, at a higher cost.

    The passenger will be moved to a Zippy Airways flight only if there is another flight on the same route later in the same day, and if that flight has a free seat. If either of these conditions is not met, then the passenger is moved to SloMo Airways.

  4. Calculating Bumped Customers (02:57)

    To calculate bumping costs, we need to understand if bumped passengers will be high cost or low cost. First, we create a column that determines if there is another Zippy Airways flight later the same day, and if so, how many free seats it has. We then use this information to identify how many bumped customers for each flight can be accommodated at a low cost. Finally, we calculate how many bumped customers will incur a high cost.

Transcript

In this lesson, we're going to calculate the remaining inputs and intermediate variables for each flight in the dataset.

Let's start off with the next input in our list which is price per ticket.

This represents the price that would be paid by the additional customers who want to get on a flight after the first 180 seats have already been sold.

We could argue that this price would be higher than the average price of the flight but without any data to back up this assertion.

Let's stay conservative and just let this value equal to the average price of the ticket which can be calculated by dividing the flight revenue by the number of bookings completed.

Once we've implemented an overbooking policy, we can check in the first couple of weeks whether this assumption is too conservative and then change it accordingly, but for now, let's just leave it at the average price.

Next, we have unit bumping cost. This figure actually depends on the number of bumped customers and the number of free seats on the next flight.

So we'll calculate this last when we have the remaining variables filled in.

The next calculation is the number of total bookings, which is the same calculation as our sample single flight model.

The minimum value of the booking limit, or demand, which is cell J7.

Then I press TAB to move to the number of extra bookings.

Extra bookings, which you might recall from the sample model is equal to the maximum value of the total bookings minus capacity or zero.

And finally, we have the number of bumped customers which is equal to the maximum value of the number of extra bookings minus the number of no-shows or zero.

We're now ready to calculate the last column which is the unit bumping cost.

When I'm faced with a tricky calculation like this, I think it's always better to draw out the solution on a sheet before jumping into Excel.

If you need to recheck the details in the case regarding bumped customers, I have included them in the show notes below this video.

So let's start with a case of a bumped customer.

We first have to check if there's another Zippy Airways flight later that day.

If there isn't then we'll have to send the customer on SlowMo Airways which is a higher cost for us.

However, if there is another flight that day, we then need to check if there is a free seat.

If there is a free seat, then we can put the customer on this flight for just the cost of the vouchers so 150 pounds? However, if there isn't a free seat, we must then send this customer on SlowMo Airways as well.

So you can see from this diagram that we must satisfy two criteria to determine the number of low cost bumped customers.

Let's return to the dataset and do this.

So I'll create a new column, with Alt + I, C and I'll entitle it number of free seats available.

I'll then create another column and I'll entitle this, number of low cost bumped.

And to calculate the number of free seats, I'm first going to check if there's another flight out that day.

And to do this, I'll use the IF function.

And I'll simply check, if the destination of the next flight is equal to the destination of this flight.

And if it is, then I can calculate the number of free seats, which is equal to the maximum value of capacity, minus total bookings for the next flight plus the number of no-shows for the next flight, or zero.

And of course, if there isn't a flight out later that day, I'll also return zero.

Then I'll press Enter to complete.

Obviously, this isn't the correct number for the free seats available and that's because I haven't yet entered the values for total bookings in the next flight.

But when I select these values and copy them for the remaining cells, you can now see that the correct free seats available are showing in this column.

To find the number of low cost bumped customers, I'll simply find the minimum of the number of free seats or bumped customers.

And finally to find the number of high cost bumped customers, I´┐Żll create a new column.

And it will equal to the total number of bumped customers minus the number of low cost bumped customers.

And again I'll copy and paste for the remaining cells.

With these variables now calculated, we're almost ready to find additional revenue, additional cost, and our main outcome, additional profit, however before we do this, we should update our influence diagram, write some comments to explain what we've done and also conduct a quick formula audit.

I'll be performing these tasks in the next lesson.

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