"For a small fee, we'll extend it all the way to the door." 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.
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.
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.
| revenue_management_problems.xlsx |
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.
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.
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.
RSS Feed
