Analyze the simulationmodel, with 50trials, for Koehler Vision Associates(KVA) with the following assumptions. Assume that the no show rate is uniform, between10% and20% of prospective patients fail to show up or cancel their exam at the last minute. A $250 charge is applied as a deposit for the actual procedure. Patients that do not show up are refunded the charged fee less a $25 processing fee. KVA can handle 125 patients per week and is considering overbooking its appointments to reduce the lost revenue associated with cancellations. However, any patient that is overbooked may spread unfavorable comments about the company; thus, the overbooking cost is estimated to be $125. Determine the averages for the net profit(revenue less overbookingcosts) and number overbooked for taking appointments between 130 and 150 patients in increments of 2. What would yourecommend?

Use excel with similar format like the attachment.

A B C D E F G H I K L M N P R S T 1 Hotel Overbooking Model Overbooked customers Net Revenue Average 0.00 0.00 0.32 1.74 4.16 6.22 7.32 Average $34,501.20 $34,963.20 $35,138.40 $35,489.00 $35,212.80 $34,864.20 $34,836.20 0 300 305 310 315 320 325 330 $35,160 300 305 310 315 320 325 330 6 Data 1 0 0 0 2 15 18 1 $34,920.00 $34,920.00 $35,640.00 $36,000.00 $35,880.00 $35,520.00 $35,900.00 2 0 0 0 0 8 13 6 2 $35,040.00 $35,280.00 $35,520.00 $35,880.00 $35,640.00 $35,900.00 $35,800.00 Rooms available 300 3 0 0 1 0 0 7 0 3 $34,920.00 $35,520.00 $35,520.00 $35,880.00 $34,700.00 $34,900.00 $33,900.00 Price $120 4 0 0 0 3 6 0 4 $33,840.00 $34,800.00 $35,900.00 $34,800.00 $35,200.00 $35,400.00 $35,760.00 Overbooking cost $100 0 0 0 0 0 13 5 $34,440.00 $34,680.00 $35,160.00 $35,900.00 $35,200.00 $35,800.00 $34,080.00 10 6 0 0 0 3 11 1 13 6 $34,320.00 $34,680.00 $34,560.00 $35,160.00 $35,300.00 $34,900.00 $34,200.00 11 Model 7 $33,840.00 $35,280.00 $34,920.00 $34,900.00 $35,800.00 $35,900.00 $35,600.00 12 8 $34,080.00 $34,440.00 $34,800.00 $36,000.00 $34,320.00 $34,400.00 $34,300.00 13 Reservation limit 310 9 $34,440.00 $34,800.00 $33,960.00 $35,500.00 $35,520.00 $34,000.00 $34,100.00 14 Customer demand 305 10 $34,800.00 $35,160.00 $34,680.00 $35,760.00 $35,640.00 $34,400.00 $34,400.00 15 Reservations made 305 11 $34,800.00 $35,280.00 $34,320.00 $35,760.00 $35,640.00 $33,240.00 $32,760.00 16 Cancellations 12 anodao 12 $34,200.00 $35,520.00 $34,800.00 $32,760.00 $35,400.00 $34,800.00 $35,100.00 17 Customer arrivals 293 19 13 $34,560.00 $35,280.00 $35,760.00 $35,400.00 $34,200.00 $34,800.00 $35,700.00 18 19 Overbooked customers 0 15 $34,560.00 $34,920.00 $34,920.00 $35,040.00 $34,920.00 $35,600.00 $34,900.00 20 Net revenue $35,160 16 ooo - wooo 14 $34,680.00 $35,040.00 $35,900.00 $35,600.00 $34,700.00 $35,700.00 $33,960.00 18 16 $34,560.00 $34,440.00 $35,640.00 $35,640.00 $34,600.00 $35,760.00 $34,200.00