11. Perform Checks on Model


Complex formulas should be audited and tested with different inputs to make sure they work correctly. In this lesson, I'll show you a couple of ways do this.


  1. Updating the Influence Diagram (00:29)

    We update our influence diagram to account for new parameters that have been created in our data set. These include the low cost and high cost of bumping passengers, as well as other new parameters. For a colleague opening the model for the first time, the influence diagram makes it easy to understand the structure of the model, therefore you should always keep it up to date.

  2. Adding Comments (01:27)

    We can also make it easy for colleagues to understand our model by adding comments to the model. Comments are text based descriptions of the contents of a cell. To add a comment to a cell, select the cell, then use the shortcut Alt, R, C to create a comment. You then type the comment and press Escape. Here we add comments to the column headers to explain the contents of the columns in the data set.

    A cell with a comment will be indicated by a red triangle in the top right corner of the cell. To view the comment, we hover the mouse over the cell, or select the cell and press Alt, R, H to view and then hide the comment.

  3. Conducting a Formula Audit (02:30)

    Finally, we conduct a quick formula audit to check our calculations work as they should. To check which cells are used in a formula, we can select a cell and select Trace Precedents with the shortcut Alt, M, P. When we do this, arrows indicate all the cells used to calculate the value in the selected cell. This gives us an indication of whether our formula is referencing the cells we expect it to reference. To remove precedents from a cell, we select it and press Alt, M, A, A.

    To check that our formulas are correct, we can do some simple calculations. For example, we can take a single row of the data set, and use intuition to calculate the values we would expect to see in its cells. If these values match the values in the cell, it suggests our formulas are set up correctly.


The calculations we performed in the last lesson provided us with the number of low-cost bumped passengers and the number of high-cost bumped passengers for each flight. We can now calculate the total additional cost by multiplying these totals by the low cost and high-cost prices. As a result, we don't need to calculate the unit bumping cost at all, so I'm going to select this collum and delete it, Alt + E + D. I'm also going to go to the influence diagram and update this off camera.

On the updated influence diagram, you can see that I've added a couple of new parameters including low-cost and high-cost price and the number of free seats on the next flight. While the number of free seats isn't strictly a parameter, it doesn't make a lot of sense here to include the full influence diagram for the next flight just so I can show the origin of this value, so instead I've decided to simplify the diagram and leave it as a parameter input.

The remaining changes, which lead to finding the number of low-cost and high-cost bumped customers should be easy to follow as it just replicates the calculations we made in the last lesson. However, for a colleague just opening the spreadsheet for the first time, the influence diagram articulates the structure of the model must better than our list of columns in the data set. Always make sure that your influence diagram is up to date for this reason. Our colleagues might also benefit from some explanatory comments that we can add to our calculations in the model. To insert a comment, I can use the shortcut Alt + R + C and then begin typing. And I'll write returns a positive number if a later flight exists and three seats are available on that flight.

And to exit, I just press escape.

Off camera, I've included comments for most of the calculations we've made in the last lesson and one or two of the control panel inputs. If a cell has a red triangle in the top right hand corner, then you know that a comment is associated with that cell. To view that comment, you can click on the cell with the mouse or if you want to use a keyboard shortcut, you can use Alt + R + H to view and then to hide the comment. Be sure to check out my comments for each of these cells in the after file below the video.

The last task I want to perform before we calculate the outputs is to conduct a quick formula audit for our calculations. I'll start with the price per ticket and on the first cell, I'll use the shortcut Alt + M + P to trace precedents. And once I'm happy, the bookings completed and flight revenue are the correct cells to reference, I can press Alt + M + A + A to remove the precedents, move on to the next cell. Again I'll press Alt + M + P to check the precedents for the pre seats available and complete this for the rest of my calculations. This audit checks that I'm referencing the correct cells in my calculations. However it's also necessary to check if my formulas are correct. For simple models like this, we can use some back of the envelope calculations. Let's take for example the 6:50 flight from London to Madrid where I have five low-cost bumped customers. These five customers were bumped because we had a full flight with nine extra bookings but only four no-shows. They were all able to be pushed onto the next flight because there were 71 seats available. These 71 seats were due to the fact that only 123 passengers booked tickets on the next flight and 14 didn't show up. 180 minus 123 plus 14 is equal to 71.

I'd also recommend repeating this check for the high-cost bumped customers which we can see by increasing the booking limit in the control panel.

Once we're happy that these formulas are correct, we can move on to calculating our output values and completing our model in the next lesson.

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