Excel - Horizontal Analysis and Vertical Analysis

By just looking at an Income Statement or a Balance Sheet it can be difficult to interpret all the dollar amounts from one accounting period to another or to interpret one company's financial records compared to another's over a period of time.  A useful way to analyze these financial statements is by performing both a vertical analysis and a horizontal analysis.  This type of analysis allows companies of varying sizes whose dollar amounts are vastly different to be compared.

Users of financial statements such as owners, creditors, investors, etc. get a better picture of the financial condition of a business by studying the relationships and comparisons of items from one accounting period to another such as comparing Accounts Receivable over three consecutive years or comparing one businesses financial records to those of another in the same industry. What is normal in one type of industry may not be in another.

A business that is incapable of paying off their debts on a timely basis is going to have a difficult time obtaining credit. A business whose net earnings are less than most in the same industry may not only have a difficult time obtaining credit but also obtaining new capital from stockholders leading to a further decline in profitability.

Vertical Analysis

A Vertical Analysis is performed for a specific period such as a month, quarter, year, etc. then it is compared to similar periods such as the first quarter of 2011, the first quarter of 2012, the first quarter of 2013, etc.

Vertical Analysis for an Income Statement↑

When performing a Vertical Analysis of an Income Statement, Net Sales usually used as the basis for which all other items are compared. Net Sales is divided by itself making it 100%. All other items in the Income Statement are divided by the Net Sales.

The following image displays all the formulas used in the Vertical Analysis for the Income Statement

Vertical Analysis of Balance Sheet↑

When creating a Vertical Analysis for a balance sheet, total assets are used as basis for analyzing each asset account. Total liabilities and stockholder’s equity is used as the basis for each liability and stockholder account.

The following image displays all the formulas used in the Vertical Analysis for the Balance Sheet.

Horizontal Analysis

Horizontal analysis is a common technique used to examine the changes in the line items of the income statement and the balance sheet from year to year.

Horizontal Analysis for an Income Statement↑

There are various formats for creating a Horizontal Analysis but the most popular is to display the variance between Income Statements in dollar amounts and percentage.  The difference in percentage is computed by taking the dollar difference in an Income Statement item and dividing it by the base year.

The following image displays all the formulas used in the Horizontal Analysis for the Income Statement.

Horizontal Analysis of Balance Sheet↑

A Horizontal Analysis for a Balance Sheet is created the same as a Horizontal Analysis for an Income Statement.   The variance for each item in the Balance Sheet is displayed in a dollar amount as well as the percent difference.

The following image displays all the formulas used in the Horizontal Analysis for the Balance Sheet.