Sign in or start a free trial to avail of this feature.
12. Calculate Model Outputs
In this lesson, we complete the model-building process by calculating the outputs in our control panel.
ALT + W , F, F: Freeze panes
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
ALT + H , 0 : Add a decimal place
ALT + H , 9 : Remove a decimal place
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
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.