Vertical analysis allows the comparison of financial statements by representing each line item on the statement as a percentage of another (base) line item.
The vertical analysis calculator allows for details of an income statement and a balance sheet to be entered, and calculates each line item as a percentage of the relevant base line item. The use of vertical analysis as a comparison tool is discussed more fully in our vertical analysis tutorial.
Formula for Vertical Analysis Calculator
For the income statement the vertical analysis calculator works out the percentage each line item is of total revenue. The vertical analysis formula used for each income statement line item is given by:
Line item % = Line item value / Revenue
For example, if the income statement has total revenue of 40,000, and shows operating expenses of 16,000, then operating expenses are 16,000/40,000 = 40.0% of total revenue
For the balance sheet, the vertical analysis calculator works out the percentage each line item is of total assets. The vertical analysis formula used for each balance sheet line item is given by:
Line item % = Line item value / Total assets
For example, if the balance sheet has total assets of 31,000, and shows inventory of 4,000, then inventory is 4,000/31,000 = 12.9% of total assets.
The Excel vertical analysis calculator, available for download below, is used to produce the vertical analysis by entering details from the income statement and balance sheet of the business. The calculator is used as follows:
Enter the relevant accounting period, for example, Year 1, 2016, Month 1 etc.
Enter the income statement information for the business. The information will be for the relevant accounting period, such as the year ending 2015, and will come from management information, published financial statements, or from industry standards used for comparisons.
The vertical analysis calculator calculates the percentage each income statement line item has to the revenue line.
Enter the balance sheet information for the business. The balance sheet information will be as at the end of the accounting period, and again will come from management information, published financial statements, or from industry standards.
The vertical analysis calculator calculates the percentage each balance sheet line item has to the total assets line.
Vertical Analysis Calculator Download
The vertical analysis calculator spreadsheet is available for download in Excel format by following the link below.
The vertical analysis calculator is one of many financial calculators used in bookkeeping and accounting, discover another at the links below.
Users use this vertical analysis calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of an accounting tool that you might use when considering how to calculate vertical analysis. It is purely illustrative. This is not intended to reflect general standards or targets for any particular business, company or sector. If you do spot a mistake in this vertical analysis excel calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.