Using the provided Excel spreadsheet, prepare the calculations and costing analysis of Years 5 and 6.

B G 1 K M N o P Students Enter Formulas HERE in illow cells 1 Project 6 Pre-Assignment 2 a 4 4 5 Total bins processed 6 7 8 9 9 Direct Material Requirements Years ww 120,000 AB 30,000 Year 6 WW 90,000 AB 90,000 10 Cubic Yards Cubic Feet 2 Cubic Foot Bags All Wood Bins All Bark Bins 11 20 540 270 0.15 0.3 Dumpster containers hold Temporary Sorting Collection Bins/Baps Raw Material Cost per Cubic Ft Yrs) Raw Material Cost per Cubic Ft Yr6) 12 5 135 67.5 0.1 0.35 13 Year 5 Year6 ww AB WW AE 2.430 14 15 Direct material cost 16 17 $ 18 19 Direct Labor 20 21 DL Hours per Bin (Year 5) 22 DL Hours per Bin Year 6) ww 4 3.8835 AB 0.666666667 0.6472 CAB is 1/6 of ww labor time required 3% increase in productivity > Wage Rate/hr $10.00 24 25 Direct Labor (Years) 26 Direct labor Year 6) 27 28 Labor Workforce Annual wages per Hours per week Weeks per year worker 40 50 40 50 Year 5 Yeart ww AB ww AB 30 31 Total hours needed 22. Total workers needed Part 1. Pre Assignment + Ready Calibri (Body) 11 ' Share Da 29 Wrap Text General Pasta BIU = EEE 2 a A thil TEX Merge & Center y $ % 2 698 K67 Conditional Format as Table A Insert Formatting Call Styles Dalata for Format Sort A Filter D E F G 1 WK L N o P R 5 A 33 34 Annual Labor Cost 35 36 Annual Total Labor Cost 37 Years 38 Years 29 Change in Dollars 40 BA Change in percentage 41 42 OVERHEAD 43 44 45 cm Check figure should be $4,281,512 Collection Facility Overhead 45 Collection Facility Foremen Years $45.000,0 Yeart 20 20 2.400,000.00 2.800.000.00 70,000.00 985,520.00 $3,455,520.00 1155 000.00 $3,955 000.00 7 Machine Operators (front-end leader) Other Indirect laborat Collection Facilities Other facility overhead 50 TOTAL 51 52 53 Collection Facility Overhead Rate based on Direct Labor Cost SA Year 5 35 ww Annual Labor Costs 56 AB Annual Labor Costs 32 TOTAL S 5 59 Year 6: 80 WW Annual Labor Costs 61 AB Annual Labor Costs TOTALS Yes Rate Years Collection Facility Overhead Allocation ww AN cm Check figure should be 54,355,520 + Year Rate Year Collection Facility Overhead Allocation ww AN s Check figure should be 54.923.500 58 s Corporate Overhead Allocated to ach Division rate is given in case as Percentage of oL cost Year State 0.82 67 Year ww Part 1 Pre Assignment IEVIEW View Tell me Calibri (Body) 11 - A A == Pasta 5 Wrap Text B T U H General a. Av ===== = K107 A Merge & Center $ Xfx % 9 fall og Format Call Styles Insert Dalate Format B D E 0.89 H 1 K F G Year 6 Corporate Overhead Allocation ww AR L M N o YEARS SUMMARY ww AB 2,430.00 . $ YEAR 6 SUMMARY ww AB $ $ $ $ s $ s 2,430.00 s 20 Year 6 rate 71 72 73 74 SUMMARY 75 26 77 78 Direct material 79 Direct labor BO Collection Facility Overhead $ B1 Division Overhead $ B2 s B3 84 Cost per Bin 85 86 B2 BB 59 199 91 92 93 94 95 95 97 SA 99 100 tos 102 03 104 ER B G 1 K M N o P Students Enter Formulas HERE in illow cells 1 Project 6 Pre-Assignment 2 a 4 4 5 Total bins processed 6 7 8 9 9 Direct Material Requirements Years ww 120,000 AB 30,000 Year 6 WW 90,000 AB 90,000 10 Cubic Yards Cubic Feet 2 Cubic Foot Bags All Wood Bins All Bark Bins 11 20 540 270 0.15 0.3 Dumpster containers hold Temporary Sorting Collection Bins/Baps Raw Material Cost per Cubic Ft Yrs) Raw Material Cost per Cubic Ft Yr6) 12 5 135 67.5 0.1 0.35 13 Year 5 Year6 ww AB WW AE 2.430 14 15 Direct material cost 16 17 $ 18 19 Direct Labor 20 21 DL Hours per Bin (Year 5) 22 DL Hours per Bin Year 6) ww 4 3.8835 AB 0.666666667 0.6472 CAB is 1/6 of ww labor time required 3% increase in productivity > Wage Rate/hr $10.00 24 25 Direct Labor (Years) 26 Direct labor Year 6) 27 28 Labor Workforce Annual wages per Hours per week Weeks per year worker 40 50 40 50 Year 5 Yeart ww AB ww AB 30 31 Total hours needed 22. Total workers needed Part 1. Pre Assignment + Ready Calibri (Body) 11 ' Share Da 29 Wrap Text General Pasta BIU = EEE 2 a A thil TEX Merge & Center y $ % 2 698 K67 Conditional Format as Table A Insert Formatting Call Styles Dalata for Format Sort A Filter D E F G 1 WK L N o P R 5 A 33 34 Annual Labor Cost 35 36 Annual Total Labor Cost 37 Years 38 Years 29 Change in Dollars 40 BA Change in percentage 41 42 OVERHEAD 43 44 45 cm Check figure should be $4,281,512 Collection Facility Overhead 45 Collection Facility Foremen Years $45.000,0 Yeart 20 20 2.400,000.00 2.800.000.00 70,000.00 985,520.00 $3,455,520.00 1155 000.00 $3,955 000.00 7 Machine Operators (front-end leader) Other Indirect laborat Collection Facilities Other facility overhead 50 TOTAL 51 52 53 Collection Facility Overhead Rate based on Direct Labor Cost SA Year 5 35 ww Annual Labor Costs 56 AB Annual Labor Costs 32 TOTAL S 5 59 Year 6: 80 WW Annual Labor Costs 61 AB Annual Labor Costs TOTALS Yes Rate Years Collection Facility Overhead Allocation ww AN cm Check figure should be 54,355,520 + Year Rate Year Collection Facility Overhead Allocation ww AN s Check figure should be 54.923.500 58 s Corporate Overhead Allocated to ach Division rate is given in case as Percentage of oL cost Year State 0.82 67 Year ww Part 1 Pre Assignment IEVIEW View Tell me Calibri (Body) 11 - A A == Pasta 5 Wrap Text B T U H General a. Av ===== = K107 A Merge & Center $ Xfx % 9 fall og Format Call Styles Insert Dalate Format B D E 0.89 H 1 K F G Year 6 Corporate Overhead Allocation ww AR L M N o YEARS SUMMARY ww AB 2,430.00 . $ YEAR 6 SUMMARY ww AB $ $ $ $ s $ s 2,430.00 s 20 Year 6 rate 71 72 73 74 SUMMARY 75 26 77 78 Direct material 79 Direct labor BO Collection Facility Overhead $ B1 Division Overhead $ B2 s B3 84 Cost per Bin 85 86 B2 BB 59 199 91 92 93 94 95 95 97 SA 99 100 tos 102 03 104 ER