Hello, I need help completing a spreadsheet with the following:

a) Compute the overhead rate using the traditional (plantwide ) approach

b) Compute the overhead rates using the activity-based costing approach. c) Determine the difference in allocation between the two approaches. I have attached the spreadsheet (2nd ) TAB E4-1

CD3 -Excel Tutorial CURRENT DESIGNS Building a kayak using the composite method is a very labor-intensive process. In the Fabrication Department, the kayaks go through several steps as employees carefully place layers of Kevlar in a mold and then use resin to fuse together the layers. The excess resin is removed with a vacuum process, and the upper shell and lower shell are removed from the molds and assembled. The seat, hatch, and other components are added in the Finishing Department. At the beginning of April, Current Designs had 30 kayaks in process in the Fabrication Department. Rick Thrune, the production manager, estimated that about 80% of the material costs had been added to these boats, which were about 50% complete with respect to the conversion costs. The costs of this inventory had been calculated to be $8,400 in materials and $9,000 in conversion costs. During April, 72 boats were started. At the end of the month, the 35 kayaks in the ending inventory had 20% of the materials and 40% of the conversion costs added to them. A review of the accounting records for April showed that materials with a cost of $17,500 had been requisitioned by the Fabrication Department and that the conversion costs for the month were $39,600. Instructions Complete a production report for April 2017 for the Fabrication Department using the weighted-average method. NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?" . CURRENT DESIGNS Fabrication Department Production Cost Report For the Month ended April 30, 2017 Quantities Units to be accounted for Work in process, April 1 Started into production Total units Units accounted for Transferred out Work in process, April 30 Total units Physical Units Equivalent Units Conversion Materials Costs 30 72 102 67 7 74 67 14 81 Materials Conversion Costs Total $25,900 74 $350 Costs Unit costs Total cost(a) Equivalent units (b) Unit costs (a) (b) 67 35 92 $48,600 81 $600 Costs to be accounted for Work in process, April 1 Started into production Total costs $74,500 $950.00 $17,400 57,100 $74,500 Cost Reconciliation Schedule Cost accounted for Transferred out Work in Process, April 30 Materials Conversion costs Total costs $63,650 $2,450 8,400 10,850 $74,500 After you have completed the requirements of CD3, consider this additional question. 1. Assume that 25 kayaks remained in ending work in process and conversion costs incurred during the period changed to 42,500. $60,000. Show the impact of this change on the calculation of equivalent units, unit cost, and on the cost reconciliation schedule. (Round calculation of cost per equivalent units to 3 decimal points.) CURRENT DESIGNS Fabrication Department Production Cost Report For the Month ended April 30, 2017 Quantities Units to be accounted for Work in process, April 1 Started into production Total units Units accounted for Physical Units 30 72 102 Equivalent Units Conversion Materials Costs Transferred out Work in process, April 30 Total units Costs Unit costs Total cost(a) Equivalent units (b) Unit costs (a) (b) 67 25 97 67 5 72 67 10 77 Materials Conversion Costs Total $42,500 72 $590 $60,000 77 $779 Costs to be accounted for Work in process, April 1 Started into production Total costs Cost Reconciliation Schedule Cost accounted for Transferred out Work in Process, April 30 Materials Conversion costs Total costs $102,500 $1,369.00 $17,400 57,100 $74,500 $63,650 $2,450 8,400 10,850 $74,500 E4-1 Assign overhead using traditional costing and ABC Saddle Inc. has two types of handbags: standard and custom. The controller has decided to use a plantwide overhead rate based on direct labor costs. The president has heard of activity-based costing and wants to see how the results would differ if this system was used. Two activity cost pools were developed: machining and machine setup. Presented below is information related to the company's operations. Standard Custom $50,000 $100,000 1,000 1,000 100 400 Direct labor costs Machine hours Setup hours Total estimated overhead costs are $240,000. Overhead cost allocated to the machining activity cost pool is $140,000 and $100,000 is allocated to the machine setup activity cost pool. Instructions (a) Compute the overhead rate using the traditional (plantwide) approach. (b) Compute the overhead rates using the activity-based costing approach. (c) Determine the difference in allocation between the two approaches. NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?" . (a) Estimated overhead Direct labor costs $240,000 $150,000 (b) = Predetermined overhead rate = Activity cost pools Machining Machine setup 160% of direct labor cost Cost drivers Machine hours Setup hours Estimated overhead $140,000 100,000 Activity-based overhead rates Machining: $1,000 1,000 = 2000 per machine hour Machine setup: $100 400 = $500 per setup hour (c ) Traditional costing: $50,000 x POH rate $100,000 x POH rate Total OH allocation Standard ? Custom ? ? ? Standard Custom Activity-based costing Machining: 1,000 MH x OH rate 1,000 MH x OH rate ? Machine setup: 100 Setup Hr x OH rate 400 Setup Hr x OH rate ? Total OH allocation ? ? ? ? After you have completed E4-1, consider the additional question. 1. Assume that total estimated overhead costs are $285,000. Overhead cost allocated to the machining activity cost pool is$150,000 and $135,000 is allocated to the machine setup activity cost pool. Redo instructions (a) to (c). E4-4 - Assign overhead using traditional costing and ABC Altex Inc. manufactures two products: car wheels and truck wheels. To determine the amount of overhead to assign to each product line, the controller, Robert Hermann, has developed the following information. Car 40,000 1 Estimated wheels produced Direct labor hours per wheel Truck 10,000 3 Total estimated overhead costs for the two product lines are $770,000. Instructions (a) Compute the overhead cost assigned to the car wheels and truck wheels, assuming that direct labor hours is used to allocate overhead costs. (b) Hermann is not satisfied with the traditional method of allocating overhead because he believes that most of the overhead costs relate to the truck wheels product line because of its complexity. He therefore develops the following three activity cost pools and related cost drivers to better understand these costs. Expected Use of Cost Drivers 1,000 setups 70,000 labor hours 1,200 inspections Activity Cost Pools Setting up machines Assembling Inspection Estimated Overhead Costs $220,000 280,000 270,000 Compute the activity-based overhead rates for these three cost pools. (c) Compute the cost that is assigned to the car wheels and truck wheels product lines using an activity-based costing system, given the following information. Expected Use of Cost Drivers per Product Car Number of setups 200 Direct labor hours 40,000 Number of inspections 100 Truck 800 30,000 1,100 (d) What do you believe Hermann should do? NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?" . (a) Direct labor hours for car wheels Direct labor hours for truck wheels Total Direct labor hours Value Value ? Total estimated overhead Total direct labor hours OH rate/DLH Value Value ? Overhead assigned: Car wheels ? ? ? Truck wheels ? ? ? Expected Use of Cost Drivers = Value Value Value ABC OH Rate ? ? ? Direct labor hours Overhead rate / direct labor hour Total OH assigned (b) (c ) Activity Cost Pool Setting up machines Assembling Inspection Estimated OH Value Value Value Car Wheels Activity Cost Pool Setting up machines Assembling Inspection Total cost assigned Expected Use of Cost Driver/Unit x Value Value Value ABC OH Rates Value Value Value Expected Use of Cost Driver/Unit x Value Value Value ? ABC OH Rates Value Value Value ? = Cost Assigned ? ? ? ? Truck Wheels Activity Cost Pool Setting up machines Assembling Inspection Total cost assigned (d) What do you believe Hermann should do? = Cost Assigned ? ? ? ? /setup /labor hour /inspection After you have completed E4-4, consider the additional question. 1. Assume that total estimated overhead costs changed to $850,000 and the estimated overhead for Setting up machines and Assembly changed to $260,000 and $320,000 respectively. Redo instruction (a) to (c). Round overhead rates to two decimal points. P4-2A Assign overhead to products using ABC and evaluate decision Schultz Electronics manufactures two ultra high-definition television models: the Royale which sells for $1,600 and a new model, the Majestic, which sells for $1,300. The production cost computed per unit under traditional costing for each model in 2017 as follows. Traditional Costing Direct materials Direct labor ($20 per hours) Manufacturing overhead ($38 per DLH) Total per unit cost Royale $700 120 228 $1,048 Majestic $420 100 190 $710 In 2017, Schultz manufactured 25,000 units of the Royale and 10,000 units of the Majestic. The overhead rate of $38 per direct labor hour was determined by dividing total expected manufacturing overhead of $7,600,000 by the total direct labor hours (200,000) for the two models. Under traditional costing, the gross profit on the models was Royale 552 ($1,600 - $1,048) and Majestic $590 ($1,300 - $710). Because of this difference, management is considering phasing out the Royale model and increasing the production of the Majestic model. Before finalizing its decision, management asks Schultz's controller to prepare an analysis using activity-based costing (ABC). The controller accumulates the following information about overhead for the year ended December 31, 2017. Activity Cost Pools Purchasing Machine setups Machining Quality control Estimated Overhead $1,200,000 900,000 4,800,000 700,000 Cost Drivers Number of orders Number of setups Machine hours Number of inspections Estimated Use of Cost Drivers 40,000 18,000 120,000 28,000 Activity-Based Overhead Rates $30/order $50/setup $40/hour $25/inspection The cost drivers used for each product were: Cost Drivers Purchase orders Machine setups Machine hours Inspections Royale 17,000 5,000 75,000 11,000 Majestic 23,000 13,000 45,000 17,000 Total 40,000 18,000 120,000 28,000 Instructions (a) Assign the total 2017 manufacturing overhead costs to the two products using activity-based costing (ABC) and determine the overhead cost per unit. (b) What was the cost per unit and gross profit of each model using ABC costing? (c) Are management's future plans for the two models sound? Explain. NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?" . (a) The allocation of total manufacturing overhead using activity-based costing is as follows: Royale Overhead Rate Drivers Used Cost Assigned Purchase order@$30 Value ? Machine setups @$50 Value ? Machine hours @$40 Value ? Inspections @$25 Value ? Total assigned costs (a) ? Units produced (b) Majestic Drivers Used Cost Assigned Value ? Value ? Value ? Value ? ? Value Value ? Cost per unit (a) (b) Total Overhead ? ? ? ? ? ? (b) The cost per unit and gross profit of each model under ABC costing were: Direct materials Direct labor Manufacturing overhead Total cost per unit Sales price per unit Cost per unit Gross profit per unit Royale Value Value Value ? Majestic Value Value Value ? Value Value ? Value Value ? (c) Are management's future plans for the two models sound? Explain. After you have completed P4-2A, consider the additional question. 1. Assume that the purchase orders used by Royale and Majestic changed to 19,000 and 21,000 respectively. Also assume that the number of inspections used by Royale and Majestic models changed to 12,000 and 16,000 respectively. Redo instsructions (a) to (c ) and round cost and gross profit per unit to two decimal points. CD4 - Excel 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 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 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 Composite Kayaks 3 6 12 12 15,000 Activity Cost Pools Cost Drivers Designing new models Number of models Creating and test prototypes Number of prototypes Creating molds for kayaks Number of molds Supervising production kaya Number of employees Curing time Number of days of curing time Driver Amount for Rotomolded Kayaks 1 2 1 12 2,000 What amount of overhead should be assigned to each composite kayak using this method? What amount of overhead 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 "?" . (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 Cost assigned per unit (b) Value Value ? Value ? Number of units Value Value Total Value ? Directly assigned Remaining amount allocated 50% to each product line Rotomolded ? 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 (a)_ Direct labor cost (b) Composite Rotomolded OH rate (a) (b) Value Value Value ? ? Composite Value (c) Rotomolded Value ? ? Value ? Directly assigned Remaining amount allocated based on direct labor costs Total Number of units Cost assigned per unit ? ? Value ? What amount of overhead should be assigned to each composite kayak and rotomolded kayak using the activity-based costing method? Activity Cost Pools Design Prototypes Molds Supervision Curing time Estimated Estimated Use Overhead of Cost Drivers Value Value Value Value Value Value Value Value Value Value ActivityBased OH Rates ? ? ? ? ? / / / / / model prototype mold employee day Composite Rotomolded Expected Use of Drivers Value Overhead Rates Value Cost Assigned ? Expected Use of Drivers Value Overhead Rates Value Cost Assigned ? Prototypes Molds Value Value Value Value ? ? Value Value Value Value ? ? Supervision Value Value ? Value Value ? Curing time Value Value ? Value Value ? Activity Cost Pools Design Total amount allocated Directly assigned Total Cost (a) Number of units(b) Cost assigned/unit (a) (b) (d) ? ? ? Value ? ? ? ? Value ? 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