Question

Problem 12-15 (Algorithmic) Strassel Investors buys real estate, develops it, and resells it for a profit. A new property is available, and Bud Strassel, the president and owner of Strassel Investors, believes if he purchases and develops this property it can then be sold for $160000. The current property owner has asked for bids and stated that the property will be sold for the highest bid in excess of $100000. Two competitors will be submitting bids for the property. Strassel does not know what the competitors will bid, but he assumes for planning purposes that the amount bid by each competitor will be uniformly distributed between $100000 and $150000. a. Develop a worksheet that can be used to simulate the bids made by the two competitors. Strassel is considering a bid of $130000 for the property. Using a simulation of 1000 trials, what is the estimate of the probability Strassel will be able to obtain the property using a bid of $130000? Round your answer to 1 decimal place. Enter your answer as a percent. 0.645 X % b. How much does Strassel need to bid to be assured of obtaining the property? $ 149,988. x What is the profit associated with this bid? $ 10,011 x c. Use the simulation model to compute the profit for each trial of the simulation run. With maximization of profit as Strassel's objective, use simulation to evaluate Strassel's bid alternatives of $130000, $140000, or $150000. What is the recommended bid, and what is the expected profit? A bid of $140000 results in the largest mean profit of $

Answer 1

a)

Spreadsheet model

E G H к А Strassel Investors Property Value = 170,000 Strassel's bid = 120,000 15.3% Competitor's bid range Lower = Upper = Probability of winning = 100,000 150,000 Expected Profit = Standard deviation of profit = 7,660 18,011 Simulation Trial Win Profit Cell Formula Copy to 0 1 2 B11:C5010 D11:05010 E11:E5010 lelwl 50,000 14 Competitive Competitive Max of two bids Bid 1 Bid 2 123,919 130,430 130,430 108,689 144,232 144,232 108,556 110,776 110,776 120,898 109,608 120,898 126,705 107,597 126,705 121,539 103,206 121,539 120,435 101,877 120,435 120,987 134,653 134,653 132,790 121,392 132,790 148,388 131,325 148,388 123,594 115,540 123,594 B11 =SC$6+RAND()*(SCS7-SCS6) 011 =MAX(B11:011) E11 =IF(D11<SF$3,1,0) F11 =($C$3-$F$3) *E11 F5 =AVERAGE(E11:E5010) =AVERAGE(F11:F5010) F8 =STDEV(F11:F5010) F11:F5010 0 0 0 0 19 9 10 11 0 0 21

Probability = 15.3 %

b) To be assured of obtaining the probablity Strassel needs to bid 1. = 150,ooo poofit associated with this bid = $20,000

c) using the simulation model, change bid value in cell Iz to 145000 and mean profit the note strassel the

@ 145000 fx о 4 F3 А в Strassel Investors с E F G H K Property Value = 170,000 Strassel's bid = 145,000 Competitor's bid range Lower = Upper = Probability of winning = 100,000 150,000 Expected Profit = Standard deviation of profit = 20,460 9,639 Simulation Trial Win Profit Cell Formula Copy to Competitive Competitive Max of two bids Bid 1 Bid 2 108,335 120,632 120,632 105,896 100,401 105,896 125,345 139,506 139,506 109,560 117,817 117,817 124,360 116,001 124,360 127,820 140,585 140,585 120,812 134,300 134,300 116,386 132,911 132,911 119,728 102,666 119,728 105,270 134,838 134,838 142,594 141,238 142,594 B11:C5010 D11:05010 E11:E5010 F11:F5010 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 B11 =$C$6+RAND()*($C$7-$C$6) D11 =MAX(B11:011) E11 =IF(D11<$F$3,1,0) F11 1=($C$3-$F$3) *E11 F5 =AVERAGE(E11:E5010) F7 =AVERAGE(F11:F5010) F8 ESTDEV(F11:F5010)

Profit associated with stoassel bid value of $120000 = 7,660 lois determined in pastaj profit associated with Strassel bid value o $145000 = 20,460 (as determined above profit associated with strassel bid value of $120000 = 20,000 =(170,000 -150,000) A bid of $145000 results in the largest mean profit of $20,460