Simulation

Dr. Sarah Benson is an ophthalmologist who, in addition to prescribing glasses and contact lenses, performs optical laser surgery to correct nearsightedness. This surgery is fairly easy and inexpensive to perform. Thus, it represents a potential gold mine for her practice. To inform the public about this procedure, Dr. Benson 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. Benson cancels the meeting if two or fewer people have made reservations. Using data from the previous year, Dr. Benson determined that the distribution of reservations is as follows:

# 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 data from the past year, Dr. Benson determined that each person who attends an information session has a 0.25 probability of electing to have the surgery. Of those who do not, most cite the $2,000 cost of the procedure as their major concern. Develop a simulation model in EXCEL and use it to answer the following questions.

1. On average, how much revenue does Dr. Benson's practice in laser surgery generate each week? (Use 500 replications.)
2. On average, how much revenue would the laser surgery generate each week if Dr. Benson did not cancel sessions with two or fewer reservations?
3. Dr. Benson believes that 40% of the people attending the information sessions would have the surgery if she reduced the price to $1,500. Under this scenario, how much revenue could Dr. Benson expect to realize per week from laser surgery?

----------
Guidelines for the simulation problem

Please analyze the case and write a report addressing the issues raised in the case problems. Please use:

A Microsoft EXCEL workbook containing all necessary worksheets (containing the model, results and any other sheet used).

A Microsoft WORD file containing the case analysis report. The report should be typed with double spacing and should consist of:

?A description of all elements of the model, including the definition of the decision variables, objective function and the constraints. The target cell is the objective function and it is the green cell. The changing cell is the decision variable and it is the yellow cell. The constraint cell is the cell representing the LHS formulae in the constraints and it is the blue cell.
?A detailed discussion of the case with answers to the questions posed in the case. You need not limit the discussion to the issues raised in the case.
?Appendix containing any necessary Tables, Graphs etc. (no page limit).

? For the Simulation problem use Excel Data Table. It is a good idea to change the calculation feature in Excel to Manual or Automatic except tables (Tools/Option/Calculation) when using Data Table. Otherwise, Excel will recalculate the entire Data Table each time you make any change in the spreadsheet. For the same reason, it is a good idea to set up each simulation model on a separate Excel file, rather than on different sheets of the same file. If you change the calculation feature, remember that Excel will recalculate values only when you press the F9 key. Also, the Data Table will initially show the same result value for all applications. You need to press F9 to get the final values.