10. Complex Model formulas
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
- Drawing a diagram of a complex formula can greatly simplify the task
- Complex formulas can require additional auxiliary variables to be calculated
- For example, to find the Unit Bumping Cost the # free seats on next flight is needed
ALT + I , C: Insert new column
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
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.