Review the excel screenshots below carefully and then read the question and solve it, presenting your answer on EXCEL please.

• A-items for the company are fairly predictable in sales volume, and have an MAD of 15%
• B-items for the company are less predictable in sales volumes, and have an MAD of 25%
• C-items for the company have very erratic demand, and have an MAD of 40%.

ASSUMPTIONS:

• Lead time for the majority of products is 10 days (2 business weeks).
  • Target service level for A-items is 99%
  • Target service level for B-items is 98%
  • Target service level for C-items is 95%.

QUESTION:

Given the info above... Create 7 alternative replenishment scenarios on the PARETO EXCEL SHEET below --- and select which is best scenario.

You will need to consider safety stock and apply change of service level from the information of spreadsheet from above.

EXTRA INFORMATION:

A B C E L Category % of sales % of products Sales ($million) Gross margin % Daily # replen 1 2 A 80% F G H . I J K Avg COGS # of Avg $Inv # of Replen/yr $Replen ($million) products ($million) Replen ($million) $6,400 1,474 12 $533 $2671 17,688 $1,200 2,010 12 $100 $50 24,120 $400 9,916 12 $331 $17 118,992 $8,000 13,4001 $333 160,800 11% 20% 68 3 B 15% 15% 93 $8,000 $1,500 $500 $10,000 20% 20% 4 C 5% 74% 458 5 Total 100% 100% 618 Target service [A-items] 99% Target service (B-items] 98% Target service [C-items] 95% 15% 25% 40% 9896 95% 99% General Formula: MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock General Formula: %MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock 10 General Formula: 9MAD (monthly) %Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock 10 10 20 20 20 1 1 6.17 9.08 11.63 H3 fx ? B D H Category % of sales % of products Sales ($million) Gross margin % COGS ($million) # of products Replen/yr Avg $Replen ($million) Avg Siny (Smillion) # of Replen Daily #replen 1 2 A 0.8 12 3 B ? 0.11 0.15 0.74 =SUM(C2:04) 0.15 0.05 =SUM(B2:34) =B2*$D$5 =B3*$D$5 =B4*$D$5 10000 0.2 0.2 10.2 =D2*(1-E2) =D3*(1-E3) =D4*(1-E4) =SUM(F2:F4) =C2*$G$5 =C3*$G$5 =C4*$G$5 13400 =F2/H2 |=F3/H3 =F4/H4 =12/2 =13/2 =14/2 =SUM(12:34) =G2*H2 =G3*H3 I=G4*H4 =SUM(K2:64) =K2/52/5 =K3/52/5 =K4/52/5 =SUM(L2:L4) 12 4 TC 5 Total =NORM.INVISNS2,0,1) 1.25*SAGSORIES3/20SNS8/SN99 A K M N BCD E F BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (A-Items 99% 5 40 10% 15% 20% 2596 30% 35% 40% 2.91 4.36 5.82 7.27 8.72 10.18 11.63 10 15 20 25 30 35 4.11 5.04 5.82 6.50 7.12 7.69 6.17 7.56 8.72 9.75 10.68 11.54 8.22 10.07 11.631 13.00 14.25 15.39 10.28 12.59 14.54 16.26 17.81 19.23 12.34 15.11 17.45 19.51 21.37 23.08 14.39 17.63 20.36 22.76 24.93 26.93 16.45 20.15 23.26 26.01 28.49 30.77 f -NORM.INVISN$2,0,1)+1.25 SA6 SORTIG$3/20)*SN$8/SN$9 8.22 12.34 16.45 20.56 24.67 28.79 32.90 45 8.72 13.09 17.45 21.81 26.17 30.53 34.90 50 9.20 13.79 18.39 22.99 27.59 32.18 36.78 General Formula: "MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buchets of safety stoch 15% 99% 10 20 1 6.17 B M N C D H BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (B-items] 98% 25 35 40 45 30 6.29 SO 8.12 25% 98% 10% 15% 20% 25% 30% 35% 40% 5 2.57 3.85 5.13 6.42 7.70 8.99 10.27 10 3.63 5.45 7.26 9.08 10.89 12.71 14.52 15 4.45 6.67 8.89 11.12 13.34 15.56 17.79 20 5.13 7.70 10.27 12.84 15.40 17.97 20.54 5.74 8.61 11.48 14.35 17.22 20.09 22.96 6.79 10.19 13.58 16.98 20.38 23.77 27.17 9.43 12.58 15.72 18.86 22.01 25.15 7.26 10.89 14.52 18.15 21.78 25.41 29.04 7.70 11.55 15.40 19.25 23.10 26.96 30.81 10 General Formula: MAD (monthly) Service Target Lead time (days) Days per forecast buchet Days per safety stock bucket Planning buckets of safety stock 12.18 16.24 20.30 24,35 28.41 32.47 20 1 9.08 E6 -NORM.INVISN$2,0,1) 1.25 SA6*SORTIES3/20) SNS8/SNS9 M N F BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (C-items] 95% 2 3 5 4 40% 5 6 95% 10% 15% 20% 25% 30% 35% 40% 10 2.91 4.36 5.82 7.27 8.72 10.18 11.63 2.06 3.08 4.11 5.14 6.17 7.20 8.22 15 3.56 5.34 7.12 8.90 10.68 12.46 14.24 20 4.11 6.17 8.22 10.28 12.34 14.39 16.45 25 4.60 6.90 9.20 11.49 13.79 16.09 18.39 30 5.04 7.55 10.07 12.59 15.11 17.63 20.15 35 5.44 8.16 10.88 13.60 16.32 19.04 21.76 40 5.82 8.72 11.63 14.54 17.45 20.35 23.26 45 6.17 9.25 12.34 15.42 18.50 21.59 24.67 50 6.50 9.75 13.00 16.25 19.51 22.76 26.01 7 8 9 10 General Formular "MAD (monthly) Service Target Leod time (days) Days per forecast buchet Days per safety stock buchet Planning buckets of safety stock 10 20 1 11.63 A B C E L Category % of sales % of products Sales ($million) Gross margin % Daily # replen 1 2 A 80% F G H . I J K Avg COGS # of Avg $Inv # of Replen/yr $Replen ($million) products ($million) Replen ($million) $6,400 1,474 12 $533 $2671 17,688 $1,200 2,010 12 $100 $50 24,120 $400 9,916 12 $331 $17 118,992 $8,000 13,4001 $333 160,800 11% 20% 68 3 B 15% 15% 93 $8,000 $1,500 $500 $10,000 20% 20% 4 C 5% 74% 458 5 Total 100% 100% 618 Target service [A-items] 99% Target service (B-items] 98% Target service [C-items] 95% 15% 25% 40% 9896 95% 99% General Formula: MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock General Formula: %MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock 10 General Formula: 9MAD (monthly) %Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buckets of safety stock 10 10 20 20 20 1 1 6.17 9.08 11.63 H3 fx ? B D H Category % of sales % of products Sales ($million) Gross margin % COGS ($million) # of products Replen/yr Avg $Replen ($million) Avg Siny (Smillion) # of Replen Daily #replen 1 2 A 0.8 12 3 B ? 0.11 0.15 0.74 =SUM(C2:04) 0.15 0.05 =SUM(B2:34) =B2*$D$5 =B3*$D$5 =B4*$D$5 10000 0.2 0.2 10.2 =D2*(1-E2) =D3*(1-E3) =D4*(1-E4) =SUM(F2:F4) =C2*$G$5 =C3*$G$5 =C4*$G$5 13400 =F2/H2 |=F3/H3 =F4/H4 =12/2 =13/2 =14/2 =SUM(12:34) =G2*H2 =G3*H3 I=G4*H4 =SUM(K2:64) =K2/52/5 =K3/52/5 =K4/52/5 =SUM(L2:L4) 12 4 TC 5 Total =NORM.INVISNS2,0,1) 1.25*SAGSORIES3/20SNS8/SN99 A K M N BCD E F BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (A-Items 99% 5 40 10% 15% 20% 2596 30% 35% 40% 2.91 4.36 5.82 7.27 8.72 10.18 11.63 10 15 20 25 30 35 4.11 5.04 5.82 6.50 7.12 7.69 6.17 7.56 8.72 9.75 10.68 11.54 8.22 10.07 11.631 13.00 14.25 15.39 10.28 12.59 14.54 16.26 17.81 19.23 12.34 15.11 17.45 19.51 21.37 23.08 14.39 17.63 20.36 22.76 24.93 26.93 16.45 20.15 23.26 26.01 28.49 30.77 f -NORM.INVISN$2,0,1)+1.25 SA6 SORTIG$3/20)*SN$8/SN$9 8.22 12.34 16.45 20.56 24.67 28.79 32.90 45 8.72 13.09 17.45 21.81 26.17 30.53 34.90 50 9.20 13.79 18.39 22.99 27.59 32.18 36.78 General Formula: "MAD (monthly) Service Target Lead time (days) Days per forecast bucket Days per safety stock bucket Planning buchets of safety stoch 15% 99% 10 20 1 6.17 B M N C D H BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (B-items] 98% 25 35 40 45 30 6.29 SO 8.12 25% 98% 10% 15% 20% 25% 30% 35% 40% 5 2.57 3.85 5.13 6.42 7.70 8.99 10.27 10 3.63 5.45 7.26 9.08 10.89 12.71 14.52 15 4.45 6.67 8.89 11.12 13.34 15.56 17.79 20 5.13 7.70 10.27 12.84 15.40 17.97 20.54 5.74 8.61 11.48 14.35 17.22 20.09 22.96 6.79 10.19 13.58 16.98 20.38 23.77 27.17 9.43 12.58 15.72 18.86 22.01 25.15 7.26 10.89 14.52 18.15 21.78 25.41 29.04 7.70 11.55 15.40 19.25 23.10 26.96 30.81 10 General Formula: MAD (monthly) Service Target Lead time (days) Days per forecast buchet Days per safety stock bucket Planning buckets of safety stock 12.18 16.24 20.30 24,35 28.41 32.47 20 1 9.08 E6 -NORM.INVISN$2,0,1) 1.25 SA6*SORTIES3/20) SNS8/SNS9 M N F BUCKETS OF SAFETY STOCK FOR TARGET SERVICE LEVEL MAD per Forecast Bucket Lead Times (Business Days) Target service (C-items] 95% 2 3 5 4 40% 5 6 95% 10% 15% 20% 25% 30% 35% 40% 10 2.91 4.36 5.82 7.27 8.72 10.18 11.63 2.06 3.08 4.11 5.14 6.17 7.20 8.22 15 3.56 5.34 7.12 8.90 10.68 12.46 14.24 20 4.11 6.17 8.22 10.28 12.34 14.39 16.45 25 4.60 6.90 9.20 11.49 13.79 16.09 18.39 30 5.04 7.55 10.07 12.59 15.11 17.63 20.15 35 5.44 8.16 10.88 13.60 16.32 19.04 21.76 40 5.82 8.72 11.63 14.54 17.45 20.35 23.26 45 6.17 9.25 12.34 15.42 18.50 21.59 24.67 50 6.50 9.75 13.00 16.25 19.51 22.76 26.01 7 8 9 10 General Formular "MAD (monthly) Service Target Leod time (days) Days per forecast buchet Days per safety stock buchet Planning buckets of safety stock 10 20 1 11.63