12. Calculate Model Outputs

Overview

In this lesson, we complete the model-building process by calculating the outputs in our control panel.

Summary

  1. Setting up the Workbook (00:04)

    Before we perform the final calculations in our dataset, we make a few changes and additions to the workbook. In our data set, we freeze panes, using the shortcut Alt W, F, F, in order to ensure the first four columns are always visible as we scroll through the data set. We also add several new columns, representing the final values we need to calculate.

  2. Calculating Values (01:06)

    We calculate the following additional values for each flight in our data set:

    • Total additional revenue: This is the number of extra bookings multiplied by the price per ticket.
    • Total additional cost from low cost bumped passengers: This is the number of low cost bumped passengers multiplied by the cost of bumping a low cost passenger.
    • Total additional cost from high cost bumped passengers: This is the number of high cost bumped passengers multiplied by the cost of bumping a high cost passenger.
    • Total additional profit: This is the total additional revenue minus the two additional cost figures.
  3. Adding Outputs to the Control Panel (02:07)

    On the control panel, we want to calculate the total additional revenue, total additional cost, and total additional profit for the whole dataset. We calculate these by taking the sum of the relevant columns we created in the dataset. This completes our model. We can adjust the input cells, and immediately see the impact on the total additional profit for our data set.

Transcript

It's now time to calculate the upward values for our model. You'll notice that I've added the low cost bumped price and the high cost bumped price to our input section and named these cells. I've also added two lines underneath the total addition of the cost that splits this value between the high cost and low cost bumped customers. This split is not necessary for calculating total additional profit, but it may help us better understand the changes in total cost when we alter the booking limit. Let's now return to our dataset and the first thing I'll do is freeze panes. Because our columns are now extending beyond the screen and I'd like to keep the first four columns in place when I scroll horizontally. So I'll use the shortcut Alt + W, F, F and move to the right-hand side of the dataset. Off camera, I'll add a couple of new columns, total revenue, total additional cost for the high cost bumped, total additional cost for the low cost bumped and total additional profit. Calculate total additional revenue will simply take the extra bookings, multiply by price per ticket. To calculate total additional cost for the low cost bumped customers, we'll take the number of low cost bumped customers and multiply it by the low cost bumped price. And for the high cost bumped customers, we'll do the same. So we'll take the total number of customers and multiply it by the high cost bumped price. And then total additional profit is simply equal to the revenue minus the two cost figures. Then we can copy these formulas for the remaining cells. We'll then do some quick formatting with Alt + H, 0 and Alt + H, 9. And we now have our totals calculated in the correct format. Let's now link these cells to our control panel. So total additional revenue is equal to the sum of the values in our total additional revenue column. So I'll skip up to the top of the dataset, select all of the cells, close the bracket and press Enter. Total additional cost is just the sum of the total cost columns. So I'll select these columns, again skipping up to the top of the dataset. Ctrl + Shift + down arrow and close the bracket. And finally, total additional profit is simply equal to the total additional revenue minus total additional cost. Now when we change the booking limit, let's say to 190, you can see the immediate impact on total additional profit. As an exercise, I'll leave it for you to calculate the two percentage lines underneath the total additional cost. Leaving my answer in the after file which you can download beneath this video. Although the model is now complete, our work is not yet finished. In the next lesson, we'll examine some of the weaknesses in our model and try to quantify the risks that can affect our results and our conclusions.

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