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 also want to highlight cells according to whether the calculated ratio increases or decreases more than a specified amount. Required: Download spreadsheet CommonsizeCaseData-d86777, xiex You will build a spreadsheet that performs commpn-size analysis using five 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 celis that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH 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. 1. Horizoncal aralysis: Creste a new sheet nithin your Excel workbook that you will use to perform the horizontal analysis, comparing each yesr's income atatement item to its correspending item in yeer 1 . Copr column A from the Financial statement Date shest to Column A in your new alset. Also copy the information in row 1 to row 1 in your new sheet so that the basic structure of the cible wa be the rame in the pew sheet but , wat have no dats. a. Now create formulas in your new sheet uung the data in the Financial statement Data sheet that will reflect the correct percentages as described in the previous question. thatead of creating wery formula ene by one, how ceuld you create just one formula in cell 82 that can be copied and pasted to all cells? Note that there are negative values in the Finandal stivement out shent, Chopes the cerrect, farmula. 2. Vertical analrsts: Create another naw sheet to perform the vertical analysis, comparing each year's item to that year's net sales: 2. Instest of eroating every formula one by one, bow con you creste just one formula in cet 82 that can be copled and pasted to all cella? Note that there are negative values in. the Financid statement onta aheet. Chosse the cerrect formule. For the celis that have an increase of more than los percent, une a yellow fil whth dork yelow toxt. For cells that have a decrease of less than 95 percent, use a light red fill with 4, Explain the ute of the commont siza finanolal statements. If you were a maneger, which area would recuire further analysis? The input in the bax below witi not be craded, but miny be reviewed and considered by your instructer: 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 also want to highlight cells according to whether the calculated ratio increases or decreases more than a specified amount. Required: Download spreadsheet CommonsizeCaseData-d86777, xiex You will build a spreadsheet that performs commpn-size analysis using five 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 celis that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH 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. 1. Horizoncal aralysis: Creste a new sheet nithin your Excel workbook that you will use to perform the horizontal analysis, comparing each yesr's income atatement item to its correspending item in yeer 1 . Copr column A from the Financial statement Date shest to Column A in your new alset. Also copy the information in row 1 to row 1 in your new sheet so that the basic structure of the cible wa be the rame in the pew sheet but , wat have no dats. a. Now create formulas in your new sheet uung the data in the Financial statement Data sheet that will reflect the correct percentages as described in the previous question. thatead of creating wery formula ene by one, how ceuld you create just one formula in cell 82 that can be copied and pasted to all cells? Note that there are negative values in the Finandal stivement out shent, Chopes the cerrect, farmula. 2. Vertical analrsts: Create another naw sheet to perform the vertical analysis, comparing each year's item to that year's net sales: 2. Instest of eroating every formula one by one, bow con you creste just one formula in cet 82 that can be copled and pasted to all cella? Note that there are negative values in. the Financid statement onta aheet. Chosse the cerrect formule. For the celis that have an increase of more than los percent, une a yellow fil whth dork yelow toxt. For cells that have a decrease of less than 95 percent, use a light red fill with 4, Explain the ute of the commont siza finanolal statements. If you were a maneger, which area would recuire further analysis? The input in the bax below witi not be craded, but miny be reviewed and considered by your instructer