Question

CH 7 Applying Excel

Instructions: 1. Update this spreadsheet with the appropriate formulas in the cells below with question marks. 2. Then, make a copy of the sheet in this same workbook and prepare Requirement 2 on page 340. Note: You can also make the changes suggested in Requirement 1, if you want, to check your worksheet the first time to make sure it works properly.

Answer 1

	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	4	orders
	Number of filing cabinets ordered in total	80	units
	Selling price	$595
	Direct materials	$180
	Direct labor	$50
	Review Problem: Activity-Based Costing
1	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
2	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	4	orders	$4,960
	Supporting customers	$1,000	per customer	1	customer	$1,000
3	Determine the customer margin for the OfficeMart orders under Activity-Based Costing
	Sales (80 units x $ 595)		$47,600
	Costs:
	Direct materials (80 x $ 180)	$14,400
	Direct labor ( 80 x $ 50)	$4,000
	Unit-related overhead	$22,400
	Order-related overhead	$4,960
	Customer-related overhead	$1,000	$46,760
	Customer margin		$840
4	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 ( 80 x $ 500)	$40,000	$58,400
	Traditional costing product margin		($10,800)

A B F G H Nm 7 Data Manufacturing overhead Selling and administrative overhead 500000 300000 000 0.2 Assembling Units Processing Orders Supporting Customers Other Manufacturing overhead 10.5 0.35 0.05 0.1 Selling and administrative overhead 0.1 0.45 0.25 Total activity 1000 250 100 units orders customers OfficeMart orders: Customers customer Orders 14 orders Number of filing cabinets ordered in total 180 units Selling price 595 Direct materials 180 Direct labor 50 14 17 Review Problem: Activity-Based Costing 19 20

А D E F G H 19 В | Review Problem: Activity-Based Costing 20 1 Perform the first stage allocations Assembling Units Processing Orders Supporting Customers Other Total =$D$3*D7 =$D$3*E7 =$D$3*F7 =$D$3*G7 =SUM(D23:G23) =$D$4*D8 =$D$4*E8 =$D$4*F8 =$D$4*G8 =SUM(D24:G24) =SUM(D23:D24) =SUM(E23:E24) =SUM(F23:F24) =SUM(G23:G24) =SUM(H23:H24) Manufacturing overhead Selling and administrative overhead Total cost Compute the activity rates Activity Cost Pools Assembling units Processing orders Supporting customers Total Cost =D25 =E25 =F25 Total Activity =D9 =E9 =F9 units orders customers Activity Rate =D28/E28 =D29/E29 =D30/E30 per unit per order per customer 30 33 34 2 Compute the overhead cost attributable to the OfficeMart orders Activity Cost Pools Activity Rate Assembling units =G28 per unit Processing orders =G29 per order Supporting customers =G30 per customer Activity =D14 =D13 =D12 units orders customer ABC Cost =D34*F34 =D35*F35 =D36*F36 38 3 Determine the customer margin for the OfficeMart orders under Activity-Based Costing Sales (80 units x $ 595) =D14*D15 Costs: Direct materials (80 x $ 180) D14*D16 Direct labor ( 80 x $ 50) =D14*D17 Unit-related overhead =H34 Order-related overhead =H35 Customer-related overhead =H36 =SUM(D41:045) Customer margin =E39-E45

AB 4 Determine the product margin for the OfficeMart orders under a traditional cost system Manufacturing overhead =H23 Total activity =D9 units Manufacturing overhead per unit =D49/050 per unit =E39 Sales Costs: Direct materials Direct labor Manufacturing overhead ( 80 x $ 500) Traditional costing product margin =D41 =D42 =D14*D51 =SUM(D55:057) =E53-E57