How to Prepare Balance Sheet from Trial Balance in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 of the balance sheet and trial balance and show you how to prepare a balance sheet from a trial balance in Excel.


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 flow 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 this 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. However, a trial balance helps to correct those errors and helps 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.


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

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.

how to prepare balance sheet from trial balance in excel

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.

how to prepare balance sheet from trial balance in excel

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.

=SUM(C5:C7)

how to prepare balance sheet from trial balance in excel

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

how to prepare balance sheet from trial balance in excel

Read More: Debit Credit Balance Sheet with Excel Formula


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.

=SUM(C11:C14)

how to prepare balance sheet from trial balance in excel

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

how to prepare balance sheet from trial balance in excel

Read More: How to Make Trial Balance in Excel


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.

=SUM(F5:F7)

how to prepare balance sheet from trial balance in excel

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

how to prepare balance sheet from trial balance in excel

Read More: Calculate Debit Credit Running Balance Using Excel Formula


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.

=SUM(F11:F13)

how to prepare balance sheet from trial balance in excel

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

how to prepare balance sheet from trial balance in excel


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.

=SUM(C8,C15)

how to prepare balance sheet from trial balance in excel

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.

=SUM(F8,F14)

how to prepare balance sheet from trial balance in excel

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

how to prepare balance sheet from trial balance in excel

Read More: How to Keep a Running 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 it yourself while you go through the article.


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.


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.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo