Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formutas using cell references. 2. create absolute cell references to perform calculations efficiently and correctly. 3. apply conditional formatting to highilight 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 also want to highilght celis according to whether the calculated ratio increases of decreases more than a specified amount. Required: Download spreadsheet Comman5izeCaseData-83bc62.xlsx You wili buld a spreadsheet that performs common-size analysis using five years of a company's income statementsi specifically, you will create two sheets within the warkboak, 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 refiect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGA 1. Horizontal analysis: Creste a new sheet within your Excel workboek that you wull use to perform the horizontal analysis, comparing each year's income statement item to lts corresponding item in year 1. Cepy column A from the Financial Statement Data sheet to Column A in your few sheet. Also copy the information in row 1 to row I in your new sheet so that the batic structure of the table will be the same in the new sheet but it will have no data. a. Now create formulas in your new sheet using the data in the financil Statement Data sheet that will rellect the comect percentages as detchbed in the previeus 1. Horizontal analysis: Create a 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 Financial Statement Data sheet to Column A in your new 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 in the new sheet but it will have no data. a. Now create formulas in your new sheet using the data in the Financial 5tatement Data sheet that wall reflect the correct percentages as described in the previous question. Instead of creating every formula one by ane, how could you create just one formula in cell B2 that con be copied and pasted to all ceis? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. 1. W'Financial Statement Data'ts 82 FFinsncial Statement Data's 32 I1. = Financial Statement Data'182/Financial 5tatement Dato/sB2 IIt. ='Financial Statement Data' 82 fFinancial Statement Data'18\$2 IV. = Financial Statement Data'ib2/Financial Statement Data'isB\$2 2. Vertical analysist Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales: a. Instead of creating every formula one by one, how can you create just one formula in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. 1. =ABS[Financial Statement Data'13B2/Financial 5tatement Data'182) II. = AES[Financial Statement Data'IB2fFinancial Statement Data'15B2) III. =ABS('Financial Statement Data'182/'Financial Statement Data'18\$2) IV, = Ass ('Financal Statement Data'iB2F'Financial Statement Data'1sBs2) b. Again, copy and paste that one farmula to all the relevant cells in your new sheet. found your answers to two decimal places, Enter all percentages as positive numbers. IV, = ABS(Financial Statement Data'182/Finaricial Statement Dsta'15B\$2) b. Again, copy and paste that one formula to all the relevant celis in your new sheet. Round your answers to two decimal places. Enter all percentages as positivit numbers. 3. Conditional formatting: You want to show the increases and decreases with visually different colors. Apply the following conditional formatting to your honzontal analysis sheet: For the cells that have an increase of more than 105 percent, use a yellow fil with dark yellow text. For celis that have a decrease of less than 95 . percent, use a light red fil with dark red text. Choose the table with the correct conditional formatting. 3. Conditional formatting: You want to show the increases and decreases with visually different colors. Apply the following conditional formatting to your horizontal analysis sheet: For the cells that have an increase of mare than 105 percent, use a yellow fill with dark yellow text. For cells that have a decrease of less than 95 percent, use a light red fill with dark red text, Choose the table with the correct conditional formatting. The correct sheet is 4. Explain the use of the common sise financial statements. If you were a manager, which area would require further analyeis? Thet input in the box below will not be graded, but may be reviened and considered by your instructor