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-1b190d.xlsx

You will build a spreadsheet that performs common-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 cells that reflect increases and cells that reflect decreases over a specified percentage.

WALKTHROUGH: Common-Size Financial Statement Analysis Using Excel

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.
	You should create a new sheet, and name it Horizontal Analysis. Copy the headings, i.e., column A and row 1 into this new sheet.
	a.	Now create formulas in your new sheet using the data in the Financial Statement Data sheet that will reflect the correct percentages as described in the previous question. Instead of creating every formula one by one, how could 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.
		The correct solution for B2:
		='Financial Statement Data'!B2/'Financial Statement Data'!$B2
		Change the display to percent style.
	b.	Copy and paste that one formula to all the relevant cells in your new sheet.
		You can copy and paste in multiple ways: use the copy and paste icons on the tool bar with a mouse, use Ctrl+C, shift to highlight cells, and Ctrl+V, etc.
2.	Vertical analysis: Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales:
	You should create a new sheet and name it Vertical Analysis. Copy the headings, i.e., column A and row 1 into this new sheet.
	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.
		The correct solution for B2:
		=ABS('Financial Statement Data'!B2/'Financial Statement Data'!B$2) where ABS means absolute value. By taking the absolute value, negative values will not cause a problem.
		Change the cell to percent style.
	b.	Again, copy and paste that one formula to all the relevant cells in your new sheet.
		You can copy and paste in multiple ways: use the copy and paste icons on the tool bar with a mouse, use Ctrl+C, shift to highlight cells, and Ctrl+V, etc.
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 more 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.
	Select all the cells. Then choose Conditional Formatting Highlight Cells Rules Greater Than...
	After selecting Greater Than, a dialog window will appear. Specify greater than 1.05 (i.e., 105 percent), and Format with Yellow Fill with Dark Yellow Text.
	Add a second rule to Format with Light Red Fill with Dark Red Text for values less than 0.95.
4.	Evaluate your results. Are these the correct amounts? How can you tell if they are correct or not?
	It is important for users to always check their results. This is especially important when calculating ratios. The user can check their results in multiple ways. The following are a few examples:
	a.	The basis of comparison should be correct, as one moves to the right or moves down the worksheet, for horizontal analysis and vertical analysis, respectively.
	b.	For horizontal analysis, the first column should have 100 percent for all values because it is the basis of comparison.
	c.	For vertical analysis, the first row should have 100 percent for all values because it is calculated as the year 1 value divided by the year 1 value, since the year 1 value is the basis for all income statement item comparisons.
	d.	For vertical analysis, the cost of goods sold and the gross margin should add up to 100 percent. Then, the operating expenses, taxes, and net income should add up to the gross margin percentage.

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.

Now create formulas in your new sheet using the data in the Financial Statement Data sheet that will reflect the correct percentages as described in the previous question. Instead of creating every formula one by one, how could 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.

='Financial Statement Data'!$B2/'Financial Statement Data'!B2

='Financial Statement Data'!B2/'Financial Statement Data'!$B2

='Financial Statement Data'!B2/'Financial Statement Data'!B$2

='Financial Statement Data'!B2/'Financial Statement Data'!$B$2

correct formula. I. ='Financial Statement Data'!\$B2/'Financial Statement Data'!B2 II. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B2 III. ='Financial Statement Data'!B2/'Financial Statement Data'!B\$2 IV. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B \$2 2. Vertical analysis: Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales: Data sheet. Choose the correct formula. I. =ABS('Financial Statement Data'!\$B2/'Financial Statement Data'!B2) II. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B2) III. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!B\$2) IV. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B\$2)