The histogram of best (i.e., lowest) competing bids in Table B of Problem 11 mirrors closely a normal distribution with a mean of 60 and a standard deviation of 30. Create a spreadsheet modeled on the sample given to find the firm's optimal bid markup. In the sample spreadsheet, note that a bid at a 60 percent markup has a .5 chance of winning and implies an expected profit of (.5)(60) = 30.
a. First, experiment with other markups in your search for maximum expected profit.
b. Use your spreadsheet's optimizer to find the optimal markup.
c. Find the firm's optimal markups if the BCB distribution has a (less favorable) mean of 40 or if it has a (more favorable) mean of 80.
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
|
1
|
|
|
|
|
|
|
|
|
|
2
|
|
|
|
|
OPTIMAL STRATEGY WHEN BCB
|
|
3
|
|
|
|
|
IS NORMALLY DISTRIBUTED
|
|
4
|
|
|
|
|
|
|
|
|
|
5
|
|
Mean =
|
60
|
|
Markup
|
Pr(win)
|
E(Profit)
|
|
|
6
|
|
St Dev =
|
30
|
|
|
|
|
|
|
7
|
|
|
|
|
20
|
0.9088
|
18.176
|
|
|
8
|
|
|
|
|
40
|
0.7475
|
29.900
|
|
|
9
|
|
|
|
|
60
|
0.5000
|
30.000
|
|
|
10
|
|
|
|
|
80
|
0.2525
|
20.199
|
|
|
11
|
|
|
|
|
|
|
|
|
|
12
|
|
|
|
|
|
|
|
|
(Hint: Cells F7 to F10 should be computed utilizing the normal distri- bution function included with your spreadsheet. This function typi- cally takes the form:
Normal(value, mean, standard deviation).
Thus, you can simply change the value of the mean in cell C5 to 40 or 80 and reoptimize the problem.)