Question

Enter formulas in the cells that contain question marks. For example, in cell C17 enter the formula "= B8*B10".

After entering formulas in all of the cells that contained question marks, verify that the calculated numbers match the numbers in the Review Problem.

Check your worksheet by reducing the direct labor-hours for the Deluxe model in cell B10 from 5 to 2. The Deluxe model's unit product cost under traditional costing should now be $74.00 and the ABC unit product cost should be $143.89. If you do not get these results, fmd the errors in your worksheet and correct them.

1 Chapter 4: Applying Excel 3 Enter a formula into each of the cells marked with a ? below 4 Review Problem: Activity-Based Costing 6 Data 8 Annual sales in units 9 Direct materials per unit 10 Direct labor-hours per unit Deluxe 2,000 $25 Tourist 10,000 $17 4 12 Direct labor rate $12 per DLH Total 16 Activities and Activity Measures 17 Labor related direct labor-hours) 18 Machine setups (setups) 19 Production orders (orders) 20 General factory (machine-hours) Estimated Overhead Cost $ 80,000 150,000 70,000 250,000 Expected Activity Deluxe Tourist ? 3,000 2,000 100 300 12,000 28,000 23 Compute the predetermined overhead rate 24 Estimated total manufacturing overhead (a) 25 Estimated total amount of the allocation base (b) 26 Predetermined over head rate (a) = (b) ? DLHs ? per DLH 28 Compute the manufacturing overhead applied 29 Direct labor-hours per unit (a) 30 Predetermined over head rate (b) 31 Manufacturing overhead applied per unit (a) (b) Deluxe ? DLHS ? per DLH Tourist ? DLHS ? per DLH Deluxe Tourist 33 Compute traditional unit product costs 34 Direct materials 35 Direct labor 36 Manufacturing overhead applied 37 Traditonal unit product cost ? 2 27 ? 39 Compute activity rates 0 Estimated Overhead Cost 21 Activities 2 Labor related 23 Machine setups 14 Production orders 5 General factory Total Expected Activity ? DLHS 7 setups ? orders ? MHS Activity Rate ? per DLH ? per setup ? per order ? per MH ? 7 Compute the ABC overhead cost per unit Activity Rate Deluxe Expected Activity Amount Tourist Expected Activity Amount 29 Activities 50 Labor related 51 Machine setups 52 Production orders 53 General factory 54 Total overhead cost assigned (a) 55 Number of units produced (b) 56 ABC overhead cost per unit (a) (b) ? ? ? ? ? ? ? Deluxe Tourist 58 Compute the ABC unit product costs 59 Direct materials 50 Direct labor 51 ABC overhead cost per unit (see above) 52 ABC unit product cost

Answer 1

Data
	Deluxe	Tourist
Annual Sales in unit	2,000	10,000
Direct material per unit	25	17
Direct labor hours per unit	5	4
Direct labour rate	12	per DLH
	Estimated
	Overhead	Expected Activity
Activities and Activity measures	Cost	Deluxe	Tourist	Total
Labor related (direct labor hours)	80,000	10,000	40,000	50,000
Machine setups (setups)	1,50,000	3,000	2,000	5,000
Production orders (orders)	70,000	100	300	400
General factory (machine-hours)	2,50,000	12,000	28,000	40,000
	5,50,000
Compute the predetermined overhead rate
Estimated total manufacturing overhead (a)		5,50,000
Estimated total amount of the allocation base (b)		50,000	DLHs
Predetermined overhead rate (a) ÷ (b)		11	Per DLH
Compute the manufacturing overhead applied	Deluxe	Tourist
Direct labor-hours per unit (a)	5	DLHs	4	DLHs
Predetermined overhead rate (b)	11	Per DLH	11	Per DLH
Manufacturing overhead applied per unit (a) × (b)	55		44
Compute traditional unit product costs	Deluxe	Tourist
Direct materials	25	17
Direct labor	60	48
Manufacturing overhead applied	55	44
Traditonal unit product cost	140	109
Compute activity rates	Estimated
	overhead
