How to Prepare Financial Statements from Trial Balance in Excel

Financial Statement

Financial statements are essential documents that summarize an organization’s or company’s financial situation. There are four main types of financial statements:

Balance Sheet

Also known as a Statement of Financial Position or Condition, the balance sheet summarizes the company’s financial position after a specific period. It consists of three parts: assets, liabilities, and owner’s equity. The equation for a balance sheet is:

  • Assets: These are the main resources owned by the company, including current and fixed assets, tangible and intangible assets, etc.
  • Liabilities: These represent what the company owes to others, such as cash, loans, etc.
  • Owner’s Equity: The value for the company’s shareholders after all assets have been sold off and liabilities paid.

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

This statement reports the profit and loss of a company over a specific period. It consists of three components: revenue, expenses, and profits. The net profit is calculated by subtracting total expenses from total revenue.


Trial Balance

A trial balance is another financial report that shows the closing balances of all accounts at a specific point in time. The balances from all ledgers are typically organized into debit and credit columns. The total values of these credits and debits should be equal. The trial balance helps ensure the accuracy of a company’s bookkeeping system. Although it should ideally be balanced, discrepancies may occur due to errors.


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 an Income Statement from Trial Balance

Creating Income Statement from Trial Balance

Steps:

  • Start by creating a separate sheet for the income statement (named “IS” for Income Statement).

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

  • Reference the cell values from the main sheet (trial balance) into this income statement sheet.

  • Type the following formula in cell C10 and press ENTER. (There is only one item for the revenue, if there were more items, you would need to add them.)

=C9

  • Calculate the total expenses by linking all expense items, using the following formula:

=SUM(C12:C17)

  • Press ENTER. This will return the total expenses.
  • Compute net income by typing the following formula in cell C19:

=C10-C18

  • Lastly, press ENTER.
  • A negative net income indicates a net loss if total revenue is less than total expenses.

Read More: How to Automate Financial Statements in Excel


2. Making Owner’s Equity Statement

In this example, we’ll create an owner’s equity statement using data from the trial balance in Excel. We’ll utilize the SUM function to add up the values in the statement. Additionally, we’ll account for withdrawals by inserting a negative sign.

Steps:

  • Start by typing the relevant values on a new sheet. You can also use the same sheet as the trial balance but separating them onto different sheets enhances visualization.

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

  • Link the values from the trial balance. The initial value is 0, and the value for Investments matches the owner’s capital.
  • For withdrawals, include a negative sign before linking the value:

=-main!C16

  • Calculate the total equity using the SUM formula in cell C12:

=SUM(C8:C11)

  • Press ENTER to compute the owner’s capital as of August 31.

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


3. Preparing Balance Sheet

In this final example, we’ll create a balance sheet from the trial balance in Excel. Again, we’ll link all the necessary values in this financial statement. The owner’s equity value will be linked from the previous example. Ensure that the balance sheet balances; otherwise, we’ll need to adjust the trial balance.

Steps:

  • On a new sheet, type the values categorized as shown in the first example.

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

  • Link the asset values from the respective trial balance entries.
  • Calculate the total assets using the formula in cell C15:

=SUM(C9:C14)

  • Press ENTER.
  • Link the liabilities and compute their total:

=SUM(C18:C22)

  • Press ENTER.
  • Next, link the owner’s equity value as of August 31.
  • Finally, calculate the total liabilities and owner’s equity:

=C23+C25

  • Press ENTER. This will return the values of the total liabilities and owner’s equity.

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

As you’ve observed, linking values to cells from the trial balance (located in the “main” sheet) allows the financial statements to automatically update whenever values change. You can easily modify the trial balance and automate other calculations in Excel.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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