Wolverine Airlines has provided the following suggested fleetings for markets A-B, B-C, and A-C in Tables 1-4. Use the information in the tables to calculate the spill costs associated with each fleeting. Calculate the operational cost, spill cost and contribution for each fleeting. 

Step 1: Pull the information out of the tables so you can see the whole picture:
 
We have 2 flights: Flight 1 and Flight 2
Flight 1: 100 pax traveling, fares per are $250.00
Flight 2: 150 pax traveling, fares per are $300.00
Flight 1 & 2 combined: 125 pax traveling, fares per are $450.00

We have 4 Fleets:
Fleet 1: operates A320 on flight 1 and 2
Fleet 2: operates A320 on flight 1, B757 on flight 2
Etc.

First we need to calculate the operating costs:

Fleet 1, which operates on both flights costs: 10,000 for flight 1 and 15,000 for flight 2. Add those to total the operating costs.

Do the same for fleets 2-4.

To calculate the Spill Cost we are looking for the revenue lost when the flight cannot accommodate the passenger demand. As the Spill figures are given, we only need to calculate the loss:

So for Fleet 1, 75 pax traveling A-C are spilled. Take the fare cost of that leg ($450) and multiply it by 75 =    then do the same for leg B-C at 50 pax spilled (50 x 300) and add the figures together = $48,750.00

 
Contribution is the profit, or maximum potential revenue, minus the spill cost and operating costs....

So for Fleet 1 again, to find the max potential revenue we need to multiply the number of passengers booked on all flights by the fares:

A-B leg = 100 pax, no spill x $250 = $25,000
A-C leg = 125 pax booked – 75 pax spill = 50 pax total x $450 fare = $22,500
B-C leg = 150 pax – 50 pax spill = 100 pax total x $300 fare = $30,000
Total max potential revenue is then: $22,500 + $30,000 + $25,000 = $77,550.

Add Spill cost and operating costs for Fleet 1 = $25,000 + $48,750 = $73,750.00

Now subtract Spill and operating costs from the max potential revenue =  $3,750.00

 
…. Okay, now it's your turn. ;)

Construct two different time-space networks using the information found in the Wolverine scenario (below).
This will allow you to compare the profits associated with each fleeting. 

An example of a time-space network can be found on page 188 of "The Global Airline Industry". Label each time-space network as either Fleet Assignment 1 or Fleet Assignment 2.

To start, I recommend drawing the schedule map of the existing routes in Table 1. Once you have your drawing, duplicate it. Now you have the foundation map for both fleet assignments. To create your a fleet assignment, refer to Table 2. We have 4 airplanes and 3 aircraft types to utilize. 

For Fleet #1, choose 1 aircraft type and assign it to one of the flight numbers in Table 1. Continue assigning aircraft to flight numbers until all aircraft are utilized and all routes have been assigned. Be careful to make sure that the route you assign to a particular aircraft is feasible; consider return trips and departure times. Also, be sure to indicate which aircraft type is associated with each flight number on your drawing.

For Fleet #2, take your duplicate foundational schedule map and assign different aircraft to the scheduled routes, repeating the steps above for Fleet #1. Change it up! 

Now, calculate the profits associated with each fleet assignment. Refer to the figures in Table 3 to locate the various amounts associated with aircraft types and flight numbers. Indicate your profit total at the bottom of your Fleet Assignment drawings. (Be sure to include your name on them too!)

After calculating the probabilities and EMSR for each fare class, the airline must decide how many seats to protect and limit for each fare class in this flight. It makes sense for the airline to protect seats for a higher fare class as long as the EMSR for the additional seat is greater than the revenue received for that seat from a lower fare class. 

For example, the EMSR for the 19th seat is $672.27 for the Y fare and $659.44 for the M fare; however, the 20th seat sold would yield an EMSR of only $541.56 for the Y fare and $659.44 for the M fare. Because the yield for the 20th seat in the Y class is less than the yield for the 20th seat of the M class, the airline would protect 19 seats for the Y fare, or the seats above the 20th seat, where the discrepancy occurs.   

The airline would then proceed to protect seats in the M fare class until the EMSR level of the nth seat for the B class exceeds that of the M class. In other words, continue to look for a similar discrepancy between the M and B fare classes. In like manner, the airline will protect the seats beginning with the seat above the discrepancy.

Booking limits for each class are determined by subtracting the number of protected seats of each higher fare class from the available seat capacity. In this case, the booking limit for the Y fare class would be 70 because that is the total number of available seats and is there is no higher fare class; therefore, the booking limit is equal to the available seat capacity.   

In the example of the Y fare class, there are 19 protected seats, which number we learn from finding the discrepancy in fares from our chart in table 3. Joint protected seats refers to joining two classes of protected seats. The Y class shows only 19 joint protected seats because there is not a higher class with which to add together with the protected seats from Y.

So, what are the protected seats in the M fare class? How do we determine the number? 

Refer again to our chart in table 3 and locate the fare discrepancy between the M and B fare classes, where the B class fare is more expensive than the M class fare,  then simply refer to the seat number above it as the marker for protected seats for the M class. Input the seat number in the space provided for Protected Seats for the M fare class. 

To find the joint protected seats for the M fare class, what must we add to our number of protected seats for the M fare class? Remember in the example of the Y class that the joint protected seats remain 19 because there is no higher class to combine with it. In the case of the M class, do we have a higher fare class with which we can combine? (Yes). 

Add the number of protected seats from the M class with the number of protected seats from the higher fare Y class to get your total. 

