Refer to the spreadsheet above:

A. Recompute the Net Profit for the first five simulation trials if Gross Profit per Unit is changed to $40.00 and Shortage Cost per Unit is changed to $25.00 with everything else remaining unchanged.

B. Assuming that simulation trials are located in rows 16 through 315, give the Excel formulas for Mean Profit, Minimum Profit, Maximum Profit, and Service Level.

C. Using the original values for Holding Cost per Unit and Shortage Cost per Unit, would an increase in Replenishment Level to 100 units result in more or less Net Profit for the first five simulation trials? Explain your answer.

2. DVILCN CLEL INCAL UPELT - CALEI FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri AA Wrap Text General Normal Bad * Cut Copy Format Painter Paste BIV- A.Et Merge & Center $ . % , Calculation 4:89 Conditional Format as Neutral Formatting Table Clipboard Font Alignment Number Styles A1 x fr Butler Electrical Supply Co. D E F G H 1 $30.00 B 1 Butler Electrical Supply Co. 2 3 Gross Profit Per Unit $50.00 4 Holding Cost Per Unit $15.00 5 Shortage Cost Per Unit 6 7 Replenishment Level(Q) 90 8 9 Monthly Demand (Normal Distribution) 10 Mean 1001 11 Std Deviation 20 12 13 SIMULATION TRIALS SUMMARY STATISTICS Mean Profit Standard Deviation Minimum Profit Maximium Profit Service Level $3,837.83 $526.50 $925.00 $4,500.00 87.18% 14 15 16 Month 1 2 Demand 116 110 Sales 90 90 17 Gross Profit $4,500.00 $4,500.00 $4,150.00 $4,150.00 $3,600.00 Holding Cost $0.00 $0.00 $105.00 $105.00 $270.00 Shortage Cost $780.00 $600.00 $0.00 $0.00 $0.00 Net Profit $3,720.00 $3,900.00 $4,045.00 $4,045.00 $3,330.00 18 3 83 83 19 4 83 83 20 5 72 72