Data Annual sales in units Direct materials per unit Direct labor-hours per unit Direct labor rate Deluxe Tourist 2,000 10,000 $25 $17 5 4 $12 per DLH Estimated Overhead Expected Activity Activities and Activity Measures Cost Deluxe Tourist Total Labor related (direct labor-hours) $ 80,000 ? ? ? Machine setups (setups) 150,000 3,000 2,000 ? Production orders (orders) 70,000 100 300 ? General factory (machine-hours) 250,000 12,000 28,000 ? ? ? DLHS ? per DLH Compute the predetermined overhead rate Estimated total manufacturing overhead (a) Estimated total amount of the allocation base (b) Predetermined overhead rate (a) + (b) Compute the manufacturing overhead applied Direct labor-hours per unit (a) Predetermined overhead rate (b) Manufacturing overhead applied per unit (a) x (b) Compute traditional unit product costs Direct materials Direct labor Manufacturing overhead applied Traditonal unit product cost Compute activity rates Activities Labor related Deluxe ? DLHS ? per DLH 2 2 Deluxe Tourist ? ? ? ? ? ? ? ? Machine setups Production orders General factory Compute the ABC overhead cost per unit Acthatios Tourist ? DLHs ? per DLH ? Estimated Overhead Cost ? ? DLHS ? ? setups ? ?orders ? ? MHs Deluxe Total Expected Activity Activity Rate ? per DLH ? per setup ? per order ? per MH Tourist Activity Expected Expected Rate Activate Amount Activite Amount Activities Labor related Machine setups Production orders General factory Compute the ABC overhead cost per unit Activities Labor related Machine setups Production orders General factory Total overhead cost assigned (a) Number of units produced (b) Cost ? ? ? setups ? ? orders ? ? MHs Total Expected Activity ? DLHS Activity Rate ? per DLH ? per setup ? per order ? per MH Deluxe Tourist Activity Expected Expected Rate Activity Amount Activity Amount ? ? 2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ABC overhead cost per unit (a) + (b) Compute the ABC unit product costs Direct materials Deluxe Tourist ? ? Direct labor ABC overhead cost per unit (see above) ABC unit product cost ? ? ? ? ? ? Requirement 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 200 orders in cell D19. The Data area should now look like this: 1 Chapter 4: Applying Excel 2 3 Enter a formula into each of the cells marked with a ? below 4 Review Problem: Activity-Based Costing 5 6 Data: B C D E 7 Deluxe Tourist 8 Annual sales in units i 2,000 10,000 9 Direct materials per unit $ 25 $ 17 10 Direct labor-hours per unit 5 4 11 12 Direct labor rate $ 12 per DLH 13 141 15 Estimated Overhead Expected Activity 16 Activities and Activity Measures Cost Deluxe Tourist Total 17 Labor related (direct labor-hours) $ 80,000 10,000 18 Machine setups (setups) 150,000 3,000 40,000 2,000 50,000 5,000 19 Production orders (orders) 70,000 300 200 500 20 General factory (machine-hours) 250,000 12,000 28,000 40,000 21 $ 550,000 (a). What is the traditional unit product cost for the Deluxe model after this change? Traditional unit product cost for the Deluxe model (c). What is the ABC unit product cost for the Deluxe model after this change? (Round your intermediate calculations and final answer to 2 decimal places.) ABC unit product cost for the Deluxe model Requirement 3: Change the data in your worksheet so that it matches the following: 123 Chapter 4: Applying Excel A Enter a formula into each of the cells marked with a ? below B C D E 4 Review Problem: Activity-Based Costing 5 6 Data 7 Deluxe 8 Annual sales in units 1,000 9 Direct materials per unit $ 45 $ 10 Direct labor-hours per unit 1 Tourist 9,000 45 1 11 12 Direct labor rate $ 25 per DLH 13 14 Estimated 15 Overhead 16 Activities and Activity Measures Cost Deluxe 17 Labor related (direct labor-hours) $ 35,000 1,000 18 Machine setups (setups) 120,000 40 19 Production orders (orders) 70,000 35 20 General factory (machine-hours) 150,000 10,000 21 $ 375,000 Expected Activity Tourist Total 9,000 10,000 60 15 100 50 10,000 20,000 (a). What is the traditional unit product cost for the Deluxe model now? (Round your intermediate calculations and final answer to 2 decimal places.) Traditional unit product cost for the Deluxe model (b). What is the traditional unit product cost for the Tourist model? (Round your intermediate calculations and final answer to 2 decimal places.) Traditional unit product cost for the Tourist model (c). What is the ABC unit product cost for the Deluxe model? (Round your intermediate calculations and final answer to 2 decimal places.) ABC unit product cost for the Deluxe model (d). What is the ABC unit product cost for the Tourist model? (Round your intermediate calculations and final answer to 2 decimal places.) ABC unit product cost for the Tourist model