Koehler Vision Associates (KVA) specializes in laser-assisted corrective eye surgery. Prospective patients make appointments for prescreening to determine their candidacy for the surgery; if they qualify, a $250 charge is applied as a deposit for the actual procedure. The weekly demand is 150, and about 12% of prospective patients fail to show up or cancel their test at the last minute. (Round no-shows/cancellations to the nearest integer using the ROUND function.) Patients that do not show up are refunded the prescreening 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 feel angry; thus, the overbooking cost is estimated to be $125. Develop a spreadsheet model for calculating net revenue.

Excel form layout

Use a data table to find the net revenue if 140, 141, 142, ...., 160 appointments are booked and the no-show/cancellation rate varies between 5% and 20% (in increments of 1%).

.

1. According to the data table you created for the Koehler Vision Associates model, what is the maximum net revenue based on these possible combinations of appointments booked and no-show/cancellation rate? (Do not include a dollar sign.) (Tip: Applying conditional formatting to your data table may make this question easier to answer.)

Koehler Vision Associates Data Procedure deposit Weekly demand No-show/cancellation rate (%) Cancellation fee Weekly capacity Overbooking cost $250 150 23% $25 125 $125 Model Appointments booked No-shows/cancellations Arrivals Patients seen Overbooked Revenue Procedure deposits Cancellation fees Costs Overbook costs Net Revenue