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

What Is a Balance Sheet?

A balance sheet is the summary of a company’s assets and liabilities. There are two essential parts of a balance sheet, the Assets and the Liabilities and Owner’s equity.

Financial Results from a Balance Sheet

There are five main financial results we can get from a balance sheet.

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

Prepare the heading of the balance sheet.

Type ‘Balance Sheet’ in some merged cells in a larger font size.
Enter the Company Name in the next row.
Enter the years the balance sheet refers to in the next row.

Step 2 – Input Assets Data

Enter the heading ‘Assets’ in some merged cells at a larger font size.
Enter the heading ‘Current Assets’ in the next row. Type the current asset types of your company on the left side and record the assets’ values on the right side in the years’ columns.

Note: Select the Accounting format from the Format Cells dialog box for the asset cells.

Next, you need to calculate total current assets.
Select the E11 cell and insert the following formula to calculate total current assets in the year 2021.

=SUM(E7:E10)

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

The total current assets for the year 2022 also appears.

List the other assets items and their values like the current assets list.
Calculate the value of the total assets per year. To do this, select the E14 cell and paste the following formula in the formula bar.

=SUM(E11:E13)

The value of the total assets for the year 2021 is returned.
Place your cursor in the bottom right position of the E14 cell, the fill handle will appear.
Drag the fill handle to the right to copy the formula and calculate total assets for the year 2022.

The asset section of the balance sheet is complete.

Step 3 – Input Liabilities & Owner’s Equity Data

Enter the current liabilities, other liabilities, and owner’s equity.

Calculate the total current liabilities by selecting the E20 cell and inserting the formula below.

=SUM(E17:E19)

The total current liability for the year 2021 is returned.
Put the cursor in the bottom right position of the cell and drag the fill handle to the right to calculate the total current liabilities for the year 2022.

The total current liabilities for both year has been recorded.

Calculate the total liabilities for the following year by selecting the E23 cell and inserting the following formula.

=SUM(E20:E22)

Put the cursor in the bottom right position of the cell and drag the fill handle to the right to copy the formula.

The total liabilities for the years 2021 and 2022 have been recorded.

Calculate the total owner’s equity by clicking on the E27 cell and entering the following formula.

=SUM(E25:E26)

Place the cursor on the bottom right of the cell and drag the fill handle to the right.

The total owner’s equity for the year 2022 is returned.

Find the total liabilities and owner’s equity by selecting the E28 cell and entering the following formula.

=SUM(E23,E27)

The total liabilities and owner’s equity for the year 2021 are returned.

Drag the fill handle to the right to can calculate the total liabilities and owner’s equity for the year 2022.

Step 4 – Calculate Financial Results from Balance Sheet

To calculate the Debt Ratio for the year 2021, select the cell E31 and insert the following formula.

=E23/E14

Place your cursor in the bottom right position of the cell and drag the fill handle to the right to calculate the ratio for the year 2022.

To find the Current ratio click on the E32 cell and insert the following formula.
Drag the fill handle to the right to calculate the ratio for the year 2022.

=E11/E20

To calculate the Working Capital for the year 2021 select the E33 cell and enter the following formula.
Drag the fill handle to the right to calculate the Working Capital for the year 2022.

=E11-E20

Calculate the Assets to Equity Ratio by selecting the E34 cell and entering the following formula.
Use the fill handle to calculate the ratio for the year 2022.

=E14/E27

Calculate the Debt to Equity Ratio by clicking on the E35 cell and inserting the following formula.
Use the fill handle to calculate the ratio for the year 2022.

=E23/E27

The final financial results summary will look like this.

Advantages of Keeping a Balance Sheet

The advantages of a balance sheet are as follows:

You can analyze a company’s growth.
You can inform decisions like investing or withdrawing shares.
You can track the progress of a company over several years.

Things to Remember

It is important to know 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.

Balance Sheet.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

6 Comments

Niefer Jun 15, 2022 at 9:52 PM

Better to make column B wider than to merge cells B:D.
- Reply
  Tanjim Reza Jul 19, 2022 at 1:21 PM
  
  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
  - Reply
    Shabbir Feb 14, 2023 at 3:04 PM
    
    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
    - Eshrak Kader Feb 16, 2023 at 3:00 PM
      
      Hello SHABBIR,
      
      You can follow a step-by-step guide or use the free template for your simple accounting system by following this article on How to do Bookkeeping for Small Businesses.
      
      Regards,
      ExcelDemy
harry Jan 28, 2023 at 5:02 PM

send me a copy
- Reply
  Shamima Jan 30, 2023 at 9:41 AM
  
  Hello, Harry!
  
  You can download the copy from Download Sample Workbook
  
  Regards
  ExcelDemy