11. Perform Checks on Model

Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial


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.

Lesson Notes

Perform checks on model

- Other users find the influence diagram very helpful so make sure it's kept up-to-date
- Formula auditing checks if your formulas are referencing the right cells
- Test calculations check if your formulas are written correctly

Keyboard shortcuts

ALT + R , C: Insert a comment
ALT + R , H: View / Hide a comment
ALT + M , P: Trace precedents
ALT + M , A, A: Remove all traces


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 column and delete it, Alt + E, D. I'm also going to go to the influence diagram and update this off-camera. On the updated influenced diagram, you can see that I have 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 influenced 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 this spreadsheet for the first time, the influence diagram articulates the structure of a model much better than a 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 free seats are available on that flight. And to exit, I just press ESC. 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 right 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 a price per ticket and on the first cell, I'll use the shortcut Alt + M, P to trace precedence. And once I'm happy that booking is completed and flight revenue are the correct cells to reference, I can press Alt + M, A, A through the precedence and move on to the next cell. Again I'll press Alt + M, P to check the precedence 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 simpler 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 5 low cost bumped customers. These five customers are bumped because we had a full flight with nine extra bookings but only four no-shows. They were all able to be pushed on to 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.