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 5 hours to 2 hours? Explain.

b. Compare the unit product costs for the Tourist model before and after changing the direct laborhour 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 5 to 2 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 5 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 E F G 1 Chapter Applying Excel 3 Enter a formula into each of the cells marked with a ? belo Review Problem: Activity-Based Costing 6 Data 8 Annual sales in units 9 Direct materials per unit 10 Direct labor-hours per unit 11 12 Direct labor rate 34 15 16 Activities and Activity Measure 17 Labor-related (direct labor-hours) 18 Machine setups (setups) 19 Production orders (orders) 20 General factory (machine-hours) 21 22 23 Compute the predetermined overhead rate 24 Estimated total manufacturing overhead(a) 25 Estimated total amount of the allocation base (b) 26 6 Predetermined overhead tate()+(b) 27 28 Compute the manufacturing overhead applied 29 Direct labor-hours per unit (a) 30 Predetermined overhead rate (b) 32 Manufacturing overhead applied per unit (a) (b) 33 Compute traditional unit product costs 34 Direct materials 35 Direct labor 36 Manufacturing overhead applied 37 Traditonal unit product cost 38 39 Compute activity rates 40 41 Activities 42 Labor-related 43 Machine setups 44 Production orders 45 General factory 46 47 Compute the ABC overhead cost per unit 48 49 Activities 30 Labor-related Delare 2.000 Tourist 10,000 525 517 5 4 $12 per DLH Estimated Overhead Coat Expected Activity De Tourist $ 80.000 ? 150.000 3.000 2.000 100 250 000 12.000 300 28000 70,000 Deluxe 7 DLHe ?per DLH " ? OLH 7 per DLH ? Delore Tour ? 7 ? ? ? Total Touret 7 DLHa ?per DUH ? Estimated Overhead Cost 7 Total Expected Activity 7 OLH ? 2 ?orders 7 7MHs Delare Activity Expected Rate Activity Amount 2 2 ???? Activity Rate ? per DLH ?p ? per order 7 por MH Tourist Expected Activity Amount 2 31 Machine setups 52 Production des 32 General factory 34 Total overhead cost assigned (a) 55 Number of units produced (b) 56 ABC overhead cast par unit (a)+(b) 58 Compute the ABC amit product costs 59 Direct materials 60 Direct labor 61 ABC overhead cost per unit (see above) 62 ABC unit product cost 7 ? ? 7 Delore Tourist 7 7 ? ? ? P ? ? ? ? F