A form for using Excel to recreate the Review Problem related to Aerodec, Inc., is shown below. For simplicity, the form excludes the Parts Administration and Material Receipts activities that were included in the Review Problem. Download the workbook containing this form in the Connect Library. On the website you will also receive instructions about how to use this worksheet form.

You should proceed to the requirements below only after completing your worksheet.

Required:
1. Check your worksheet by reducing the direct labor-hours per unit to 2 for the Deluxe model in cell B10. The Data area of the worksheet should look like this:

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, find the errors in your worksheet and correct them.

a. What happened to the predetermined overhead rate when the direct labor-hour requirement for the Deluxe model dropped from five hours to two hours? Explain.

b. Compare the unit product costs for the Tourist model before and after changing the direct labor-hour requirements for the Deluxe model. The traditional unit product cost of the Tourist model increased from $109 to $115 when the direct labor-hour requirement for the Deluxe model changed from five to two hours per unit. In contrast, the ABC unit product cost of the Tourist model increased from $100.15 to $101.02. Which costing method do you trust more for making decisions? Explain.
2. Change the direct labor-hour requirement for the Deluxe model back to five hours in cell B10. Assume that the production orders change so that the Deluxe model will have 300 orders in cell C19 and the Tourist model will have 100 orders in cell D19. The Data area should now look like this:

a. What effect does this change have on the traditional unit product costs? Explain.

b. What effect does this change have on the ABC unit product costs? Explain.

c. Which method, the traditional direct labor-based costing system or the ABC costing system, apparently provides more accurate costs? Explain.
3. Change the data in red so that the Data area looks like this:

a. Are the traditional unit product costs for the two products the same or different? Explain.

b. Are the ABC unit product costs for the two products the same or different? Explain.

c. Which method, the traditional direct labor-based costing system or the ABC costing system, apparently provides more accurate costs? Explain.

Transcribed Image Text:

c D C " Chapter & Applying Excel Enter a formula into each of the cells marked with a ? below Review Problem: Activity Based Costing Data Annual sales in units Direct materials per unit 10 Direct labor-hours per unit 11 12 Direct labor rate Delore Tourist 2,000 10.000 $25 $17 5 4 $12 per DLH 13 14 Estimated Overhead Expected Activity 16 Activities and Activity Measures Cost Delure Tourist Total 17 Labor-related (direct labor-hours) $00.000 ? ? 18 Machine setups (setups) 19 Production orders orders) 150,000 70,000 250,000 3,000 2,000 " 100 300 ? 12,000 28.000 ' 22 General factory (machine-hours) 23 Compute the predetermined overhead rate 24 Estimated total manufacturing overhead (a) 25 Estimated total amount of the allocation base (h) 25 Predetermined overhead rate (a)+(b) 27 Compute the manufacturing overhead applied 29 Direct labor hours per unit (a) 30 Predetermined overhead rate (b) 21 Manufacturing overhead applied per unit (a) (b) 13 Compute traditional unit product cost 34 Direct materials 35 Direct labor Manufacturing overhead applied Traditonal unit product cost Compute activity rates 41 Activities 42 Labor-related 43 Machine setups ? OLMS 7 per DLH Deluxe 7 DLH ?per DLH Tourat OLH per DLH ? Delare Tourist ? ? ? ? ? Estimated Overhead Coet Total Expected Activity ? DLH Activity Rate ?per DLH ? setups 44 Production orders General factory Compute the ABC overhead cost per unit ? ' 7 orders per setup 7 per under per MH Delare Tours Activity Expected Expected Activities Rale Activity Amount Activity Amount 50 Labor-related ? ? 51 Machine setups ? ? ' 52 Production orders ? ? ? ? 53 General factory " ? ? " 54 Total overhead cost assigned (a) ? 2 55 Number of units produced (b) ABC overhead cost per unit (a)+(b) Compute the ABC unit product costs 59 Direct materials so Direct labor ABC overhead cost per unit (see above) 62 ABC unit product cost Delave Tourist 2 2 ? 2 2 '