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 for an Income Statement
Vertical Analysis for a Balance Sheet
Horizontal Analysis for an Income Statement
Horizontal Analysis for a Balance Sheet

You can download the incomplete Income and Balance sheets to practice on.   download Income and Balance Sheets


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.

Vertical Analysis for an Income Statement

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

Vertical Analysis for Income Statement with Formulas


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.

Vertical Analysis for a Balance Sheet


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

Vertical Analysis for a Balance Sheet showing formulas


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.

Horizontal Analysis for Income Statement


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

Horizontal Analysis for Income Statement showing formulas


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.

Horizontal Analysis for a Balance Sheet


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

Horizontal Analysis for a Balance sheet showing formulas