Please complete the assignment using Excel.
BatchCo. produces and sells three models of yard waste shredders-Low, Mid, and High. The company currently uses a traditional absorption costing system to allocate manufacturing overhead costs to product lines. This traditional costing system allocates manufacturing overhead cost on the basis of each product's direct labor hours used. But the company's CEO, Adrian, recently attended a conference on Activity Based Costing (ABC), and he's considering implementing ABC at BatchCo. Adrian collected the following product data for the year just ended: Low Mid High Units produced and 3000 1200 800 sold Direct labor hours 9,000 6,000 6,400 used in production Direct labor cost used $135,000 $90,000 $96,000 in production Kilowatt Hours used 175,000 162,000 198,000 Number of product 1 1 1 lines Pounds of Materials 55,000 30,000 25,000 Handled Number of Employees 7 4 12 The company's CFO computed actual manufacturing overhead costs related to each of the following four key activities for the year just ended as: Activity Costs Activity Assembly Quality Control Materials Ordering Supervision Total Manufacturing Overhead Cost $580,000 $240,000 $120,000 $270.000 $1,210,000 Direct materials costs were $120 per unit for Low, $170 per unit for Mid, and $190 per unit for High. Required: 1. For the year just ended, allocate manufacturing overhead costs to each product line and compute a total manufacturing cost per unit for each product line using the traditional absorption costing approach based on direct labor hours. 2. Assume that the cost driver used for the Assembly activity is the number of kilowatt hours used, for the Quality Control activity is the number of product lines, for the Materials Ordering activity is the number of pounds of materials handled, and for the Supervision activity is the number of employees. Allocate manufacturing overhead costs for the year just ended to each product line and compute a total manufacturing cost per unit for each product line using Activity Based Costing. 3. Which of the two methods from questions 1 and 2 is better for decision management? Thoroughly explain why that method is better for decision management. 4. Suppose BatchCo, has been pricing its products for sale using a simple pricing rule of adding a 20% markup to its computed product cost. Compute the unit selling prices for each individual product line on the basis of that product line's per unit product cost, as computed in each of questions 1 and 2. Discuss the differences in selling prices between products and across product costing methods. What recommendations can you make based on this data? 5. Which method for allocating manufacturing overhead costs to products, a traditional absorption costing (like in question 1) approach or an ABC approach (like in question 2), is better for decision control purposes? Thoroughly explain why
