Excel File View Insert Format Tools Data Window Help TOOM LINE B D Q 8 Sun Nov 27 8:11 PM AutoSave OFF MAY. CEDED ... Ch11 HW Template 212 Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Share Comments v 86 Cut Aria 10 AA = 207 V ap Wrap Text v General Currency 2 Currency 3 AutoSum Av OV [ Copy Fill Paste Format BIUV UVAv Merge & Center $ ~ % " Conditional Format Currency 4 Normal 2 Insert Delete Format Find & Analyze Clear v SOIT & Sensitivity Formatting as Table Filter Select Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No J27 4 X V D G H K M New Car Problem Statement - > > > > > > > > GM is trying to decide whether to introduce a new car model. The selling price for the car will be $32,000. Input Data All the $ amounts are in $thousands Statistics The fixed cost of developing the car is assumed to be uniformly distributed between $600 million and $1.1 The demand for the car is described by a normal distribution with a mean of 120,000 units and a standard Unit Selling Price $32 Sample Size The unit variable cost for the car is distributed as shown below. C) Mean Profit Fixed Cost (Uniform Distribution) Unit Variable Cost Standard Deviation #DIV/O Cost per Unit Probability Lower bound $600,000 Minimum Profit $17,000 0.15 Upper bound $1,100,000 $850,000 Probability Lower Limit Upper Limit Cost per Unit Maximum Profit $19,00 0.35 0.15 0.00 0.15 $17 Count of Positive Profits $20,000 0.30 Demand (Normal Distribution) 0.35 0.15 0.50 $19 Count of Profits >= 100 mil $22,000 0.20 Mean 120,000 0.30 0.50 0.80 $20 d) Probability of Positive Profit Standard Dev 30,000 0.20 0.80 1.00 $22 Probability of Profit >= 100 mil a) Simulate the profit with 1000 trials. Express all the $ amounts including profit in $thousands Recommendation? b) What are the mean and standard deviation of the profit from the simulation? c) GM is willing to introduce the car if there is at least 95% probability of making a profit AND at least 85% profit of at least $100 million. Compute these two probabilities and make a recommendation. 18 Simulation Trials (Selling price - Unit VC) Did - FC (1) (2) (3) (4) (5 (6) (7) (8) Randon Random 20 Trial Random Number Fixed Cost Number Unit Variable Cost Number Demand Profit 21 0.95 0.611 $ 0.652 13171 22 0.04 3 $ 0.331 106894 23 0.757 0.970 $ 5.154 89471 24 0.807 0.253 $ 0.883 55664 25 0.798 0.191 $ 0.600 27632 26 0.854 0.495 $ .192 93925 27 0.697 0.696 $ 0.373 110283 28 0.796 0.441 $ 0.909 159951 29 0.951 0.968 $ 0.461 17090 0.936 0.140 $ 0.175 91947 996 0.245 0.569 $ 0.075 76715 1017 997 0.976 .509 $ 0.985 185072 1018 998 0.785 0.580 $ 0.777 142875 019 999 0.568 0.091 $ 0.421 020 1000 0.855 0.229 S 114023 0.936 165694 1021 022 #DIV/O! $19.6 120310.8 #DIV/O! 1023 1024 025 026 1027 1028 1029 030 1031 1032 033 1035 036 037 1038 038 040 1041 042 043 1044 1045 046 Instructions-read this first! A Sanotronics B C + Ready Ex Accessibility: Investigate 100%