Sign in or start a free trial to avail of this feature.
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.
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
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 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.