D4 Exel Tutorial As you learned in the previous chapters, Current Designs has two main product lines- composite kayaks, which are handmade and very labor-intensive, and rotomolded kayaks, which require less labor but employ more expensive equipment. Current Design's controller, Diane Buswell, is now evaluating several different methods of assigning overhead to these products. It is important to ensure that costs are appropriately assigned to the company's products. At the same time, the system that is used must not be so 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 employees Curing time :(the time that is needed for the chemical Cost $121,000 152,000 188,500 40,000 28,000 180,000 processes to finish before the next step in the production process; many of these costs are related to the space required in the building) 190,500 $900,000 Total As Diane examines the data, she decides that the cost of operating the oven for the rotomolded kayaks and the cost of operating the vacuum line for the composite kayaks can be directly assigned to each of these products lines and do not need to be allocated with the other costs. Instructions For purpose of this analysis, assume that Current Designs uses $234,000 in direct labor costs to produce 1,000 composite kayaks and $286,000 in direct labor costs to produce 4,000 rotomolded kayaks each year (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. 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 rotomolded kayak line, and the amount to be assigned to each of the units in each line. (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 line using this method. (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. Driver Amount for Driver Amount for Composite Kayaks 3 6 12 12 Rotomolded Kayaks Cost Drivers Activity Cost Pools Designing new models Creating and test prototypes Number of prototypes Creating molds for kayaks Number of molds Supervising production kayal Number of employees Curing time Number of models 2 12 2,000 15,000 Number of days of curing time of overhead What amount of overhead should be assigned to each composite kayak using this method? What amount should be assigned to each rotomolded kayak using this method? (d) Which of the three methods do you think Current Designs should use? Why? NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with 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. (a) Composite Rotomolded Value Value Directly assigned Remaining amount allocated 50% Value Value to each product line Total Value Value Number of units Cost assigned per unit (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 Value Total OH costs (a)_ Direct labor cost (b Composite Rotomolded Value Value OH rate (a) + (b) Composite Rotomolded Directly assigned Remaining amount allocated Value Value based on direct labor costs Total Number of units Cost assigned per unit 2 Value Value (c) What amount of overhead should be assigned to each composite kayak and rotomolded kayak using the activity-based costing method? Estimated Use Activity- Activity Cost Estimated of Cost Based OH Overhead Drivers Rates Pools Design Prototypes Molds Supervision Value Value Value Value Value Value Value Value / model / prototype / mold / employee Value ?/ day Curing time Value Composite Rotomolded Expected Use of Drivers Value Value Value Value Value Expected Drivers Value Value Value Value Value Use of Overhead Cost Overhead Rates Value Value Value Value Value Cost Assigned Activity Cost Rates Assigned Value Value Value Value Value Pools Design Prototypes Molds Supervision Curing time Total amount allocated Directly Total Cost (a) Number of units (b) Cost assigned/unit Value Value (d) Which of the three methods do you think Current Designs should use? Why? After you have completed CD4, consider the additional question. 1. Assume that the number of prototypes created for composite kayaks and rotomolded kayaks changed to 8 and 4 respectively. Redo instruction (c) and round cost assigned /unit to two decimal points