Background: Wilkerson Company's profit has been declining, especially for the pump line, as they continue to reduce pump line prices to stay competitive in the market. Yet, none of their competitors have tried to match Wilkerson's price for flow controllers. Robert Parker, president of Wilkerson Company, is considering whether the problem is the costing system, and is thus pondering whether to change their cost accounting system to an activity-based system. He believes it may help with pricing decisions for the three product lines: pumps, valves, and flow controllers. You have been hired by Robert Parker to help evaluate Wilkerson Company's costing system. Specifically, Robert wants your assessment of whether he should change its existing cost accounting system to an activity-based costing system. In addition, Robert also wants your assessment of what actions he can take to improve the company's profitability along with explanations for your recommendations. Quantitative Analysis (Individual Assignment) Complete all calculations using excel formulas and cell references and use the excel template provided for this case-based assignment. 1. Calculate (i) product cost per unit and (ii) profit margin per unit for Wilkerson's three product lines using Activity-Based Costing with activity rates calculated based on capacity level as total activity driver. a. You may use the ABC--Capacity worksheet in the Wilkerson Workbook file. b. Hint: Look for budgeted capacity information in the case (page 3 of the case) rather than exhibits, except for "Hours of engineering work" which could be found in Exhibit 4. Use the budgeted capacity information to complete cells D3-D7 in the ABC--Capacity worksheet in the Wilkerson Workbook file. 2. Calculate the cost of unused capacity at Wilkerson. a. You may use the Unused Capacity worksheet in the Wilkerson file. b. Hint: Unused capacity cost is the difference between total cost of activity (Exhibit 1) and total allocated overhead costs based on capacity level (Part 1 above). 3. Prepare graphs for a report on Product Costing at Wilkerson. a. Your graphs should draw attention to how the selection of costing method (the existing cost system, Exhibit 2, versus ABC, part 1 above) affects the cost per unit and gross margins for each of three products. Exhibit 1 Wilkerson Company: Operating Results (March 2000) Sales $2,152,500 100% Direct Labor Expense 271,250 Direct Materials Expense 458,000 Manufacturing Overhead Machine-related expenses 336,000 Setup labor 40,000 Receiving and production control 180,000 Engineering 100,000 Packaging and shipping 150,000 Total Manufacturing Overhead Gross Margin General, Selling & Admin Expense Operating Income (pre-tax) 806,000 617,250 29% 559,650 57,600 3% Exhibit 2 Product Profitability Analysis (March 2000) Direct labor cost Direct material cost Manufacturing overhead (@300%) Standard unit costs Target selling price Planned gross margin (%) Valves Pumps Flow Controllers $10.00 $12.50 $10.00 16.00 20.00 22.00 30.00 37.50 30.00 $56.00 $70.00 $62.00 $86.15 $107.69 $95.38 35% 35% 35% Actual selling price $86.00 $87.00 $105.00 Actual gross margin (%) 34.9% 19.5% 41.0% Exhibit 3 Product Data Product Lines Materials per unit Valves Pumps Flow Controllers 4 components 5 components 10 components 2 @ $2 = $4 3 @ $2 = $6 4 @ $1 = $4 2 @$ 6 = 12 2 @$ 7 = 14 5 @ $2 = 10 1 @ $8 Materials cost per unit $16 $20 8 $22 Direct labor per unit .40 DL hours .50 DL hours .40 DL hours Direct labor $/Unit @ 25/DL hour $10.00 $12.50 $ 10.00 (including employee benefits) Machine hours per unit 0.5 0.5 0.3 Exhibit 4 Monthly Production and Operating Statistics (March 2000) Valves Pumps Flow Controllers Total Production (units) Machine hours 7,500 12,500 4,000 24,000 3,750 6,250 1,200 11,200 Production runs 10 50 100 160 Number of shipments 10 70 220 300 Hours of engineering work 250 375 625 1,250 Calculation of activity rates: Activity Machine-related expenses Setup labor Receiving and production control Engineering Packaging and shipping ABC Manufacturing OH Allocations: Activity Machine-related expenses Setup labor Receiving and production control Engineering Packaging and shipping Total Manufacturing Overhead Manufacturing Overhead Per Unit: Total Manufacturing Overhead Number of Units Manufacturing Overhead Per Unit Total cost of activity Activity Driver Total Activity Driver Cost/Driver Valves Pumps Flow Controllers Total Valves Pumps Flow Controllers Product Costs Per Unit Using ABC: Valves Pumps Flow Controllers Direct materials Direct labor Manufacturing Overhead Per Unit Product Costs Per Unit Using ABC Profitability Analysis: Actual Selling Price Product Costs Per Unit Using ABC Gross Profit per unit Gross Margin % Valves Pumps Flow Controllers Costs of Unused Capacity Activity Machine-related expenses Setup labor Receiving and production control Engineering Packaging and shipping Total Manufacturing Overhead Valves Pumps Flow Controllers Total Allocated Overhead Cost Total cost of activity Costs of Unused Capacity