Required information Chapter 4: Applying Excel Step 1: Download the Applying Excel form located on the left-hand side, under files. If you have trouble, the file is also located in D2L under Course Administration Step 2: Then enter formulas in all cells that contain question marks. For example, in cell C17 enter the formula "=88*810". Step 3: 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 Step 4: Proceed to the requirements below only after completing your worksheet as it will be used to answer the following questions, Chapter 4: Applying Excel: Exercise Arial 10 > Wr Paste BIU I a. Av TIT INI IM Me 122 B D F H Chapter 4: Applying Excel Enter a formula into each of the cells marked with a ? below 1 Review Problem: Activity-Based Costing 5 Data 3 B Annual sales in units 9 Direct materials per unit 10 Direct labor hours per unit 11 12 Direct laborate 13 Delare 2.000 $25 5 Tourist 10,000 $17 4 $12 per DLH Tourist Total Estimated Overhead Cost $ 80.000 150.000 70 000 250.000 7. Eyed AMY Daluare 7 ? 3.000 2,000 100 300 12.000 28,000 ? ? ? 7 7 7 DLHS 7 per DU Deluxe 7 DUH 7 per DLH Tourist 7 DUHS 7 per DLM 7 Deluxe ? ? 7 Tourist 7 7 2 2 16 Activities and Activity Measures 17 Labor related direct labor-hours) 18 Machine sobe (upe) 19 Production orders (orders) 20 General factory (machine hours) 21 22 23 Compute the predetermined overhard rate 24 Estimated total manufacturing overhead (a) 25 Estimated total amount of the location base (1) 26. Predetermined overhead rate (a) (b) 27 28 Compute the manufacturing overhead applied 29 Direct labor hours per unit(a) 30 Predetermined overhead rate (b) 31 Manufacturing overhead applied per unit(a) (b) 32 33 Compute traditional unit product costs 34 Direct materials 35 Direct labor 36 Manufacturing overhead applied 37 Traditonal unit product cost 3B 39 Compute activity rates 40 41 Actes 42 Labor related 43 Machines. 44 Production orders 45 General factory 46 47 Compute the ABC overhead coal per un 48 49 Active 50 Labor related 51 Machine sups 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) 57 58 Compute the ABC unit product costs 59 Drect materials 60 Direct labor 61 ABC overhead cost per unit (see above) 62 ABC unit product cost 63 Estimated Overhead Cost 7 7 ? 7 Tot Expected Activity 7 DLHS 7 soupe 7 orders 7 MHS Activity Rate 7 per DUH 7 perso 7 per order 7 per MH Acovily Rate 7 7 2 7 Expected Activity 7 ? ? ? Amount ? 7 2 2 Teri Expected Activity Amour 27 ? 2 ? 2 7 ? 7 7 21 ? ? ? Tourist Delare ? ? 2 ? 2 7 2 Chapter 4 Form Sheet1 Sheet2 + Required information 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 240 orders in cell C19 and the Tourist model will have 260 orders in cell D19. The Data area should now look like this: B D E Deluxe 2,000 25$ Tourist 10,000 17 4 $ 5 A 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 7 8 Annual sales in units 9 Direct materials per unit 10 Direct labor-hours per unit 11 12 Direct labor rate 13 14 15 16 Activities and Activity Measures 17 Labor related (direct labor hours) 18 Machine setups (setups) 19 Production orders (orders) 20 General factory (machine-hours) 21 $ 12 per DLH Estimated Overhead Cost $ 80,000 150,000 70,000 250.000 $ 550.000 Expected Activity Deluxe Tourist 10,000 40.000 3,000 2.000 240 260 12.000 28,000 Total 50,000 5,000 500 40,000 Required information 21 $ 550,000 (a). What is the traditional unit product cost for the Deluxe model after this change? ok Traditional unit product cost for the Deluxe model ces (b). Which of the following statements are true? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) The traditional unit product costs for the two models are unchanged by the change in production orders because neither the total estimated overhead cost nor the total direct labor-hours were affected The traditional unit product costs for the two models have been changed by the change in production orders because traditional overhead allocations based on direct labor-hours ignore other cost drivers such as production orders. The traditional unit product costs for the two models are unchanged by the change in production orders because traditional overhead allocations based on direct labor hours ignore other cost drivers such as production orders. (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 (d). Which of the following statements are true concerning the effects of the change in production orders on the ABC unit product costs? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) The ABC unit product cost of the Deluxe model has increased and the ABC unit product cost of the Tourist model has decreased because a lower proportion of the production orders are now attributable to the Deluxe model. 7 The ABC unit product cost of the Deluxe model has increased and the ABC unit product cost of the Tourist model has decreased because a higher proportion of the production orders are now attributable to the Deluxe model. The ABC unit product cost of the Deluxe model has decreased and the ABC unit product cost of the Tourist model has increased because a lower proportion of the production orders are now attributable to the Deluxe model. 2 The ABC unit product cost of the Deluxe model has decreased and the ABC unit product cost of the Tourist model has increased because a higher proportion of the production orders are now attributable to the Deluxe model. Requirement 3: Change the data in your worksheet so that it matches the following: A B D E 1 Chapter 4: Applying Excel 2 3 4 5 Enter a formula into each of the cells marked with a 7 below Review Problem: Activity-Based Costing 6 Data 7 Tourist 9.000 8 Deluxe 1,000 50 Annual sales in units Direct materials per unit Direct labor-hours per unit 9 $ $ 50 10 1 1 11 Direct labor rate $ 21 per DLH 12 13 14 15 Activities and Activity Measures 16 Labor related (direct labor-hours) 17 Machine setups (setups) 18 Production orders (orders) 19 General factory (machine-hours) 20 Estimated Overhead Cost $ 35,000 120,000 70,000 150,000 $ 375,000 Expected Activity Deluxe Tourist 1,000 9,000 40 60 35 15 10,000 10,000 Total 10,000 100 50 20,000 Seved Required information 19 General factory (machine-hours) 10,000 10,000 20.000 150.000 375.000 20 $ 21 (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 1 (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 un product cost for the Tourist model