How to Prepare Balance Sheet from Trial Balance in Excel

The balance sheet is one of the most used statements in the modern business world. In fact, it is one of the three core financial statements used that is used for the evaluation of businesses. This article will focus on a brief discussion on the balance sheet and trial balance and show you how to prepare a balance sheet from a trial balance in Excel.

Download Practice Workbook

You can download the workbook with the example used for a demonstration from the download box below. Download and try yourself while you go through the article.

What Is Balance Sheet?

In Accounting, the balance sheet is a financial statement that is used as a measure of evaluation of an organization. It reflects the “book value” of an organization. It is one of the three core financial statements used in the accounting of a business. The other two are the income statement and cash flows statement. A balance sheet provides a glimpse of the company’s finances. The sheet consists of the company’s liabilities, assets, and shareholder’s equities. Balance sheets are organized according to the equation: Assets = Liabilities + Shareholder’s Equity. So the sum of the liabilities and shareholder’s equity must be identical for any balance sheet. If it isn’t the case, then there must be problems with data entries or more underlying problems with the organization. Usually, there is a reporting period for balance sheets- either a half-yearly or quarterly.

What Is Trial Balance?

A trial balance is another form of financial report that shows closing balances of all accounts at a point in time. The balance of all ledgers is usually arranged into debit and credit columns. The total values of these credits and debits should be equal at the end. It is used to ensure the correctness of a company’s bookkeeping system, As a trial balance- as the name suggests- should be considered balanced. But this is not always the case because of some forms of errors in a company. Although, a trial balance helps to correct those errors and help make financial records more balanced.

As both debit and credit items are (presumably) recorded correctly of a company’s income, expenses, assets, and liabilities in a trial balance, it can be used to make a balance sheet very easily.

Step-by-Step Procedure to Prepare Balance Sheet from Trial Balance in Excel

A balance sheet consists of the assets, liabilities, and equities of a company, which can be easily obtained from a trial balance. For demonstration, let’s look at a sample trial balance of a company.

All the accounts are written in the same list with debit and credit columns. We are going to differentiate all the assets and liabilities from here and thus we can easily prepare a balance sheet. For a detailed guide, follow these steps.

Step 1: Estimate Non-Current Assets

First of all, let’s prepare the balance sheet in a new one. For that, create a new spreadsheet. We will use the SUM function to calculate non-current assets.

If we look back at our list, there are three non-current assets on the list- Land and buildings, Equipment and Depreciation of equipment (which is a credit of assets).

So we fill out the non-current assets segment of the balance sheet with these accounts.

Remember to put the negative values for all the credit accounts. This makes the rest of the calculations easier.

Now select cell C7 and write down the following formula.

• By pressing Enter you will have all the non-current assets for the balance sheet. Finally, the section will look something like this.

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

Step 2: Evaluate Current Assets

Now let’s go back to our main dataset. We can see there are a total of four current assets accounts in the trial balance.  We will use the SUM function to calculate current assets.

Make a section for current assets in the balance sheet and put these values in them.

After that, select cell C8 and write down the following formula.

After pressing Enter, the balance sheet will now look like this.

Read More: Balance Sheet Format in Excel for Proprietorship Business

Step 3: Calculate Liabilities

Again, let’s go back to the trial balance and identify all the liabilities on the list. As we can see from the figure, there are a total of three- Creditors control, Income received in advance, and accrued expense. We will use the SUM function to calculate the liabilities.

Now put them all in a section on the balance sheet.

Next, select cell F4 and write down the following formula.

Finally, press Enter and you will have the liabilities segment completed. The balance sheet will look something like this now.

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

Step 4: Evaluate Equity

Once again, let’s go back to the trial balance and find the list of equities in the accounts. As we can see, there are only two entries here- the capital and drawings. But we need to calculate all the earnings in this segment too. We need external sources for that. And as the equity is a debit, in this case, it should have a negative value when compared with the rest. So we are putting a negative value on it here on the balance sheet.

Enter these values and the earnings in the balance sheet now.

Next, select cell F8 and write down the following formula. We will use the SUM function to calculate equities.

Finally, press Enter and you will have the equity list completed on the balance sheet.

Step 5: Determine Total Assets and Liabilities

Now calculate the total assets and liabilities and find out whether the sheet is indeed balanced or not. For that purpose, we need to use the SUM function.

To find the total assets add the non-current and current assets. For that, select cell C13 and write down the following formula.

After that, press Enter and you will have the total assets on the balance sheet.

To calculate the sum of liabilities and equity, select cell F12 and write down the following formula.

Press Enter and finally, you will have your balance sheet prepared in Excel from trial balance.

Conclusion

These were all the steps you need to prepare a balance sheet from a trial balance in Excel. Hope you can confidently prepare your balance sheet from your trial balance. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below. For more guides like this, visit Exceldemy.com.