The goal of this portion of the assignment is to conduct an SKU rationalization to address the capacity issue from the Statement of Facts document in Content/Material for Final Project/Problems in D2L. You will need to conduct an analysis of the Finished Goods Before and determine which if any of the SKUs are candidates to be eliminated. I would sort the data provided by cases from highest to lowest before starting on the rationalization. All the yellow boxes on Finished goods before must be populated. Highlight the SKUs you are considering for elimination in red. On the Finished goods after tab you will adjust the numbers in columns C and F to reflect the increased productivity from the results of your ABC analysis and from the removal of SKUS. Mark the sku to be removed in red and make the cases 0. All yellow boxes must be populated. Again I would sort by Cases. Totals for Cases and Productivity in Finished Goods After should all be higher than the original numbers. Your findings and conclusions will be summarized in the executive summary. 1 Finished goods Before Set up hours required Required line hours Required shifts minutes number Shifts per Days per weeks per line Shifts per of lines day week per year shift year 4 2 5.29 50 450 2116 Set up time per case (minutes) 0.05035 0.03742 0.00000 0.00000 0.09099 0.05715 avg cases per Efficiency line hour factor 1160 0.870 1200 0.900 1333 1.000 1333 1.000 1050 0.788 1140 0.855 - 0.04377 1180 0.885 0.915 1220 0.03127 0.03127 0.03127 1220 1220 - 0.915 0.915 0.915 0.915 0.03127 1220 1220 0.03127 0.06966 0.02532 1105 0.829 0.930 1240 - 2 SKU 3 FG 1037 4 4 FG 1038 5 FG 1039 6 FG 1040 7 FG 1041 8 FG 1042 9 FG 1043 10 FG 1044a 11 FG 1045 12 FG 1046 13 FG 1047 14 FG 1048 15 FG 1049a 16 FG 1050 17 FG 1051 18 FG 1052 8 19 FG 1053 20 FG 1054 21 FG 1055 2 FG 1056 23 FG 1057a 24 FG 1058 25 FG 1059 26 FG 1060 27 FG 1061 8 FG 1062 P9 FG 1063 30 FG 1064 31 FG 1065 2 FG 1066a 33 FG 1067 0.02242 Profit per case (Total Revenue- Total Cost) Cases $ 2.00 $ 2.00 $ 2.00 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 $ 2.25 $ 2.00 $ 1.85 $ 1.95 $ 2.00 $ 2.00 $ $ 2.29 $ 2.00 $ 2.00 $ 2.55 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 166,667 250,000 1,250,000 1,666,667 125,000 166,667 208,333 250,000 291,667 333,333 375,000 416,667 458,333 500,000 541,667 583,333 625,000 666,667 708,333 750,000 791,667 625,000 816,667 413,333 783,333 766,667 575,000 733,333 716,667 320,000 350,000 1250 1270 1300 1310 0.01675 line hrs per case 0.000862069 0.000833333 0.000750188 0.000750188 0.000952381 0.000877193 0.000847458 0.000819672 0.000819672 0.000819672 0.000819672 0.000819672 0.000904977 0.000806452 0.0008 0.000787402 0.000769231 0.000763359 0.000793651 0.000754717 0.000784314 0.00078125 0.000909091 0.000819672 0.000754717 0.000754717 0.000793651 0.000754717 0.000803213 0.000819672 0.000819672 0.938 0.953 0.975 0.983 0.945 0.994 0.956 0.960 0.825 0.915 0.994 0.00857 0.00593 0.01956 0.00204 0.01536 0.01398 0.07151 0.03127 0.00204 0.00204 0.01956 0.00204 - 1260 1325 1275 1280 1100 1220 1325 - 1325 1260 0.994 0.945 1325 0.994 0.02386 1245 0.934 - 0.03127 1220 0.915 0.03127 1220 0.915 Set up Effective Capacity cases per hour hours required Required line hours Required shifts minutes number Shifts per Days per weeks per line Shifts per of lines day week per year shift year 4 2 2 5.29 50 450 2116 1333.0 Set up time per case (minutes) 0.05035 0.03742 0.00000 0.00000 0.09099 0.05715 avg cases per Efficiency line hour factor 1160 0.870 1200 0.900 1333 1333 1.000 1050 0.788 1140 0.855 1.000 0.04377 0.885 1180 1220 1220 1220 1220 0.03127 0.03127 0.03127 0.03127 0.03127 0.06966 0.02532 0.02242 1220 1105 1240 Profit per case (Total Revenue Total Cost) Cases $ 2.00 $ 2.00 $ 2.00 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 $ 2.25 $ 2.00 $ $ 1.85 $ 1.95 $ 2.00 $ 2.00 $ $ 2.29 $ 2.00 $ 2.00 $ 2.55 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 SKU FG 1037 FG 1038 5 FG 1039 FG 1040 FG 1041 3 FG 1042 FG 1043 0 FG 1044a 1 FG 1045 2 FG 1046 3 FG 1047 4 FG 1048 5 FG 1049a 6 FG 1050 7 FG 1051 8 FG 1052 9 FG 1053 0 FG 1054 1 FG 1055 2 FG 1056 3 FG 1057a 4 FG 1058 5 FG 1059 6 FG 1060 7 FG 1061 8 FG 1062 9 FG 1063 0 FG 1064 1 FG 1065 2 FG 1066 3 FG 1067 1250 166,667 250,000 1,250,000 1,666,667 125,000 166,667 208,333 250,000 291,667 333,333 375,000 416,667 458,333 500,000 541,667 583,333 625,000 666,667 708,333 750,000 791,667 625,000 816,667 413,333 783,333 766,667 575,000 733,333 716,667 320,000 350,000 0.915 0.915 0.915 0.915 0.915 0.829 0.930 0.938 0.953 0.975 0.983 line hrs per case 0.000862069 0.000833333 0.000750188 0.000750188 0.000952381 0.000877193 0.000847458 0.000819672 0.000819672 0.000819672 0.000819672 0.000819672 0.000904977 0.000806452 0.0008 0.000787402 0.000769231 0.000763359 0.000793651 0.000754717 0.000784314 0.00078125 0.000909091 0.000819672 0.000754717 0.000754717 0.000793651 0.000754717 0.000803213 0.000819672 0.000819672 0.01675 0.00857 0.00593 1270 1300 1310 1260 0.945 0.01956 0.00204 0.01536 1325 1275 0.01398 1280 0.994 0.956 0.960 0.825 0.915 0.994 1100 0.07151 0.03127 1220 0.00204 1325 0.00204 0.994 1325 1260 0.01956 1325 1245 0.00204 0.02386 0.03127 0.03127 0.945 0.994 0.934 0.915 1220 1220 0.915 The goal of this portion of the assignment is to conduct an SKU rationalization to address the capacity issue from the Statement of Facts document in Content/Material for Final Project/Problems in D2L. You will need to conduct an analysis of the Finished Goods Before and determine which if any of the SKUs are candidates to be eliminated. I would sort the data provided by cases from highest to lowest before starting on the rationalization. All the yellow boxes on Finished goods before must be populated. Highlight the SKUs you are considering for elimination in red. On the Finished goods after tab you will adjust the numbers in columns C and F to reflect the increased productivity from the results of your ABC analysis and from the removal of SKUS. Mark the sku to be removed in red and make the cases 0. All yellow boxes must be populated. Again I would sort by Cases. Totals for Cases and Productivity in Finished Goods After should all be higher than the original numbers. Your findings and conclusions will be summarized in the executive summary. 1 Finished goods Before Set up hours required Required line hours Required shifts minutes number Shifts per Days per weeks per line Shifts per of lines day week per year shift year 4 2 5.29 50 450 2116 Set up time per case (minutes) 0.05035 0.03742 0.00000 0.00000 0.09099 0.05715 avg cases per Efficiency line hour factor 1160 0.870 1200 0.900 1333 1.000 1333 1.000 1050 0.788 1140 0.855 - 0.04377 1180 0.885 0.915 1220 0.03127 0.03127 0.03127 1220 1220 - 0.915 0.915 0.915 0.915 0.03127 1220 1220 0.03127 0.06966 0.02532 1105 0.829 0.930 1240 - 2 SKU 3 FG 1037 4 4 FG 1038 5 FG 1039 6 FG 1040 7 FG 1041 8 FG 1042 9 FG 1043 10 FG 1044a 11 FG 1045 12 FG 1046 13 FG 1047 14 FG 1048 15 FG 1049a 16 FG 1050 17 FG 1051 18 FG 1052 8 19 FG 1053 20 FG 1054 21 FG 1055 2 FG 1056 23 FG 1057a 24 FG 1058 25 FG 1059 26 FG 1060 27 FG 1061 8 FG 1062 P9 FG 1063 30 FG 1064 31 FG 1065 2 FG 1066a 33 FG 1067 0.02242 Profit per case (Total Revenue- Total Cost) Cases $ 2.00 $ 2.00 $ 2.00 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 $ 2.25 $ 2.00 $ 1.85 $ 1.95 $ 2.00 $ 2.00 $ $ 2.29 $ 2.00 $ 2.00 $ 2.55 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 166,667 250,000 1,250,000 1,666,667 125,000 166,667 208,333 250,000 291,667 333,333 375,000 416,667 458,333 500,000 541,667 583,333 625,000 666,667 708,333 750,000 791,667 625,000 816,667 413,333 783,333 766,667 575,000 733,333 716,667 320,000 350,000 1250 1270 1300 1310 0.01675 line hrs per case 0.000862069 0.000833333 0.000750188 0.000750188 0.000952381 0.000877193 0.000847458 0.000819672 0.000819672 0.000819672 0.000819672 0.000819672 0.000904977 0.000806452 0.0008 0.000787402 0.000769231 0.000763359 0.000793651 0.000754717 0.000784314 0.00078125 0.000909091 0.000819672 0.000754717 0.000754717 0.000793651 0.000754717 0.000803213 0.000819672 0.000819672 0.938 0.953 0.975 0.983 0.945 0.994 0.956 0.960 0.825 0.915 0.994 0.00857 0.00593 0.01956 0.00204 0.01536 0.01398 0.07151 0.03127 0.00204 0.00204 0.01956 0.00204 - 1260 1325 1275 1280 1100 1220 1325 - 1325 1260 0.994 0.945 1325 0.994 0.02386 1245 0.934 - 0.03127 1220 0.915 0.03127 1220 0.915 Set up Effective Capacity cases per hour hours required Required line hours Required shifts minutes number Shifts per Days per weeks per line Shifts per of lines day week per year shift year 4 2 2 5.29 50 450 2116 1333.0 Set up time per case (minutes) 0.05035 0.03742 0.00000 0.00000 0.09099 0.05715 avg cases per Efficiency line hour factor 1160 0.870 1200 0.900 1333 1333 1.000 1050 0.788 1140 0.855 1.000 0.04377 0.885 1180 1220 1220 1220 1220 0.03127 0.03127 0.03127 0.03127 0.03127 0.06966 0.02532 0.02242 1220 1105 1240 Profit per case (Total Revenue Total Cost) Cases $ 2.00 $ 2.00 $ 2.00 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 $ 2.25 $ 2.00 $ $ 1.85 $ 1.95 $ 2.00 $ 2.00 $ $ 2.29 $ 2.00 $ 2.00 $ 2.55 $ $ 2.00 $ 2.00 $ 2.00 $ 2.00 $ 2.00 SKU FG 1037 FG 1038 5 FG 1039 FG 1040 FG 1041 3 FG 1042 FG 1043 0 FG 1044a 1 FG 1045 2 FG 1046 3 FG 1047 4 FG 1048 5 FG 1049a 6 FG 1050 7 FG 1051 8 FG 1052 9 FG 1053 0 FG 1054 1 FG 1055 2 FG 1056 3 FG 1057a 4 FG 1058 5 FG 1059 6 FG 1060 7 FG 1061 8 FG 1062 9 FG 1063 0 FG 1064 1 FG 1065 2 FG 1066 3 FG 1067 1250 166,667 250,000 1,250,000 1,666,667 125,000 166,667 208,333 250,000 291,667 333,333 375,000 416,667 458,333 500,000 541,667 583,333 625,000 666,667 708,333 750,000 791,667 625,000 816,667 413,333 783,333 766,667 575,000 733,333 716,667 320,000 350,000 0.915 0.915 0.915 0.915 0.915 0.829 0.930 0.938 0.953 0.975 0.983 line hrs per case 0.000862069 0.000833333 0.000750188 0.000750188 0.000952381 0.000877193 0.000847458 0.000819672 0.000819672 0.000819672 0.000819672 0.000819672 0.000904977 0.000806452 0.0008 0.000787402 0.000769231 0.000763359 0.000793651 0.000754717 0.000784314 0.00078125 0.000909091 0.000819672 0.000754717 0.000754717 0.000793651 0.000754717 0.000803213 0.000819672 0.000819672 0.01675 0.00857 0.00593 1270 1300 1310 1260 0.945 0.01956 0.00204 0.01536 1325 1275 0.01398 1280 0.994 0.956 0.960 0.825 0.915 0.994 1100 0.07151 0.03127 1220 0.00204 1325 0.00204 0.994 1325 1260 0.01956 1325 1245 0.00204 0.02386 0.03127 0.03127 0.945 0.994 0.934 0.915 1220 1220 0.915