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

Creating a balance sheet is a must-to-do task for financial analysis and company evaluation. You can quickly overview a company’s present worth and growth from a balance sheet. There are various templates on the balance sheet. In this article, I will discuss about balance sheet in Excel format of a company. Read the full article below to understand better and enhance your knowledge in this regard.


Download Sample Workbook

You can download our balance sheet template for free from here.


What Is a Balance Sheet?

A balance sheet of a company is the summary of the company’s assets and liabilities. It portrays the summarized overview of the company at a glance. You can say if a company is gaining profit or sinking into debt by analyzing this sheet.

Essential Parts of a Balance Sheet:

There are mainly 2 essential parts of a balance sheet. Such as the Assets part and the Liabilities & Owner’s equity part.

1. Assets

Assets are mainly the resources that have the capability to gain benefits in the future. Cash, inventory, properties, pieces of equipment, goodwill, etc. are examples of assets. Assets can be tangible or intangible. Moreover, assets can be short-term or long term too.

2. Liabilities & Owner’s Equity

  • Liabilities

Liabilities are the sources where a company loses economic benefit or remains at a financial obligation of sacrificing worth.

  • Owner’s Equity

Owners equity is mainly the share of the company value between the shareholders. This is the ratio in which the company value will be distributed if sold.


Financial Results from a Balance Sheet

There are mainly 5 financial results we can get from a balance sheet.

Such as:

Debt Ratio: This is the ratio between total liabilities and total assets.

Current Ratio: This is the ratio between current assets and current liabilities.

Working Capital: This is the difference between current assets and current liabilities.

Assets to Equity Ratio: This is the ratio between total assets and owner’s equity.

Debt to Equity Ratio: This is the ratio between total liabilities and owner’s equity.


Steps to Make a Balance Sheet Format of a Company in Excel

📌 Step 1: Make Balance Sheet Heading

At first, you have to prepare the heading for your balance sheet. Follow the steps below to prepare this. 👇

  • At the very beginning, write ‘Balance Sheet’ in some merged cells at a larger font size. This will make the heading more attractive.
  • Following, write your Company Name similarly in the next row.
  • Next, write the years for which you are creating this balance sheet in the next row.

Following these 3 steps will result in the following scenario. 👇

Heading of an Excel Balance Sheet of a Company


📌 Step 2: Input Assets Data

Now, after completing the heading part, you have to create your asset dataset and calculate your assets. To accomplish this, follow the steps below. 👇

  • At first, write the heading ‘Assets’ in some merged cells at a larger font size.
  • Next, write the heading ‘Current Assets’ similarly in the next row. Following, write the current asset types of your company on the left side and record the assets’ values on the right side in the years’ columns. After doing this, you will have a similar result. 👇

Current Assets of Balance Sheet of a Company

Note:

It would be better if you choose the assets’ value cells as the Accounting format from the Format Cells dialogue box.

Accounting Format Selection

  •  Next, you need to calculate your total current assets. For doing this, select the E11 cell and insert the following formula to calculate total current assets in the year 2021.
=SUM(E7:E10)

Calculate Total Current Assets of a Balance Sheet

  • Now, put your cursor on the bottom right corner of the E11 cell and the fill handle will appear. Subsequently, drag it rightward to calculate total current assets for the year 2022.

Use Fill Handle to Copy Formula

  • As a result, you can see the total current assets for the year 2022 too.

Total Current Assets of a Balance Sheet of a Company

  • Now, list the other assets items and their values just like the current assets list.
  • Next, you will need to calculate the value of the total assets per year. To do this, select the E14 cell and write the following formula in the formula bar.
=SUM(E11:E13)

Calculate Total Assets of Balance Sheet

  • As a result, you will get the value of the total assets for the year 2021. Next, place your cursor in the bottom right position of the E14 cell. Consequently, the fill handle will appear. Following, drag the fill handle rightward to copy the formula and calculate total assets for the year 2022.

Use Fill Handle to Copy Formula

Finally, you have made the asset section of your balance sheet. And, It should look like this. 👇

Total Assets of Balance Sheet of a Company

Read More: How to Make Projected Balance Sheet in Excel (with Quick Steps)


📌 Step 3: Input Liabilities & Owner’s Equity Data

Next thing is, that you need to create the Liabilities and Owner’s Equity dataset for your balance sheet. Go through the steps below to create this. 👇

  • Just like the Assets dataset, write the current liabilities, other liabilities, and owner’s equity. In addition to these, record the values of every type. The dataset will look like this. 👇

Liabilities & Owner's Equity of Balance Sheet of a Company

  • Now, you need to calculate the total current liabilities. To do this, select the E20 cell and insert the formula below.
=SUM(E17:E19)

Calculate Current Liabilities of Balance Sheet

  • Consequently, you will get the total current liabilities for the year 2021. Now, put your cursor in the bottom right position of the cell and the fill handle will appear. Following, drag the fill handle rightward to calculate the total current liabilities for the year 2022.

