Revenue Management

Problem 1: Probability & EMSR (You may need to scroll down to see Problem 2!)

Instructions:
A daily flight has a cabin capacity of 70 available seats that must be divided among three fare classes: Y, M, and B, each with respective fares of $900, $750, and $600. The number of tickets sold in each fare class each day last month is shown in Table 1 below. This data was used to calculate the mean number of tickets sold daily and standard deviation for each fare class, shown in Table 2.

Complete Table 3 by doing the following:

1. Use the Normal Distribution formula (NORMDIST) and data from Table 2 to calculate the probability of selling each seat in each fare class. See Excel Help if
you need instructions on using the NORMDIST formula.
2. Calculate the expected marginal seat revenue (EMSR) for each seat by multiplying the probability of selling each seat by the full fare class value (e.g. $900 for Y).

The probabilities and EMSR for the Y fare class have been done as an example for you.
Table 1: Tickets Sold in Each Fare Class                   Table 2: Means & Standard Deviations                   Table 3: Probabilities and EMSR
Fare Class                   Fare Class                   Fare Class
Day   Y ($900)   M ($750)   B ($600)           Y ($900)   M ($750)   B ($600)       Seat   Y Probability   Y EMSR   M Probability
1   22   29   35       Mean   20.633   30.467   33.033       1   100.000%   $900.00
2   22   20   41       Sigma (Standard Deviation)   2.456   9.790   8.028       2   100.000%   $900.00
3   23   42   38                           3   100.000%   $900.00
4   24   35   27                           4   100.000%   $900.00
5   22   42   19                           5   100.000%   $900.00
6   24   30   39                           6   100.000%   $900.00
7   18   25   28                           7   100.000%   $900.00
8   22   36   41                           8   100.000%   $900.00
9   23   16   41                           9   100.000%   $900.00
10   20   44   32                           10   99.999%   $899.99
11   24   32   34                           11   99.996%   $899.96
12   20   42   25                           12   99.978%   $899.80
13   17   30   28                           13   99.906%   $899.15
14   17   40   23                           14   99.654%   $896.88
15   17   18   34                           15   98.909%   $890.18
16   18   38   19                           16   97.037%   $873.34
17   17   24   32                           17   93.046%   $837.41
18   21   16   41                           18   85.816%   $772.34
19   20   35   22                           19   74.696%   $672.27
20   20   34   40                           20   60.174%   $541.56
21   19   28   21                           21   44.067%   $396.60
22   23   25   43                           22   28.897%   $260.07
23   21   16   41                           23   16.764%   $150.88
24   22   47   40                           24   8.524%   $76.72
25   21   34   44                           25   3.772%   $33.95
26   21   46   29                           26   1.445%   $13.00
27   17   18   40                           27   0.477%   $4.29
28   24   18   31                           28   0.135%   $1.22
29   23   20   22                           29   0.033%   $0.30
30   17   34   41                           30   0.007%   $0.06
31   0.001%   $0.01
32   0.000%   $0.00

Problem 2: Booking Limits & Seat Protection

Instructions:

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 $643.12 for the M fare. The airline would protect 19 seats for the Y fare class. 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.

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 there is no higher fare class; therefore, the booking limit is equal to the available seat capacity.

Table 4 below contains the protected seats and booking limits for the Y fare class.
Complete the table by calculating the protected seats and booking limits for the M and B classes. Use the rationale and instructions above to make your calculations.

Table 4: Protected Seats & Booking Limits
Forecasted Demand
Fare Class   Fare   Mean   Sigma   Protected Seats       Joint Protected Seats       Booking Limit
Y   $900    20.633   2.456   19       19       70
M   $750    30.467   9.790
B   $600    33.033   8.028

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