Suppose Sanotronic's management team re evaluates the situation! and determines alternative values for the random Variables. The new values for the random Variables are: Direct Laber cost per Unit = $42 to 848 Cinstead of $43-847) Part Cost per Unit = $80 to $110 Cinstead of $80-$100) Demand = 0 to 40 000 Cinstead of O to 30000) Questim ! : following to figure il excel Worksheet example, Construct an Excel spreadsheet Simulation model and determine the profit for the worst-case Scenario. A put your answer on the Blackboard. Question 2 : Run the Excel spreadsheet Sinulation model and determine the profit for the Best-case scenario. Put your answer on the Blackboard Question 3 : Use the following excel Sinulation function for the Demand. = NORM. INV (RAND(), 20000 ,5000) Run this demand Sinulation function and get the Sigulated demand value. Repeat this five times (5 trials) and answer the following questions. put your answers on the Blackboard. Question 3 a: Maximum Sinulated demand Question 36 : Minimum Simulated demand Question 3c : Average simulated demand Sanotronics LLC is a start-up company that manufactures medical devices for use in hospital Clinics. Inspired by experiences with family members who have battled cancer Sanotronics'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. The new device features an innovative design and has the potential to capture a substantial share of the market Sanotronics would like an analysis of the first-year profit potential for the device Because of Sanotronics's tight cash flow situation management is particularly concerned about the potential for a loss. Sanotronics has identified the key parameters in determining first-year profit: selling price per unit (p), first-year administrative and advertising costs (.), direct labor cost per unit (c) parts cost per unit (,) and first-year demand (d). After conducting market research and a financial analysis. Sanotronics estimates with a high level of certainty that the device's selling price will be $249 per unit and that the first-year administrative and advertising costs will total $1,000,000 Sanotronics 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, Sanotronics's base esti- mates of these inputs are $45 per unit for the direct labor cost $90 per unit for the parts cost, and 15,000 units for the first-year demand. We begin our risk analysis by considering a small set of what if scenarios Base-Case Scenario Sanotronics's first-year profit is computed as follows: Profit= (p - -C) Xd - (ILD Recall that Sanotronics is certain of a selling price of $249 per unit, and administrative and advertising costs total $1,000,000. Substituting these values into equation (11.1) yields Profit (249- -c) Xd - 1,000,000 (11.2) Sanotronics's base-case estimates of the direct labor cost per unit, the parts cost per unit, and first-year demand are $45, 590, and 15.000 units, respectively. These values consti- tute the base-case scenario for Sanotronics. Substituting these values into equation (11.2) yields the following profit projection: Profit - (249 - 45 - 90X15.000) - 1,000,000 - 710,000 Thus, the base-case scenario leads to an anticipated profit of S710,000 Although the base-case scenario looks appealing. Sanotronics is aware that the values of direct labor cost per unit, parts cost per unit, and first-year demand are uncertain, so the base-case scenario may not occur. To help Sanotronics gauge the impact of the uncertainty. the company may consider performing a what if analysis. A what if analysis involves con- sidering alternative values for the random variables (direct labor cost, parts cost, and first- year demand) and computing the resulting value for the output (profit) Sanotronics 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 base-case scenario. For instance, suppose that Sanctronics believes that direct labor costs could range from $43 to 547 per unit, parts cost could range from S80 to $100 per unit, and first-year demand could range from 0 to 30,000 units. Using these ranges, what if analysis can be used to evaluate a worst-case scenario and a best-case scenario Worst-Case Scenario The worst-case scenario for the direct labor cost is $47 (the highest value), the worst case scenario for the parts cost is $100 (the highest value and the worst-case scenario for demand is o units (the lowest value). Substituting these values into equation (11.2) leads to the following profit projection: Profit - (249 - 47 - 100X0) - 1.000.000 - -1,000,000 So, the worst-case scenario leads to a projected loss of $1,000,000 FIGURE 11.1 Excel Worksheet for Sanotronics 1 Sanotronics 2 3 Parameters 4 Selling Price per Unit 5 Administrative & Advertising Cost 6 Direct Labor Cost Per Unit 7 Parts Cost Per Unit 8 Demand 249 1000000 4 5 190 15000 10 Model 11 Profit 12 ((B4-B6-B7)B8)-BS 1 Sanotronics 21 3 Parameters 4 Selling Price per Unit 5 Administrative & Advertising Cost 6 Direct Labor Cost Per Unit 7 Parts Cost Per Unit 8 Demand $249.00 $1,000,000 $45.00 $90.00 15,000 Model 10 11 Profit $710.000.00