CORRECTED: ABC ORIGINAL PROBLEM DATA (18I 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 S35 EX90 S48 Direct materials Direct labor (0.75 hour and 1.0 hour a $12 per hour) S9 S12 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: 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 D'Focus E I 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 Estimated Overhead Cost Number of part types S180,000 Number of purchase orders l _ 90,000 Number of tests run 230,000 Machine-hours 5 00000 Expected Activits 90 150 Maintaining parts inventory hase orders Processing purchase orders Quality control 800 2,500 200 3.250 Page 1 of 12 0W @ 2102% 2 llum tenternet can contain Viruses. Unless you need to edit it's safer to stay in Protected view Enable Editing Machine related 4,000 6,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). 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 cach product. 3. 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: SmithExcell.xls or SmithExcell.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: SmithExcellF1 xls or Smith ExcellF1.xlsx (depending on which version of Microsoft you are using). 10- ABC WHAT IF ANALYSIS DATA (2ND Excel File): Focus Type here to search Ein BSW @ W2020 O i s safer to stay in Protected View. Enable Editing CHECK FIGURES: AX40 Traditional Unit Cost: $59.98 EX90 ABC Unit Cost: S117.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.). 59.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 942 words Type here to search Et in SO 2200 Page 1 of 3 E O Umes you need to edit, it's safer to stay in Protected View Share Comments Enable Editing 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: Estimated Overhead Cost Maintaining parts inventory* $164.925 Processing purchase orders $ 91,800 Quality control** $190,000 Machine related*** $549,900 Expected Activity AX 40 EX 90 150 800 220 2,750 7,700 4.000 "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). 942 words . Focus Type here to search E i n 30 PM St $0W @ 211/2000 ACCY 207 EXCEL ASSIGNMENT #1 Spring 2020 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 EX90 Annual sales in units Direct materials per unit Direct labor cost per unit Direct labor hours per unit E Page 1 of 3 718 words D. Focus R w 3 A 7:47 PM 2/17/2020 11 O Type here to search PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Direct labor hours per unit Activity Expected Activity Applied Overhead (ABC) Ex90 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 TRADITIONAL POHR: Focus H D - Page 1 of 3 words gi 7:43 PM 2/172020 O Type here to search PROTECTED VIEW Be care files from the Internet can con TRADITIONAL POHR: Estimated total manufacturing overhead Estimated total allocation base (DLH) POHR per DLH MOH Applied Per Unit (Traditional MOH Applied Per Unit (ABC) 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 748 PM 1 O Type here to search SAVING YOUR FILES: See the instructions in your problem data file SUBMISSION OF YOUR EXCEL ASSIGNMENTS: Put a fogter on each page in the bottom right-hand corner which You will somit TWO files to Rh Follow the instructions on Bb for submitting both your files. DO NOT CLICK SUBMIT UNTIL YOU HAVE ATTACHED BOTH FILES TO THE BO ASSIGNMENT SITE. You will need to attach one file, then add another file attachment before you submit the excel files to Bb. You can click the save button first, then check to be sure both files are listed under "Attached Fides" before clicking the Submit button The original file should contain the following items: 1. Data Block area with the original problem data Analysis contrasting Traditional and ABC product cost for Adria Manufacturing (using the original problem data) The what if file should contain the following items Data block area with the "What If Analysis" data Analysis contrasting Traditional and ABC product cost for Adria Manufacturing (using "What if" data) Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets or copy files (including the data block area). We will assume you cheated and you both (or all) will get a zero for the signment Save your work fregistently Do not be the next person telling horror stories about lost work! Back up your work on a disk! Do not fail to do this! There have been many students over the years that have had to redo the entire assignment because of a lack of a backup copy or failing to save their work frequently while they were working on it # O Type here to search 203 PM 2/17/22093 PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Direct labor hours per unit Activity Expected Activity Applied Overhead (ABC) 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 TRADITIONAL POHR: focus - Page 1 Tord 2:43 PM 217/2020 O Type here to search