Simulation Model for Tire Life and Cost Analysis for Promotion

Q3)
The simulation model in excel and calculation of the answers is given below along with formulas:
Formula used for simulation of tire life in Excel range B2:B502 =NORM.INV(RAND(),36500,5000) Note:
Once this formula is entered in all the cells in range B2:B502, copy these cells and paste as values, so
that Goal seek function can work for part
Q 3
T i re T i re l i f e
1 31365.02
2 29868.81 P roba bility tha t a tire fa ils to re a c h a life of 30000 m ile s = 0.086
3 25751.53
4 37003.05 F or tire s w ith life le s s tha n 30000 m ile s , a ve ra ge num be r of m ile s s hort of 30000 m ile s = 2650.4756
5 24615.43
6 38895.61 a . F o r e a c h tire so ld , w ha t is the e xp e c te d c o st o f the p ro m o tio n = 2.279409
7 41258.66
8 31115.54
9 39243.49
10 35881 b . W ha t is the p ro b a b ility tha t G re a r w ill re fund m o re tha n $ 5 0 fo r a tire ?
11 32500.01 P ro b a b ility tha t G re a r w ill re fund m o re tha n $ 5 0 fo r a tire is e q ua l to the p ro b a b ility
12 37320.84 o f a tire ha ving a life le ss tha n 2 5 0 0 0 m ile s = 0.014
13 38101.83
14 42034.93
15 44825.57
16 32366.08
17 40321.49
18 37331.09
19 39926.4
20 38313.02
21 36987.94 c . W ha t m ile a ge sho uld G re a r se t the p ro m o tio n c la im if it w a nts the e xp e c te d c o st to b e $ 2 = 29907.972
22 38283.82 P ro b a b i l i ty th a t a ti re f a i l s to re a ch a l i f e o f 30000 m i l e s = 0.0798403
23 39790.62 F o r ti re s w i th l i f e l e s s th a n 30000, a v e ra g e n u m b e r o f m i l e s s h o rt o f 30000 m i l e s = 2752.4372
24 33183.45 F o r e a ch ti re s o l d , e x p e cte d co s t p ro m o ti o n ( $ p e r l i tre ) = 2.1975547