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 = Liabilities + Shareholder’s Equity

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.

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.

1. Creating an Income Statement from Trial Balance

Steps

Create a separate sheet for the income statement (named “IS” for Income Statement).

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

Enter 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 inserting the following formula in cell C19:

=C10-C18

Press ENTER.
A negative net income indicates a net loss if total revenue is less than total expenses.

2. Creating the 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

Capture the relevant values on a new sheet. You can also use the same sheet as the trial balance, however separating them onto different sheets enhances visualization.

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.

3. Preparing the 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, enter the values categorized as shown in the first example.

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.
Link the owner’s equity value as of August 31.
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.

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.