How to Create Vertical Balance Sheet Format in Excel

Excel is a wonderful tool for creating balance sheets as it provides many valuable features which are very easy to apply. In this article, we will learn how to create vertical balance sheet format in excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Vertical Balance Sheet

A vertical balance sheet is one in which the line items on the balance sheet are shown as a single column of figures, starting with assets, then liabilities, and finally shareholders’ equity. Line items are displayed inside each of these categories in decreasing order of liquidity. As a result, the presentation within the top block of line items (for assets) starts with cash and typically concludes with fixed assets or goodwill (which are far less liquid than cash). Similar to the assets part, the liabilities section often starts with accounts payable and finishes with long-term debt.


4 Easy Steps to Create Vertical Balance Sheet in Excel

In this section, we will see how we can create a vertical balance sheet format in excel. For ease of illustration, we have divided the whole process into 4 steps. Let’s begin.


Step 01: Make Heading of the Balance Sheet

The first step is to create a heading for the balance sheet. A beautiful and well-formatted heading increases the aesthetic value of the balance sheet. Follow the steps below to prepare this.

  • First, open a blank excel sheet. On the top of the sheet, take some merged cells and write “Vertical Balance Sheet” with a large font size and suitable background color. Also, make the font bold.

  • In the figure above, we merged and centered the B2:F2 and wrote the heading.
  • Now in the cell below, write the date with suitable formatting.

How to Create Vertical Balance Sheet in Excel

Read More: How to Create Common Size Balance Sheet in Excel


Step 02: Input Assets Data

After writing the heading and date, we will move to the main part of our balance sheet. As we know, a balance sheet consists of three major components, i.e., Assets, Liabilities, and Stakeholder’s Equity. The first component that appears on the balance sheet is Assets. To input the Assets data, follow the steps below.

  • First, write down the heading “Assets” in some merged cells with suitable font size and background color.

How to Create Vertical Balance Sheet in Excel

  • Now, we will first input the data of the current assets. Hence, write the heading “Current Assets” in the same number of merged cells, but unlike the previous cells, it will be left aligned.

How to Create Vertical Balance Sheet in Excel

  • Below the heading, merge one less number of cells (B6 to E6) and make it left aligned.

  • Now give input your current assets and their corresponding values on the adjacent right cells.

  • It is better to express figures in the accounting format. So we will convert the number formatting of the whole F column into Accounting. To do that, click on the column heading F, then go to the Number group and click on the $

How to Create Vertical Balance Sheet in Excel

  • As a result, all the numbers will convert into the accounting format.

How to Create Vertical Balance Sheet in Excel

  • Now, we will calculate the total current asset by the following formula.
=SUM(F6:F8)

  • Similarly, we will input the data of Fixed Assets.

How to Create Vertical Balance Sheet in Excel

  • Now, calculate the total fixed assets with the following formula
=SUM(F11:F12)

How to Create Vertical Balance Sheet in Excel

  • Now to calculate total assets, we add Total Current Assets (F9) and Total Fixed Assets(F13).
=SUM(F9,F13)

Read More: Balance Sheet Format in Excel with Formulas (Create with Easy Steps)


Similar Readings


Step 03: Input Liabilities Data

After inputting assets data, we have to insert the liabilities data. There are two types of liabilities: Current and Long Term. Follow the steps below to insert liabilities data.

  • Like in the Assets, input a heading of Liabilities in some merged cells with proper formatting.

  • Now insert current liabilities data like the figure below and calculate total liabilities with the formula below.
=SUM(F17:F18)

How to Create Vertical Balance Sheet in Excel

  • Now input the Long Term Liabilities and calculate the total long-term liabilities by the formula below.
=SUM(F21:F22)

How to Create Vertical Balance Sheet in Excel

  • Now adding the Total Curren Liabilities and Total Long Term Liabilities, we get the total liabilities.
=SUM(F19,F23)

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)


Step 04: Input Stakeholder’s Equity

This is the last item we need to input. Follow the steps below to input the Stakeholder’s Equity data.

  • Like the 2nd and 3rd steps, write a heading for Stakeholder’s Equity.

How to Create Vertical Balance Sheet in Excel

  • Now input all the items of Stakeholder’s Equity and their corresponding values.

How to Create Vertical Balance Sheet in Excel

  • Now calculate the Total Equity by the following formula.
=SUM(F26:F27)

  • And lastly, we have to calculate the Total Liabilities and Equity by adding Total Liabilities( F24) and Total Equity (F28)
=SUM(F24,F28)

How to Create Vertical Balance Sheet in Excel

So, overall our balance sheet will look like this.

How to Create Vertical Balance Sheet in Excel

Read More: Balance Sheet Format in Excel for Proprietorship Business


Things to Remember

  • In a balance sheet, the amount of Total Assets must be equal to Total Liabilities and Equity.
  • You have to add additional elements according to your needs that are not given in the sheet. Then you need to adjust the formula as well.

Conclusion

That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo