Whenever needed, enter your answers with 2 decimal places. Do not round any intermediate calculation, only round the final answer.

South Central Airlines (SCA) operates a commuter flight between LAX and Denver. The airplane holds 50 passengers, and currently SCA sells up to 50 tickets. Past data shows that SCA always sells all 50 tickets but that, on average, two passengers do not show up. As a result, with 50 tickets sold, the flight is often being flown with empty seats.

To capture additional profit, SCA is considering a new policy involving overbooking: In this new strategy they would now sell 52 tickets even though the airplane holds only 50 passengers. SCA believes that it will always be able to sell all the 52 tickets (given the current price of tickets).

SCA receives a marginal profit of $100 for each passenger who purchases a ticket (regardless of whether or not they show up). The airline will also incur a cost for any passenger denied seating on the flight which is on average $250. This cost includes the added expenses of rescheduling the passenger as well as a negotiated compensation amount to be paid to the customer for their inconvenience. Develop a spreadsheet simulation model for this overbooking system thatcalculates the total profitfor any given number of passengers that show up.

Hint: In developing the spreadsheet, have a cell to show how many of the ticketed passengers would show up for the flight. Also separately calculate the total revenue of selling tickets and the total cost of overbooking. You can use the IF function to facilitate calculation of total cost of overbooking.

Question1

Not yet answered

Points out of 1.00

Flag question

Question text

If 48 passengers show up, what would be the total profit for SCA?

Answer:

Question2

Not yet answered

Points out of 1.00

Flag question

Question text

If 52 passengers show up, what would be the total profit for SCA?

Answer:

Information

Flag question

Information text

The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:

Passengers Showing Up

Probability

48

0.05

49

0.25

50

0.50

51

0.15

52

0.05

Question3

Not yet answered

Points out of 1.00

Flag question

Question text

What type of random variable is the number of passengers that show up?

Select one:

Discrete random variable

Continuous random variable

Clear my choice

Question4

Not yet answered

Points out of 1.00

Flag question

Question text

What is the likelihood that overbooking would happen? (enter as a number between 0 and 1)

Answer:

Information

Flag question

Information text

Create a simulation model to capture the impact of uncertainty in number of customers showing up on total profit and total cost of overbooking. Run 10,000 trials of this simulation.

Note that in all questions below that require an answer based on your simulation, your answer would be approximate based on the random numbers generated in your version of Excel. Just enter the numbers that you get, the answers are graded with enough tolerance to allow for this.

Question5

Not yet answered

Points out of 1.00

Flag question

Question text

What is the average profit with this overbooking strategy?

Answer:

Question6

Not yet answered

Points out of 1.00

Flag question

Question text

What is the standard deviation of profit with this overbooking strategy?

Answer:

Question7

Not yet answered

Points out of 1.00

Flag question

Question text

What is the likelihood that SCA would be able to earn a profit of $5100 or more? (enter as a number between 0 and 1)

Answer:

Information

Flag question

Information text

SCA knows that the cost of overbooking per passenger is going to be uncertain as it might be able to negotiate different amounts with different passengers. It is estimated that the TOTAL cost of overbooking (for all customers overbooked) is normally distributed with a mean equal to "350*number of overbooked customers", and a standard deviation of "100*square root of number of overbooked customers". Implement this information in your simulation model.

(Hint: In order to not get an error when the number of overbooked customers is 0, you would need to first check if there is any customer overbooked. If this was true, use the information above to generate a random total overbooking cost. If no customer is overbooked, then total overbooking cost would be 0. IF function can be very useful.)

Question8

Not yet answered

Points out of 1.00

Flag question

Question text

What type of random variable is the total cost of overbooking?

Select one:

Discrete random variable

Continuous random variable

Clear my choice

Question9

Not yet answered

Points out of 1.00

Flag question

Question text

What would be the average profit when uncertainty in overbooking cost is incorporated?

Answer:

Question10

Not yet answered

Points out of 1.00

