Formula: Divide; Cell Referencing

Some cells are merged.

E7.7 - Using Excel to Calculate Overhead Rates

Student Work Area

PROBLEM

Required: Provide input into cells shaded in yellow in this template. Use mathematical formulas with cell references to the Problem area as indicated.

Eric Parker has been studying his department's profitability reports for the past six months. He has just completed a managerial accounting course and is beginning to question the company's approach to allocating overhead to products based on 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 budget amounts and activity cost pools.

a.

Calculate the company's traditional overhead rate based on machine hours.

Traditional overhead rate

?

Estimated machine hours used to allocate in current budget

45,340

b.

Calculate the company's overhead rates using the proposed activity-based costing pools.

Cost Pool Expected

Expected Cost

Expected Activities

Product assembly

$ 680,000

40,000

machine hours

Product assembly

?

Machine setup and calibration

320,000

5,000

setups

Machine setup and calibration

?

Product inspection

67,500

1,500

batches

Product inspection

?

Raw materials storage

66,000

300,000

pounds

Raw materials storage

?

$ 1,133,500