Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell references. 2. create absolute cell references to perform calculations efficiently and correctly. 3. apply conditional formatting to highlight cells based on criteria that you specify. Case scenario: You are working at a small business that has been mostly stable. You want to analyze the financial statements of the past five years to examine the trends over time and compare the percentages of income statement items relative to overall sales. You aiso want to highlight cells according to whether the calculated ratio increases or decreases more than a specifled amount. Required: Downioad spreadshet CommonSizeCaseData-d86777, xlax You will buld a spreadsheet that performs common-size analysis using flve years of a company's income statements. Specifically, you will create two sheets within the workbook, one for the horizontal analysis and one for the vertical analysis. Further, you will use conditional formatting to visually differentiate between cells that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH 1. Horizontal analysis: Create o new sheet within your Excel workbook that you will use to perform the horizontal analysis, comparing each year's income statement item to its corresponding item in year 1. Copy column A from the Financlal Statement Data sheet to Column A in your new sheet. Also copy the information in row 1 in mw Cogy column A from the finandal scotement Data sheet to Column A in your nea sheet. Also copy the information in row 1 to row 1 in your new sheet so that the basic structure of the table will be the same is the new sheet but it wirf have no dnth. a. Naw create formulas in your new sheet using the dwa in the Financial statement Data sheet that will reflect the correct percenteges as described in the previous question. tostead of creating every formula one by ses, how could you create fust one formula in cell bz that can be copied and pasted to all cells? Note that there are negative values in the Financial statement Date sheet. Choose the correct formuls. IN. = Foncial Statement Datar a2/Finoncial Statement Dota'ises2 2. vertical analysisi Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales: a. Instesd of cresting overy formula one by one, how can you create just one formula in cell 82 that can be copled and pasted to all cellis? Note that there are negative values in the Financial Statement Data sheet choose the correct formula. III. =ABS(Finencial Statemenk Data' B2/Fancial Statement Date' Bs2) IV. = ABS('Finansial statement Data'iB2/Financial 5tatement Data's8s2) 3. Cendibonal formatting: You want to show the increases and decreaset with visually diffecent colors. Aoply the following conditional formatting to your horizontal analysis sheet: For the cells that have an increase of mare than 105 percent, use a yelow fill wath dark yelow text. For cells that have a dicrease of less than 95 porcent, use o light red fall with dork red text, Choose the table with the correct condtional focmatting