Activities	cost	Total expected activity	Activity rate
Labor related	80,000	50,000	DLHs	1.60	per DLH
Machine setups	1,50,000	5,000	Setup	30.00	per Setup
Production orders	70,000	400	orders	175.00	per orders
General factory	2,50,000	40,000	Mhs	6.25	per Mhs
Compute the ABC overhead cost per unit		Deluxe	Tourist
	Activity	Expected		Expected
Activities	Rate	Activity	Amount	Activity	Amount
Labor related	1.60	10,000	16,000	40,000	64,000
Machine setups	30.00	3,000	90,000	2,000	60,000
Production orders	175.00	100	17,500	300	52,500
General factory	6.25	12,000	75,000	28,000	1,75,000
Total overhead cost assigned (a)			1,98,500		3,51,500
Number of units produced (b)			2,000		10,000
ABC overhead cost per unit (a) ÷ (b)			99.25		35.15
Compute the ABC unit product costs		Deluxe	Tourist
Direct materials		25.00	17.00
Direct labor		60.00	48.00
ABC overhead cost per unit (see above)		99.25	35.15
ABC unit product cost		184.25	100.15

The formula used is given in the table below:

Data
	Deluxe	Tourist
Annual Sales in unit	2000	10000
Direct material per unit	25	17
Direct labor hours per unit	5	4
Direct labour rate	12	per DLH
	Estimated
	Overhead	Expected Activity
Activities and Activity measures	Cost	Deluxe	Tourist	Total
Labor related (direct labor hours)	80000	=B8*B10	=C8*C10	=C17+D17
Machine setups (setups)	150000	3000	2000	=C18+D18
Production orders (orders)	70000	100	300	=C19+D19
General factory (machine-hours)	250000	12000	28000	=C20+D20
	=SUM(B17:B20)
Compute the predetermined overhead rate
Estimated total manufacturing overhead (a)		=B21
Estimated total amount of the allocation base (b)		=E17	DLHs
Predetermined overhead rate (a) ÷ (b)		=C24/C25	Per DLH
Compute the manufacturing overhead applied	Deluxe	Tourist
Direct labor-hours per unit (a)	=B10	DLHs	=C10	DLHs
Predetermined overhead rate (b)	=C26	Per DLH	=B30	Per DLH
Manufacturing overhead applied per unit (a) × (b)	=B29*B30		=D29*D30
Compute traditional unit product costs	Deluxe	Tourist
Direct materials	=B9	=C9
Direct labor	=B10*B12	=C10*B12
Manufacturing overhead applied	=B31	=D31
Traditonal unit product cost	=SUM(B34:B36)	=SUM(C34:C36)
Compute activity rates	Estimated
	overhead
Activities	cost	Total expected activity	Activity rate
Labor related	=B17	=E17	DLHs	=B42/C42	per DLH
Machine setups	=B18	=E18	Setup	=B43/C43	per Setup
Production orders	=B19	=E19	orders	=B44/C44	per orders
General factory	=B20	=E20	Mhs	=B45/C45	per Mhs
Compute the ABC overhead cost per unit		Deluxe	Tourist
	Activity	Expected		Expected
Activities	Rate	Activity	Amount	Activity	Amount
Labor related	=E42	=C17	=C50*B50	=D17	=E50*B50
Machine setups	=E43	=C18	=C51*B51	=D18	=E51*B51
Production orders	=E44	=C19	=C52*B52	=D19	=E52*B52
General factory	=E45	=C20	=C53*B53	=D20	=E53*B53
Total overhead cost assigned (a)			=SUM(D50:D53)		=SUM(F50:F53)
Number of units produced (b)			=B8		=C8
ABC overhead cost per unit (a) ÷ (b)			=D54/D55		=F54/F55
Compute the ABC unit product costs		Deluxe	Tourist
Direct materials		=B34	=C34
Direct labor		=B35	=C35
ABC overhead cost per unit (see above)		=D56	=F56
ABC unit product cost		=SUM(C59:C61)	=SUM(D59:D61)
Please do upvote if you found the answer useful.
Feel free to reach in the comment section in case of any clarification or queries.