Randolph Electric Motor Company (REMCO) manufactures electric motors for commercial use. The company produces three models, designated as standard, deluxe, and super. The company uses a job-order costing system with manufacturing overhead applied based on direct labor hours. The system has been in place with little change for 25 years. Product costs and annual sales data are shown below: Standard Model 20,000 Deluxe Model 1,000 Super Model 10,000 Annual sales (units) $ $ $ 42 Product costs: Materials used Direct labor: Labor rate Labor hours used Manufacturing overhead Total product cost per unit 10 10 20 0.5 25 20 20 $ $ 20 1.0 Budgeted manufacturing overhead: Depreciation, machinery Maintenance, machinery Engineering Inspection and repair of defects Purchasing, receiving and shipping Material handling Depreciation, taxes, and insurance for factory Miscellaneous manufacturing overhead costs 1,480,000 120,000 350,000 375,000 250,000 400,000 Activity 1 Activity 1 Activity 2 Activity 2 Activity 3 Activity 3 300,000 Activity 4 295,000 Activity 4 Total budgeted manufacturing overhead 3,570,000 Target Price 110% For the past 10 years, the company's pricing formula has been to set cach product's target price at 110 percent of its full product costs. Recently, however, the standard-model motor has come under increasing price pressure from offshore competitors. The result was that the price on the standard model has been lowered to $110. John York, company president, recently asked Maddie Siler, the controller, "Why can we not compete with these other companies? They are selling motors just like our standard model for $106. That is only a dollar more than our production cost. Are we that inefficient? What gives? Maddie responded by saying, "I think this is due to an outmoded product-costing system. As you may remember, I raised a red flag about our system when I came on board last year. However, the decision was to keep our current system in place. In my judgment, our product-costing system is distorting our product costs. Let me run a few numbers to demonstrate what I mean." Getting John's permission to proceed, Maddic compiled the basic data needed to implement an activity-based costing system. These data are displayed in the following table: Product Lines Standar Cost Drivers Activity Cost Pool Heavy Deluxe Machine hours 160,000 Machine hours ? 52,000 Depreciation of mach. Maintenance, machinery 188,000 ? Engineering, inspection, 17,000 2,250 17,000 Engineer Hours Engineer. Hours Repair of defects 2,400 500 2,300 Purchasing, shipping, Material handling # of material orders # of material orders 7,500 used 21,500 Misc. manuf overhead Depreciation, taxes, and insurance for factory Factory sq. ft Factory s. A 21,000 ? Factory sq.ft used ? ? Required: 1. On Excel, compute the plant-wide predetermined manufacturing overhead rate. Also, prepare a table (see Format Example on REMCO Data Excel file) showing for each product line the estimated cost for direct materials, direct labor, and manufacturing overhead in total dollars and per unit. Calculate the target prices for the three models, based on the traditional, volume-based product-costing system. Pay attention to good form (using S sign at the beginning and total amounts, proper underlines and double underlines, no cents unless per unit amount, proper headings with name of entity, name of schedule, and date for the schedule and headings for columns (center)). Be sure to type a number only once in the worksheet (entire file) and refer to it using formulas. Check figures: Standard model per unit cost=$105 and target price =$115.50. 2. In an Excel table, compute the activity rates, new product costs for the three products, based on the new data collected by Maddie. Prepare a table, similar to Requirement 1, showing for each product line the estimated cost for each of the following cost elements: direct material, direct labor, machining, inspection, material handling, and miscellaneous overhead in total dollars and then per unit. Calculate the target prices for the three models, based on the activity-based costing system. Round to the nearest cent and follow good form as noted in requirement 1. Check figures: Deluxe model per unit cost- S449.75 and target price = $494.73. 3. Prepare a schedule on Excel comparing the target price for each model under the traditional and activity-based costing systems. Also on Excel, prepare a table showing each activity cost driver and the amount used by each model (one row for each driver showing the cost driver description on the left and the model name for each column heading, should have four rows and three columns). Using this table, prepare a bar chart that shows the percentage of the activity driver being consumed by each model (Excel, "Insert", "Bar"). Both of these should be included on your Excel file for submission. 4. In a Word document, use information developed in requirements 1-3 to explain what is happening as a result of the firm's traditional, volume-based product costing system. What strategic options does REMCO have? What do you recommend and why