Excel HelpDYMO Label QuickBooks AutoSave OD B O E xcel Templatestigemer004) File Home Insert Page Layout Formulas Data Review View X V CD4 Excel Tutorial A A B C D E 1 CD4. Excel Tutorial 3 As you learned in the previous chapters. Current Designs has two main product lines - composite kayaks, which are handmade and 4 very Isbor-Intensive and rotomolded kayaks, which require less Isbor but employ more expensive equipment. Current Design's 5 controller, Diane Buswell, is now evaluating several different methods of assigning overhead to these products. It is important to 6 ensure that costs are appropriately ass gned to the company's products. At the same time, the system that is used must not be so 7 complex that its costs are greater than its benefits Diane has decided to use the following activities and costs to evaluate the methods of assigning overhead Activities Designing new models Creating and testing prototypes Creating molds for kayaks Operating oven for the rotomolded kayaks Operating the vacuum line for the composite kayaks Supervising production omployees Curing time (the time that is needed for the chemical processes to finish before the next step in the production process, many of these costs are related to the space required in the building) Total Cost $121,000 152,000 188,500 40,000 28,000 180,000 190.500 $900.000 25 As Diane examines the date, she decides that the cost of operating the oven for the retomolded Kayaks and the cost of operating 26 the vacuum line for the composite kayaks can be directly assigned to each of these products lines and do not need to be allocated 27 with the other costs. 29 Instructions 30 For purpose of this analysis assume that Current Designs uses $234.000 in direct labor costs to produce 1.000 composite Kayaks 31 and $226,000 in direct labor costs to produce 4,000 robomolded kayaks each year. 320) One method of allocating overhead would allocate the common costs to each product line by using a location basis such as the number of omployees working on each type of kayak or the amount of factory space used for the production of each type of kayak Dlone knows that about 60% of the area of the plant and 50% of the employees work on the composite kayaks, and the remaining space 35 and other employees work on the rotomolded kayaks. Using this information and remembering that the cost of operating the oven ESTE EST PL-JAN-2 coyo U Search File Home DYMO Label QuickBooks Insert 1 C SREERANCAS). Excel Page Layout Formulas Data Review View Help f CD4 - Excel Tutorial D E A A B 29 Instructions 30 For purpose of this analysis, assume that Current Designs uses $234.000 in direct labor costs to produce 1.000 composite kayaks 31 and $206,000 in direct labor costs to produce 4.000 rolomolded kayaks each year, 32 (a) One method of allocating overhead would allocate the common costs to each product line by using a allocation basis such as the number of employees working on each type of kayak or the amount of factory space used for the production of each type of kayak 34 Diane knows that about 50% of the area of the plant and 50% of the employees work on the composite kayaks, and the remaining space and other employees work on the rotomolded kayaks. Using this information and remembering that the cost of operating the oven and vacuum line have been directly assigned, determine the total amount to be assigned to the composite kayak line and the 37 rotomolded kayak line, and the amount to be assigned to each of the units in each line. 36 39 (b) Another method of allocating overhead is to use direct labor dollars as an allocation basis. Remembering that the costs of the oven and the vacuum line have been assigned directly to the product lines, allocate the remaining costs using direct labor dollars as the allocation basis. Then, determine the amount of overhead that should be assigned to each unit of each product 42 line using this method. 47 44 (c) Activity-based costing requires a cost driver for each cost pool. Use the following Information to assign the costs to the product lines using the activity-based costing approach 46 Driver Amount for Driver Amount for 48 Activity Cos! Pools Cost Drivers Composite Kayako Rotomolded Kayaks 49 Designing new model Number of models Croating and tost prototyper Number of prototypos 51 Creating molds for kayaks Number of molds Supervising production kayt Number of employees Curing time Number of days of curing time 15,000 2.000 53 56 What amount of overhead should be assigned to osch composite kayak using this method? What amount of overhead should be assigned to each rotomoldod kayak using this mothod? 56 (d) Which of the three methods do you think Current Designs should use? Why? 6. NOTE: Enter a number in colls requesting a value; enter either a number or a formula in cells with a "?". 61 TE- 14- 44-9 P4-1A PA2A CD4 Type here to search D Autosave . ExcelTemplate Assignment, Cho4 (5) Excel Search File Home Insert Page Layout Formulas Data Review View HelpDYMO Label QuickBooks A1 X V CD4 - Excel Tutorial 14 A B C D E 64 (a) Determine the total amount to be assigned to the composite kayak line and the rotomolded kayak line, and the amount to be assigned to each of the units in each of the units in each line, Composite Value Rotomolded V alue Value Value Directly assigned Remaining amount allocated 50% to each product line Total Number of units Cost assigned per unit 12 Value Value 74 77 (b) Determine the amount of overhead that should be assigned to each unit of each product using direct labor dollars as an allocation basis. Overhead Rate Total OH costs () Direct labor cost (b) Composite Rotomolded OH rate (a) (b) Value Value 7 Composite Value Rotomolded Value Directly assigned Remaining amount allocated based on direct labor costs Total Nurrber of units Cost assigned per unit Value 23 ayla marec h alam O Type here to search W E xcel Templatestigiment 01016). Excel Search File Home Insert Page Layout Formulas Data Review ViewH elpDVMO Label QuickBooks A1 . 1 X s 004 Excel Tutorial A B C D E F G 97 (c) What amount of overhead should be assigned to each composite kayak and rolomolded kayak using the activity based costing method? Estimated Overhead Activity Based OH Rates 100 101 102 103 104 05 106 Activity Cost Pools Design Prototypes Molds Supervision Curing time Estimated Use of Cost Drivers Value Valje Value Value Value Value Value Value 7 model / prototype mold employee day Compost Rotomolded Expected Use of Drivers Overhead Rates VAN 107 Expected Use of Drivers Cost Assigned Overhead Rates Cost Assigned 08 10 Activity Cost Pools Design Prototypes Molds Supervision Curing time Value Valve Value Total amount allocated Directly Total Cost(a) Number of unso) Cost assignedunt (0) (b) 1220) Which of the the methods do you like Current Designs should use? Why? T 44 PM TN-PACO AutoSave OD Fle Home Insert A1 . 1 LAB C D Excellemplate Alignmerk 01043) Excel S earch Page Layout Formulas Data Review View HelpDYMO Label QuickBooks CD4 Excel Tutorial E F G H I Composite Rolomolded Expected Expected Use of Overhead Cost Use of Overhead Cost Drivers Rates Assigned Orivers Rates Assigned Value Value Value Value Value Value Value Value Value Value Value Valve Value Value Veu VA Value Value Value 107 100 109 Activity Cost Pools Design Prototypes Molds Supervision Curing time 110 Total amount located Directly Total Costa) Number of units) Cost assigned unit ( D) Value 122. Which of the three methods do you in Current Designs should use? Why? After you have completed CD4, consider the add tonal question 1. Assume that the number of prototypes created for composite kayaks and rotomolded Kayaks changed to 8 and 4 respectively, Reconstruction () and round cost assigned to wo decal points E4-1 4- 449 PIA PACD4 Excel HelpDYMO Label QuickBooks AutoSave OD B O E xcel Templatestigemer004) File Home Insert Page Layout Formulas Data Review View X V CD4 Excel Tutorial A A B C D E 1 CD4. Excel Tutorial 3 As you learned in the previous chapters. Current Designs has two main product lines - composite kayaks, which are handmade and 4 very Isbor-Intensive and rotomolded kayaks, which require less Isbor but employ more expensive equipment. Current Design's 5 controller, Diane Buswell, is now evaluating several different methods of assigning overhead to these products. It is important to 6 ensure that costs are appropriately ass gned to the company's products. At the same time, the system that is used must not be so 7 complex that its costs are greater than its benefits Diane has decided to use the following activities and costs to evaluate the methods of assigning overhead Activities Designing new models Creating and testing prototypes Creating molds for kayaks Operating oven for the rotomolded kayaks Operating the vacuum line for the composite kayaks Supervising production omployees Curing time (the time that is needed for the chemical processes to finish before the next step in the production process, many of these costs are related to the space required in the building) Total Cost $121,000 152,000 188,500 40,000 28,000 180,000 190.500 $900.000 25 As Diane examines the date, she decides that the cost of operating the oven for the retomolded Kayaks and the cost of operating 26 the vacuum line for the composite kayaks can be directly assigned to each of these products lines and do not need to be allocated 27 with the other costs. 29 Instructions 30 For purpose of this analysis assume that Current Designs uses $234.000 in direct labor costs to produce 1.000 composite Kayaks 31 and $226,000 in direct labor costs to produce 4,000 robomolded kayaks each year. 320) One method of allocating overhead would allocate the common costs to each product line by using a location basis such as the number of omployees working on each type of kayak or the amount of factory space used for the production of each type of kayak Dlone knows that about 60% of the area of the plant and 50% of the employees work on the composite kayaks, and the remaining space 35 and other employees work on the rotomolded kayaks. Using this information and remembering that the cost of operating the oven ESTE EST PL-JAN-2 coyo U Search File Home DYMO Label QuickBooks Insert 1 C SREERANCAS). Excel Page Layout Formulas Data Review View Help f CD4 - Excel Tutorial D E A A B 29 Instructions 30 For purpose of this analysis, assume that Current Designs uses $234.000 in direct labor costs to produce 1.000 composite kayaks 31 and $206,000 in direct labor costs to produce 4.000 rolomolded kayaks each year, 32 (a) One method of allocating overhead would allocate the common costs to each product line by using a allocation basis such as the number of employees working on each type of kayak or the amount of factory space used for the production of each type of kayak 34 Diane knows that about 50% of the area of the plant and 50% of the employees work on the composite kayaks, and the remaining space and other employees work on the rotomolded kayaks. Using this information and remembering that the cost of operating the oven and vacuum line have been directly assigned, determine the total amount to be assigned to the composite kayak line and the 37 rotomolded kayak line, and the amount to be assigned to each of the units in each line. 36 39 (b) Another method of allocating overhead is to use direct labor dollars as an allocation basis. Remembering that the costs of the oven and the vacuum line have been assigned directly to the product lines, allocate the remaining costs using direct labor dollars as the allocation basis. Then, determine the amount of overhead that should be assigned to each unit of each product 42 line using this method. 47 44 (c) Activity-based costing requires a cost driver for each cost pool. Use the following Information to assign the costs to the product lines using the activity-based costing approach 46 Driver Amount for Driver Amount for 48 Activity Cos! Pools Cost Drivers Composite Kayako Rotomolded Kayaks 49 Designing new model Number of models Croating and tost prototyper Number of prototypos 51 Creating molds for kayaks Number of molds Supervising production kayt Number of employees Curing time Number of days of curing time 15,000 2.000 53 56 What amount of overhead should be assigned to osch composite kayak using this method? What amount of overhead should be assigned to each rotomoldod kayak using this mothod? 56 (d) Which of the three methods do you think Current Designs should use? Why? 6. NOTE: Enter a number in colls requesting a value; enter either a number or a formula in cells with a "?". 61 TE- 14- 44-9 P4-1A PA2A CD4 Type here to search D Autosave . ExcelTemplate Assignment, Cho4 (5) Excel Search File Home Insert Page Layout Formulas Data Review View HelpDYMO Label QuickBooks A1 X V CD4 - Excel Tutorial 14 A B C D E 64 (a) Determine the total amount to be assigned to the composite kayak line and the rotomolded kayak line, and the amount to be assigned to each of the units in each of the units in each line, Composite Value Rotomolded V alue Value Value Directly assigned Remaining amount allocated 50% to each product line Total Number of units Cost assigned per unit 12 Value Value 74 77 (b) Determine the amount of overhead that should be assigned to each unit of each product using direct labor dollars as an allocation basis. Overhead Rate Total OH costs () Direct labor cost (b) Composite Rotomolded OH rate (a) (b) Value Value 7 Composite Value Rotomolded Value Directly assigned Remaining amount allocated based on direct labor costs Total Nurrber of units Cost assigned per unit Value 23 ayla marec h alam O Type here to search W E xcel Templatestigiment 01016). Excel Search File Home Insert Page Layout Formulas Data Review ViewH elpDVMO Label QuickBooks A1 . 1 X s 004 Excel Tutorial A B C D E F G 97 (c) What amount of overhead should be assigned to each composite kayak and rolomolded kayak using the activity based costing method? Estimated Overhead Activity Based OH Rates 100 101 102 103 104 05 106 Activity Cost Pools Design Prototypes Molds Supervision Curing time Estimated Use of Cost Drivers Value Valje Value Value Value Value Value Value 7 model / prototype mold employee day Compost Rotomolded Expected Use of Drivers Overhead Rates VAN 107 Expected Use of Drivers Cost Assigned Overhead Rates Cost Assigned 08 10 Activity Cost Pools Design Prototypes Molds Supervision Curing time Value Valve Value Total amount allocated Directly Total Cost(a) Number of unso) Cost assignedunt (0) (b) 1220) Which of the the methods do you like Current Designs should use? Why? T 44 PM TN-PACO AutoSave OD Fle Home Insert A1 . 1 LAB C D Excellemplate Alignmerk 01043) Excel S earch Page Layout Formulas Data Review View HelpDYMO Label QuickBooks CD4 Excel Tutorial E F G H I Composite Rolomolded Expected Expected Use of Overhead Cost Use of Overhead Cost Drivers Rates Assigned Orivers Rates Assigned Value Value Value Value Value Value Value Value Value Value Value Valve Value Value Veu VA Value Value Value 107 100 109 Activity Cost Pools Design Prototypes Molds Supervision Curing time 110 Total amount located Directly Total Costa) Number of units) Cost assigned unit ( D) Value 122. Which of the three methods do you in Current Designs should use? Why? After you have completed CD4, consider the add tonal question 1. Assume that the number of prototypes created for composite kayaks and rotomolded Kayaks changed to 8 and 4 respectively, Reconstruction () and round cost assigned to wo decal points E4-1 4- 449 PIA PACD4