Comimon-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell refterences. 2. create obsolute cell refectences to pevform calculations efficienty and courectly. 3. apply condelionel formatting to highilight celis based on criteris that you specify. Cace scenario: You ore working - ot o smal business that has been mostly stable. vou want to analyze the financial statements of the past five years to examine the trends over time and compare the pertentages of income statement itums relative to overall sales, You also want to highlight cells according to whether the calculated ratio increases or decreases more than a upecified amount. Required: x for the horitontal analysis and one fer the vertical analysis. Further, you will use condtional formatting to visualiy differentiate befween celis that reflect increases and cella that reflect. decresses over a specified percentage. comtupending tem in yese 1. 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 raw 1 in your new sheet so that the basie structure of the table wil 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 statement bata sheet that will refect the correct percentages as described in the previous question. Insteas of creating every formula ane by one, how could you create just one formula in cell biz that can be copled and pasted to all cells? Note that there are negative values in the Financial statement Dato sheet. Chosse the correct formuls. III. - Finencial Statement Dots'ie2/Financial statement Data' Bsz NV. = Finandal statament Data') B2/Fnancis Statement Osta'ses2 Vertical analysis: Create another new sheet to perform the vertical analydis, comparing eoch year's item to that year's net sales: a. Irstead of creating every formuls one by one, hew can you create fust cne formuls in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Finandal statement Data sheet, Choese the correct formula. the eels that have an increase of more than 105 parcent, wse a velow fil with dark yelow test. For cells that have a decrease of leas than 95 persent, use a light red flil wath Lik red text choote the table nith the carrect cendidional formatting. Explan the use of the common site financial atatements, If you were a manager, which ares would require further anslyus? The input in the box below will not be graded, but roy be reviewed and consifered by vour instructer