AutoSave . Off) HW_Ch 11 - Excel Search Souza, Lethicia H. SL X File Home Insert Page Layout Formulas Data Review View Help Share Comments 10 ~ A" A Insert Arial ab General Ex AP O Ex Delete Paste E $ ~ % " Conditional Format as Cell Sort & Find & Analyze Sensitivity Formatting Table Styles Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity D2 X V A B C D E F G H K M Sanotronics Simulation with 100 Trials This is the Sanotronics simulation with 100 trials that was covered in the Data lecture. Here, we want to illustrate how the results can change when 4 Selling Price per Unit $249 different distributions are used for some of the random variables. Adm/Adv Cost $1,000,000 Suppose the following changes occur in the assumptions. -Parts cost follows a normal distribution with mean of $90 and standard Direct Labor Cost Parts Cost (Normal Distribution) deviation of $10. Mean $90 - Demand now follows a uniform distribution between 7,500 and 30,000 Lower End Upper End of units. 9 Probability of Interval Interval Cost per Unit Standrard Deviation $10 The appropriate changes have been made in the Excel sheet. Do not 10 0.1 0.0 0.1 $43 change the given random numbers. 11 0.2 0.1 0.3 $44 0.4 Now, what are the 12 0.3 0.7 $45 Demand (Uniform Distribution) 13 0.2 0.7 0.9 $46 Lower Bound 7500 a) mean profit (rounded to whole $) 14 0.1 0.9 1.0 $47 Upper Bound 30000 b) the probability of loss (in %) and 15 Expected Direct Labor Cost = $ 45.00 c) value at risk (rounded to whole $)? Remember to give the absolute 16 value. 17 Simulation Trials d) Is this more or less risky than the original example? 18 19 (1) (2) (3 (4) (5) (6) (7) (8) Radom Direct Labor Radom First-Year 20 Trial Number Cost per Unit Radom Number Parts Cost per Unit Number Demand Profit Instructions A Sanotronics B C + Ready + 100% Type here to search O W P x 6:56 PM 78OF A XX O ( () ENG 8/16/2021AutoSave . Off) HW_Ch 11 - Excel Search Souza, Lethicia H. SL X File Home Insert Page Layout Formulas Data Review View Help Share Comments 10 ~ A" A Insert Arial ab General Ex AP O Ex Delete Paste E $ ~ % 9 Conditional Format as Cell Sort & Find & Analyze Sensitivity Formatting Table Styles Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity D2 X V fx A B C D E F G H J K L M 21 0.385 0.909 0.520 22 0.528 0. 195 0.049 23 0.037 0.574 0.963 UI AWN 24 0.587 0.028 0.554 25 0.087 0.848 0.782 115 95 0.572 0.756 0.066 16 96 0.098 0.753 0.545 117 97 0.024 0.822 0.562 18 98 0.392 0.703 0.335 119 99 0.670 0.560 0.507 120 100 0.381 0. 137 0.831 121 Average 122 123 Summary Statistics 124 125 Sample Size 126 Mean Profit 127 Standard Deviation 128 Minimum Profit 129 Maximum Profit 130 Number of Losses 131 Probability of Loss 132 Drah / Profit > - 500k) Instructions A Sanotronics B C + Ready + 100% Type here to search O W x 6:56 PM 78OF A XO (() ENG 8/16/2021AutoSave . Off) HW_Ch 11 - Excel Search Souza, Lethicia H. SL X File Home Insert Page Layout Formulas Data Review View Help Share Comments Insert Arial 10 ~ A" A ab General Ex AP O Ex Delete Paste Analyze Sensitivity E $ ~ % " Conditional Format as Cell Sort & Find & Formatting Table Styles Format Filter ~ Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity D2 X V fix B C D E F G H I J K L M A 116 96 0.098 0.753 0.545 117 97 0.024 0.822 0.562 118 98 0.392 0.703 0.335 119 99 0.670 0.560 0.507 120 100 0.381 0.137 0.831 121 Average 122 123 Summary Statistics 124 125 Sample Size 126 Mean Profit 127 Standard Deviation 128 Minimum Profit 129 Maximum Profit 130 Number of Losses 131 Probability of Loss 132 Prob(Profit >= 500K) 133 5th Percentile 134 VaR 135 136 137 128 Instructions A Sanotronics B C + + 100% Ready x 6:56 PM Type here to search O 78OF A XO (() ENG 8/16/2021
