Simulating Normal Random Variables

1. Simulating Normal Random Variables • Project: • Several options for bid: • Bid our signal • Develop several strategies • Develop stable bidding strategy

2. Simulating Normal Random Variables • Project: • Several options for bid: • Bid our signal • Develop several strategies • Develop stable bidding strategy

3. Simulating Normal Random Variables • Bidding the geologist’s signal is a bad idea • Leads to an average loss of approx. $24.7 million • What should be done?

4. Simulating Normal Random Variables • Create a simulation of 5000 auctions including all companies (use NORMINV) on a new worksheet • Find maximum error for each auction (represented by the variable C) • Find average of 5000 maximum values: E(C)

5. Simulating Normal Random Variables • Project:

6. Simulating Normal Random Variables Average amount overbid: $24.7 M

7. Simulating Normal Random Variables • The average of the 5000 maximum values is called the “Winner’s Curse” • Defined as the average amount the winner of the auction would overbid by bidding their signal

8. Simulating Normal Random Variables • “First Plan” for a reasonable profit is to subtract E(C) from the geologist’s signal • This ensures that the winning company would make a fair and reasonable profit.

9. Simulating Normal Random Variables • This plan is not ideal, since the goal of a company is to maximize their profit • To find a better strategy, find the gap between 1st place company and 2nd place company

10. Simulating Normal Random Variables • The monetary gap between 1st and 2nd is a wasted addition to the amount bid • The 1st place company must only outbid the 2nd place company by $0.01

11. Simulating Normal Random Variables • Find the value of the 2nd place company for each of the 5000 sample auctions (use LARGE function) • Once the 2nd place values are found, find the difference between 1st and 2nd (represented by variable B)

12. Simulating Normal Random Variables • For 5000 differences, find the average: E(B) • The average difference between 1st and 2nd place is called the “Winner’s Blessing” • Find 10 samples of E(C) and E(B) and average them

13. Simulating Normal Random Variables • Project:

14. Simulating Normal Random Variables • “Second Plan” ensures the winner of making a profit on average • Average profit is equal to E(B) • Strategy is not stable

15. Simulating Normal Random Variables • We left off at. . .

16. Simulating Normal Random Variables • “First Plan” • subtract E(C) from all signal errors • ensures that the winner makes the fair and reasonable profit • Average profit above the “fair and reasonable profit” is 0. We would like to increase this extra profit.

17. Simulating Normal Random Variables • “Second Plan” • subtract E(C) and E(B) from the signal error • ensures the winner of making a profit (above the “fair and reasonable” profit) on average • Average profit is equal to E(B)

18. Simulating Normal Random Variables • If all companies adjust their signals by the sum of the winner’s “curse” and “blessing” • every company has an equal chance to win auction (geologists are all equally expert) • everyone’s profit upon winning is roughly $6.3M (the winner’s blessing) • everyone’s expected value of the extra profit is approximately 0.330

19. Simulating Normal Random Variables • Suppose Company 1 decides to deviate from the “Second Plan” and adjust their signal differently: • If the adjustment < (E(C) + E(B)) • Company 1 could expect to win more often… • …but the extra profit will be less • If the adjustment > (E(C) + E(B)) • Company 1 could expect to win less often… • …but the extra profit will be more

20. Simulating Normal Random Variables • We can easily see how the probability of winning and mean profit (if we win) will change in relation to the difference between our company’s adjustment vs. the other company’s adjustment to their signal, but how does that affect the expected value of our extra profit? • Need a simulation of many (5,000) auctions and track: • who wins each auction • probability of our company winning • mean extra profit if Company 1 wins • expected value of extra profit

21. Simulating Normal Random Variables • Create 5000 simulated auctions as before. From every company’s error, subtract off E(C) and E(B)

22. Simulating Normal Random Variables • You will be eventually changing the adjustment (the amount subtracted from the signal) for Company 1, so it’s a good idea to cell-reference the adjustment. This way, when you want to change the adjustment, you only change one cell, and not an entire column.

23. For the winning company (Comp. 1 or other) we need to find the extra profit. The profit is the opposite of the error. the maximum error— this tells you the error of the maximum bid (i.e. how much under/ over the bid was from the proven value) =MAX(B4:S4) for example. if the max was the error from Company 1, then Comp. 1 won the auction. I will denote this by a “1” in this column if they won, and a “0” if another company won: =IF(T4=B4,1,0) where T4 is the maximum, B4 is Comp. 1. =IF(U4=0,-T4,"") =IF(U4=1,-T4,"") Simulating Normal Random Variables • For every row, find:

