From Excel Data Analysis - Modeling and Simulation Second Edition (by Hector Guerrero)

For Fr. Efias OLPS problem perform the following changes:

1. Introduce a 4th weather condition, Absolutely Miserable, where the number of alumni attending is a point estimate of only 750.

2. Perform all the financial calculations in a separate area below the others.

3. Add the scroll bar (range of 500900) and the option button associated with the new weather condition, such that the look of the spreadsheet is consistent.

4. What will the entry fee for the new weather condition have to be in order for the profit to equal that in Exhibit 7.7?

5. Find a different combination of Player odds that leads to the same Profit ($125,000) in Exhibit 7.10. f. Create a two-variable Data Table for cloudy weather, where the variables are Bet Value ($10 to $100 in $10 increments) and OTSD player odds (1080% in 10% increments).

File Home Insert Draw Page Layout Formulas Data Review View Developer 621 fi =IF(E21=0,0,$C$3*E21) G H Range SO....$50 1000...2000 2000...3500 3500...5000 This spreadsheet provides Fr. Efia with a convenient model for analyzing his Vegas Night at OLPS event. The scroll bars ("Sensitivity") allow the various parameters of the problem to be changed (Entry Fee, Bets, etc.). The corresponding values of Revenue are shown for each weather condition selected. 10%...50% 25% 50% 45%...65% B D E F 1 Brain Sensitivity 2 Entry Fee 3 $ 10 4 Attendance 5 Rain 1500 6 Cloudy 2500 7 Sun Shine 4500 8 Player Odds 9 OTSD 20% 10 WOD 35% 11 BT 55% 12 Bets 13 $ 50 14 15 Model Results/Calculations 16 Weather Attendance 17 18 Rainy C 19 20 21 Cloudy 2500 22 23 24 Sun Shine 25 $25...$100 Author: Author: -(1-C9)*821-C13 =IF (E21=0,0,$C$3*E21) Revenue of Events Entry Fee OTSO WOD Author: =E21*C9*C13 BT TOTAL $25,000 Costs=> $100,000 $25,000 $81,250 $43,750 $56,250 $68,750 Profit => $262,500 $137,500 $125,000 26 File Home Insert Draw Page Layout Formulas Data Review View Developer 621 fi =IF(E21=0,0,$C$3*E21) G H Range SO....$50 1000...2000 2000...3500 3500...5000 This spreadsheet provides Fr. Efia with a convenient model for analyzing his Vegas Night at OLPS event. The scroll bars ("Sensitivity") allow the various parameters of the problem to be changed (Entry Fee, Bets, etc.). The corresponding values of Revenue are shown for each weather condition selected. 10%...50% 25% 50% 45%...65% B D E F 1 Brain Sensitivity 2 Entry Fee 3 $ 10 4 Attendance 5 Rain 1500 6 Cloudy 2500 7 Sun Shine 4500 8 Player Odds 9 OTSD 20% 10 WOD 35% 11 BT 55% 12 Bets 13 $ 50 14 15 Model Results/Calculations 16 Weather Attendance 17 18 Rainy C 19 20 21 Cloudy 2500 22 23 24 Sun Shine 25 $25...$100 Author: Author: -(1-C9)*821-C13 =IF (E21=0,0,$C$3*E21) Revenue of Events Entry Fee OTSO WOD Author: =E21*C9*C13 BT TOTAL $25,000 Costs=> $100,000 $25,000 $81,250 $43,750 $56,250 $68,750 Profit => $262,500 $137,500 $125,000 26