ACCOUNTING Format for excel assignment :

CURRECTED: ABC ORIGINAL PROBLEM DATA (W Excel File): CHECK FIGURES: AX40 Traditional Unit Cost: $62.75 EX90 ABC Unit Cost: $116.80 For several years, Adria Manufacturing has produced a single product called AX40. Then two years ago, the company automated a portion of its plant and at the same time introduced a second product called EX90 which has become increasingly popular. The EX90 is a more complex product, requiring one hour of direct labor time per unit to manufacture and extensive machining in the automated portion of the plant. The AX40 requires only 0.75 hour of direct labor time per unit and only a small amount of machining. Manufacturing overhead costs are currently assigned to products on the basis of direct labor hours. Despite the growing popularity of the company's EX90, profits have been declining steadily. Management is beginning to believe that there may be a problem with the company's costing system. Material and labor costs per unit are as follows: AX40 $35 EX90 $48 Direct materials Direct labor (0.75 hour and 1.0 hour @ $12 per hour) $9 $12 Management estimates that the company will incur $1,000,000 in manufacturing overhead costs during the current year and 40,000 units of the AX40 and 10,000 units of the EX90 will be produced and sold. REQUIRED: 1. Compute the predetermined manufacturing overhead rate assuming that the company continues to apply manufacturing overhead costs on the basis of direct labor hours. Using this rate and other data from the problem (Direct materials and labor cost), determine the unit product costs of each product. 2. Management is considering using activity-based costing to apply manufacturing overhead cost to products. The activity-based costing system would have the following activity cost pools and expected activity: Activity Cost Pool Maintaining parts inventory Processing purchase orders Quality control Machine related Activity Measure Number of part types Number of purchase orders Number of tests run Machine-hours Estimated Overhead Cost $180,000 90,000 230,000 500,000 Maintaining parts inventory Processing purchase orders Quality control Machine related Expected Activity AX40 EX 90 75 150 800 200 3,250 4.000 6,000 2,500 Determine the activity rate for each of the four activity cost pools. Using the activity rates, you computed, determine the total amount of manufacturing overhead cost that would be assigned to each product using the activity-based costing system. After these totals have been computed, determine the amount of manufacturing overhead cost per unit of each product. Compute the unit product cost of each product using this data and the other data from the problem (direct materials and labor cost). 3. From the data you have developed in parts 1 and 2 above, identify the following factors that may account for the company's decline in profits: Show all calculations. 1. Determine what percentage of the total sales is related to each product. 2. Determine what percentage of the total part types is related to each product. 3. Determine what percentage of the total tests is related to each product. 4. Determine what percentage of the total machine hours is related to each product. 5. Using this information, explain why you think profits have been declining. SAVING YOUR FILES: 1. Save the original file according to the following name format: Original data file:_(Your Last Name, First Name Initial) Excel#1. For Example: SmithJExcell.xls or SmithJExcell.xlsx (depending on which version of Microsoft you are using). 2. Save the What If file according to the following name format: Open the original file and save it under the new following name format: What If data file: (Your last name, First name initial). Excel IF#1 For Example: SmithJExcellF1.xls or SmithJExcellF1.xlsx (depending on which version of Microsoft you are using). ABC WHAT IF ANALYSIS DATA (2ND Excel File: CHECK FIGURES: AX40 Traditional Unit Cost: $59.98 EX90 ABC Unit Cost: $117.70 In your data block area (the highlighted area in your new What If file make the following changes: AX40 EX90 Direct materials cost per unit has changed for both products. $32.00 $50.00 The new union contract that the company signed at the end of last year increased labor costs for both models ($13.00 per hr.). $9.75 $13.00 The direct labor hours per unit has not changed. 0.75 hr. 1.0 hr. Due to the increase in demand for the EX90 product, production will increase next year. The production for AX40 will remain the same. 40,000 11,000 The company has made several improvements over the past year to improve efficiency and lower costs for the popular EX90 product. The following estimated overhead costs have changed for next year: Expected Activity AX40 EX 90 Estimated Overhead Cost Maintaining parts inventory* $164,925 Processing purchase orders $ 91.800 Quality control** $190,000 Machine related*** $549,900 150 800 2,000 4,000 220 2,750 7,700 *The company has changed to a computer based inventory management system which has improved the efficiency and lowered the overall cost of managing the parts inventory. **Improvements in production efficiency reduced the number of tests run on the AX40 and EX90 models thus lowering the total cost of quality control. ***Engineering improvements has reduced the amount of machine time needed to produce the EX90. Your spreadsheets should automatically recalculate using the new data. You should not have to change any of the cell references or formulas for either of your statements. (If you dothen you have done the cell referencing incorrectly.) You are only changing the Data Block area (the highlighted area). Adria Manufacturing GENERAL DATA: Annual sales in units Direct materials per unit Direct labor cost per unit Direct labor hours per unit Estimated Overhead Expected Activity Rates Applied Overhead (ABC) AX40 EX90 Total Sxxo xxx $ ? $ ? ACTIVITIES & ACTIVITY MEASURES Maintaining parts inventory (number of part types Processing purchase orders (number of purchase orders) Quality control (number of tests run) Machine related machine hours) ? $ ? $ $ $ ? ? ? $ $ ? ? 7 $ ? AX40 EX90 $ TRADITIONAL POHR: Estimated total manufacturing overhead Estimated total allocation base (DLH) POHR per DLH ? ? ? $ MOH Applied Per Unit (Traditional): MOH Applied Per Unit (ABC): $ $ ? ? $ $ ? ? AX40 EX90 UNIT PRODUCT COSTS (TRADITIONAL): Direct Materials Direct Labor Manufacturing Overhead Traditional Unit Product Cost UNIT PRODUCT COSTS (ABC): Direct Materials Direct Labor Manufacturing Overhead ABC Unit Product Cost