Excel 2007
Horizontal and Vertical Analysis

The images were created while running Windows XP.

Horizontal and Vertical Analysis

This is a partial Balance Sheet containing the Current Assets for Southwest Airlines.

1. All Borders used for A1:E8.
2. B3:C8 has been formatted for currency, 2 decimals, and negative in red parentheses.
3. D2 and E2 have Text Wrapping.

Horizontal Analysis

Horizontal Analysis compares an account over a period of time.

The formula has two parts, calculate the difference and divide by the previous year.

Horizontal Analysis Formula: =(Current Year - Previous Year)/Previous Year

Use parentheses to subtract first and then divide.

1. Select D4.
2. Enter the formula: =(B4-C4)/C4
3. Press the ENTER key.
4. The result will be displayed as a decimal. Select D4.
5. Click the Percent Icon on the Home Ribbon.
6. Fill Handle to D8.

Vertical Analysis

Vertical Analysis compares an account to the total, Cash to Total Current Assets, Net Receivables to Total Current Assets, etc.

Vertical Analysis Formula: =Account/Total

The total is made absolute so that when the formula is filled, the cell for the total remains constant.

1. Select E4.
2. Type the formula: =B4/B8 and press F4 to make B8 absolute
3. =B4/$B$8
4. Press ENTER.
5. Select E4. Click the Percent Icon on the Home Ribbon (Numbers Group)
6. Position the mouse on the Fill Handle. Click and drag to E7.
7. Select E8.
8. Double click the AutoSum Icon. Click the Percent Icon.

Completed Worksheet and Cell Formulas

1. Hold down the CTRL key and press ~ to display the Cell Formulas.
2. Press CTRL and ~ to return to the worksheet.

Rename Worksheet Tab

1. Double click the worksheet tab.
2. Type: Analysis
3. Press ENTER.

Updated September 6, 2007