Academic Writing

Statistics - Decision Support Tools Assignment QUESTION 3 Simulation 20 marks Dr Catscan 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 Dr Catscan 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. Dr Catscan cancels the meeting if 2 or fewer people have made reservations. Using data from the previous year Dr Catscan 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 Dr Catscan 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. Dr Catscan 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) 10 marks On average, how much revenue does Dr Catscan’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) 3 marks Adjust your model to determine on average, how much revenue would be generated each week if Dr Catscan did not cancel sessions with 2 or fewer reservations? Paste results into Word. (c) 3 marks Dr Catscan 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 Dr Catscan expect from laser surgery? Modify your Excel model to answer this and paste results into Word. (d) 4 marks Write a brief report with your recommendations to Dr Catscan on the most appropriate strategy.