NOTE: The final ABC analysis only applies to Materials. After the initially sorting Packaging may be deleted or just ignored. 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 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 anlaysis 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 Citems 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 27 minutes to complete using one employee that is paid $18.75 per hour with a fully loaded labor rate with fringes of $25.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 will be included in the Executive Summary and should include a chart, table or graph similar to the examples in the powerpoint The Finance and Accounting Departments of your company remind you a rule exists that states that every item whether a material or a finished good must be counted at least twice 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 as long as you calcualate the amount of time spent on counting correctly. You also will not reduce downtime by 1% but will only save .25%. kaw materials and packaging Total hours used Number of counts per year Shelf life (days) Total cost Frequency to count Annual usage Lead time (days) 7 7 5 10 5 Item number x 1000 x x 1001 x 1002 x 1003 x 1004 9 5 14 5 14 5 x 1005 14 5 12 5 14 5 12 x 1006 x 1007 x 1008 x 1009 x 1010 x 1011 x 1012 5 10 Annual cost ABC $ 6,870,000 $ 13,720,000 $ 1,560,000 $ 32,000,000 $ 52,500,000 $ 4,750,000 $ 2,750,000 $ 40,800,000 $ 9,000,000 $ 50,000 $ 52,500 $ 3,000 $ 27,300 $ 8,400 $ 150 $ 224 180 180 10 180 10 10 Cost per uom $ $ 2.29 $ 1.96 $ 1.56 $ 4.00 S 3.50 $ 4.75 $ 2.75 $ 3.40 $ 4.50 $ 0.25 $ 0.25 $ S 0.25 $ 0.35 $ 0.35 $ 0.15 $ 0.16 $ 0.31 $ 0.33 $ $ 0.35 $ 0.37 $ 0.39 $ 0.41 $ $ 0.43 $ 0.45 $ 0.65 180 180 10 Descpription Chicken wings Chicken tenders Boneless dark meat Brisket Chopped beef Ends and pieces Sliced pork Pulled pork Ends and pieces BBQ base 1 BBQ base 2 BBQ base 3 BBQ base 4 BBQ base 5 Spice 1 Spice 2 Spice 3 Spice 4 Spice 5 Spice 6 Spice 7 Spice 8 Spice 9 Spice 10 Spice 11 Spice 12 Spice 13 Spice 14 Spice 15 Spice 16 Spice 17 360 25 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 Raw material Raw material Raw material Raw material Raw material Unit of measure(uom) 3,000,000.00 lbs 7,000,000.00 lbs 1,000,000.00 lbs 8,000,000.00 lbs 15,000,000.00 lbs 1,000,000.00 lbs 1,000,000.00 lbs 12,000,000.00 lbs 2,000,000.00 lbs 200,000.00 lbs 210,000.00 lbs 12,000.00 lbs 78,000.00 lbs 24,000.00 lbs 1,000.00 lbs 1,400.00 lbs 1,800.00 lbs 2,200.00 lbs 2,600.00 lbs 1,200.00 lbs 780.00 lbs 671.00 lbs 98.00 lbs 238.00 lbs 378.00 lbs 518.00 lbs 658.00 lbs 798.00 lbs 1,600.00 lbs 1,800.00 lbs 1,000.00 lbs 360 25 x 1013 x 1014 x 1015 x 1016 x 1017 x 1018 x 1019 x 1020 x 1021 25 558 360 360 10 726 12 910 25 444 12 304 360 360 360 360 360 360 25 275 x 1022 25 42 25 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ x 1023 X 1024 x 1025 107 20 246 360 360 14 0.99 513 x 1026 360 20 875 x 1027 360 20 $ $ $ S $ $ x 1028 360 20 1.33 1.67 2.01 2.35 2.69 1,333 3,216 4,230 2,690 x 1029 20 180 140 x 1030 20