How to Prepare Financial Statements from Trial Balance in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you how to prepare three financial statements from an adjusted trial balance in Excel. Before everything else, let us briefly discuss financial statements and the trial balance.


Financial Statement

There are four main types of financial statements; balance sheet, income statement, owner’s equity statement, and cash flow statement. These statements summarize the financial situation of an organization or company. They are the written documents or records that the company keeps throughout the year. Here, we will talk about the first two main parts of a financial statement report:

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made up of three parts: 1. Revenue 2. Expense 3. Profits. So, it contains all incomes and expenses for a certain period and calculates net profit.

Balance Sheet

It summarizes the financial position of the company after a certain period and is also known as a Statement of Financial Position/condition. In general, a balance sheet contains three parts. And, these are assets, liabilities, and owners’ equity. A balance sheet provides a glimpse of the company’s finances. The sheet consists of the company’s liabilities, assets, and shareholder’s equity. Balance sheets are organized according to the equation:

Assets = Liabilities + Shareholder’s Equity

  • Assets: These are the main resources owned by the company. Assets can be categorized into many types. Example- current and fixed assets, tangible and intangible assets, etc.
  • Liabilities: They are things that the company owes to a person or another company, like cash, loans, etc.
  • Owner’s Equity: It represents the value for a company’s shareholders after all the company’s assets have been sold off and all company liabilities have been paid off.

Trial Balance

A trial balance is another form of financial report that shows the 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, A trial balance- as the name suggests- should be considered balanced. But this is not always the case because of some types of errors in a company. However, a trial balance aids in correcting those mistakes and improving the balance of financial records.

Now, let us demonstrate the steps to prepare financial statements from a trial balance.


How to Prepare Financial Statements from Trial Balance in Excel: 3 Examples

We will prepare three financial statements from the trial balance. Firstly, we will create an income statement. Following that, we will generate an owner’s equity statement. Finally, we will prepare a balance sheet. Moreover, this is a snapshot of the adjusted trial balance for this article.

3 Examples to Prepare Financial Statements from Trial Balance in Excel


1. Creating Income Statement from Trial Balance

For the first example, we will create an income statement from the trial balance in Excel. Additionally, we will reference all the values to the cell values from the trial balance sheet. Therefore, it will make the whole process automated. Before doing so, let us categorize the account titles. Here, we have labeled all the accounts into two categories: “Balance Sheet” and “Income Statement”.

Creating Income Statement from Trial Balance

Steps:

  • Firstly, type all the revenues and expenses on a separate sheet. We have named this sheet “IS” acronym for Income Statement.

Prepare Income Statement of the Financial Statements from Trial Balance in Excel

  • Secondly, reference the cell values from the “main” sheet into this sheet. We have shown you how to reference or link a cell value in the following animated image.

  • Thirdly, type the following formula in cell C10 and press ENTER. Here, there is only one item for the revenue, if there were more items, then you would need to add them.

=C9

  • Then, link all the expenses and type this formula in cell C18.

=SUM(C12:C17)

  • After that, press ENTER. This will return the total expenses.
  • Then, type another formula in cell C19.

=C10-C18

  • Lastly, press ENTER. Our net income will result from this. If the total revenue was less than the total expenses, then we would get a negative net income, which would imply a net loss.

Read More: How to Automate Financial Statements in Excel


2. Making Owner’s Equity Statement

In the second example, we will make an owner’s equity statement from the trial balance in Excel. We will use the SUM function to add the values in the statement. Moreover, we will insert a negative sign for the withdrawals in this example.

Steps:

  • To begin with, type these values on a new sheet. You can also type these on the same sheet as the trial balance. However, we are preparing each statement on a separate sheet for better visualization.

Making Owner’s Equity Statement to Prepare Financial Statements from Trial Balance in Excel

  • Then, link the values from the trial balance. Here, the first value is 0 and the value for “Investments” is the same as the value of the owner’s capital.
  • After that, put a negative sign before linking the value of “Withdrawals”.

=-main!C16

  • Then, type this formula in cell C12.

=SUM(C8:C11)

  • Finally, press ENTER. Thus, we will calculate the value of the owner’s capital on August 31.

Read More: How to Create a Personal Financial Statement in Excel


3. Preparing Balance Sheet

In this last example, we will prepare a balance sheet from the trial balance in Excel. We will again link all the values in this financial statement. The value of the owner’s equity will be linked to the last example. Lastly, we will need to make sure the balance sheet balances; else, we will need to re-adjust the trial balance.

Steps:

  • Firstly, type these values on a new sheet. These values are obtained from the categorization shown in the first example.

Preparing Balance Sheet from Trial Balance to Prepare Financial Statements in Excel

  • Next, link all the asset values from the respective values from the trial balance.
  • Then, type the following formula in cell C15.

=SUM(C9:C14)

  • After that, press ENTER and link the liabilities.
  • Then, type another formula in cell C23.

=SUM(C18:C22)

  • Next, press ENTER and link the owner’s equity value on August 31.
  • Afterward, type this formula in cell C26.

=C23+C25

  • Finally, press ENTER. This will return the values of the total liabilities and owner’s equity.
  • Lastly, we can see the balance sheet balances, and this concludes our journey to prepare financial statements from a trial balance in Excel.

Final Balance Sheet Snapshot of Prepare Financial Statements from Trial Balance in Excel

Read More: How to Link 3 Financial Statements in Excel


Automating Financial Statements from Trial Balance

You have seen that we linked the values to the cells from the trial balance, which is in the “main” sheet of this file. For that reason, whenever we change the values, the financial statements will automatically update. Therefore, you can easily change the trial balance and automate everything else in Excel. However, if you have new items, then you will need to link those values again.


Download Practice Workbook

You can download the Excel file from the link below.


Conclusion

We have shown you three quick examples to prepare financial statements from a trial balance in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Thanks for reading, keep excelling!


Related Articles


<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel

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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo