2 4 Student Work Area Required: Provide input into cells shaded in yelow in this template. Use mathematical formulas with cel references to the Problem area as indicated 3 E7.7. Using Excel to Calculate Overhead Rates PROBLEM 5 Eric Parker has been studying his department's profitability reports for the past six 6 months. He has just completed a managerial accounting course and is beginning to 7 question the company's approach to allocating overhead to products based on 8 machine hours. The current department overhead budget is based on machine hours In an initial analysis of overhead costs, Eric has identified the following overhead 10 budget amounts and activity cost pools a. Traditional overhead rate per 11 12 13 Estimated machine hours 45,340 b. Product assembly Machine setup and calibration Product inspection Raw materials storage per per per per 14 15 Cost Pool Expected Product assembly Machine setup and calibration Product inspection Raw materials storage 16 17 18 Expected Cost $ 680,000 320,000 67,500 66,000 $ 1133 500 Expected Activities 40,000 machine hours 5,000 setups 1,500 batches 300,000 pounds 19 20 21 a. Calculate the company's traditional overhead rate based on machine hours b. Calculate the company's overhead rates using the proposed activity based costing pools 23 24