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

Get FREE Advanced Excel Exercises with Solutions!

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.


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 two essential parts of a balance sheet. Such as the Assets part and the Liabilities and 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.

Read More: How to Create Daily Bank Balance Report Format in Excel


Financial Results from a Balance Sheet

There are five main 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

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 in 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

Read More: Income and Expenditure Account and Balance Sheet Format in Excel


📌 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. To do 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: Create a Balance Sheet Format for Trading Company in Excel


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

The 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: Balance Sheet Format for Construction Company in Excel


📌 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


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 make 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 total liabilities and owner’s equity must be equal.

Download Sample Workbook

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


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.


Related Articles


<< Go Back to Balance Sheet | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

6 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

      • I need an accounting system for a small trading company dealing in mainly safety building items & hardware goods.

        My business is mainly through a retail shop, where I buy my goods both on credit and cash. Similarly, my sales are in cash and on credit. I also maintain a stock were certain times I buy items and stock it for future sales. No online business
        My expenses chart is limited to a few items like salaries, rent, admin exp etc.

        Was looking out for a simple accounting system

  2. send me a copy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo