In: Statistics and Probability
The Rogers Construction Company is trying to decide whether to make a bid on a project against 4 competitors. The lowest bid will win the contract and be paid the amount they bid. It believes it will cost the company £10,000 to complete the project (if it wins the contract) and £350 to prepare the bid. Based on historical data, Rogers believes each competitor’s bid has a normal distribution with mean £15,000 and standard deviation £1,500.
a) Set up a simulation model in excel to help the company make the decision of how much to bid.
b) Discuss on the obtained results and make suggestions. For example, what if the competitor’s bid has different distributions?
SOLUTION:
From given data,
The lowest bid will win the contract and be paid the amount they bid. It believes it will cost the company £10,000 to complete the project (if it wins the contract) and £350 to prepare the bid. Based on historical data, Rogers believes each competitor’s bid has a normal distribution with mean £15,000 and standard deviation £1,500.
standard deviation = £1,500
mean = £15,000
a) Set up a simulation model in excel to help the company make the decision of how much to bid.
Simulation model for 500 trials is following:
EXCEL FORMULAS:
B3 =NORMINV(RAND(),15000,1500) copy to B3:E502
F3 =MIN(B3:E3) copy to F3:F502
I3 =AVERAGE(F3:F502)
I7 =SUM(I3:I6)
b) Discuss on the obtained results and make suggestions. For example, what if the competitor’s bid has different distributions?
Recommended bid amount = $ 13,407
This decision will result in estimated profit of $ 3,057
If the competitor's bid has different distributions,
the simulation model will be modified to incorporate distribution of each competitor's bid.