You will be submitting two excel files to Bb for this assignment. REQUIREMENTS: Prepare an analysis of the estimated cost for Adria Manufacturing company's two products contrasting a traditional costing system and an ABC system using the data in the problem data file posted to Blackboard. (This will be your first excel file) Answer Part 3 under the requirements on a separate sheet in your first excel file only. Rename the sheet: Part 3. You do not need to provide an analysis for the What If file. Complete the "What If Analysis described below. (This will be your second excel file) DATA BLOCK AREA: Your first step is to set up a Data Block (or data entry) area and enter the data from the problem. You will use this area as a "data entry" area and then "cell reference" this information to complete your analysis report contrasting traditional vs. ABC product cost. You can use the following format for your data block/data entry area (see the highlighted area below). Highlight the data entry area with a #color of your choice. Make sure the color is not too dark and that it looks professional. Adria Manufacturing GENERAL DATA: AX40 XXXXX EX90 XXXXX $ XXXX $ xxxx Annual sales in units Direct materials per unit Direct labor cost per unit Direct labor hours per unit $ XXXX $ XXXX XXX XXX Estimated Overhead Activity Rates Expected Activity EX90 Applied Overhead (ABC) AX40 EX90 Cost AX40 Total $XXXXXX XXXXXXX 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) XXXXXX XXXXXXX $ ? $ ? $ ? $ ? XXXXXXX XXXXXXX XXXXXXX $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? S ? XXXXXX XXXXXXX ? XXXXXX XXXXXXX 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 AX40 EX90 $ ? UNIT PRODUCT COSTS (ABC): Direct Materials Direct Labor Manufacturing Overhead ABC Unit Product Cost ABC ORIGINAL PROBLEM DATA (1ST 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: 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 1 500,000 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. ABC WHAT IF ANALYSIS DATA (2ND Excel File): CHECK FIGURES: AX40 Traditional Unit Cost: $59.98 EX90 ABC Unit Cost: $117.70 The "What If part of the assignment will help you determine whether you have correctly used cell referencing in your spreadsheets. The country's economic situation has improved over the past year and Adria Manufacturing has had some cost changes. 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. 4. 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 5. 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: Estimated Overhead Cost Expected Activity AX40 EX 90 LUSU LA U Maintaining parts inventory* $164,925 Processing purchase orders $ 91,800 Quality control** $190,000 Machine related*** $459,900 75 800 2,000 4,000 150 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 do then you have done the cell referencing incorrectly.) You are only changing the Data Block area (the highlighted area). You will be submitting two excel files to Bb for this assignment. REQUIREMENTS: Prepare an analysis of the estimated cost for Adria Manufacturing company's two products contrasting a traditional costing system and an ABC system using the data in the problem data file posted to Blackboard. (This will be your first excel file) Answer Part 3 under the requirements on a separate sheet in your first excel file only. Rename the sheet: Part 3. You do not need to provide an analysis for the What If file. Complete the "What If Analysis described below. (This will be your second excel file) DATA BLOCK AREA: Your first step is to set up a Data Block (or data entry) area and enter the data from the problem. You will use this area as a "data entry" area and then "cell reference" this information to complete your analysis report contrasting traditional vs. ABC product cost. You can use the following format for your data block/data entry area (see the highlighted area below). Highlight the data entry area with a #color of your choice. Make sure the color is not too dark and that it looks professional. Adria Manufacturing GENERAL DATA: AX40 XXXXX EX90 XXXXX $ XXXX $ xxxx Annual sales in units Direct materials per unit Direct labor cost per unit Direct labor hours per unit $ XXXX $ XXXX XXX XXX Estimated Overhead Activity Rates Expected Activity EX90 Applied Overhead (ABC) AX40 EX90 Cost AX40 Total $XXXXXX XXXXXXX 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) XXXXXX XXXXXXX $ ? $ ? $ ? $ ? XXXXXXX XXXXXXX XXXXXXX $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? S ? XXXXXX XXXXXXX ? XXXXXX XXXXXXX 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 AX40 EX90 $ ? UNIT PRODUCT COSTS (ABC): Direct Materials Direct Labor Manufacturing Overhead ABC Unit Product Cost ABC ORIGINAL PROBLEM DATA (1ST 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: 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 1 500,000 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. ABC WHAT IF ANALYSIS DATA (2ND Excel File): CHECK FIGURES: AX40 Traditional Unit Cost: $59.98 EX90 ABC Unit Cost: $117.70 The "What If part of the assignment will help you determine whether you have correctly used cell referencing in your spreadsheets. The country's economic situation has improved over the past year and Adria Manufacturing has had some cost changes. 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. 4. 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 5. 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: Estimated Overhead Cost Expected Activity AX40 EX 90 LUSU LA U Maintaining parts inventory* $164,925 Processing purchase orders $ 91,800 Quality control** $190,000 Machine related*** $459,900 75 800 2,000 4,000 150 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 do then you have done the cell referencing incorrectly.) You are only changing the Data Block area (the highlighted area)