Question

How do I solve this using excel

Problem 11-01 (Risk Analysis for Sanotronics LLC) s Question 18 of 22 Check My Work eBook (All answers were generated using 1,000 trials and native Excel functionality.) Galaxy Co. distributes wireless routers to Internet service providers. Galaxy procures each router for $75 from its supplier and sells each router for $125. Monthly demand for the router is a normal random variable with a mean of 100 units and a standard deviation of 20 units. At the beginning of each month, Galaxy orders enough routers from its supplier to bring the inventory level up to 100 routers. If the monthly demand is less than 100, Galaxy pays $15 per router that remains in inventory at the end of the month. If the monthly demand exceeds 100, Galaxy sells only the 100 routers in stock. Galaxy assigns a shortage cost of $30 for each unit of demand that is unsatisfied to represent a loss-of-goodwill among its customers. Management would like to use a simulation model to analyze this situation (a) what is the average monthly profit resulting from its policy of stocking 100 routers at the beginning of each month? Round your answer to the nearest whole number (b) What is the proportion of months in which demand is completely satisfied? Round your answer to the nearest whole number. (c) Use the simulation model to compare the profitability of monthly replenishment levels of 100, 120, and 140 routers. Which monthly replenishment level maximizes profitability? Select your answer-V Use the corresponding 95% confidence intervals on the average profit to make your comparison Round your answers to the nearest whole number. Lower Bound: Upper Bound Check My Work

Answer 1

Answer Date: --11/04/2019 Let D be the monthly demand for the routers. D is normally distributed with mean 100 and standard deviation 20 units. We will generate D using the function NORM.INV(RANDO,200,20), and then we round it to the nearest integer. In excel, the random number thus generated changes whenever you make anyother change in the sheet. One way to prevent it is to paste the demand as values once you fill the sheet. O-100 is the order quantity at the begenning of each month The quantity sold (S) is minimum of D and O Revenue is S 125 The cost of Routers sold is S#75 The - quantity sold) 15 holding cost is Inventory at the beginning The loss of goodwill cost is (Demand- quantity sold) 30 The profit is revenue - (cost of routers+holding cost+loss of goodwill) We prepare the following sheet

demand 2 Distribution T Normal 3 Mean 4 Standard Dew20 5 Cost per Rout 75 6 Sel 100 125 100 15 30 Cost of 10 11 Simulation of Routers sold t of loss-of 12 Trial # 13 1 4 A13 1 15 A14+1 16 A15+1 17 I-A16.1 SoldRevenue Profit Demand ROUNDINORM.INVIRANDO.SB$3,$8 ROUNDI(NORMINVIRAN eROUND(NORM. INVİ RANDU. SBS3,$8 «ROUND(NORM.INVİRANDO-SB$3.$8 «ROUND(NORM.INVİRANDO.SBS3.SBS4),0) $B$7 SBS7 l-SeS7 MIN (B13,C13) MIN(B14,C14 MIN B15,C MIN 816.C 013 $8$6 D13 $8S (C13-D13) $Bs8 C14-D14) $B58 B13-D13) $B$9 E13-F13-G13-H13 814-D14) $BS9E14-F14-G14-H14 B15-D15) SB$9 E15-F15-G15-H15 B16-D16 $B9 E16-F16-G16-H16 17-F17-G17-H17 016 $8$6 D16 s855(C16-D16) $BS8 (817,C17 017 $8s6 repeat the rows to make 1000 trials Get the following

Monthly demand Normal 2 DistributionT 3 Mearn 4 Standard Deviation 5 Cost per Router 6 Selling price /Router 7 Replenishment Level 8 Holding Cost/Router 9 Cost of Loss-of-goodwill /Router 10 11 Simulation 100 $125 100 15 Cost of lo Cost of Beginning Quantity Revenue Routers Holding of goodwill Inventory Sold($) Profit sold ()Cost ( () $240 $4,760 $0 $3,440 $570 $4,430 $330 $4,670 $30 $4,970 $270 $4,730 $990 $4,010 $90$4,910 $0$1,260 $3,740 $0 $3,830 $480 $4,520 $0 $4,610 Demand 7500 12 Trial # 13 14 15 16 17 18 19 100 100 100 100 100 100 100 100 100 100 100 100 100 76 100 100 100 100 100 100 100 82 100 108 76 119 9500 5700 $360 4 101 109 133 103 142 82 116 10250 6150$270 21 10 11750 7050 $90 23 24 12

paste the column corresponding to demand as values to avoid any change (you can also use option setting to disable the refresh) Now the calculations a) The average profit is the average of profit column for 1000 simulations b) When the demand is completely satisfied, the cost due to loss of goodwill is zero. We will count the numbXY เข้ ntonihs in which ih;58XMal!;2%Y 3 and ckwick'this By.lix)[) ogi n«l piopenii!)n. the following is the excel

Cost of loss-of goodwill Proportion of months the (813-D13) SBS9 (B14-D14) $B$9 Profit E13-F13-G13-H13 E14-F14-G14-H14 Average Monthly profit demand is completely satified AVERAGE(113:11012) COUNTIF(H13:H1012,"-o"/1000 (B15-D15)SBS9 E15-F15-G15-H15 get the following

3 Mean 4 Standard Deviation 5 Cost per Router $75 ishment Level Cost/Router 9 Cost of Loss-of 10 11 Simulation Proportion of of Revenue Routers t of the 12 Trial# 13 Sold Profit 7500 5700 a) The average profit is S4,217 (You will get a value in this ball park) b) Proportion of months the demand is completely satisfied is 53% c) We need to find the 95% confidence interval for average profit. Let x be the average profit in n=1000 simulations s be the sample standard deviation of profit for n 1000 simulation The standard error of average profit is 95% confidence interval indicates that the level of significance is a1 -95/100 0.05 The critical value of z is P(Z > za/2) α/2 0.05/2-0.025

this can also be written as P(Z z21-0.025 0.975 From the standard normal tables (or excel function NORM.INV(0.975,0,1) we get 2a/2 1.96 Now the lower bound of 95% confidence interval is The upper bound is The following excel does these 0 Proportion of months the demand is deviation Standard error of of Profit E13-F13-G13-H13 Monthl atisfied Lower Bound Upper Bound K13-N13 1.96 K13 N13 1.96 avera AVERAGE(013:11012 COUNTIF(H13:H STDEV.S(113:11 -M13/SQRT(1 F14 G14-H14 get the following for replenishment level of 100

get the following for replenishment level of 100 demand 5 Cos perRouter Replenhhment Level Cost of Proportion of Cost o RouterHolding f goodwill ost of loss Average |demand is |standard error of Monthlycompletely deviation average owerupper Reve 13 I S3440 next get the values for 120 and 140 (change the value in the yellow box) and get the following

next get the values for 120 and 140 (change the value in the yellow box) and get the following Replenishment AverageProportion of standard Standard Lower Upper level Monthly months the deviationerror ofBound Bound profit and isof profit average completely satisfied profit 100 S 4,217 53% 682.04 21.57 4,174 4,259 120 S4,513 83% 1,081.6734.21 4,446 4,580 140 $ 4,382 97% 1,307.61 41.35 4,301 4,463 We can see that the 95% confidence interval for replenishment level of 120 routers is totally above that for 100 and only slight overlaps with 140. Hence we can say that monthly replenishment level of 120 maximizes the profit. ans: 120 Lower Bound: $4,446 Upper Bound: $4,580