Chapter_6_Applying_Excel.xls
Sales 95,200$
Costs:
Direct materials 28,800$
Direct labor 8,000
Manufacturing overhead 80,000 116,800
Traditional costing product margin (21,600)$
Page 8
Chapter_6_Applying_Excel.xls
Sales 63,600$
Costs:
Direct materials 14,800$
Direct labor 7,200
Manufacturing overhead 40,000 62,000
Traditional costing product margin 1,600$
Chapter_6_Applying_Excel.xls
Chapter 6: Applying Excel
Data
Manufacturing overhead $500,000
Selling and administrative overhead $300,000
Assembling
Units
Processing
Orders
Supporting
Customers
Other
Manufacturing overhead 50% 35% 5% 10%
Selling and administrative overhead 10% 30% 40% 20%
Total activity 1,000 250 100
units orders customers
OfficeMart orders:
Customers 1 customer
Orders 20 orders
Number of filing cabinets ordered in total 80 units
Selling price $795
Direct materials $185
Direct labor $90
Enter a formula into each of the cells marked with a ? below
Review Problem: Activity-Based Costing
Perform the first stage allocations
Assembling
Units
Processing
Orders
Supporting
Customers
Other Total
Manufacturing overhead 250,000$ 175,000$ 25,000$ 50,000$ 500,000$
Selling and administrative overhead 30,000 90,000 120,000 60,000 300,000
Total cost 280,000$ 265,000$ 145,000$ 110,000$ 800,000$
Compute the activity rates
Activity Cost Pools Total Cost
Assembling units $280,000 1,000 units $280 per unit
Processing orders $265,000 250 orders $1,060 per order
Supporting customers $145,000 100 customers $1,450 per customer
Compute the overhead cost attributable to the OfficeMart orders
Activity Cost Pools ABC Cost
Assembling units $280 per unit 80 units $22,400
Processing orders $1,060 per order 20 orders $21,200
Supporting customers $1,450 per customer 1 customer $1,450
Sales 63,600$
Costs:
Direct materials 14,800$
Direct labor 7,200
Unit-related overhead 22,400
Order-related overhead 21,200
Customer-related overhead 1,450 67,050
Customer margin (3,450)$
Manufacturing overhead $500,000
Total activity 1,000 units
Manufacturing overhead per unit $500 per unit
Total Activity
Activity Rate
Activity Rate
Activity
Determine the customer margin for the OfficeMart orders under Activity-Based Costing
Determine the product margin for the OfficeMart orders under a traditional cost system
Page 11
Chapter_6_Applying_Excel.xls
Sales 63,600$
Costs:
Direct materials 14,800$
Direct labor 7,200
Manufacturing overhead 40,000 62,000
Traditional costing product margin 1,600$