NOTE: The final ABC analysis only applies to Materials. After initially sorting, Packaging should be deleted or separated where it does not impact the calculations or readability of the final submissions. Sometimes having too much much data can create issues. All yellow boxes on Materials and Packaging tab must be populated using an Excel formula. Using material from Skills Development in the SKU/Final Assignment tab you will sort the items as needed and develop a workable ABC analysis and a cycle count plan. Column K will be the total cost on an annual basis. It is your choice to conduct the ABC analysis using either total annual usage in pounds or unit of measure, total annual cost, or any other method as referenced in the powerpoint. Column L will have a designation in each cell either A, B, C. Column M will be the frequency that the counts occur. A items should be less than 8 days (weekly). B items should be 30 to 60 days, and C items 90 to 180 days. You may choose to make all A items the same time (recommended) or some combination of frequency as long as it is within the paramenters stated above. All B items and Citems will follow this same pattern. Example and Exception: If you decide to count all A items weekly but identify one item as problematic and wish to count it more frequently that is allowable. Column N is the cost to count. Assume each count takes 37 minutes to complete using one employee that is paid $18.75 per hour with a fully loaded labor rate with fringes of $26.50 per hour. The sum of column N will be the total annual cost. The sum of column P will be the total number of counts needed annually. Column O will be the time in hours to count each item and will be needed to calculate final cost. Columns O and P are not required for credit but may be helpful in the other calculations. The rationale and decisions that go into your analysis should be included in the yellow text box at the bottom of the Materials and packaging spreadsheet and will be included in the Executive Summary and should include a chart, table or graph similar to the examples in the powerpoint. 3 The Finance and Accounting Departments of your company is implementing a rule that requires every item whether a material or a finished good must be counted at least once per year. The reason you are conducting the ABC analysis is the plant has an issue with running out of material and either making a mid shift unplanned changeover or loses the last 15 to 90 minutes of a shift as the changeover cannot be accomplished in a manner that makes it worthwhile to hold the crew. Estimates are that up to 2.5% of run time is lost to short shifts and unplanned changes. Studies suggest than 1% could be eliminated with a robust cycle count program. Approximately 1% is related to changeovers that cannot be avoided and .5% is related to supplier issues. You are permitted to recommend the cycle count program that just meets the company requirements (which is already partially in place) and you will get 70% on this problem. You also will not reduce downtime by 1% but will only save .25% which may reduce the effectiveness of the SKU rationalization problem. 4 5 Materials and packaging Instructions ABC Cycle count Total cost the Shelf life (days) ABC Frequency count Lead time (days) 7 14 14 Consolidate 14 10 12 120 120 1.98 10 14 4 Description Item number 5 Chicken wings x 1000 6 Chicken tenders x 1001 7 Boneless dark meat x 1002 8 Brisket x 1003 9 Chopped beef x 1004 10 Ends and pieces x 1005 11 Sliced pork x 1006 12 Pulled pork x 1007 13 Ends and pieces x 1008 14 BBQ base 1 x 1009 15 BBQ base 2 x 1010 16 BBQ base 3 x 1011 17 BBQ base 4 x 1012 18 BBQ base 5 x 1013 19 Spice 1 x 1014 20 Spice 2 x 1015 21 Spice 3 x 1016 22 Spice 4 x 1017 23 Spice 5 x 1018 24 Spice 6 x 1019 25 Spice 7 x 1020 26 Spice 8 x 1021 27 Spice 9 x 1022 28 Spice 10 x 1023 29 Spice 11 X 1024 30 Spice 12 x 1025 Materials and packaging Type of product Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material 130 110 120 90 120 Annual usage Unit of measure(uom) Cost per uom 5 3,000,000.00 lbs $ 2.79 5 7,000,000.00 lbs $ 3.50 5 8,000,000.00 lbs $ 3.01 5 4,000,000.00 lbs $ 4.00 5 5,500,000.00 lbs $ 2.16 5 2,000,000.00 Ibs $ 4.50 30 18,000,000.00 lbs $ 0.47 30 30,000,000.00 Ibs $ 0.27 15 3,000,000.00 lbs $ 5 1,000,000.00 lbs $ 4.75 57 10,000,000.00 lbs $ 0.45 59 8,000,000.00 lbs $ 0.55 30 6,000,000.00 lbs $ 0.71 61 6,000,000.00 lbs $ 0.65 30 5,000,000.00 lbs $ 0.67 63 4,000,000.00 lbs $ 0.75 5 1,000,000.00 lbs $ 2.75 15 1,400,000.00 lbs $ 1.65 5 1,000,000.00 lbs $ 1.56 65 2,000,000.00 Ibs $ 0.75 67 1,500,000.00 lbs $ 0.75 60 1,000,000.00 lbs $ 1.07 29 1,650,000.00 lbs $ 0.62 31 1,675,000.00 lbs $ 0.58 15 300,000.00 lbs $ 1.79 27 725,000.00 lbs $ 0.66 Annual cost $ 8,370,000 $ 24,500,000 $ 24,080,000 $ 16,000,000 $ 11,880,000 $ 9,000,000 $ 8,460,000 $ 8,100,000 $ 5,940,000 $ 4,750,000 $ 4,500,000 $ 4,400,000 $ 4,260,000 $ 3,900,000 $ 3,350,000 $ 3,000,000 $ 2,750,000 $ 2,310,000 $ 1,560,000 $ 1,500,000 $ 1,125,000 $ 1,070,000 $ 1,023,000 $ 971,500 $ 537,000 $ 478,500 70 12 180 9 50 30 120 200 200 14 200 Instructions ABC Cycle count + 27 33 200 23 25 21 x 1025 x 1026 x 1027 x 1028 x 1029 x 1030 x 1031 x 1032 x 1033 x 1091 x 1092 x 1093 lbs Ibs lbs Ibs Ibs lbs lbs lbs lbs lbs lbs 35 37 55 240 0.66 0.68 0.68 0.70 0.66 0.50 0.46 0.35 0.64 0.42 0.62 0.38 0.60 0.34 19 39 180 17 41 15 Ibs Ibs lbs 43 13 0.58 30 Spice 12 31 Spice 13 32 Spice 14 33 Spice 15 34 Spice 16 35 Spice 17 36 Spice 18 37 Spice 19 38 Spice 20 39 Vegetable 1 40 Vegetable 2 41 Vegetable 3 42 Vegetable 4 43 Vegetable 5 44 Frozen beef 45 Fresh beef 46 Deboned chicken 47 Dry food ingredient 1 48 Dry food ingredient 2 49 Dry food ingredient 3 50 Dry food ingredient 4 51 Dry food ingredient 5 52 Dry food ingredient 6 53 Dry food ingredient 7 54 Dry food ingredient 8 55 Dry food ingredient 9 56 Dry food ingredient 10 57 Dry food ingredient 11 58 Dry food ingredient 12 59 Dry food ingredient 13 lbs lbs 725,000.00 700,000.00 675,000.00 600,000.00 550,000.00 725,000.00 750,000.00 975,000.00 525,000.00 775,000.00 500,000.00 800,000.00 475,000.00 825,000.00 450,000.00 850,000.00 425,000.00 950,000.00 875,000.00 400,000.00 900,000.00 375,000.00 350,000.00 925,000.00 210,000.00 200,000.00 1,345.00 78,000.00 24,000.00 1,800.00 Raw material 200 Raw material 200 Raw material Raw material 200 Raw material 200 Raw material 240 Raw material Raw material 150 Raw material 180 Raw material 240 Raw material Raw material 240 Raw material 180 Raw material 240 Raw material 180 Raw material 225 Raw material 180 Raw material 165 Raw material 210 Raw material 180 Raw material 195 Raw material 180 Raw material 180 Raw material 180 Raw material Raw material 180 Raw material 20 Raw material 180 Raw material 180 Raw material 180 nat Instructions ABC Cycle count $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 45 478,500 476,000 459,000 420,000 363,000 362,500 345,000 341,250 336,000 325,500 310,000 304,000 285,000 280,500 261,000 255,000 238,000 237,500 227,500 216,000 198,000 195,000 175,000 166,500 52,500 50,000 37,633 27,300 8,400 4,230 11 x 1094 x 1095 x 1096 x 1097 X 1098 x 1111 x 1112 x 1113 x 1114 x 1115 x 1116 x 1117 x 1118 x 1119 x 1120 x 1121 x 1122 x 1123 Ibs 53 47 9 49 lbs lbs lbs lbs lbs lbs lbs lbs 7 5 0.30 0.56 0.25 0.26 0.54 0.22 0.52 0.50 0.18 0.25 0.25 27.98 0.35 0.35 2.35 51 180 10 10 20 Ibs Ibs lbs 10 10 20 lbs lbs - Jul 14 Materials and packaging + 20 20 180 360 180 140 10 20 2.35 2.01 0.25 2.69 3.37 1.67 0.35 1.33 60 20 20 12 20 10 0.33 20 59 Dry food ingredient 13 60 Dry food ingredient 14 61 Dry food ingredient 15 62 Dry food ingredient 16 63 Dry food ingredient 17 64 Dry food ingredient 18 65 Dry food ingredient 19 66 Dry food ingredient 20 67 Dry food ingredient 21 68 Dry food ingredient 22 69 Dry food ingredient 23 70 Dry food ingredient 24 71 Dry food ingredient 25 72 Dry food ingredient 26 73 Dry food ingredient 27 74 Dry food ingredient 28 75 Dry food ingredient 29 76 Dry food ingredient 30 77 Dry food ingredient 31 78 Dry food ingredient 32 x 1123 x 1124 x 1125 x 1126 x 1127 x 1128 x 1129 x 1130 x 1131 x 1132 x 1133 x 1134 x 1135 x 1136 x 1137 x 1138 x 1139 x 1140 x 1141 x 1142 Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material 1,800.00 lbs 1,600.00 lbs 12,000.00 lbs 1,000.00 lbs 600.00 lbs 798.00 lbs 2,600.00 lbs 658.00 lbs 2,200.00 lbs 200.00 Ibs 1,800.00 lbs 518.00 Ibs 1,200.00 lbs 780.00 lbs 671.00 lbs 378.00 lbs 1,400.00 lbs 1,000.00 Ibs 238.00 lbs 98.00 lbs $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 360 360 360 360 100 360 360 360 360 360 360 360 360 360 360 25 $ 4,230 $ 3,216 $ 3,000 $ 2,690 $ 2,022 $ 1,333 $ 910 $ 875 $ 726 $ 606 $ 558 $ 513 $ 444 $ 304 $ 275 $ 246 $ 224 $ 150 Consolidate 07 42 14 3.03 0.31 0.99 0.37 0.39 25 12 25 0.41 20 25 25 0.65 0.16 0.15 0.45 0.43 25 25 Total of column F Total columns $ 164,790,554 Brief explanation of decisions related to ABC analysis 79 80 81 82 83 84 85 86. Materials and packaging Instructions ABC Cycle count +