1 Problem 3 Simulation (35 points) 2 Auto dealer Ais attending an auction which seis xury cars 3 His objective is to purchase a used luxury car, perform a necessary service on and sell it, hoping to make some profit 4 in the process. The bidding is going on for a one year old fusire luxury cat, which was used as a rental 5 Autodealer estimates that the competing bid will follow normal distribution with a mean of $28,000 and a standard deviation of 54000 & Dealer A also estimates that the selling price for such a car wil depend on the economy 7 In case of a rocasion, the estimated selling price is $38.000. On the other hand, if the economy picks up, the selling price is expected to go up to $46,000 Bit is estimated that the likelihood of economy remaining in a recension is approximately 50% 9 Dealer A also collected some data on additional costs (including taxes, transportation and service) to get similar 10 second hand cars ready for sale 11 12 Sample of additional cost taxes transportation service) for similar cars 13 $3,000 14 2 $4,000 15 3 $3,400 16 4 $4,400 17 5 $5,800 18 B $3,600 19 7 $3,000 20 8 $6,000 21 22 Suggested is the setup for modeling cells. Just propose the functions which should be used. No need to do simulation: 21 ATTACHED ARE SIMULATION RESULTS. USE THOSE RESULTS TO ANSWER QUESTIONS a) and b): 24 25 bid by A competing bid 26 additional cost selling price 40 27 profit 28 29 The dealer A is considering the bid of $36,000. Bid results are shown in the worksheet Parta) simulation report. 30 31 is $36000 a good bid? What is the probability of loosing money? 32 21 22 Suggested is the setup for modeling cells. Just propose the functions which should be used. No need to do simulation: 23 ATTACHED ARE SIMULATION RESULTS. USE THOSE RESULTS TO ANSWER QUESTIONS a) and b): 24 25 bid by A competing bld 26 additional cost selling price 97 profit 28 20 ) The dealer is considering the bid of $30,000. Bid results are shown in the worksheet Parta) simulation report, 30 31 536000 a good bid? What is the probability of loosing money? 32 33 14 b) The results of the sensitivity analysis with the following bld values: 35 28,000 30,000 32,000 34,000 36,000 38 are shown in worksheets Part b) OR bid by AB 25 and Part b) bid by A B 25. 37 38 What can you conclude from the sensitivity analysis results ? Advice on bidding amount. Justify your answer. profit 7.000 36.000 SON M Hamm Man Shd Devon Varan V 6000 $7,000.00 37.16 1421 195 25,433,467 Skene 000 ! Medan Had tax 0.0021 11821 SCO $6000 IN 7,000.00 55 100 B. 14.000 2.0 H. H.000 Right Right 24 Per profit 36.800 $7.900 Perence Value SU 10 DO DHDHy*8 0.4 36,000.00 36,000.00 $6,000.00 $5.000.00 $4.000.00 - 600,00 $ 0.00 $6,000.00 $6,400.00 $6,000.00 $2.000.00 12,000.00 $7,000.00 23 SN 10N 204 25 SON EN GON 909 55 92.5% 99% to 000 . 16.000 12.000 40 41 profit tapused by effect on Open Upper Change in Kask Name 1 ing price 2 dos 3 competing tid Lower $100.20 $940.00 $37240 $41 35,649.60 52.095.00 SL0440 seco 00 Input High Input Low 1.034.40 65.000 $1,000 2011 11.10 at 55.000 Problema Part Simulation report Part N37 Jx D F G B H P K L M N 0 + Outputt profit Input: bid by A 3 Type of Analysis: Table: 28000 to 36000 Number of Stepst 5 Base Value 35000 0 Cumulative Distributions of profit B27 for Changing Values of bid by A B25 12 1 Table: 20000.00 Tate: 00.00 0 1 2 33 4 04 5 5 Aurayev Tabe: 3200000 -table:M000.00 -Table:M000.00 -10000 000 00006 15.00 inputs 6 Analysis 7 bid by A B25 Table: 20000.00 9 bid by A B25 Table: 30000.00 -9 bid by A B25 Table: 32000.00 Obid by A B25 Tablo: 34000.00 -1 bid by A B25 Table: 30000.00 -2 3 34 15 36 Value Mean 20000 43726 30000 4676.34 32000 4020 8 34000 2572.12 38000 743.74 Output Min Max 5% 95% Stoly 0 150000 14600 5077.630561 0 13000 0 126005273.130834 -2000 11000-2000 11000 5006 319375 4000 9000 4000 9000 4994.486270 8000 7000 8000 7000 5062.10799 + Mean of profit B27 vs bid by A B25 Value 14 COO 15 16 17 18 Map 2000 100 DO YO COOK 100D INDO bid by AV Cumulative Distributions of profit B27 for Changing Values of bid by A 825 Tablet 200.00 44 06 tibles Table 3.00 Tel. le 4 07 9889898 ARB 1 Problem 3 Simulation (35 points) 2 Auto dealer Ais attending an auction which seis xury cars 3 His objective is to purchase a used luxury car, perform a necessary service on and sell it, hoping to make some profit 4 in the process. The bidding is going on for a one year old fusire luxury cat, which was used as a rental 5 Autodealer estimates that the competing bid will follow normal distribution with a mean of $28,000 and a standard deviation of 54000 & Dealer A also estimates that the selling price for such a car wil depend on the economy 7 In case of a rocasion, the estimated selling price is $38.000. On the other hand, if the economy picks up, the selling price is expected to go up to $46,000 Bit is estimated that the likelihood of economy remaining in a recension is approximately 50% 9 Dealer A also collected some data on additional costs (including taxes, transportation and service) to get similar 10 second hand cars ready for sale 11 12 Sample of additional cost taxes transportation service) for similar cars 13 $3,000 14 2 $4,000 15 3 $3,400 16 4 $4,400 17 5 $5,800 18 B $3,600 19 7 $3,000 20 8 $6,000 21 22 Suggested is the setup for modeling cells. Just propose the functions which should be used. No need to do simulation: 21 ATTACHED ARE SIMULATION RESULTS. USE THOSE RESULTS TO ANSWER QUESTIONS a) and b): 24 25 bid by A competing bid 26 additional cost selling price 40 27 profit 28 29 The dealer A is considering the bid of $36,000. Bid results are shown in the worksheet Parta) simulation report. 30 31 is $36000 a good bid? What is the probability of loosing money? 32 21 22 Suggested is the setup for modeling cells. Just propose the functions which should be used. No need to do simulation: 23 ATTACHED ARE SIMULATION RESULTS. USE THOSE RESULTS TO ANSWER QUESTIONS a) and b): 24 25 bid by A competing bld 26 additional cost selling price 97 profit 28 20 ) The dealer is considering the bid of $30,000. Bid results are shown in the worksheet Parta) simulation report, 30 31 536000 a good bid? What is the probability of loosing money? 32 33 14 b) The results of the sensitivity analysis with the following bld values: 35 28,000 30,000 32,000 34,000 36,000 38 are shown in worksheets Part b) OR bid by AB 25 and Part b) bid by A B 25. 37 38 What can you conclude from the sensitivity analysis results ? Advice on bidding amount. Justify your answer. profit 7.000 36.000 SON M Hamm Man Shd Devon Varan V 6000 $7,000.00 37.16 1421 195 25,433,467 Skene 000 ! Medan Had tax 0.0021 11821 SCO $6000 IN 7,000.00 55 100 B. 14.000 2.0 H. H.000 Right Right 24 Per profit 36.800 $7.900 Perence Value SU 10 DO DHDHy*8 0.4 36,000.00 36,000.00 $6,000.00 $5.000.00 $4.000.00 - 600,00 $ 0.00 $6,000.00 $6,400.00 $6,000.00 $2.000.00 12,000.00 $7,000.00 23 SN 10N 204 25 SON EN GON 909 55 92.5% 99% to 000 . 16.000 12.000 40 41 profit tapused by effect on Open Upper Change in Kask Name 1 ing price 2 dos 3 competing tid Lower $100.20 $940.00 $37240 $41 35,649.60 52.095.00 SL0440 seco 00 Input High Input Low 1.034.40 65.000 $1,000 2011 11.10 at 55.000 Problema Part Simulation report Part N37 Jx D F G B H P K L M N 0 + Outputt profit Input: bid by A 3 Type of Analysis: Table: 28000 to 36000 Number of Stepst 5 Base Value 35000 0 Cumulative Distributions of profit B27 for Changing Values of bid by A B25 12 1 Table: 20000.00 Tate: 00.00 0 1 2 33 4 04 5 5 Aurayev Tabe: 3200000 -table:M000.00 -Table:M000.00 -10000 000 00006 15.00 inputs 6 Analysis 7 bid by A B25 Table: 20000.00 9 bid by A B25 Table: 30000.00 -9 bid by A B25 Table: 32000.00 Obid by A B25 Tablo: 34000.00 -1 bid by A B25 Table: 30000.00 -2 3 34 15 36 Value Mean 20000 43726 30000 4676.34 32000 4020 8 34000 2572.12 38000 743.74 Output Min Max 5% 95% Stoly 0 150000 14600 5077.630561 0 13000 0 126005273.130834 -2000 11000-2000 11000 5006 319375 4000 9000 4000 9000 4994.486270 8000 7000 8000 7000 5062.10799 + Mean of profit B27 vs bid by A B25 Value 14 COO 15 16 17 18 Map 2000 100 DO YO COOK 100D INDO bid by AV Cumulative Distributions of profit B27 for Changing Values of bid by A 825 Tablet 200.00 44 06 tibles Table 3.00 Tel. le 4 07 9889898 ARB