Balance Sheet Format in Excel for Proprietorship Business

Step 1 – Estimate Current Assets

The current assets typically consist of three items: cash, accounts receivable, and inventory.

• Add those assets to the current assets section.
• Include the amounts for individual assets.
• Format them in a simple table like in the image below.

• Select cell C9 (the first cell below the numbers).

• Use the following formula in the formula box (we’re summing all the assets):

`=SUM(C6:C8)`

• Press Enter to apply the formula.

Step 2 – Evaluate Fixed Assets

The fixed assets consist of equipment, less accumulated depreciation, and intangible assets. Less accumulated depreciation provides negative amounts.

• Add those assets to the fixed assets section.
• Include the amounts for individual fixed assets.
• Format them in a table similar to the first one, see below.

• Select cell F9.

• Use the following formula in the formula box.
`=SUM(F6:F8)`

• Press Enter to apply the formula.

Step 3 – Calculate Total Assets

• Select cell C11.

• Use the following formula.
`=SUM(C9,F9)`

• Press Enter to apply the formula.

Step 4 – Prepare Current Liabilities

The current liabilities consist of three items: accounts payable, short-term debt, and other current liabilities.

• Open a new sheet in the workbook.
• Add those liabilities in the current liabilities section.
• Include the amounts for individual liabilities.
• Format in a table as needed.

• Select cell C9.

• Use the following formula in the formula box.
`=SUM(C6:C8)`

• Press Enter to apply the formula.

Step 5 – Estimate Long-Term Liabilities

For long-term liabilities, we divide them into three sub-sections: long-term debt, deferred income tax, and other.

• Add those liabilities in the long-term liabilities section.
• Include the amounts for long-term liabilities.
• Format in a table.

• Select cell C15.

• Use the following formula in the formula box.
`=SUM(C6:C8)`

• Press Enter to apply the formula.

Step 6 – Evaluate Total Liabilities

• Select cell C17.

• Use the following formula.
`=SUM(C9,F9)`

• Press Enter to apply the formula.

Step 7 – Calculate Shareholder Equity

The shareholder’s equity consists of common stock, retained earnings, and others.

• Add those equities to the shareholder’s equity section.
• Include the amounts for individual equity.
• Format in a table like the previous sections.

• Select cell F9.

• Use the following formula in the formula box.
`=SUM(F6:F8)`

• Press Enter to apply the formula.

Step 8 – Estimate Total Liabilities and Shareholder’s Equity

• Select cell C19.

• Use the following formula in the formula box.
`=SUM(C17,F9)`

• Press Enter to apply the formula.

Step 9 – Prepare the Final Balance Sheet

• Put the assets balance sheet along with liabilities and shareholder’s balance on the same page.
• Check the total assets and the combination of the total liabilities and shareholder’s equity.
• In both cases, the total value is the same. So, our balance is perfect to use.

• To use this format for further purposes in the future, you can easily use the template we made. You can enter the values and get the desired balance sheet.

You can use the following file as a template for your company and change the values and add rows as needed.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF