Hello,

Will you create an excel spreadsheet for the following:

(a) Compute the physical units for January and May

(b) Compute the equivalent units of productions for (1) materials and (2) conversion costs for each month. I have attached the spreadsheet.

SEE TAB E-5

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 Costs Unit costs Total cost(a) Equivalent units (b) Unit costs (a) (b) Physical Units Equivalent Units Conversion Materials Costs Value Value ? Value Value ? Value Value ? Value ? ? Materials Conversion Costs Total ? Value ? ? Value ? 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 ? ? ? ? ? ? ? ? ? ? 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.) 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 2,000 500 Activity-based overhead rates Machining: $140,000 2,000 = 70 Machine setup: $100,000 500 = $200 per machine hour per setup hour (c ) Traditional costing: Standard 80,000 $50,000 x POH rate $100,000 x POH rate Custom 160,000 Total OH allocation 80,000 160,000 Activity-based costing Standard Machining: 1,000 MH x OH rate 1,000 MH x OH rate Custom 70,000 70,000 Machine setup: 100 Setup Hr x OH rate 400 Setup Hr x OH rate $20,000 $80,000 Total OH allocation 90,000 150,000 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). (a) Estimated overhead Direct labor costs $285,000 $150,000 (b) Activity cost pools Machining Machine setup = Predetermined overhead rate = 190% of direct labor cost Cost drivers Machine hours Setup hours Activity-based overhead rates Estimated overhead $150,000 135,000 Machining: $150,000 2,000 = 75 Machine setup: $135,000 500 = $270 per machine hour per setup hour (c ) Traditional costing: $50,000 x POH rate $100,000 x POH rate Total OH allocation Standard 95,000 Custom 190,000 95,000 190,000 Activity-based costing Standard 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 Custom 75,000 75,000 $27,000 $108,000 102,000 183,000 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 Cost Drivers Number of orders Number of setups Machine hours Number of inspections Estimated Overhead $1,200,000 900,000 4,800,000 700,000 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 17,000 $510,000 Machine setups @$50 5,000 250,000 Machine hours @$40 75,000 3,000,000 Inspections @$25 11,000 275,000 Total assigned costs (a) $4,035,000 Majestic Drivers Used Cost Assigned 23,000 $690,000 13,000 $650,000 45,000 $1,800,000 17,000 $425,000 $3,565,000 Units produced (b) 25,000 10,000 Cost per unit (a) (b) $161.40 Total Overhead $1,200,000 $900,000 $4,800,000 $700,000 $7,600,000 $356.50 (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 $700.00 $120.00 161.40 $981.40 Majestic $420.00 $100.00 $356.50 $876.50 $1,600.00 981.40 $618.60 $1,300.00 876.50 $423.50 (c) Are management's future plans for the two models sound? Explain. No, management should not phase out the Royale model as it has a higher gross profit per unit compared to the Majestic brand. This discrepancy was caused due to the use of a plant-wide traditional overhead rate approach which led to the over-allocation of manufacturing overhead costs to the Royale model, when those costs were truly attributable to the Majestic brand. 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. (a) The allocation of total manufacturing overhead using activity-based costing is as follows: Royale Overhead Rate Drivers Used Cost Assigned Purchase order@$30 19,000 $570,000 Machine setups @$50 5,000 250,000 Machine hours @$40 75,000 3,000,000 Inspections @$25 12,000 300,000 Total assigned costs (a) $4,120,000 Majestic Drivers Used Cost Assigned 21,000 $630,000 13,000 $650,000 45,000 $1,800,000 16,000 $400,000 $3,480,000 Units produced (b) 25,000 10,000 Cost per unit (a) (b) $164.80 $348.00 (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 $700.00 $120.00 164.80 $984.80 Majestic $420.00 $100.00 $348.00 $868.00 $1,600.00 984.80 $615.20 $1,300.00 868.00 $432.00 (c) Are management's future plans for the two models sound? Explain. No, management should not phase out because the Royale model continues to provide the Company with the greater gross profit per unit. Total Overhead $1,200,000 $900,000 $4,800,000 $700,000 $7,600,000 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 Rotomolded Directly assigned Remaining amount allocated 50% to each product line $28,000 $40,000 416,000 416,000 Total 444,000 456,000 1,000 4,000 $444.00 $114.00 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 Total OH costs (a)_ Direct labor cost (b) Composite $234,000 Rotomolded $286,000 OH rate (a) (b) $832,000 $520,000 $1.60 Composite $28,000 (c) Rotomolded $40,000 374,400 402,400 1,000 $402.40 Directly assigned Remaining amount allocated based on direct labor costs Total Number of units Cost assigned per unit 457,600 497,600 4,000 $124.40 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 ActivityEstimated Estimated Use Based OH Overhead of Cost Drivers Rates 121,000 4 $30,250.00 152,000 8 $19,000.00 188,500 13 $14,500.00 180,000 24 $7,500.00 190,500 17,000 $11.21 / / / / / model prototype mold employee day Composite Rotomolded Expected Use of Drivers 3 Overhead Rates $30,250 Cost Assigned $90,750 Expected Use of Drivers 1 Prototypes Molds 6 12 $19,000 $14,500 $114,000 $174,000 2 1 $19,000 $14,500 $38,000 $14,500 Supervision 12 $7,500 $90,000 12 $7,500 $90,000 Curing time 15000 $11 $168,088 2000 $11 $22,412 Activity Cost Pools Design Total amount allocated Directly assigned Total Cost (a) Number of units(b) Cost assigned/unit (a) (b) (d) Overhead Rates Cost Assigned $30,250 $30,250 $636,838 $28,000 $664,838 1,000 $664.84 $195,162 $40,000 $235,162 4,000 $58.79 Which of the three methods do you think Current Designs should use? Why? I would recommend using the ABC costing method - This is because the overhead is comprised of many different activities and, therefore, using ABC method would provide the most appropriate allocation of the costs. 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. (c) 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 ActivityEstimated Estimated Use Based OH Overhead of Cost Drivers Rates 121,000 4 $30,250.00 152,000 12 $12,666.67 188,500 13 $14,500.00 180,000 24 $7,500.00 190,500 17,000 $11.21 / / / / / model prototype mold employee day Composite Activity Cost Pools Design Prototypes Molds Supervision Curing time Expected Use of Drivers 3 8 12 12 15000 Overhead Rates $30,250 $12,667 $14,500 $7,500 $11 Rotomolded Cost Assigned $90,750 $101,333 $174,000 $90,000 $168,088 Expected Use of Drivers 1 4 1 12 2000 Overhead Rates Cost Assigned $30,250 $30,250 $12,667 $50,667 $14,500 $14,500 $7,500 $90,000 $11 $22,412 Total amount allocated $624,172 $207,828 Directly assigned $28,000 $40,000 Total Cost (a) Number of units(b) Cost assigned/unit (a) (b) $652,172 1,000 $652.17 $247,828 4,000 $61.96 In Shady Company, materials are entered at the beginning of each process. Work in process inventories, with the prcentage of work don on conversion costs, and production data Beginning Work in Process Months January March May July Ending Work in Process Conversion Cost% Units 0 0 0 0 Units Transferred Out Units 11,000 12,000 14,000 10,000 (a) Compute the physical units for January and May (b) Compute the equivalent units of production for (1) materials and (2) conversion costs for each month Conversion Cost% 2,000 60 3,000 30 7,000 80 1,500 40 on data for it Strilizing Department in selected months during 2017 are as follows