Flag question

Question text

What is the average profit whenever overbooking does happen? (Hint, you need to use AVERAGEIF function)

Answer:

Question11

Not yet answered

Points out of 1.00

Flag question

Question text

The likelihoods for number of passengers showing up (as given in the table earlier) are obtained based on some managerial estimates. To better reflect on this uncertainty, data on all past flights from LAX to Denver has been studied and it is found that 4.6% of all passengers that bought a ticket did not show up on the flight reserved. This tells us that approximately, a passenger with purchased ticket has a 4.6% change of not showing up. Which of the following best describes a probability distribution for the number of passengers that would show up out of those that purchase ticket in the considered overbooking strategy (i.e. selling 52 tickets while there are 50 seats)?

Select one:

Binomial distribution with trial probability of 95.4% and number of trials of 52

Binomial distribution with trial probability of 4.6% and number of trials of 52

Binomial distribution with trial probability of 4.6% and number of trials of 50

Binomial distribution with trial probability of 95.4% and number of trials of 50

Normal distribution with mean of 95.4 and standard deviation of 52

Normal distribution with mean of 49.6 and standard deviation of 0.046

Clear my choice

Information

Flag question

Information text

SCA also thinks that marginal profit per passenger to be uncertain as well, as some passengers purchase early at a discounted price but business passengers buy later or with more flexibility leading to higher margins. The data file provided shows margins obtained for 200 passengers flying on this flight in the past.

Question12

Not yet answered

Points out of 1.00

Flag question

Question text

What type of random variable is the profit margin per passenger?

Select one:

Discrete random variable

Continuous random variable

Clear my choice

Question13

Not yet answered

Points out of 1.00

Flag question

Question text

Draw a histogram of the data. Which of the following distributions could potentially be used to best describe this data?

Select one:

Uniform distribution

Normal distribution

Binomial distribution

Exponential distribution

And this is the data table. Thank you so much for your help!

profit margin

107.77

99.68

109.84

100.29

83.1

109.57

87.13

99.5

105.33

87.97

89.38

97.53

105.03

92.01

82.86

88.6

90.29

109.34

83.81

101.38

87.17

90.01

104.38

85.88

87.69

95.74

96.27

99.17

92.39

89.23

95.76

107.26

90.93

100.97

94.9

99.63

89.44

93.26

103.25

102.82

92.22

85.43

109.02

81.26

106.11

87.64

107.87

93.41

105.92

108.78

106.86

108.3

92.5

96.49

94.49

98.04

90.09

92.75

86.95

89.79

92.39

106.18

109.24

83.45

109.55

102.95

100.81

95.41

91.73

97.61

84.38

108.84

107.08

96.92

107.52

99.44

102.66

83.53

99.82

94.97

95.67

89.56

103.78

100

96.62

102.58

107.66

106.49

95.29

88.69

91.35

84.81

108.25

86.12

87.27

80.68

99.33

88.08

81.1

95.95

96.85

107.13

90.47

103.37

108.07

93.55

103.73

98.18

95.53

89.82

85.64

85.19

83.57

104.99

95.29

96.06

97.68

99.17

99.33

100.08

98.77

99.43

82.75

81.7

97.93

100.3

106.57

101.95

80.72

90.6

84.71

87.41

92.71

99.32

82.04

94.71

101.63

84.69

91.56

94.36

85.09

107.7

80.43

84.31

90.62

92.81

89.16

104.78

103.14

102.47

99.76

83.53

85.59

101.38

90.39

83.66

89.78

88.18

89.77

96.88

107.97

86.29

85.45

83.53

89.19

97.21

90.65

87.91

100.32

88.84

105.63

82.39

104.29

93.75

99.34

91.82

105.91

104.54

83.63

104.78

100.14

99.95

93.23

86.31

107.78

86.88

83.28

85.02

98

98.71

91.64

107.94

102.33

108.11

95.35

101

103.92

91.8

90.74

82.74