Please provide excel explination if possible!!! Thank you! A building contractor

Question

Please provide excel explination if possible!!! Thank you!

A building contractor is preparing a bid on a new construction project. Two other contractors will be submitting bids for this project. In this case, the lowest bid wins the contract. Based on past bidding practices, bids from the other contractors can be described by the following probability distributions:

Contractor

Probability Distribution of Bid

A

Uniform probability distribution between $600,000 and $800,000

B

Normal probability distribution with a mean bid of $700,000 and a standard deviation of $50,000

Suppose the building contractor submits a bid of $650,000, what is the probability that the contractor submits the lowest bid and wins the contract for the new construction project? Build a simulation model, in an Excel file to simulate 1000 trials of the contract bidding process

The building contractor is also considering bids of $625,000 and $615,000. Repeat the simulation process with bids of 625,000 and $615,000 as you did in part a. In each case what is the probability the building contractor will obtain the bid?

If the building contractor would like to bid such that the probability of winning the bid is about 0.80, what bid would you recommend?

Contractor

Probability Distribution of Bid

A

Uniform probability distribution between $600,000 and $800,000

B

Normal probability distribution with a mean bid of $700,000 and a standard deviation of $50,000

Explanation / Answer

X ~ Uniform distribution and Y ~ Normal distribution

Hence, Prob(given bid is lowest)

= P(X> given bid) * P(Y>given bid)

= (800000-650000)/(800000-600000) * ( 1 - normdist(650000,700000,50000) )

Here, normdist is a function in Excel to get CDF of normal distrution with given mean and standard deviation.

So, P (given bid is lowest)

= 0.75 * 0.841345 = 0.63

If given bid is 625000, then P (given bid is lowest)

= (800000-625000)/(800000-600000) * ( 1- normdist(625000,700000,50000) )

= 0.875 * 0.933193

= 0.82

If given bid is 615000, then P (given bid is lowest)

= (800000-615000)/(800000-600000) * ( 1- normdist(615000,700000,50000) )

= 0.925 * 0.955435

= 0.88

Now, if the desired P(bid being lowest) is 0.80, then bid should be at value of $627,364

For this, GoalSeek feature of excel can be used after setting formula for all the probabilities and setting the final probability cell value to 0.80 by chaging the value of cell for bid.

As per Chegg Policy, an excel data can't be posted here and hence a simulated data of 1000 points can't be displayed here.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.