Sign in or start a free trial to avail of this feature.
6. Build Simplified Model
We start by building a simplified model for a single-flight - which will calculate additional profits for Zippy from some sample input data.
Intermediate variable calculations
Total bookings = The lower value of booking limit and demand
# Extra bookings = The higher value of (Total bookings - Capacity) and 0
# Bumped customers = The higher value of (Extra bookings - No-Shows) and 0
Additional Revenue = # Extra bookings x Price per additional ticket
Additional Cost = # Bumped customers x Unit bumping cost
Additional Profit = Additional Revenue - Additional Cost
Now that we have the first iteration of our model laid out and some sample data included for our inputs, our next step is to build formulas to calculate intermediate variables and then our outputs. Let's start at the top with total bookings which depend primarily on demand for tickets. If the demand exceeds the booking limit, then the booking limit will be the total number of bookings. If demand is lower than the booking limit, then the demand figure will be the total number of bookings. To build this formula in Excel, we can use an IF function. So I'll write, equals IF and open the bracket. And the logical test will be, if demand, which is cell D13 is greater than or equal to the booking limit. And if it is, I'll return the booking limit, which is D7. And if it isn't, I'll return demand. I'll then close the bracket and press Enter. And given that demand is currently greater than the booking limit, then the total number of bookings is the booking limit. If I decrease demand to under 200 seats, let's say 185, you can see the total bookings goes to 185, exactly what we want. We can also build this formula with the MIN function. So instead, I'll write, equals min. And I'll return the minimum value of the booking limit or demand. And this gives me the exact same result as the previous IF statement. Now we move to the number of extra bookings. If the total bookings is greater than capacity, then extra bookings is equal to the total bookings minus capacity. If total bookings is less than capacity then we'll return zero. Again, we can use an IF function to solve this formula or the MAX function which I'll use here. So I'll write, equals max, and I'll return the maximum value of total bookings minus capacity or zero. I'll close the bracket and press Enter. And as you can see, given that the total bookings is five greater than the capacity, then the number of extra bookings is five. If I change demand so that it's lower than capacity, let's say 165, you can see that extra bookings goes to zero, exactly what we want. Changing the values of inputs is a great way to test whether your formulas are working correctly. And as you'll see I do this for almost all my intermediate variables and output calculations. Our last intermediate variable is the number of bumped customers. Customers are bumped if the number of extra bookings is greater than the number of no-shows per flight otherwise, the number is zero. Again, we'll use the MAX function. So I'll write equals max, and this will return the maximum of the number of extra bookings minus the number of no-shows or zero. Then I'll close the bracket and press Enter. And given that we have no extra bookings on this particular flight, the number of bumped customers is also zero. However, if I increase demand to let's say 200 tickets, we can now see that the extra bookings are 20, the number of no-shows are 10 and hence the number of bumped customers is 10. So the formula is working correctly. Let's wrap up this lesson by calculating the outputs for our model. So additional revenue is equal to the number of extra bookings, multiplied by the price per ticket, which is cell D14. Additional cost is equal to the number of bumped customers, which is K11 multiplied by the unit bumping cost which is D16. Then I'll press Enter to complete. I find the change in value is simply the additional revenue minus the additional cost. And on this particular flight, the change in value is zero. We now have a simple one flight model that gives us the additional profit per flight based on a set of sample inputs. When I change the inputs, for example, moving the booking limit to 190 seats, you can see the additional profit value changing accordingly. Even without looking at the actual data set, this simple model can provide us with some valuable insights from Excel's WHATIF analysis tools. The most useful of these Excel data tables will help us do a sensitivity analysis in the next lesson.