150k views
5 votes
A government agency is putting a large project out for low bid. Bids are expected from ten contractors and will have a normal distribution with a mean of $3.3 million and a standard deviation of $0.27 million. Devise and implement a sampling experiment for estimating the distribution of the minimum bid and the expected value of the minimum bid. C Place "Mean" and "Std Dev" in column A in rows 1 and 2, respectively, and place their corresponding values in column B. Place the column headers "Bid 1", "Bid 2", and so on out to "Bid 10" in cells C1, D1, and so on out to L1, respectively. To generate random numbers for the first bid, in the cells in the "Bid 1" column, enter the formula =NORM.INV( $$$$) in the cells in column C below C1. To generate random numbers for the remaining bids, enter in the cells in columns D through L below row 1. To determine the winning bid for the bids in row 2, enter the column header "Winner" in cell M1, and enter the formula =MIN() in cell M2. Winners for other rows can be calculated using

User Skubski
by
9.1k points

1 Answer

1 vote

Final answer:

To estimate the minimum bid distribution and its expected value for a government contract with bids normally distributed, you would use spreadsheet software to simulate the bids and calculate the minimum using specified formulas.

Step-by-step explanation:

To estimate the distribution of the minimum bid and the expected value of the minimum bid when ten contractors are expected to submit bids with a normal distribution, a mean of $3.3 million, and a standard deviation of $0.27 million, you would follow the steps provided in a spreadsheet software.

  1. Enter "Mean" in cell A1 and "Std Dev" in cell A2. Then, put 3300000 in cell B1 and 270000 in cell B2.
  2. Label cells C1 through L1 with "Bid 1" through "Bid 10" to represent each contractor's bid.
  3. In cells C2 through C11 enter the formula =NORM.INV(RAND(), $B$1, $B$2). This formula generates random numbers based on the specified normal distribution for the first contractor's bid.
  4. Copy the formula from step 3 for the remaining columns D through L to simulate the bids for the other contractors.
  5. In cell M1, enter the header "Winner", and in cell M2, input the formula =MIN(C2:L2) to calculate the minimum bid, which would be the winning bid. Copy this formula down for each row of simulated bids.

To estimate the expected value of the minimum bid after running the simulation many times, you would take the average of the winning bids. This process involves statistical simulation and provides an illustration of how the minimum bid may be distributed and how the lowest bid can be expected on an average basis.

User Duderoot
by
8.4k points