Please develop a simulation model for one of the replay opti

Please develop a simulation model for one of the replay options based upon the problem description below. Please screen capture the excel doc and show or write out the formulas for the excel cells which need to be generated. I will later use the simulation model and adapt it to the other replay option and compare, which will then be followed up with the Crystal Ball model simulation. I just need the simulation model to start the process based upon one of the replay options.

Harbor Dunes Golf Course was recently honored as one of the top public golf courses in

South Carolina. The course, situated on land that was once a rice plantation, offers some of the best views of saltwater marshes available in the Carolinas. Harbor Dunes targets the upper end of the golf market and, in the peak spring golfing season, charges green fees of $160 per person and golf cart fees of $20 per person.

Harbor Dunes takes reservations for tee times for groups of four players (foursomes) starting at 7:30 each morning. Foursomes start at the same time on both the front nine and the back nine of the course, with a new group teeing off every nine minutes. The process continues, with new foursomes starting play on both the front and back nine at noon. To enable all players to complete 18 holes before darkness, the last two afternoon foursomes start their rounds at 1:21 P.M. Under this plan, Harbor Dunes can sell a maximum of 20 afternoon tee times.

Last year Harbor Dunes was able to sell every morning tee time available for every day of the spring golf season. The same result is anticipated for the coming year. Afternoon tee times, however, are generally more difficult to sell. An analysis of the sales data for last year enabled Harbor Dunes to develop the probability distribution of sales for the afternoon tee times as shown in Table 16.12. For the season, Harbor Dunes averaged selling approximately 14 of the 20 available afternoon tee times. The average income from afternoon green fees and cart fees has been $10,240. However, the average of six unused tee times per day resulted in lost revenue.

In an effort to increase the sale of afternoon tee times, Harbor Dunes is considering an idea popular at other golf courses. These courses offer foursomes that play in the morning the option to play another round of golf in the afternoon by paying a reduced fee for the afternoon round. Harbor Dunes is considering two replay options: (1) a green fee of $25 per player plus a cart fee of $20 per player; (2) a green fee of $50 per player plus a cart fee of $20 per player. For option 1, each foursome will generate additional revenues of $180; for option 2, each foursome will generate additional revenues of $280. The key in making a decision as to what option is best depends upon the number of groups that find the option attractive enough to take the replay offer. Working with a consultant who has expertise in statistics and the golf industry, Harbor Dunes developed probability distributions for the number of foursomes requesting a replay for each of the two options. These probability distributions are shown in Table 16.13.

In offering these replay options, Harbor Dunes’ first priority will be to sell full-price afternoon advance reservations. If the demand for replay tee times exceeds the number of afternoon tee times available, Harbor Dunes will post a notice that the course is full. In this case, any excess replay requests will not be accepted.

PROBABILITY DISTRIBUTION OFSALES FOR THEAFTERNOON TEE TIMES Number of Tee Times Sold Probability 0.01 0.04 0.06 0.08 0.10 0.11 0.12 0.15 0.10 0.09 0.07 0.05 0.02 9 10 12 13 14 15 16 17 18 19 20

Solution