DrCatscan is an ophthalmologist who, in addition to prescribing glasses and contact lenses, performs laser surgery to correct myopia. This laser surgery is fairly easy and inexpensive to perform.
To inform the public about this procedure DrCatscan advertises in the local paper and holds information sessions in her office one night a week at which she shows a videotape about the procedure and answers any questions potential patients might have.
The room where these meetings are held can seat 10 people, and reservations are required. The number of people attending each session varies from week to week. DrCatscan cancels the meeting if 2 or fewer people have made reservations.
Using data from the previous year DrCatscan determined that reservations follow this pattern:
|
Number of reservations
|
0
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
|
Probability
|
0.02
|
0.05
|
0.08
|
0.16
|
0.26
|
0.18
|
0.11
|
0.07
|
0.05
|
0.01
|
0.01
|
Using the data from last year DrCatscan determined that 25% of the people who attended information sessions elected to have the surgery performed. Of those who do not, most cite the cost of the procedure ($2,000 per eye, $4,000 in total as almost everyone has both eyes done) as their major concern. The surgery is regarded as cosmetic so that the cost is not covered by Medicare or private hospital insurance funds.
DrCatscan has now hired you as a consultant to analyse her financial returns from this surgery. In particular, she would like answers to the following questions, which you are going to answer by building an Excel model to simulate 20 weeks of the practice. Random numbers must be generated in Excel and used with the VLOOKUP command to determine the number of reservations,0 and there must be no data in the model itself. The same set of random numbers should be used for all three parts. An IF statement is required for part (a) to determine attendance each week, given cancellation of meetings.
(a) On average, how much revenue does DrCatscan's practice in laser surgery earn each week? If your simulation shows a fractional number of people electing surgery use such fractional values in determining revenue. Paste your model results into Word including a copy of formulas with row and column headings.
(b) Adjust your model to determine on average, how much revenue would be generated each week if DrCatscan did not cancel sessions with 2 or fewer reservations? Paste results into Word.
(c) DrCatscan believes that 35% of people attending the information sessions would have the surgery if she reduced the price to $1,500 per eye or $3,000 in total. Under this scenario how much revenue per week could DrCatscan expect from laser surgery? Modify your Excel model to answer this and paste results into Word.
(d) Write a brief report with your recommendations to DrCatscan on the most appropriate strategy.