Link to Google drive with Excel file:

https://drive.google.com/drive/folders/13vrAJsqL3Fn0I_p6AZzNj5KyE0lZSgli?usp=share_link

Project 4 Part 1 Students Enter Formulas HERE in yellow cells Direct Labor OVERHEAD Collection Facility Overhead SUMMARY Cost per Bin Table 1: Year 5 Actual Results by Product Operated at approximately 95% of facility capacity Table 2: Year 5 Actual Costs *Each full-time equivalent employee works 40 hours per week and 50 weeks per year. Direct labor is traced to each type of product (AB or WW) for costing purposes. However, the salaries of facility managers and the wages of machine operators are included as part of total collection facility overhead, which is allocated to AB and WW using direct labor cost as an allocation base. Total collection facility overhead also includes some "Other Indirect Labor", which totaled $70,000 in Year 5. "Other Indirect Labor" costs are fixed with respect to production. MEM has one additional category of collection facility overhead called "Other Facility Overhead" that includes repairs, utilities, insurance, but the largest component of this category is depreciation on machinery and buildings. "Other Facility Overhead" totaled $985,520 in Year 5. In addition to facility overhead, each collection facility is allocated a share of corporate-level overhead which includes the cost of executive salaries as well as centralized support services (such as human resources and accounting). Corporate overhead is allocated to divisions on the basis of direct labor cost. The MEM division further allocates its share of corporate overhead to collection facilities and manufacturing plants based on their respective direct labor costs. The allocation rate for corporate overhead in Year 5 was 82% of direct labor cost. In February of Year 7, in advance of Year 6 performance evaluations for division managers, The Mulch Company's COO was reviewing cost data prepared by the company's controller for MEM's collection facilities. In addition to the summary report for the 20 collection facilities, the controller provided the following information regarding Year 6 : - The price paid for a usable cubic yard of WW material decreased by 1/3 but the premium paid for each cubic yard of AB material increased to 250% of WW prices. - As of January 1, Year 6 , all wage rates and salaries were increased by 5% across the board at all collection facilities. Table 3: Year 6 Results Other data to assist with analysis of this case: Table 4: Containers Table 5: Raw Material Costs per Cubic Ft Table 6: Direct Labor Hours per Bin Project 4 Part 1 Students Enter Formulas HERE in yellow cells Direct Labor OVERHEAD Collection Facility Overhead SUMMARY Cost per Bin Table 1: Year 5 Actual Results by Product Operated at approximately 95% of facility capacity Table 2: Year 5 Actual Costs *Each full-time equivalent employee works 40 hours per week and 50 weeks per year. Direct labor is traced to each type of product (AB or WW) for costing purposes. However, the salaries of facility managers and the wages of machine operators are included as part of total collection facility overhead, which is allocated to AB and WW using direct labor cost as an allocation base. Total collection facility overhead also includes some "Other Indirect Labor", which totaled $70,000 in Year 5. "Other Indirect Labor" costs are fixed with respect to production. MEM has one additional category of collection facility overhead called "Other Facility Overhead" that includes repairs, utilities, insurance, but the largest component of this category is depreciation on machinery and buildings. "Other Facility Overhead" totaled $985,520 in Year 5. In addition to facility overhead, each collection facility is allocated a share of corporate-level overhead which includes the cost of executive salaries as well as centralized support services (such as human resources and accounting). Corporate overhead is allocated to divisions on the basis of direct labor cost. The MEM division further allocates its share of corporate overhead to collection facilities and manufacturing plants based on their respective direct labor costs. The allocation rate for corporate overhead in Year 5 was 82% of direct labor cost. In February of Year 7, in advance of Year 6 performance evaluations for division managers, The Mulch Company's COO was reviewing cost data prepared by the company's controller for MEM's collection facilities. In addition to the summary report for the 20 collection facilities, the controller provided the following information regarding Year 6 : - The price paid for a usable cubic yard of WW material decreased by 1/3 but the premium paid for each cubic yard of AB material increased to 250% of WW prices. - As of January 1, Year 6 , all wage rates and salaries were increased by 5% across the board at all collection facilities. Table 3: Year 6 Results Other data to assist with analysis of this case: Table 4: Containers Table 5: Raw Material Costs per Cubic Ft Table 6: Direct Labor Hours per Bin