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-onder 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) Product costs: Materials used Direct labor: Labor rate Labor hours used Manufacturing overhead Total product cost per unit 10 S 10 20 25 S 20 20 $ 42 20 $ 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 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 SI 10. 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 S106. That is only a dollar more than our production cost. Are we that inefficient? What gives Maddie responded by saying, "I think this is cue 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, Maddie compiled the basic data needed to implement an activity based costing system. These data are displayed in the following table: Product Lines Activity Cost Pool Cost Driver Standar Heavy Deluxe $2,000 188,000 Depreciation of mach. Maintenance, machinery Machine hours 160,000 Machine hours ? ? ? Engineering, inspection, 17,000 2,250 17,000 Engineer Hours Engineer Hours Repair of defects 200 0 200 Purchasing, shipping # of material onders # of material orders v of material Material handling 2 21,000 7,500 21.500 Misc, manuf overhead Depreciation, taxes, and insurance for factory Factory sq. ft used Factory soft 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 Sorm (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 un it cost-S105 and target price $115.50. Please create tables similar to the format provided below. Also, show the formulas in order to show where you get the numbers from in order to do the calculations. Format Requirement : example Plantwide rate: Estimated MOH costs Estimated DL HRS - 2 = ? per DL HR Standard Model Deluxe Super Model Model Product costs & target price: Materials used Direct labor Manufacturing overhead Total production costs Estimated units to be produced Total unit cost Price markup Target price 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- $449.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 now 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 and 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 dogs REMCO have? What do you recommend and why