Balance Sheet Format in Excel for Proprietorship Business

Get FREE Advanced Excel Exercises with Solutions!

A balance sheet is really important when it comes to proprietorship business or any type of business. It shows a proper balance including assets, liability, and owner’s equity. Microsoft Excel will provide you with a platform where you can easily create a balance sheet format for proprietorship. This article will mainly focus on how to create a balance sheet format in Excel for the proprietorship business. I hope you find this article very informative and gather lots of knowledge regarding the balance sheet format for the proprietorship business.


Download Practice Workbook

Download this practice workbook.


What Is Balance Sheet?

A balance sheet can be defined as a financial statement that is used to evaluate an organization.  It is one of the three core financial statements used in the accounting of a business. The other two are the cash flows statement and income statement. A perfect balance sheet consists of three items. One is assets and the other two are liability and equity. In an ideal situation, assets must be equal to the combination of liability and shareholder’s equity. If it is not the case, there must be some hidden problems in the balance sheet of that company or you miss to put some data.


Step-by-Step Procedure to Create Balance Sheet Format in Excel for Proprietorship Business

To create a balance sheet format in Excel for the proprietorship business, we will show step-by-step procedures. Here, we discuss how to create assets sheet, liability, and shareholder’s equity sheet individually. After that, we check the calculation of assets, liability, and equity. If the total assets are equal to the combination of liability and shareholder’s equity, then our balance sheet for proprietorship will be completed.


Step 1: Estimate Current Assets

Our first step is mainly based on the estimation of current assets of your company’s balance sheet. The current assets consist of three items including cash, account receivable, and inventory.

  • At first, we need to add those assets to the current assets section.
  • Then, include the amounts for individual assets.
  • After that, we find this format in Excel. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business

  • Then, we need to calculate the total current assets using the SUM function.
  • As we have three different items on current assets, so, we need to add them.
  • First, select cell C9.

  • After that, write down the following formula in the formula box.

=SUM(C6:C8)

  • Press Enter to apply the formula.
  • Here, we have found the total current assets for the proprietorship business. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business

Read More: How to Prepare Balance Sheet from Trial Balance in Excel


Step 2: Evaluate Fixed Assets

Our next step is mainly focused on evaluating fixed assets from the balance sheet. The fixed assets consist of plant and equipment, less accumulated depreciation, and intangible assets. Here, less accumulated depreciation provides negative amounts.

  • At first, we need to add those assets to the fixed assets section.
  • Then, include the amounts for individual fixed assets.
  • After that, we find this format in Excel. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business

  • Then, we need to calculate the total fixed assets using the SUM function.
  • As we have three different items on fixed assets, so, we need to add them.
  • First, select cell F9.

  • After that, write down the following formula in the formula box.
=SUM(F6:F8)

  • Press Enter to apply the formula.
  • Here, we have found the total fixed assets for the proprietorship business. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business


Step 3: Calculate Total Assets

After getting the total of current assets and fixed assets, we need to focus on calculating the total assets. The total assets are the summation of total current assets and total fixed assets.

  • To calculate the total assets, first, select cell C11.

Balance Sheet Format in Excel for Proprietorship Business

  • Then, write down the following formula.
=SUM(C9,F9)

  • Press Enter to apply the formula.
  • Then, we have the assets balance sheet that includes current assets, fixed assets, and total assets.

Balance Sheet Format in Excel for Proprietorship Business


Step 4: Prepare Current Liabilities

After preparing the assets balance sheet, our next step is mainly based on the estimation of current liabilities of your company’s balance sheet. The current liabilities consist of three items including accounts payable, short-term debt, and other current liabilities.

  • At first, we need to add those liabilities in the current liabilities section.
  • Then, include the amounts for individual liabilities.
  • After that, we find this format in Excel. See the screenshot.

  • Then, we need to calculate the total current liabilities using the SUM function.
  • As we have three different items on current liabilities, so, we need to add them.
  • First, select cell C9.

  • After that, write down the following formula in the formula box.
=SUM(C6:C8)

Balance Sheet Format in Excel for Proprietorship Business

  • Press Enter to apply the formula.
  • Here, we have found the total current liabilities for the proprietorship business. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business


Step 5: Estimate Long-Term Liabilities

Next, we need to shift our focus to estimating long-term liabilities. In terms of long-term liabilities, we divide them into three sub-section. One is long-term debt. The other two are Deferred income tax and other.

  • At first, we need to add those liabilities in the long-term liabilities section.
  • Then, include the amounts for long-term liabilities.
  • After that, we find this format in Excel. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business

  • Then, we need to calculate the total long-term liabilities using the SUM function.
  • As we have three different items on long-term liabilities, so, we need to add them.
  • First, select cell C15.

  • After that, write down the following formula in the formula box.
=SUM(C6:C8)

Balance Sheet Format in Excel for Proprietorship Business

  • Press Enter to apply the formula.
  • Here, we have found the total long-term liabilities for the proprietorship business. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business


Step 6: Evaluate Total Liabilities

After getting the total current liabilities and long-term liabilities, we need to focus on calculating the total liabilities. The total liabilities are the summation of total current liabilities and total long-term liabilities.

  • To calculate the total assets, first, select cell C17.

  • Then, write down the following formula.
=SUM(C9,F9)

Balance Sheet Format in Excel for Proprietorship Business

  • Press Enter to apply the formula.
  • Then, we have the liabilities balance sheet including current liabilities, long-term liabilities, and total liabilities.

Balance Sheet Format in Excel for Proprietorship Business

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


Step 7: Calculate Shareholder’s Equity

After that, we need to focus on the shareholder’s equity. The shareholder’s equity consists of common stock, retained earnings, and others.

  • At first, we need to add those equities to the shareholder’s equity section.
  • Then, include the amounts for individual equity.
  • After that, we find this format in Excel. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business

  • Then, we need to calculate the total shareholder’s equity using the SUM function.
  • As we have three different items on shareholder’s equity, so, we need to add them.
  • First, select cell F9.

  • After that, write down the following formula in the formula box.
=SUM(F6:F8)

  • Press Enter to apply the formula.
  • Here, we have found the total shareholder’s equity for the proprietorship business. See the screenshot.

Balance Sheet Format in Excel for Proprietorship Business


Step 8: Estimate Total Liabilities and Shareholder’s Equity

To estimate total liabilities and shareholder’s equity, we need to add the total values for both cases.

  • At first, select cell C19.

  • Then, write down the following formula in the formula box.
=SUM(C17,F9)

  • Press Enter to apply the formula.
  • There we have the balance sheet of liabilities and shareholder’s equity.

Balance Sheet Format in Excel for Proprietorship Business

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


Step 9: Prepare Final Balance Sheet for Proprietorship Business

Finally, we need to focus on whether the total asset and the combination of total liabilities and shareholder’s equity is equal or not.

  • First, take the assets balance sheet along with liabilities and shareholder’s balance on the same page.
  • Then, check the total assets and the combination of the total liabilities and shareholder’s equity.
  • As you can see, in both cases the total value is the same. So, our balance is perfect to use.

Balance Sheet Format in Excel for Proprietorship Business

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

Balance Sheet Format in Excel for Proprietorship Business


Conclusion

We have shown step-by-step procedures to create a balance sheet format in Excel for the proprietorship business. In our balance sheet, we have shown profound details about assets, liability, and equity. All the steps are really easy to digest. I think you find this article very useful and help you for further purposes. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Durjoy Paul
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo