Question

1 Approved Answer

Posted on Jul 17, 2024

Please would like Chegg Expert--Ramjakhar to solve the question below. ABC PARETO INVENTORY MANAGEMENT QUESTION Review the excel screenshots below carefully and then read the

Please would like Chegg Expert--Ramjakhar to solve the question below.

ABC PARETO INVENTORY MANAGEMENT QUESTION

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

image text in transcribed

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 5 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.

image text in transcribed

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 C3 fr 15% A B 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 3 B 0.8 0.15 0.05 =SUM(B2:34) 0.11 0.15 0.74 =SUM(C2:04) =B2*$D$5 =B3*$D$5 =B4* $D$5 ? ? ? =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 =G4*H4 =SUM(K2:K4) =K2/52/5 =K3/52/5 =K4/52/5 =SUM(L2:14) 4 C 5 Total 10000 =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 C3 fr 15% A B 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 3 B 0.8 0.15 0.05 =SUM(B2:34) 0.11 0.15 0.74 =SUM(C2:04) =B2*$D$5 =B3*$D$5 =B4* $D$5 ? ? ? =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 =G4*H4 =SUM(K2:K4) =K2/52/5 =K3/52/5 =K4/52/5 =SUM(L2:14) 4 C 5 Total 10000 =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