Simulation Model for Tire Life and Cost Analysis for Promotion

This document explains the simulation model for tire life and cost analysis for promotion. It includes the formula used for simulation, probability of tire failure, average miles short of 30000 miles, expected cost of promotion for each tire sold, and the probability of refund over $50. It also provides information on the mileage G rear should set for promotion claim to achieve an expected cost of $2.

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 ire T i re l i f e
1 31365. 02
2 29868. 81 P r ob a bility tha t a tire fa ils to re a c h a life o f 30 00 0 m ile s = 0.086
3 25751. 53
4 37003. 05 F or tire s w ith life le s s th a n 3 0 0 0 0 m ile s , a v e ra g e n u m b e r o f m ile s s h o rt o f 30 0 0 0 m ile s = 2650. 4756
5 24615. 43
6 38895. 61 a . F o r ea c h tire so ld , w ha t is the e xp e c te d c o st o f the p ro mo tio n = 2.279409
7 41258. 66
8 31115. 54
9 39243. 49
10 35881 b . W ha t is the p ro b ab ility that 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 refund m o re tha n $ 5 0 fo r a tire is e q ua l to the p ro b ab ility
12 37320. 84 o f a tire ha ving a life le ss tha n 2 5 0 0 0 mile 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 mile 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 r o b a b i li ty t h a t a ti re f a i l s to re a ch a l i f e o f 30000 m il 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 r t o f 30000 m i l e s = 2752. 4372
24 33183. 45 F o r e a ch ti r e 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

Q12)
a)
S O U T H C E N T R A L A IR LIN E
P a sse nge
rs
S ho w ing
U p
p ro b a b ili
ty
R N l o w e r b o u n d
4 8 0 .0 5 0
F lig h t c a p a c ity = 50 4 9 0 .2 5 0.05
R e s e r v a tio n a c c e p te d = 52 5 0 0 .5 0.3
P r o fit p e r b o o k in g = $100 5 1 0 .1 5 0.8
L o s s p e r o v e r b o o k in g = $150 5 2 0 .0 5 0.95
A v e r a g e p r o fit p e r flig h t with o u t o v e r b o o k in g s tr a te g y = 5 0 0 0 W h e n a irlin e b o o k s 50 s e a ts
A v e r a g e p r o fit p e r flig h t with o v e r b o o k in g s tr a te g y = 5 1 6 1 W h e n a irlin e b o o k s 52 s e a ts
b)
Probability that the net profit with the overbooking strategy will be less than the net profit without
overbooking is obtained using formula =COUNTIF(F14:F513,"<="&F9)/COUNT(F14:F513)
Result of this formula is: 0.052
c) This simulation model can be used to evaluate other overbooking strategies, by changing the
number in cell C4 (reservations accepted)
For each overbooking strategy, note the additional profit per flight and then select the overbooking
strategy, which gives the highest additional profit

Q14)
a)
To estimate the probability of winning the bid.. We will use COUNTIF function to count the number of
maximum bid less than $750,000. The n we divide by the number of trials (1000) to estimate the
probability of winning.
The probability of winning with $750,000 is 62.8%
b) To estimate the probability of winning the bid. We will use COUNTIF function to count the number of
maximum bid less than $775,000 and $785,000. The n we divide by the number of trials (1000) to
estimate the probability of winning.
C) Since the contractor would like to bid such that the probability of wining the bid is about 0.80, the
contractor should bid $775,000. With a $775,000, the contractor will have an estimated probability of
75.1% chance of winning.