kau PHD cycle count excel sheet

NOTE: The final ABC analysis only applies to Raw 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 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 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 C items 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 42 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 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 4.0% of run time is lost to short shifts and unplanned changes. Studies suggest than 2.5% 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 (count each item once per year only) and you will get 50% on this problem assuming the cost to count is correct. Raw materials and packaging Description 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 Spice 18 Spice 19 Spice 20 Tray 1 Tray 2 Tray 3 Carton 1 Carton 2 Item number x 1000 x 1001 x 1002 x 1003 x 1004 x 1005 x 1006 x 1007 x 1008 x 1009 x 1010 x 1011 x 1012 x 1013 x 1014 x 1015 x 1016 x 1017 x 1018 x 1019 x 1020 x 1021 x 1022 x 1023 x 1024 x 1025 x 1026 x 1027 x 1028 x 1029 x 1030 x 1031 x 1032 x 1033 x 1034 x 1035 x 1036 x 1037 x 1038 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 Raw material Raw material Raw material Packaging Packaging Packaging Packaging Packaging Shelf life (days) 7 10 9 14 14 14 12 14 12 180 180 180 180 180 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 180 140 100 60 20 360 360 360 360 360 Carton 3 Carton 4 Carton 5 Carton 6 Carton 7 Carton 8 Carton 9 Carton 10 Carton 11 Carton 12 Carton 13 Carton 14 Carton 15 Carton 16 Carton 17 Carton 18 Carton 19 Carton 20 Carton 21 Carton 22 Carton 23 Carton 24 Carton 25 Carton 26 Carton 27 Carton 28 Carton 29 Carton 30 Carton 31 Carton 32 Carton 33 Carton 34 Carton 35 Carton 36 Carton 37 Carton 38 Carton 39 Carton 40 Carton 41 Carton 42 Carton 43 Carton 44 Carton 45 Shipping case 1 Shipping case 2 Shipping case 3 Shipping case 4 x 1039 x 1040 x 1041 x 1042 x 1043 x 1044 x 1045 x 1046 x 1047 x 1048 x 1049 x 1050 x 1051 x 1052 x 1053 x 1054 x 1055 x 1056 x 1057 x 1058 x 1059 x 1060 x 1061 x 1062 x 1063 x 1064 x 1065 x 1066 x 1067 x 1068 x 1069 x 1070 x 1071 x 1072 x 1073 x 1074 x 1075 x 1076 x 1077 x 1078 x 1079 x 1080 x 1081 x 1082 x 1083 x 1084 x 1085 Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 360 120 120 120 120 Shipping case 5 Shipping case 6 Film 1 Film 2 Film 3 Vegetable 1 Vegetable 2 Vegetable 3 Vegetable 4 Vegetable 5 Frozen beef Fresh beef Deboned chicken Labels (shipping) 1 Labels (shipping) 2 Labels (shipping) 3 Labels (shipping) 4 Labels (shipping) 5 Labels (shipping) 6 Labels (shipping) 7 Labels (shipping) 8 Labels (shipping) 9 Labels (shipping) 10 Labels work in process 1 Dry food ingredient 1 Dry food ingredient 2 Dry food ingredient 3 Dry food ingredient 4 Dry food ingredient 5 Dry food ingredient 6 Dry food ingredient 7 Dry food ingredient 8 Dry food ingredient 9 Dry food ingredient 10 Dry food ingredient 11 Dry food ingredient 12 Dry food ingredient 13 Dry food ingredient 14 Dry food ingredient 15 Dry food ingredient 16 Dry food ingredient 17 Dry food ingredient 18 Dry food ingredient 19 Dry food ingredient 20 Dry food ingredient 21 Dry food ingredient 22 Dry food ingredient 23 x 1086 x 1087 x 1088 x 1089 x 1090 x 1091 x 1092 x 1093 x 1094 x 1095 x 1096 x 1097 x 1098 x 1099 x 1100 x 1101 x 1102 x 1103 x 1104 x 1105 x 1106 x 1107 x 1108 x 1109 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 x 1124 x 1125 x 1126 x 1127 x 1128 x 1129 x 1130 x 1131 x 1132 x 1133 Packaging Packaging Packaging Packaging Packaging Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging Packaging 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 120 120 360 360 360 120 120 120 120 120 180 14 10 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 200 200 200 200 200 200 200 240 240 240 240 240 225 210 195 Dry food ingredient 24 Dry food ingredient 25 Dry food ingredient 26 Dry food ingredient 27 Dry food ingredient 28 Dry food ingredient 29 Dry food ingredient 30 Dry food ingredient 31 Dry food ingredient 32 Labels work in process 2 x 1134 x 1135 x 1136 x 1137 x 1138 x 1139 x 1140 x 1141 x 1142 x 1110 Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Raw material Packaging Brief explanation of decisions related to ABC analysis 180 165 150 130 110 90 70 50 30 180 rials and packaging Lead time (days) Unit of measure(uom) Annual usage 5 3,000,000.00 lbs 5 5,500,000.00 lbs 5 1,000,000.00 lbs 5 2,000,000.00 lbs 5 5,000,000.00 lbs 5 1,000,000.00 lbs 5 1,000,000.00 lbs 5 8,000,000.00 lbs 5 2,000,000.00 lbs 10 200,000.00 lbs 10 210,000.00 lbs 10 12,000.00 lbs 10 78,000.00 lbs 10 24,000.00 lbs 25 1,000.00 lbs 25 1,400.00 lbs 25 1,800.00 lbs 10 2,200.00 lbs 12 2,600.00 lbs 25 1,200.00 lbs 12 780.00 lbs 25 671.00 lbs 25 98.00 lbs 25 238.00 lbs 20 378.00 lbs 14 518.00 lbs 20 658.00 lbs 20 798.00 lbs 20 1,600.00 lbs 20 1,800.00 lbs 20 1,000.00 lbs 20 200.00 lbs 20 600.00 lbs 20 1,345.00 lbs 21 120,000,000.00 each 21 80,000,000.00 each 21 40,000,000.00 each 21 2,000,000.00 each 21 3,000,000.00 each Cost per uom $ 2.79 $ 2.16 $ 1.56 $ 4.00 $ 3.50 $ 4.75 $ 2.75 $ 3.01 $ 4.50 $ 0.25 $ 0.25 $ 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 $ 0.99 $ 1.33 $ 1.67 $ 2.01 $ 2.35 $ 2.69 $ 3.03 $ 3.37 $ 27.98 $ 0.25 $ 0.25 $ 0.25 $ 0.15 $ 0.15 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 21 5 5 5 5 15,000,000.00 20,000,000.00 1,500,000.00 2,000,000.00 2,500,000.00 3,000,000.00 3,500,000.00 4,000,000.00 4,500,000.00 5,000,000.00 5,500,000.00 6,000,000.00 6,500,000.00 7,000,000.00 7,500,000.00 8,000,000.00 8,500,000.00 9,000,000.00 9,500,000.00 7,500,000.00 9,800,000.00 4,960,000.00 9,400,000.00 9,200,000.00 6,900,000.00 8,800,000.00 8,600,000.00 3,840,000.00 4,200,000.00 8,000,000.00 13,800,000.00 5,600,000.00 200,000.00 190,000.00 180,000.00 170,000.00 4,160,000.00 155,000.00 385,000.00 130,000.00 120,000.00 110,000.00 100,000.00 3,000,000.00 4,000,000.00 2,500,000.00 9,000,000.00 each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each each $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.20 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.75 0.75 0.75 0.75 5 5 21 21 21 30 30 30 30 60 15 15 15 14 14 14 14 14 14 14 14 14 14 14 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49 900,000.00 600,000.00 200,000,000.00 35,000,000.00 5,000,000.00 30,000,000.00 18,000,000.00 5,000,000.00 6,000,000.00 1,000,000.00 1,400,000.00 300,000.00 3,000,000.00 1,000,000.00 1,000,000.00 2,500,000.00 1,000,000.00 720,000.00 50,000.00 10,000.00 1,000,000.00 1,000,000.00 1,000,000.00 5,000,000.00 350,000.00 375,000.00 400,000.00 425,000.00 450,000.00 475,000.00 500,000.00 525,000.00 550,000.00 675,000.00 600,000.00 725,000.00 1,650,000.00 1,675,000.00 700,000.00 725,000.00 750,000.00 775,000.00 800,000.00 825,000.00 850,000.00 875,000.00 900,000.00 each each each print each print each print lbs lbs lbs lbs lbs lbs lbs lbs each each each each each each each each each each each lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs lbs $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0.75 1.25 0.05 0.05 0.05 0.27 0.47 0.67 0.71 1.07 1.65 1.79 1.98 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.10 0.45 0.50 0.52 0.54 0.56 0.58 0.60 0.62 0.64 0.66 0.68 0.70 0.66 0.62 0.58 0.68 0.50 0.46 0.42 0.38 0.34 0.30 0.26 0.22 51 53 55 57 59 61 63 65 67 14 Total of column F 925,000.00 950,000.00 975,000.00 10,000,000.00 8,000,000.00 6,000,000.00 4,000,000.00 2,000,000.00 1,500,000.00 2,500,000.00 lbs lbs lbs lbs lbs lbs lbs lbs lbs each $ $ $ $ $ $ $ $ $ $ 0.18 0.25 0.35 0.45 0.55 0.65 0.75 0.75 0.75 0.35 Annual cost ABC Number Total cost to Total of counts Frequency count hours used per year Total columns Blue cells are optional but may help with calculations in K-N.

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.