To receive credit for this assignment you must complete the following required elements:

• DATA BLOCK AREA included and the data cell referenced to your ABC Analysis report.
• CELL REFERENCING: ABC Analysis Report with cell referencing from data block area and other parts of the ABC Report. All ABC amounts must be supported by cell referencing or formulas using cell referencing except for the data entry area.

If you can't read the screenshots, please try zooming in

Please provide a solution following the format, and with formulas. I recommend just uploading a link to an actual excel file so you don't have to manually type the formulas every time. !!!!!!

AutoSave C H 2 0 . Excel #1 Instructions Spring 2000 (1) Compatibility Mode - Word Search Carson Lozano C - 0 x Comments File Home Insert Draw Design Layout References Mailings Review View Help Table Design Layout Share ACCY 207 EXCEL ASSIGNMENT #1 Spring 2020 Direct labor cost per unit Direct labor hours per unit You will be submitting two excel Gles to Bb for this assignment. Estimated REQUIREMENTS: Prepare an analysis of the estimated cost for Adria Manufacturing company's two products contrasting a traditional casting system and an ABC system using the data in the problem data file posted to Blackhaard. (This will be your first excel file) Answer Part 3 under the requirements on a separate sheer 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 he your second excel fila) Activity Overhead Fxpected Activity Rates Applied Overhead ABC) Total AXAD FX901 ACTIVITIES & ACTIVITY MEASURES: Maintaining parts inventory (number of part types) Processine purchase orders (number of purchase orders Quality control (number vi test 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 hughlighted ca below). Highlight the data entry area wath a color of your choice. Make sure the color is not too dark + and that it looks professional $ ? ? $? ? ? ? ? ? ? $ ? $ ? ? Machine related machine hours Adria Manufacturing GENERAL DATA: _; _ _ AXAD_ EX90 Annual sales in units TRADITIONAL POHR: Estimated total manufacturing Overhead Estimated total allocation base (CLH) Direct materials per unit POHR per DLH $ ? Fotlow the inztructions en Bboc sobmitting both your files. DO NOT CLICK SUBMIT UNTIL YOU HAVE ATTACHED BOTH FILES TO THE BE ASSIGNMENT SITE. You will need to schon le then and the file attachment before you submit the excelles to Bb You can click the same button first, then check to be seeb iles are listed under "Atached Files" before clicking the Sumit buka MOH Applied Per Unit (Traditional: MOH Applied Per Unit (ABC): $ $ ? ? $ $ ? ? The inlile should contain the following us 1. Data Block area with the Cirical probiem data Page 1 of 4 718 words D FF - + 10% us ? ^ 1:40 PM Type here to search - 3 9 . . 3/24/2020 F Carson Lozano C - AutoSave C H 2 0 C File Home Insert Draw Annual sales in units xcel #1 Instructions Spring 2000 (1) Compatibility Mode - Word Layout References Mailings Review View Help Search Table Design 0 x Comments Design Layout Share overhead Estimated total allocation base (DIH) Direct materials per unit POHR per ULH Follow the lines on Bb Reuniting oth your files DO NOT CLICK SUBMIT UNTIL YOU HAVE ATTACHED BOTH FILES TO THE Bb ASSICNMENT SITE. You will need to attach one zle, then add another file attachment before you subut the excel files lu Bb. You can clbck the button first, then check tube szoboch als are listed der Attached Files" before clicking the MOH Applied Per Unit Traditional MOH Applied Per Unit (ABC): $ $ The animale should contain the following items: 1. Data Block area with the original problem data Analysis contrasting Traditional and ABC product cost for Adra Manufacturing (using this original problem data) The what if file should coctain the following items: Data block area with the W IP Analysis dat 2. Analysis contrasting Traditional and ABC product cost for Adna Manufacturing (using What If data) UNIT PRODUCT COSTS (TRADITIONAL): Direct Materials Direct Labor Manufacturing Overhead Traditional Unit Product Cost c h deu t el Du NOT tun ; ; ; ; Portoghal yun isuudes. Yumunk us that au in duplicate spreadsheets or copy files (including the data bloc UNIT PRODUCT COSTS (ABC) Direct Materials Direct Laber Manufacturing Overhead ABC Unit Product Cost Save your work frequently! Snart bede text person the horror stories about work Hack wp wour work on a disk! Da not fail to do this! There have been m y students over be years that have had to redo the entire assiemment because oi a lack of a backup cap orailing to work t uently while they are working ; ; ; ; SAVING YOUR FILES See the structions in your problem data file SUBMISSION OF YOUR EXCEL ASSIGNMENTS: Put a footer ou each page in the bottom right hand corner which includes your name and ZID. You will mit TWO G to Bb. Page 1 of 4 718 words EX - Drows ? RA + 10% 1:40 PM 3/24/20202 Type here to search . AutoSave A O C Fuel Adria Problem Data File Spring 2020 Revised Feb 13 (1) Compatibility Made Word Search Carson Lozano - x Comments File Home Insert Draw Design Layout References Mailings Review View Help Share CORRECTED: ABC ORIGINAL PROBLEM DATA (1ST Excel File): Eupected Acti EX 150 CITECK FIGURES: AX40 Traditional Unit Cost: 562.75 EXO ABC Unit Cost: $116.80 Matinine parts Processing purchase orders Quality consol Machine related 500 2500 4,000 950 6.000 For several years, Adria Manufacturing his produced a single product called AX 40. Then eve years ago, the con sumed a portion of prind at the same time troduced a second prodat called EX90 which has become singly popular The FX90 is a more complex product require one hour of direct labortione per i 10 facture and extensive machining me nted on of the plan The AXDrucey 0.75 hou odot labore wanit and only a small amount of machining M aterne overhead costs are currently assigned to products on the oldest labor hos Determine the activity rate for each of the four activity cost poole tising the activity rates, you computed, determine the total amount of manufacturing overbead cost that w ld be seed to cach producing the activity based come wiem Ata those totals have been computed, determine the amount of facture overhead cost per ut of each product. Compute thout product cost of each product Use thas data and the other data from the problem (direct manaleid laber cos) Despite the growing popularity of the company's LX90, profits bave been declining steadily. Mer ci bem la behest there may be oblem with the company's system. Material and labor costs per unit are as follors: Sus AXD FX90 Direct materials $45 Doct (0.75 hod 10 hou S12 per hour) $12 Management estimates that the company willine $1.000.000 in manufacturing overheadcasts during the current year and 10,000 units of the AXA0 and 10,000 units of the LX90 will be nodod sold 3. Fimm the date you have develo p e d 2 bose, wify the fut that may account for the company's decluse in profits Show all calculaticus 1. Dette what percee ol' tutol sales tied to each product. 2 Dermine what percentage of the total part types is related to nach recht 3. Determine what percentage of the total tests is related to eacl product. 4 Detamine what p a ge of the total machine bus is rented to cach product 3. Using this intention, explain why you dunk protits have been decu SAVING YOUR UILES: 1. Save the original file exording to the following me formati Original data file Your Last Name Tin Name Taitial cell For example: SmithExcell.xlso Sm Excellules (deping on which won of Microsoft you are inp) REQUIRED 1 Commte the predetermind manufacturine overhead inte mine that the company continues to apply manufacturing overbead costs on the basis of direct labec hours. Using this tale other dain the problem ( Domains and labor cost) determine the sait procact costs of each podact 2. Masing consider bused costing to wpply manutacturing overhead cost to products The tiviry-hand contine wym wald have the following activity cost peole and expected activity 2. Save the What If file sccording to the following mame format Open the orirical file and save it under the new following name format: What If data file: (Your last c ustome , Excel -1 For Example SmithJxTF 1 xlsor Smith Fixa (deding on which version of Microsoft you are in ABC WHAT IF ANALYSIS DATA 20 Feel Pile) Activity Cost Pool Maintaining nea r Processing purchase order Quality control Machine related Activity Measure Niemer af Number of purchase orders Neuber of tests Machine Leare Estimated Overhead Cost SI80 000 90.000 230,000 5001000 CHECK FIGURES AX40 Traditional Unit Cost $59.99 EX90 ABC Unit Cont: $117.20 The "What If part of the ass e nt will help you determine whether you have correctly used cell refremgm youre They c ome over the past year and Adria Moctaring has had some coste Page 1 of 1 912 words - res E . - o locus @ ^ Type here to search e a W - 1:16 PM 3/24/20202 = AutoSave C . Fxral Adria Problem Data File Spring 2020 Revised Feb 18(1)(1). Compatibility Mode - Word Search Carson Lozano C - 0 x File Home Insert Draw Design Layout References Mailings Review View Help Share Comments 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: Estimated Overhead Cost Maintaining parts inventory* $164,925 Processing purchase orders S 91.800 Quality control** $190.000 Machine related** $549,900 75 Expected Activity AX40 EX 90 150 800 220 2.000 2.750 4,000 7,700 The company has changed to a compuler based inventory managertel system which has improved the efficiency and lowered the overall cost of managing the paits 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) Page 1 of 3 942 words FA - Type here to search o n Focus ^ O e e 3 . a or 1 + 100% 1:20 PM 3/24/20202 AutoSave C H 2 0 . Excel #1 Instructions Spring 2000 (1) Compatibility Mode - Word Search Carson Lozano C - 0 x Comments File Home Insert Draw Design Layout References Mailings Review View Help Table Design Layout Share ACCY 207 EXCEL ASSIGNMENT #1 Spring 2020 Direct labor cost per unit Direct labor hours per unit You will be submitting two excel Gles to Bb for this assignment. Estimated REQUIREMENTS: Prepare an analysis of the estimated cost for Adria Manufacturing company's two products contrasting a traditional casting system and an ABC system using the data in the problem data file posted to Blackhaard. (This will be your first excel file) Answer Part 3 under the requirements on a separate sheer 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 he your second excel fila) Activity Overhead Fxpected Activity Rates Applied Overhead ABC) Total AXAD FX901 ACTIVITIES & ACTIVITY MEASURES: Maintaining parts inventory (number of part types) Processine purchase orders (number of purchase orders Quality control (number vi test 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 hughlighted ca below). Highlight the data entry area wath a color of your choice. Make sure the color is not too dark + and that it looks professional $ ? ? $? ? ? ? ? ? ? $ ? $ ? ? Machine related machine hours Adria Manufacturing GENERAL DATA: _; _ _ AXAD_ EX90 Annual sales in units TRADITIONAL POHR: Estimated total manufacturing Overhead Estimated total allocation base (CLH) Direct materials per unit POHR per DLH $ ? Fotlow the inztructions en Bboc sobmitting both your files. DO NOT CLICK SUBMIT UNTIL YOU HAVE ATTACHED BOTH FILES TO THE BE ASSIGNMENT SITE. You will need to schon le then and the file attachment before you submit the excelles to Bb You can click the same button first, then check to be seeb iles are listed under "Atached Files" before clicking the Sumit buka MOH Applied Per Unit (Traditional: MOH Applied Per Unit (ABC): $ $ ? ? $ $ ? ? The inlile should contain the following us 1. Data Block area with the Cirical probiem data Page 1 of 4 718 words D FF - + 10% us ? ^ 1:40 PM Type here to search - 3 9 . . 3/24/2020 F Carson Lozano C - AutoSave C H 2 0 C File Home Insert Draw Annual sales in units xcel #1 Instructions Spring 2000 (1) Compatibility Mode - Word Layout References Mailings Review View Help Search Table Design 0 x Comments Design Layout Share overhead Estimated total allocation base (DIH) Direct materials per unit POHR per ULH Follow the lines on Bb Reuniting oth your files DO NOT CLICK SUBMIT UNTIL YOU HAVE ATTACHED BOTH FILES TO THE Bb ASSICNMENT SITE. You will need to attach one zle, then add another file attachment before you subut the excel files lu Bb. You can clbck the button first, then check tube szoboch als are listed der Attached Files" before clicking the MOH Applied Per Unit Traditional MOH Applied Per Unit (ABC): $ $ The animale should contain the following items: 1. Data Block area with the original problem data Analysis contrasting Traditional and ABC product cost for Adra Manufacturing (using this original problem data) The what if file should coctain the following items: Data block area with the W IP Analysis dat 2. Analysis contrasting Traditional and ABC product cost for Adna Manufacturing (using What If data) UNIT PRODUCT COSTS (TRADITIONAL): Direct Materials Direct Labor Manufacturing Overhead Traditional Unit Product Cost c h deu t el Du NOT tun ; ; ; ; Portoghal yun isuudes. Yumunk us that au in duplicate spreadsheets or copy files (including the data bloc UNIT PRODUCT COSTS (ABC) Direct Materials Direct Laber Manufacturing Overhead ABC Unit Product Cost Save your work frequently! Snart bede text person the horror stories about work Hack wp wour work on a disk! Da not fail to do this! There have been m y students over be years that have had to redo the entire assiemment because oi a lack of a backup cap orailing to work t uently while they are working ; ; ; ; SAVING YOUR FILES See the structions in your problem data file SUBMISSION OF YOUR EXCEL ASSIGNMENTS: Put a footer ou each page in the bottom right hand corner which includes your name and ZID. You will mit TWO G to Bb. Page 1 of 4 718 words EX - Drows ? RA + 10% 1:40 PM 3/24/20202 Type here to search . AutoSave A O C Fuel Adria Problem Data File Spring 2020 Revised Feb 13 (1) Compatibility Made Word Search Carson Lozano - x Comments File Home Insert Draw Design Layout References Mailings Review View Help Share CORRECTED: ABC ORIGINAL PROBLEM DATA (1ST Excel File): Eupected Acti EX 150 CITECK FIGURES: AX40 Traditional Unit Cost: 562.75 EXO ABC Unit Cost: $116.80 Matinine parts Processing purchase orders Quality consol Machine related 500 2500 4,000 950 6.000 For several years, Adria Manufacturing his produced a single product called AX 40. Then eve years ago, the con sumed a portion of prind at the same time troduced a second prodat called EX90 which has become singly popular The FX90 is a more complex product require one hour of direct labortione per i 10 facture and extensive machining me nted on of the plan The AXDrucey 0.75 hou odot labore wanit and only a small amount of machining M aterne overhead costs are currently assigned to products on the oldest labor hos Determine the activity rate for each of the four activity cost poole tising the activity rates, you computed, determine the total amount of manufacturing overbead cost that w ld be seed to cach producing the activity based come wiem Ata those totals have been computed, determine the amount of facture overhead cost per ut of each product. Compute thout product cost of each product Use thas data and the other data from the problem (direct manaleid laber cos) Despite the growing popularity of the company's LX90, profits bave been declining steadily. Mer ci bem la behest there may be oblem with the company's system. Material and labor costs per unit are as follors: Sus AXD FX90 Direct materials $45 Doct (0.75 hod 10 hou S12 per hour) $12 Management estimates that the company willine $1.000.000 in manufacturing overheadcasts during the current year and 10,000 units of the AXA0 and 10,000 units of the LX90 will be nodod sold 3. Fimm the date you have develo p e d 2 bose, wify the fut that may account for the company's decluse in profits Show all calculaticus 1. Dette what percee ol' tutol sales tied to each product. 2 Dermine what percentage of the total part types is related to nach recht 3. Determine what percentage of the total tests is related to eacl product. 4 Detamine what p a ge of the total machine bus is rented to cach product 3. Using this intention, explain why you dunk protits have been decu SAVING YOUR UILES: 1. Save the original file exording to the following me formati Original data file Your Last Name Tin Name Taitial cell For example: SmithExcell.xlso Sm Excellules (deping on which won of Microsoft you are inp) REQUIRED 1 Commte the predetermind manufacturine overhead inte mine that the company continues to apply manufacturing overbead costs on the basis of direct labec hours. Using this tale other dain the problem ( Domains and labor cost) determine the sait procact costs of each podact 2. Masing consider bused costing to wpply manutacturing overhead cost to products The tiviry-hand contine wym wald have the following activity cost peole and expected activity 2. Save the What If file sccording to the following mame format Open the orirical file and save it under the new following name format: What If data file: (Your last c ustome , Excel -1 For Example SmithJxTF 1 xlsor Smith Fixa (deding on which version of Microsoft you are in ABC WHAT IF ANALYSIS DATA 20 Feel Pile) Activity Cost Pool Maintaining nea r Processing purchase order Quality control Machine related Activity Measure Niemer af Number of purchase orders Neuber of tests Machine Leare Estimated Overhead Cost SI80 000 90.000 230,000 5001000 CHECK FIGURES AX40 Traditional Unit Cost $59.99 EX90 ABC Unit Cont: $117.20 The "What If part of the ass e nt will help you determine whether you have correctly used cell refremgm youre They c ome over the past year and Adria Moctaring has had some coste Page 1 of 1 912 words - res E . - o locus @ ^ Type here to search e a W - 1:16 PM 3/24/20202 = AutoSave C . Fxral Adria Problem Data File Spring 2020 Revised Feb 18(1)(1). Compatibility Mode - Word Search Carson Lozano C - 0 x File Home Insert Draw Design Layout References Mailings Review View Help Share Comments 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: Estimated Overhead Cost Maintaining parts inventory* $164,925 Processing purchase orders S 91.800 Quality control** $190.000 Machine related** $549,900 75 Expected Activity AX40 EX 90 150 800 220 2.000 2.750 4,000 7,700 The company has changed to a compuler based inventory managertel system which has improved the efficiency and lowered the overall cost of managing the paits 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) Page 1 of 3 942 words FA - Type here to search o n Focus ^ O e e 3 . a or 1 + 100% 1:20 PM 3/24/20202