Use the Fill Handle Feature to Copy Formula

  • Consequently, you will get the total current liabilities for every year.

Total Current Liabilities of Balance Sheet of a Company

  • Now, you need to calculate the total liabilities for the following year. To do this, select the E23 cell and insert the following formula.
=SUM(E20:E22)

Calculate Total Liabilities

  • Subsequently, place your cursor on the bottom right corner of the cell and when the fill handle appears, drag it rightward to copy the formula.

Use Fill Handle to Copy Formula

  • Thus, you can get the total liabilities for the years 2021 and 2022.

Total Liabilities of Balance Sheet of a Company

  • Besides, you need to calculate the total owner’s equity too. To do this, click on the E27 cell and write the following formula.
=SUM(E25:E26)

Calculate Total Owner's Equity of Balance Sheet

  • Consequently, you will get the total owner’s equity for the year 2021. Now, place your cursor on the bottom right position of the cell and drag the fill handle rightward upon the arrival of the fill handle.

Use Fill Handle to Copy Formula

  • As a result, you will get the total owner’s equity for the year 2022 too.

Total Owner's Equity of Balance Sheet of a Company

  • Last but not least, you need to find the total liabilities and owner’s equity. To do this, select the E28 cell and write the following formula.
=SUM(E23,E27)

Calculate Total Liabilities & Owner's Equity of Balance Sheet

  • Thus, you can get the total liabilities and owner’s equity for the year 2021. Following, put your cursor in the bottom right corner of the cell and when the fill handle arrives, drag it rightward.

Drag Fill Handle to Copy Formula

Thus, you can calculate the total liabilities and owner’s equity for the year 2022. And the whole liabilities and owner’s equity dataset will look like this. 👇

Total Liabilities & Owner's Equity of Balance Sheet of a Company

Read More: Net Worth Formula Balance Sheet in Excel (2 Suitable Examples)


📌 Step 4: Calculate Financial Results from Balance Sheet

After creating the balance sheet, you can find some financial results from the sheet. To find these values, follow the steps below. 👇

  • To calculate the Debt Ratio for the year 2021, select the cell E31 and insert the following formula.
=E23/E14

Calculate Debt Ratio of Balance Sheet of a Company

  • Now, place your cursor in the bottom right position of the cell and when the fill handle arrives, drag it rightward to calculate the ratio for the year 2022.

Use Fill Handle to Copy Formula

  • Next, to find the Current ratio of your balance sheet, click on the E32 cell and insert the following formula. Subsequently, drag the fill handle rightward like in the previous step to calculate the ratio for the year 2022.
=E11/E20

Calculate Current Ratio of a Balance Sheet of a Company

  • Another thing, you can calculate the Working Capital for the year 2021 from this sheet. To do this, select the E33 cell and write the following formula. Subsequently, drag the fill handle rightward to calculate the Working Capital for the year 2022.
=E11-E20

Calculate the Working capital of a Balance Sheet of a Company

  • Besides, you can calculate the Assets to Equity Ratio by selecting the E34 cell and writing the following formula. Following, use the fill handle feature similarly to calculate the ratio for the year 2022.
=E14/E27

Calculate the Assets to Equity Ratio of a Balance Sheet of a Company

  • Similarly, you can calculate the Debt to Equity Ratio by clicking on the E35 cell and inserting the following formula. Subsequently, use the fill handle to calculate the ratio for the year 2022.
=E23/E27

Calculate the Debt to Equity Ratio of a Balance Sheet of a Company

Finally, your financial results summary will look like this. 👇 And, your balance sheet in Excel format of a company is fully ready now.

All Financial Results of a Balance Sheet of a Company

Read More: Balance Sheet Format in Excel for Proprietorship Business


Advantages of Keeping a Balance Sheet

The advantages of a balance sheet are as follows:

  • You can analyze a company’s growth in a very short time.
  • You can take many big decisions like investing or withdrawing shares pretty easily through this sheet.
  • You can get several financial results and progress from this sheet.
  • You can track the progress of a company pretty easily and faster with the help of this sheet.

Things to Remember

  • A very important thing to know is that the balance sheet’s total assets and total liabilities and owner’s equity must be equal.

Conclusion

To conclude, I have discussed the balance sheet in Excel format of a company in this article. I have tried to portray all the things that you should know about a balance sheet and all the steps to create a balance sheet at large here. I hope you find this article helpful and informative. If you have any further queries or recommendations, feel free to comment here.

And, visit ExcelDemy to find more articles like this.


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

2 Comments
  1. Better to make column B wider than to merge cells B:D.

    • Hello, NIEFER!
      You have pointed out a fantastic thing.
      It is correct that you don’t need to merge the cells B:D here. You can just enlarge the B column as much as you need. I just wanted to keep the column size closer to each other. That’s why I merged. But, it is not necessary. Rather, it is better to enlarge the B column as the references will be simpler that way.
      Thank you NIEFER for your valuable feedback. I appreciate it so much.

      Regards,
      Tanjim Reza

Leave a reply

ExcelDemy
Logo