Converting a trial balance to an income statement and balance sheet is a fundamental process in financial reporting. These two statements — the Profit & Loss (P&L) and the Balance Sheet — are essential for understanding business performance and financial position.
In this article, we’ll walk through how to prepare both reports from a trial balance using Excel, complete with examples, downloadable templates, and an automated method using PivotXL.
What Is a Trial Balance?
A trial balance is a list of all general ledger accounts and their balances at a point in time. It is used to ensure total debits equal total credits and forms the basis for preparing financial statements.
Example Trial Balance:
| Account Code | Account Name | Debit | Credit |
|---|---|---|---|
| 1000 | Cash | 25,000 | – |
| 1010 | Accounts Receivable | 15,000 | – |
| 2000 | Accounts Payable | – | 10,000 |
| 3000 | Retained Earnings | – | 15,000 |
| 4000 | Sales Revenue | – | 50,000 |
| 5000 | Salaries Expense | 30,000 | – |
| 5010 | Rent Expense | 5,000 | – |
Step 1: Create the Income Statement (Profit & Loss)
The Income Statement summarizes revenues and expenses over a period to calculate Net Profit or Loss.
Steps:
- Filter only revenue and expense accounts from the trial balance.
- Use
SUMIFSto group totals by category (e.g., Operating Expenses). - Calculate net income:
=SUM(Revenue Accounts) - SUM(Expense Accounts)
Output Example:
| Income Statement |
|---|
| Sales Revenue |
| Salaries Expense |
| Rent Expense |
| Net Income |
This Net Income will be added to Retained Earnings in the Balance Sheet.
Step 2: Build the Balance Sheet
The Balance Sheet shows the financial position:
Assets = Liabilities + Equity
Steps:
- Group trial balance accounts by category: Assets, Liabilities, and Equity.
- Add the Net Income from the P&L to the Retained Earnings line.
- Use Excel formulas like
SUMIFSto calculate subtotals.
Output Example:
| Balance Sheet |
|---|
| Assets |
| Cash |
| Accounts Receivable |
| Total Assets |
| Liabilities |
| Accounts Payable |
| Equity |
| Retained Earnings (Start) |
| Current Year Net Income |
| Total Equity |
| Total Liabilities + Equity |
Now, your Balance Sheet is balanced, and both financial reports are complete.
Automate with PivotXL
If you’re doing this monthly, automating the process can save hours.
PivotXL is an Excel-based FP&A automation tool that:
- Imports trial balances from CSV or QuickBooks/Xero
- Maps accounts to P&L and Balance Sheet categories
- Generates reports in pre-formatted Excel templates
- Recalculates with one click for each reporting period
Download Excel Template
Trial Balance to Financial Statements Examples – Excel Template
👉 For a complete walkthrough on mapping trial balances to both the Income Statement and Balance Sheet, check out our full guide here: How to Prepare Financial Statements from Trial Balance
🔍 Want to try it yourself?
Download our free Excel workbook and follow along with the example. You’ll be able to click through each formula, see how trial balance accounts are mapped and grouped, and understand how they roll up into a complete Profit & Loss Statement and Balance Sheet. It’s a hands-on way to learn the structure of financial reporting from the ground up.
👉 Download the free trial balance to P&L Excel workbook
Summary Table
| Step | Description |
|---|---|
| 1 | Filter trial balance to show revenues and expenses |
| 2 | Create Income Statement with net profit |
| 3 | Use net profit to adjust Retained Earnings |
| 4 | Build Balance Sheet using account categories |
| 5 | Verify Assets = Liabilities + Equity |
Key Takeaways
- A trial balance is the source of truth for preparing both Income Statement and Balance Sheet.
- Excel formulas like
SUMIFS,XLOOKUP, and grouping logic are essential for mapping and summarizing accounts. - Net income from the P&L flows into the Balance Sheet’s equity section, linking both statements.
- Tools like PivotXL can automate the entire workflow — saving time and improving accuracy.
