Whenever needed, enter your answers with 2 decimal places. Even though exact ans
ID: 3250833 • Letter: W
Question
Whenever needed, enter your answers with 2 decimal places. Even though exact answers change every time the formulas on the spreadsheet are re-evaluated, it is OK. Just enter the number that you get, they are graded with an appropriate level of tolerance.
South Central Airlines (SCA) operates a commuter flight between LAX and Denver. The airplane holds 50 passengers, and currently SCA books only up to 50 reservations. Past data shows that SCA always sells all 50 reservations but that, on average, two passengers do not show up. As a result, with 50 reservations, the flight is often being flown with empty seats. To capture additional profit, SCA is considering an overbooking strategy in which they would accept 52 reservations even though the airplane holds only 50 passengers. SCA believes that it will be able to always book all 52 reservations.
SCA receives a marginal profit of $100 for each passenger who books a reservation (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 model for this overbooking system that calculates the total profit for any given number of passengers that show up.
Hint: In developing the spreadsheet, separately calculate the total revenue and the total cost of overbooking. You can use the IF function to facilitate calculation of total cost of overbooking.
1.If 48 passengers show up, what would be the total profit for SCA?
2.If 52 passengers show up, what would be the total profit for SCA?
The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:
52 0.05
3. What type of random variable is the number of passengers that show up?
Select one:
Discrete random variable
Continuous random variable
4.What is the likelihood that overbooking would happen?
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.
5.What is the average profit with this overbooking strategy?
6.What is the standard deviation of profit with this overbooking strategy?
7.What is the likelihood that SCA would be able to earn a profit of $5100 or more?
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 when there is any customer overbooked, the TOTAL cost of overbooking 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: When generating numbers for total cost of overbooking, don’t forget the IF function for checking if the airline is overbooked, and if that was the case, generate the random number!)
8.
What type of random variable is the total cost of overbooking?
Select one:
Discrete random variable
Continuous random variable
9.What would be the average profit when uncertainty in overbooking cost is incorporated?
10.What is the average profit whenever overbooking does happen?
11.
The likelihood 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 reserved a ticket did not show up on the flight reserved. Which of the following best describes a probability distribution for the number of passengers that would show up in the considered overbooking strategy (i.e. reserving 52 seats on a flight with 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
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.
12.
What type of random variable is the profit margin per passenger?
Select one:
Discrete random variable
Continuous random variable
13.
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
Passengers Showing Up Probability 48 0.05 49 0.25 50 0.50 51 0.1552 0.05
3. What type of random variable is the number of passengers that show up?
Select one:
Discrete random variable
Continuous random variable
4.What is the likelihood that overbooking would happen?
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.
5.What is the average profit with this overbooking strategy?
6.What is the standard deviation of profit with this overbooking strategy?
7.What is the likelihood that SCA would be able to earn a profit of $5100 or more?
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 when there is any customer overbooked, the TOTAL cost of overbooking 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: When generating numbers for total cost of overbooking, don’t forget the IF function for checking if the airline is overbooked, and if that was the case, generate the random number!)
8.
What type of random variable is the total cost of overbooking?
Select one:
Discrete random variable
Continuous random variable
9.What would be the average profit when uncertainty in overbooking cost is incorporated?
10.What is the average profit whenever overbooking does happen?
11.
The likelihood 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 reserved a ticket did not show up on the flight reserved. Which of the following best describes a probability distribution for the number of passengers that would show up in the considered overbooking strategy (i.e. reserving 52 seats on a flight with 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
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.
12.
What type of random variable is the profit margin per passenger?
Select one:
Discrete random variable
Continuous random variable
13.
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
Explanation / Answer
3) No of passengers is a continuous variable.
4) Overbooking would appen with a probability 0f = 0.15+0.05 =0.20
P(X=51)+P(X=52)
Refer the dropbox link for Q5,Q6,Q7
https://www.dropbox.com/s/fkedb8o2i9j9ksw/Book2.xlsx?dl=0
8) Total cost of overbooking is a continuous variable ( Normal distribution will have continuous variable)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.