I am having a hard time completing this in Excel, please help! I need assistance on how to set this up correctly.

You are ordering bobbleheads to be given away at a home baseball game in a few weeks. Your objective is to order enough bobbleheads to have a 95% probability of not running out. That is, you want to have a 95% probability of having a bobblehead for every fan who wants one.

Attendance at the game is dependent on several factors, but the most important are weather and position in the race for the wildcard. The following table shows typical historical attendance figures based on the weather:

Weather Conditions

Probability of Weather Conditions

Attendance

Thunderstorms in forecast

.08

5,000 - 8,000

Rainy, but no thunderstorms

.22

10,000 - 16,000

Good weather

.70

17,000 - 21,000

In addition, if we are still in the race for the wildcard when the game occurs, we can expect attendance to be 10% higher than normal. We estimate that there is a 30% chance we will still be in the wildcard race when the bobblehead promotion game occurs.

Every fan who enters the stadium is offered a bobblehead. In past bobblehead promotions, 8% - 12% of the fans have declined the offer.

In a spreadsheet or in R, perform a Monte Carlo simulation with 10,000 trials to estimate the number of bobbleheads you should order to have a 95% probability of having enough bobbleheads to give one to every fan who wants one. Hint: First model the scenario so that you can generate a number of bobbleheads needed for one game. Then, simulate 10,000 trials, keeping track of how many bobbleheads are needed in each trial.

Then, use the PERCENTILE.INC function to calculate the 95thpercentile of the 10,000 trials. In R you can use the quantile() function: quantile(nums, 0.95).

QuestionPlease answer the following question directly on your spreadsheet, in text boxes, or directly in your R script in comment lines. Please put some thought into your answer. It should be several sentences long, at a minmum:

What advantages does this type of simulation analysis provide for the decision-maker?