Question

Please, solve with Excel and provide step by step details and formulas.

A contractor has found out that her cost for a certain construction job is subject to random variation. She believes its actual value follows a continuous uniform distribution between $9000 and $11000. The cost to prepare her bid is $500. She is competing in a sealed bid competition with four other contractors. She believes that the bids submitted by the other contractors will vary triangularly with a minimum equal to her minimum cost, a most likely value equal to 1.3 times her mean cost, and a maximum equal to 2.5 times her mean cost and that their bids are independent of each other. She will win the competition if her bid is smaller than the bids of all four competitors. If she wins, her profit will be the difference between her bid and her cost minus the proposal preparation cost. If she loses her proposal preparatiorn cost is lost. Simulate 1000 bids in which her bid amount is $14,000 and determine the distribution of her profit. Include the histogram in your response. Also, determine the probability that she wins the competition and the probability that she loses money. Compare these results with bid amounts of $13000 and $15000.

Answer 1

Simulation using Excel only platform

Results

Total number of 'Wins' can be found using formula =SUM(L:L)

This is coming 152. This means that only 15.2% chance exists for making any profit. This is also clear from the histogram as 85% of the time there is no profit.

--------------------------------

To compare for Bid amount $13,000 and $15,000, change the cell $C$2 accordingly,

For $13,000

SUM(L:L) = 330 so, the probability of making a profit = 33%

For $15,000SUM(L:L) = 69 so, the probability of making a profit = 6.9%