Format :

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 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 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 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