NOTE: The final ABC analysis only applies to Raw Materials. After initially sorting, Packaging should be deleted or separated where it does hot impact the calculations or readability of the final submissions. Sometimes having too much much data can create issues. All yellow poxes on Materials and Packaging tab must be populated using an Excel formula. Using material from Skills Development in the 5KU/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 of each item 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 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 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 to count all items. 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 can be used 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 at the end of the semester. 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 elther making a mid shift unplanned changeover or leses the last 15 to 90 minutes of a shift as the chantecover cannot be accomplished in a manner that makes it worthwhile to hold the crew. Estimates are that up to 4.0% of run time is lost to short shifts and unplanned changes. Studles sutgest than 2.5 could be eliminated with a robust cycle count program. Approximately 1X 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 (count each item once per year only) and you will get 50 s on this problem assuming the cost to count is correct