24. The number of 0’s out of the 5000 auctions, divided by the number of “other” companies: =COUNTIF(U4:U5003,“0")/(5000*18) The number of 1’s out of the 5000 auctions: =COUNTIF(U4:U5003,"1")/5000 Average the extra winnings in the “Other” column: =AVERAGE(V4:V5003) Average the extra winnings in the Company 1 column: =AVERAGE(V4:V5003) Exp. Value of adjustment = P(winning) . (mean extra profit) + P(losing) . 0 =T5007*T5006 =U5007*U5006 Simulating Normal Random Variables • Then calculate:

25. Simulating Normal Random Variables • This set-up was meant as a check-up to make sure your spreadsheet is set up correctly. If Company 1 and the other companies all make the same adjustment, • probabilities of winning should be relatively equal • mean profit should be approximately E(B) • expected values should be roughly equal If this is not true, FIX YOUR SPREADSHEET NOW!

26. Simulating Normal Random Variables • Now we’ll try maximizing the expected value of the extra profit (labeled on the sheet as “expected value of the adjustment”) • Select a range of adjustment values for Company 1 only, starting around an adjustment of 13 to an adjustment of around 31, by 2. (This allows me to try several adjustments much smaller than the other companies’ adj. of E(C) + E(B), and a few greater than it). Adjust your values as you see fit.

27. Simulating Normal Random Variables • Run the simulation several times to ensure accuracy • Average the results from several simulations • Record the results for adjustment (13, 15, 17, …, 31) and the expected values received

28. Simulating Normal Random Variables • Plot the points on a graph (x-axis will be adjustment values, y-axis will be expected value) • Fit a polynomial trend line of order 4 through the points • Estimate the maximum point (want a reasonable x-value) • This tells us how much we might want to subtract from estimate

29. Simulating Normal Random Variables • For example:

30. Simulating Normal Random Variables

31. Simulating Normal Random Variables • Use Differentiating.xls to maximize the expected value (set the derivative = 0):

32. Simulating Normal Random Variables • The peak is around x = 18.2 with y = 0.49 This means that a company should subtract approximately $18.2 million from their signal and receive an average of $0.49 million profit per auction

33. Simulating Normal Random Variables • The “Second Plan” is not stable • we found that is all other companies subtracted $28.3M from their signals, it was in our best interest (gave us the greatest expected value of our extra profit) is we adjusted our signal by $18.2M. • All other companies are finding the same thing, so if we all acted in this manner, everyone would adjust by $18.2M, which would leave a negative profit, since we’re adjusting by less than the winner’s curse

34. Simulating Normal Random Variables • This makes the “First Plan” (subtracting off only the winner’s curse) seem more profitable to all those concerned. • Let’s then inspect what would be our most profitable adjustment under this plan • Go through the same steps as last time, except now try adjustments from 17 to 37 (if we subtract off any less than 17, we are forcing our company to have negative profit)

35. Simulating Normal Random Variables • Summary of results thus far: • When all other companies subtracted off the curse and blessing ($28.3M), it was most beneficial to Company 1 to only subtract off $18.2M. • When all other companies subtracted off just the curse ($22.5M), it was in Company 1’s best interest to subtract off $28.6M.

36. Simulating Normal Random Variables • We see competing tendencies here: Second Plan First Plan

37. Simulating Normal Random Variables • Stable Equlibrium • A stable bidding strategy means that any deviation from the suggested bid would not be beneficial over a large number of trials • Stated another way, if we found a stable bidding strategy (an adjustment in which all companies will make to their signals), then any company that deviated from this strategy/adjustment will actually decrease their expected value of extra profit.

38. Nash Equilibrium • The optimal adjustment is called a “Nash Equilibrium” value • “Nash Equilibrium” is named after a mathematician named John Nash • The optimal situation for one person (company) may not be most beneficial to the whole group (all companies)

39. Simulating Normal Random Variables • Update your course file, Auction Equilibrium.xls • You will need 4 pieces of information to run this program: • # of companies • standard deviation • winner’s curse • winner’s blessing

40. Simulating Normal Random Variables • Put these values in cells B10:E10

41. Simulating Normal Random Variables • In cell E39, type the adjustment all other companies will make (initially this is curse + blessing) • Run the macro Optimize so that a value for Company 1’s adjustment appears in the yellow box in cell D39

42. Simulating Normal Random Variables • Average the values in cells D39 and E39 • Replace cell E39 value with the average that was just computed • Press F9 to recompute values

43. Simulating Normal Random Variables • Continue finding the average and replacing the E39 value until the D39 and E39 values are the same (our adjustment is equal to other company’s adjustment) • The optimal adjustment is called a “Nash Equilibrium” value • Take note of this value, then re-run the macro Optimize, and find a new equilibrium value

44. Simulating Normal Random Variables • Get about 10 of these values and average them together. This will be your company’s adjustment to their signal. *You may stop when the adjustment for Company 1 is within 3 decimal places of the other companies’ adjustment.

45. Simulating Normal Random Variables • What does this mean for our Company 1? • If each company reduces its signal by $24,385,000 then there would be no gain for any one company, if it deviated from this plan. • Specifically, we will reduce our signal of $121,600,00 by $24,385,000 and submit a bid of $97,215,000

46. Simulating Normal Random Variables • Going further: • What real world circumstances might make our auction model unreliable? • What informative plots could you create with your data on the probability of winning and the mean extra profit, if a company wins? • Can you find a way to use trend lines and Solver to eliminate the trial and error use of Auction Equilibrium.xls? • How can you show that the Nash equilibrium strategy is always safe, in the sense that it will have as high, or higher, an expected value of adjustment for Company 1 than for any other company? • Look for ways to display your results effectively. • What else can you think of to explore?