60.1K

Verified Solution

Link Copied!

I just need the formula for set up hours required, required like hours and requires shifts. thanks

A 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. A B D E F G Finis Ooooh 2 SKU FG 1037 FG 1038 FG 1039 FG 1040 7 FG 1041 FG 1042 FG 1043 10 FG 1044a 11 FG 1045 12 FG 1046 13 FG 1047 14 FG 1048 15 FG 10498 16 FG 1050 17 FG 1051 18 FG 1052 19 FG 1053 20 FG 1054 21 FG 1055 22 FG 1056 23 FG 1057a 24FG 1058 25 FG 1059 26 FG 1060 27FG 1061 28 FG 1062 29 FG 1063 30 FG 1064 31 FG 1055 32 FG 1066a 33 FG 1067 34 FG 1068 35 FG 1069 36 FG 1070 37 FG 1071 38 FG 1072a Profit per case (Total Revenue- Total Cost) Cases S 2.00 S 2.00 S 2.00 $ 2.00 $ 2.00 S 2.00 S 2.00 $ 2.00 s 2.00 $ 2.00 $ 200 S 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 S 2.25 S 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 212 2.00 2.00 $ 200 $ 200 Set up time per case (minutes) 166,667 0.05035 250,000 0.03742 1,250,000 0.00000 1,666,667 0.00000 125,000 0.09099 166,667 0.05715 208,333 0.04377 250,000 0.03127 291,667 0.03127 333,333 0.03127 375,000 0.03127 416,667 0.03127 458,333 0.06966 500,000 0.02532 541,667 0.02242 583,333 001675 625,000 0.00857 666,667 0.00593 708,333 0.01956 750,000 0.00204 791,667 0.01586 625,000 0.01398 816,667 0.07151 413,333 003127 783,333 0.00204 766,667 0.00204 575,000 0.01956 733,333 0.00200 716,667 0.02386 320,000 0.03127 350,000 0.03127 666,667 0.00857 1,150,000 0.00000 466,667 0.02827 16,667 0.11242 15,833 0.00076 avg cases per Efficiency line hour Factor line hrs per 1160 0.870 0.00086. 1200 0.900 0.000839 1333 1000 0.000750 1333 1.000 0.000750 1050 0.788 0.000952 1140 0.855 0.0008723 1180 0.885 0.0008474 1220 0915 0.0008196 1220 0915 0.0008196 1220 0.915 0.0008196. 1220 0915 0.00081963 1220 0.915 0.00081967 1105 0.829 0.00090497 1240 0.930 0.00080645 1250 0.938 0.0008 1270 0953 0.000787402 1300 0.975 0.000769231 1310 0.983 0.000763359 1260 01945 0.000793652 1325 0.994 0.000754717 1275 0.956 0.000784314 1280 0.960 0.00078125 1100 085 O 000909091 1220 0915 0.0008 19672 1325 0.994 0.000754717 1325 0.994 0.000754717 1260 0.945 0.000793551 1325 0994 0.000754727 1245 01934 0.000803213 1220 0915 0.000819672 1220 0915 0.000819672 1300 0975 0.000769231 1333 1.000 0.000750188 1230 0.923 00 813008 1000 0.750 0001 1330 0998 0.00075188 USU Finished goods Before Finished goods After instructions SKU rationalizatio F F G H K M N Finished goods Before Set up hours required Required line Required hours shifts number Shifts per Days per We of lines day week per 4 2 5.29 avg cases per Efficiency line hour factor line hrs per case 1160 0.870 0.000862069 1200 0.900 0.000833333 1333 1.000 0.000750188 1333 1.000 0.000750188 1050 0.788 0.000952381 1140 0.855 0.000877193 1180 0.885 0.000847458 1220 0,915 0.000819672 1220 0.915 0.000819672 1220 0.915 0.000819672 1220 0.915 0.000819672 1220 0915 0.000819672 1105 0.829 0.000904977 1240 0.930 0.000806452 1250 0.938 0.0008 1270 0.953 0.000787402 1300 0975 0.000769231 1310 0.983 0.000769359 1260 0.945 0.000793651 1325 0.994 0.000754717 1275 0.956 0.000784314 1280 0.960 0.00078125 1100 0.825 0.000909091 1220 0.915 0.000819672 1325 0.994 0.000754217 1325 0.994 0.000754717 1260 0.945 0.000793651 1325 0.994 0.000754717 1245 0.934 0.000803213 1220 0.915 0.000819672 1220 0.915 0.000819672 1300 0.975 0.000769231 1333 1.000 0.000750188 1230 0.923 0.000813008 1000 0.750 0.001 1330 0998 0.00075188 Instructions SKU rationalizatio M N O P R S re T U ne Required shifts minutes number Shifts per Days per weeks per line of lines day week per year shift year 4 2 5.29 50 450 2116 Shifts per Effective Capacity cases per hour 1333.0 A 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. A B D E F G Finis Ooooh 2 SKU FG 1037 FG 1038 FG 1039 FG 1040 7 FG 1041 FG 1042 FG 1043 10 FG 1044a 11 FG 1045 12 FG 1046 13 FG 1047 14 FG 1048 15 FG 10498 16 FG 1050 17 FG 1051 18 FG 1052 19 FG 1053 20 FG 1054 21 FG 1055 22 FG 1056 23 FG 1057a 24FG 1058 25 FG 1059 26 FG 1060 27FG 1061 28 FG 1062 29 FG 1063 30 FG 1064 31 FG 1055 32 FG 1066a 33 FG 1067 34 FG 1068 35 FG 1069 36 FG 1070 37 FG 1071 38 FG 1072a Profit per case (Total Revenue- Total Cost) Cases S 2.00 S 2.00 S 2.00 $ 2.00 $ 2.00 S 2.00 S 2.00 $ 2.00 s 2.00 $ 2.00 $ 200 S 2.00 $ 1.60 $ 2.00 $ 2.00 $ 2.00 S 2.25 S 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 212 2.00 2.00 $ 200 $ 200 Set up time per case (minutes) 166,667 0.05035 250,000 0.03742 1,250,000 0.00000 1,666,667 0.00000 125,000 0.09099 166,667 0.05715 208,333 0.04377 250,000 0.03127 291,667 0.03127 333,333 0.03127 375,000 0.03127 416,667 0.03127 458,333 0.06966 500,000 0.02532 541,667 0.02242 583,333 001675 625,000 0.00857 666,667 0.00593 708,333 0.01956 750,000 0.00204 791,667 0.01586 625,000 0.01398 816,667 0.07151 413,333 003127 783,333 0.00204 766,667 0.00204 575,000 0.01956 733,333 0.00200 716,667 0.02386 320,000 0.03127 350,000 0.03127 666,667 0.00857 1,150,000 0.00000 466,667 0.02827 16,667 0.11242 15,833 0.00076 avg cases per Efficiency line hour Factor line hrs per 1160 0.870 0.00086. 1200 0.900 0.000839 1333 1000 0.000750 1333 1.000 0.000750 1050 0.788 0.000952 1140 0.855 0.0008723 1180 0.885 0.0008474 1220 0915 0.0008196 1220 0915 0.0008196 1220 0.915 0.0008196. 1220 0915 0.00081963 1220 0.915 0.00081967 1105 0.829 0.00090497 1240 0.930 0.00080645 1250 0.938 0.0008 1270 0953 0.000787402 1300 0.975 0.000769231 1310 0.983 0.000763359 1260 01945 0.000793652 1325 0.994 0.000754717 1275 0.956 0.000784314 1280 0.960 0.00078125 1100 085 O 000909091 1220 0915 0.0008 19672 1325 0.994 0.000754717 1325 0.994 0.000754717 1260 0.945 0.000793551 1325 0994 0.000754727 1245 01934 0.000803213 1220 0915 0.000819672 1220 0915 0.000819672 1300 0975 0.000769231 1333 1.000 0.000750188 1230 0.923 00 813008 1000 0.750 0001 1330 0998 0.00075188 USU Finished goods Before Finished goods After instructions SKU rationalizatio F F G H K M N Finished goods Before Set up hours required Required line Required hours shifts number Shifts per Days per We of lines day week per 4 2 5.29 avg cases per Efficiency line hour factor line hrs per case 1160 0.870 0.000862069 1200 0.900 0.000833333 1333 1.000 0.000750188 1333 1.000 0.000750188 1050 0.788 0.000952381 1140 0.855 0.000877193 1180 0.885 0.000847458 1220 0,915 0.000819672 1220 0.915 0.000819672 1220 0.915 0.000819672 1220 0.915 0.000819672 1220 0915 0.000819672 1105 0.829 0.000904977 1240 0.930 0.000806452 1250 0.938 0.0008 1270 0.953 0.000787402 1300 0975 0.000769231 1310 0.983 0.000769359 1260 0.945 0.000793651 1325 0.994 0.000754717 1275 0.956 0.000784314 1280 0.960 0.00078125 1100 0.825 0.000909091 1220 0.915 0.000819672 1325 0.994 0.000754217 1325 0.994 0.000754717 1260 0.945 0.000793651 1325 0.994 0.000754717 1245 0.934 0.000803213 1220 0.915 0.000819672 1220 0.915 0.000819672 1300 0.975 0.000769231 1333 1.000 0.000750188 1230 0.923 0.000813008 1000 0.750 0.001 1330 0998 0.00075188 Instructions SKU rationalizatio M N O P R S re T U ne Required shifts minutes number Shifts per Days per weeks per line of lines day week per year shift year 4 2 5.29 50 450 2116 Shifts per Effective Capacity cases per hour 1333.0

Answer & Explanation Solved by verified expert

See Answer