Question

2. Assume that OfficeMart places orders more frequently, but everything else remains the same. On your worksheet increase the number of orders from 4 to 20. (Prior to completing this requirement, change all worksheet figures back to their original amounts.)

Answer 1

Chapter 7: 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%	45%	25%	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	$595
Direct materials	$180
Direct labor	$50
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	$135,000	$75,000	$60,000	$300,000
Total cost	$280,000	$310,000	$100,000	$110,000	$800,000
Compute the activity rates
Activity Cost Pools	Total Cost	Total Activity		Activity Rate
Assembling units	$280,000	1,000	units	$280	per unit
Processing orders	$310,000	250	orders	$1,240	per order
Supporting customers	$100,000	100	customers	$1,000	per customer
Compute the overhead cost attributable to the OfficeMart orders
Activity Cost Pools	Activity Rate		Activity		ABC Cost
Assembling units	$280	per unit	80	units	$22,400
Processing orders	$1,240	per order	20	orders	$24,800
Supporting customers	$1,000	per customer	1	customer	$1,000
Determine the customer margin for the OfficeMart orders under Activity-Based Costing
Sales		$47,600
Costs:
Direct materials	$14,400
Direct labor	$4,000
Unit-related overhead	$22,400
Order-related overhead	$24,800
Customer-related overhead	$1,000	$66,600
Customer margin		($19,000)
Determine the product margin for the OfficeMart orders under a traditional cost system
Manufacturing overhead	$500,000
Total activity	1,000	units
Manufacturing overhead per unit	$500	per unit
Sales		$47,600
Costs:
Direct materials	$14,400
Direct labor	$4,000
Manufacturing overhead	$40,000	$58,400
Traditional costing product margin		($10,800)
a) The customer margin under activity-based costing is now $19000
b) The product margin under traditional costing is now $1,0800
c) Option A and D are correct

A D 3 Data 4 Manufacturing overhead 5 Selling and administrative overhead 500000 300000 8 Manufacturing overhead 9 Selling and administrative overhead 10 Total activity Assembling Units 0.5 0.1 1000 units Processing Orders 0.35 0.45 250 orders Supporting Customers 0.05 0.25 100 customers Other 0.1 0,2 12 OfficeMart orders: 13 Customers 14 Orders 15 Number of filing cabinets ordered in total 16 Selling price 17 Direct materials 18 Direct labor customer orders units 180 20 Enter a formula into each of the cells marked with a ? below 21 Review Problem: Activity-Based Costing 22 Perform the first stage allocations 23 24 Manufacturing overhead 25 Selling and administrative overhead 26 Total cost Assembling Units =$B$4*B8 =$B$5*B9 =B24+B25 Processing Orders =$B$4*C8 =$B$5*09 =C24+C25 Supporting Customers =$B$4*D8 =$B$5*D9 =D24+D25 Other =$B$4 E8 =$B$5*E9 =E24+E25 Total =SUM(B24:E24) =SUM(B25:E25) =F24+F25 28 Compute the activity rates 29 Activity Cost Pools 30 Assembling units 31 Processing orders 32 Supporting customers Total Cost =B26 =C26 =D26 Total Activity =B10 =C10 =D10 units orders customers Activity Rate =B30/C30 =B31/031 =B32/C32 per unit per order per customer 34 Compute the overhead cost attributable to the OfficeMart orders 35 Activity Cost Pools 36 Assembling units 37 Processing orders 38 Supporting customers Activity Rate =E30 =E31 =E32 per unit per order per customer Activity =B15 =B14 =B13 units orders customer ABC Cost =B36*D36 =B37*D37 =B38 D38 39


39 =B16 B15 40 Determine the customer margin for the OfficeMart orders under Activity-Based Costing 41 Sales 42 Costs: 43 Direct materials 44 Direct labor 45 Unit-related overhead 46 Order-related overhead 47 Customer-related overhead 48 Customer margin =B17 B15 =B15 B18 =F36 =F37 =F38 =SUM(B43:B47) =C41-C47 49 50 Determine the product margin for the OfficeMart orders under a traditional cost system 51 Manufacturing overhead 52 Total activity 53 Manufacturing overhead per unit =B4 =B10 =B51/B52 units per unit =C41 55 Sales 56 Costs: 57 Direct materials 58 Direct labor 59 Manufacturing overhead 60 Traditional costing product margin =B43 =B44 =B53315 =B57+B58+B59 =C55-C59 62 a) The customer margin under activity-based costing is now $19000 63 b) The product margin under traditional costing is now $1,0800 64 c) Option A and D are correct