I need to fill in the blue cells. I have an idea but I am unsure how to get the upper and lower bounds.

Thank you

Homework #9 Simulation MedicTronix is a startup company that manufactures medical devices for use in hospital clinics. Inspired by experiences with family members who have battled cancer, MedicTronixs's founders have developed a prototype for a new device that limits health care workers' exposure to chemotherapy treatments while they are preparing, administering, and disposing of these hazardous medications. This new device features an innovative design and has the potential to capture a substantial share of the market. MedicTronix would like an analysis of the first-year profit potential of the device. MedicTronixs has identified the key parameters in determining first-year profit: selling price per unit (p), first-year administrative and advertising costs (ca), direct labor cost per unit (c), parts cost per unit (Cp), and first-year demand (d). After conducting market research and a financial analysis, MedicTronix estimates with a high level of certainty that the device's selling price will be $2,495 per unit, and the first-year administrative and advertising costs will total $5,000,000. MedicTronix is not certain about the values for the cost of direct labor, the cost of parts, and the first-year demand. At this stage of the planning process, MedicTronix's base estimates of these inputs are $1400 per unit for the direct labor cost, $350 per unit for the parts cost, and 25,000 units for the first-year demand (with a standard deviation of 10% of demand). MedicTronix is interested in what happens if the estimates of the direct labor cost per unit, parts cost per unit, and first-year demand do not turn out to be as expected under the basecase scenario. For instance, suppose that MedicTronix believes that direct labor costs could range from $1200 to $1600 per unit, the parts cost could range from $250 to $500 per unit, and the first-year demand could range from 0 to 50,000 units. Because MedicTronixs models the direct labor cost per unit with a discrete probability distribution, the direct labor cost per unit can only take on a value of $1200, $1300, $1400, $1500, or $1600. Costs per unit probability is 10% for $1200, 21% for $1300, 38% for $1400, 21% for $1500, and 10% for $1600. Part A. Using these ranges, develop a base-case simulation. For your chart, make sure that you can see a frequency distribution. Hint: For your Bin column, start around 5-6 Million and increase each row by a factor of 1 Million. Part B. Marketing has seen your baseline simulation and has informed you that their administrative and advertising forecast costs have increased to $5.5 Million. Therefore, you have increased your selling price per unit to $2795. Inflation has been determined to increase price costs 10% for both boundaries of parts costs. Demand is expected to be 27000 with a 5 % deviation. With the inflation, all costs per unit have increased $100. the direct labor cost per unit can only take on a value of $1300, $1400, $1500, $1600, or $1700. Costs per unit probability is 10% for $1300, 21% for $1400, 38% for $1500, 21% for $1600, and 10% for $1700. Using these ranges, develop a new base-case simulation. For your chart, make sure that you can see a frequency distribution. Hint: For your Bin column, look at your minimum profit and maximum profit and integrate the Bin values that will show the majority of probability values. For your HW Report. Use the baseline values to start, then use the impact of inflation on your values with suggestions. PLAY WITH DATA in BLUE!!!! E6 C D F G . L 250 an 52,415.09 ss.onC,000 51,000 $30M 23,311 350 200 $16,041, GS.ti Cielo hit Prability Parts Cast (Uniforre Tributinn) Law Bound Upper und 2 1sper End of love 000 000 0.00 0.00 00 WWW WOOD Dennd (Numel Distributia Man Standl Devin ID SES,200 OSTS 25,000 2.90 YOSIVES 1240A Anam 21.00 Prelit Parameters 4 Selling Pris per il 5 Administrative & Advertising Cat 6 Tina Talur Cod Per Init 7 Parts Cast Pur Chit 8 Temand S 10 Model 11 Traht 1? 13 Tirol Labor Cod Lure Enelinlerval 15 16 10 17 10 18 10 19 00 20 21 Simulatine Trial 22 1 1 23 2 24 3 25 4 25 5 27 28 2 29 8 30 1 31 10 32 11 33 12 34 13 35 14 35 15 37 16 38 12 39 18 40 19 41 Frequency Derand 28.311 24.465 21.892 25.971 21.846 25.250 25.651 17.117 Prolit Rummary Statistics Mean Standard Deitinn Minimum Prelit Maximum Prulit Profit $1.3,602,SON 51,877,004 53,818,470 $31,352,914 $ 0.000 . D 3 3 7 19 SR 99 Dirol Laber Cut Perlinit 51,40 $1,400 $1,400 $1,401 51,40 $1,40 51,4 $1,400 51,4M S1,40 51,40 51,4 $1,400 $1,40 51,400 51.400 $1,400 $1,400 51,400 slm Parts Cost Per Limit SIS $10.00 $15.CO $150.00 $350.00 $15.CO SISRCA $350.00 $150.00 $350.00 SS.CO $350.00 SISE.CO $150.00 SS.CO SISRCA 550.00 5:50.cn SISRCA S150CM 525000 25.96 24.562 27.454 25.420 24.035 25.929 25,467 21.447 25,400 22.457 2 S16091.185 513,226,427 511,309,429 $14,148,52 $11,275, 106 SIRI 1.011 $14,114,186 57,916, 151 513,497, ISK $14,299.652 513,298 540 515,451,033 513,937,874 $12,00RS 514,11% 127 514,TID 512,467,940 $13,029,95 511, Tan 13 . Bin 56,000,000 57,000,000 58,00 DIN , 59,000,000 SIANO,000 $11,00 $12,000,000 $13,00, $14,000,000 SS.CO $1,000,000 $13, $18.00, $19,000,000 520,000,000 521,00,000 522,000,000 523,000,000 534 196 199 17 139 99 26 5 0 D 1 D D Homework #9 Simulation MedicTronix is a startup company that manufactures medical devices for use in hospital clinics. Inspired by experiences with family members who have battled cancer, MedicTronixs's founders have developed a prototype for a new device that limits health care workers' exposure to chemotherapy treatments while they are preparing, administering, and disposing of these hazardous medications. This new device features an innovative design and has the potential to capture a substantial share of the market. MedicTronix would like an analysis of the first-year profit potential of the device. MedicTronixs has identified the key parameters in determining first-year profit: selling price per unit (p), first-year administrative and advertising costs (ca), direct labor cost per unit (c), parts cost per unit (Cp), and first-year demand (d). After conducting market research and a financial analysis, MedicTronix estimates with a high level of certainty that the device's selling price will be $2,495 per unit, and the first-year administrative and advertising costs will total $5,000,000. MedicTronix is not certain about the values for the cost of direct labor, the cost of parts, and the first-year demand. At this stage of the planning process, MedicTronix's base estimates of these inputs are $1400 per unit for the direct labor cost, $350 per unit for the parts cost, and 25,000 units for the first-year demand (with a standard deviation of 10% of demand). MedicTronix is interested in what happens if the estimates of the direct labor cost per unit, parts cost per unit, and first-year demand do not turn out to be as expected under the basecase scenario. For instance, suppose that MedicTronix believes that direct labor costs could range from $1200 to $1600 per unit, the parts cost could range from $250 to $500 per unit, and the first-year demand could range from 0 to 50,000 units. Because MedicTronixs models the direct labor cost per unit with a discrete probability distribution, the direct labor cost per unit can only take on a value of $1200, $1300, $1400, $1500, or $1600. Costs per unit probability is 10% for $1200, 21% for $1300, 38% for $1400, 21% for $1500, and 10% for $1600. Part A. Using these ranges, develop a base-case simulation. For your chart, make sure that you can see a frequency distribution. Hint: For your Bin column, start around 5-6 Million and increase each row by a factor of 1 Million. Part B. Marketing has seen your baseline simulation and has informed you that their administrative and advertising forecast costs have increased to $5.5 Million. Therefore, you have increased your selling price per unit to $2795. Inflation has been determined to increase price costs 10% for both boundaries of parts costs. Demand is expected to be 27000 with a 5 % deviation. With the inflation, all costs per unit have increased $100. the direct labor cost per unit can only take on a value of $1300, $1400, $1500, $1600, or $1700. Costs per unit probability is 10% for $1300, 21% for $1400, 38% for $1500, 21% for $1600, and 10% for $1700. Using these ranges, develop a new base-case simulation. For your chart, make sure that you can see a frequency distribution. Hint: For your Bin column, look at your minimum profit and maximum profit and integrate the Bin values that will show the majority of probability values. For your HW Report. Use the baseline values to start, then use the impact of inflation on your values with suggestions. PLAY WITH DATA in BLUE!!!! E6 C D F G . L 250 an 52,415.09 ss.onC,000 51,000 $30M 23,311 350 200 $16,041, GS.ti Cielo hit Prability Parts Cast (Uniforre Tributinn) Law Bound Upper und 2 1sper End of love 000 000 0.00 0.00 00 WWW WOOD Dennd (Numel Distributia Man Standl Devin ID SES,200 OSTS 25,000 2.90 YOSIVES 1240A Anam 21.00 Prelit Parameters 4 Selling Pris per il 5 Administrative & Advertising Cat 6 Tina Talur Cod Per Init 7 Parts Cast Pur Chit 8 Temand S 10 Model 11 Traht 1? 13 Tirol Labor Cod Lure Enelinlerval 15 16 10 17 10 18 10 19 00 20 21 Simulatine Trial 22 1 1 23 2 24 3 25 4 25 5 27 28 2 29 8 30 1 31 10 32 11 33 12 34 13 35 14 35 15 37 16 38 12 39 18 40 19 41 Frequency Derand 28.311 24.465 21.892 25.971 21.846 25.250 25.651 17.117 Prolit Rummary Statistics Mean Standard Deitinn Minimum Prelit Maximum Prulit Profit $1.3,602,SON 51,877,004 53,818,470 $31,352,914 $ 0.000 . D 3 3 7 19 SR 99 Dirol Laber Cut Perlinit 51,40 $1,400 $1,400 $1,401 51,40 $1,40 51,4 $1,400 51,4M S1,40 51,40 51,4 $1,400 $1,40 51,400 51.400 $1,400 $1,400 51,400 slm Parts Cost Per Limit SIS $10.00 $15.CO $150.00 $350.00 $15.CO SISRCA $350.00 $150.00 $350.00 SS.CO $350.00 SISE.CO $150.00 SS.CO SISRCA 550.00 5:50.cn SISRCA S150CM 525000 25.96 24.562 27.454 25.420 24.035 25.929 25,467 21.447 25,400 22.457 2 S16091.185 513,226,427 511,309,429 $14,148,52 $11,275, 106 SIRI 1.011 $14,114,186 57,916, 151 513,497, ISK $14,299.652 513,298 540 515,451,033 513,937,874 $12,00RS 514,11% 127 514,TID 512,467,940 $13,029,95 511, Tan 13 . Bin 56,000,000 57,000,000 58,00 DIN , 59,000,000 SIANO,000 $11,00 $12,000,000 $13,00, $14,000,000 SS.CO $1,000,000 $13, $18.00, $19,000,000 520,000,000 521,00,000 522,000,000 523,000,000 534 196 199 17 139 99 26 5 0 D 1 D D