What would be the booking limit?

As there are 70 available seats on the aircraft, and Y is the highest fare class there is no higher fare class to subtract to get a total of 70 seat booking limit, correct? 
Let's try the same with M class. If there is a 70 available seat booking limit for the aircraft and we have 19 protected seats from Y class, what is left over for the booking limit for the M class? (70-19)

What about B fare class? How many protected seats would be awarded to B fare class? 

As there are only 3 fare classes and the Y and M fare class protected seats have already been established, does it stand to reason then that B would get what else is left over?

What about B class joint protected seats? 

Again, as there are only 3 fare classes and B is the lowest fare we have no need to protect it from a seat fare that is lower as B is the lowest there is! In this case, would joint protected seats need to be calculated at all? (No)

What do you think the booking limit would be for the B class? We've already established 70 seats for Y and 51 for M. Do we need to calculate a booking limit at all given that all the B seats are what is left over after calculating Y and M? (No). B class booking limit is then the same as the protected seats = everything that is left over after Y and M calculations.                                          

First of all, don't panic! 

Although there is a bit of statistics involved in the Revenue Management     assignment, it is the overall understanding of the concepts we are after rather than the actual calculations themselves.

What we are trying to accomplish with revenue management is to maximize revenue.... filling each seat with the highest possible fare and by thus doing protecting seat bookings at certain fares. One of the major problems in airline revenue management is the issue of overbooking. 

Because of certain human characteristics, such as ticket purchasing habits of business travelers vs. leisure travelers, it is necessary to determine an overbooking factor due to the inability to predict with accuracy the actual no-show rate for a flight.

To address the overbooking problem we use a more scientific approach to deal with the no-show uncertainty by applying the Gaussian or normal probability for distribution, which includes a mean (NSR) and a standard deviation (STD).

The objective here is to find the authorized seats (AU) that will keep denied boardings (DB) to some specified target level. The capacity and estimates of NSR and STD are specified to keep DB at 0 with 95% confidence..... in other words, we will be using the normal distribution (bell curve) model to determine the percentages of certain revenue levels (seat fares) on a flight.

First, locate the excel file link on the assignment page or locate it below and download the file to your computer/device. Excel has the formula already set to do the calculations automatically. All you need to do is input the data and excel does the work for you.

Next locate the formula tab at the top of the page in the menu bar. Click on the "Fx" tab to reveal the formula bar. Then, double-click the "Fx" on the formula bar to make the formula menu appear.

Scroll down the formula menu to locate NORMDIST. Double click it to open the 'arguments' and description. From here you can enter your values.

All probabilities for the Y-fare class are given to you. Notice that the first few seats show a probability of 100%. Given that we are determining a bell curve distribution and a portion of that distribution is already accounted for (Y class), is it possible for the other two classes to also have a probability of 100%? (No). Think of it as though the number of seats apportioned to the Y class is the ceiling for M and B classes. Where Y may start out at 100%, M and B must start out below that.

Now we need to determine the M and B fare probabilities. Click on the empty cell for Seat 1 under the M Probability. If your formula builder has disappeared, double click on 'Fx' in the formula bar.

The mean and standard deviation are given to you in Table 2 for both M and B classes. Be sure you enter the correlating figures in the formula builder or your answers will not be correct. This leaves us with determining the X-value and the cumulative.

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function. We are of course looking for a distribution function. Enter the value as 'true'.

What then is the X-value? As the scenario points out we are determining the probabilities of fares on an aircraft with a capacity of 70 seats. I think of it this way:

Each seat has both a spacial value and a numerical value. Seat 1 for example is both the number of the seat in that space, as well as the seat number or label. If we were to determine the spacial value for that seat, which is what matters in this scenario, it would be 1 because seat #1 is also space 1. If we were to determine the spacial value for seat 10, it would be (1+1+1+1+1+1+1+1+1+1 = 10) as seat 10 also occupies 10 spaces. (It also carries the same numerical label, 10).

The X-value is obviously the spacial value of the seats we are trying to class. So it is logical to input the X-value that correlates to the seat number, as it is also the spacial value of the seat.

If you pressed enter and received a number that looks something like: .001453... is this a correct value for the M probability for Seat 1? Compare it to the probability for the Y class Seat 1.... If the Y Class Seat 1 has a probability of 100% and the M class Seat 1 has a probability of .1453% is it likely that the Y class takes up 99.85% of the fares? (No!)

How then can we get a logical and hopefully a correct figure? Let's look at a bell curve.

Suppose the Y class is represented by the dark blue shading at the center of the bell curve. M class would be the middle shade fo blue and the B class as the light blue on the ends of the curve. 

As a normal distribution calculation considers all parts of the curve, do we need to include the Y class into our calculations? (No)

Why not? Because the probabilities have already been determined. So, how can we extract the Y class from our calculations? 

As we have 3 distinct classes: Y, M, B, which are all represented on the bell curve (or normal distribution probability), each class represents a piece of the curve. If we already have 1 piece of the 3 pieces, we are only calculating for 2/3 of the curve.

Continuing our example from Seat 1, in the formula bar it will be necessary to subtract the Y class (which value is represented as 1) from the formula, shown as: 1-NORMDIST. 

If you then press enter your values for Seat 1, M probability should read: .998693366 or 99.869%. Calculating the Expected Marginal Seat Revenue (EMSR) becomes a simple task as we take our M probability and multiply it by the Fare Class dollar amount per seat, which for the M class is $750.00.