You are ordering bobbleheads to be given away at a home baseball game in a few w
ID: 3275975 • Letter: Y
Question
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, 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: Simulate 10,000 trials, keeping track of how many bobbleheads are needed in each trial. Then, use the PERCENTILE.INC function to calculate the 95th percentile of the 10,000 trials).
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
Explanation / Answer
Shown above is a part of the spreadsheet that was used to simulate 10000 Monte Carlo runs for the given question. The Monte Carlo Simulation Add-in can be downloaded from the internet and added to MS Excel, then it can be used.
In above, here are the formulas:
=rand() --- to generate a random number between 0 and 1
=IF(D3>0.3,3,IF(D3>0.08,2,1)) --- (Condition on a given day) - D3 contained a random no. between 0 and 1. This gives condition of the day as 1 (Thunderstorm), 2 (Rainy) or 3 (Good)
=IF(E3=3,17000+4000*F3,IF(E3=2,10000+6000*F3,5000+3000*F3)) --- (No. of ppl based on the condition) - F3 contained a random no. between 0 and 1. This gives the no. of ppl present before considering wild card.
=CEILING(G3*(1+0.1*H3),1) --- H3 = 1 means wildcard is still available, 0 means not available. This gives no. of ppl after wildcard consideration.
=88+F5*4 --- F5 is a random number. No. of ppl who would accept bobbleheads. (Percentage of attendees accepting bobbleheads)
=CEILING(I3*J3/100,1) --- No. of ppl accepting bobble heads
Condition Probability Attendance Randome mumber generated to find the condition on a given day Condition on a given day Random numbers No. of ppl based on the condition Wildcard (Yes=1, No=0) Attendance after wildcard consideration Percentage of attendees accepting bobbleheads No. of ppl accepting bobbleheads 95th percentile Thunderstorms (1) 0.08 5000 - 8000 0.156291969 2 0.8975366 15385.21967 1 16924 90.25574739 15275 19824 Rainy, no thunderstorms (2) 0.22 10000 - 16000 0.0957135 Good weather (3) 0.7 17000 - 21000 0.5639368Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.