Business Analytics: Simulation of Tire, Airline, and Bidding Scenarios

This assignment solution addresses three business simulation problems. The first problem analyzes Grear Tire Company's tire promotion, calculating the expected cost of the promotion, the probability of large refunds, and the optimal mileage for the promotion claim. The solution utilizes Excel simulations to model tire life and determine the probabilities. The second problem focuses on South Central Airlines and its overbooking strategy, comparing the profitability of overbooking versus not overbooking, and assessing the probability of lower profits with overbooking. The solution employs probability distributions and calculations to evaluate different overbooking scenarios. The third problem involves a bidding scenario, estimating the probability of winning a bid at different bid amounts using COUNTIF functions and analyzing the impact of bidding strategies on the probability of winning. The solution demonstrates how simulation models can be used to make informed business decisions.

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.