Mapping a trial balance to financial statements is one of the most essential accounting workflows. From that one report, accountants can create a full Income Statement, Balance Sheet, and Cash Flow Statement.
In this guide, we’ll walk you through trial balance to financial statements examples — using Excel formulas, free templates, and an automated method with PivotXL.
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
What Is a Trial Balance?
A trial balance is a list of all general ledger accounts and their debit/credit balances. It’s used to confirm that total debits equal total credits and forms the foundation for preparing financial statements.
Sample Trial Balance
| Account Code | Account Name | Debit | Credit |
|---|---|---|---|
| 1000 | Cash | 50,000 | – |
| 1010 | Accounts Receivable | 25,000 | – |
| 2000 | Accounts Payable | – | 15,000 |
| 3000 | Retained Earnings | – | 20,000 |
| 4000 | Sales Revenue | – | 100,000 |
| 5000 | Salaries Expense | 60,000 | – |
| 5010 | Rent Expense | 10,000 | – |
Example 1: Trial Balance to Income Statement
The Income Statement (Profit & Loss) shows revenues and expenses during a period.
Steps:
- Filter trial balance accounts to only include revenue and expense accounts.
- Use SUMIFS or XLOOKUP to group them into categories (e.g., Sales Revenue, Operating Expenses).
- Calculate Net Profit:
= Total Revenue – Total Expenses
Example Output:
| Income Statement |
|---|
| Sales Revenue |
| Salaries Expense |
| Rent Expense |
| Net Income |
This net income will later flow into the Retained Earnings on the Balance Sheet.
Example 2: Trial Balance to Balance Sheet
The Balance Sheet shows Assets = Liabilities + Equity.
Steps:
- Map all accounts to their type: Asset, Liability, or Equity.
- Include Retained Earnings from trial balance + Net Income from P&L.
- Use Excel formulas like
SUMIFSto total each section.
Example Output:
| Balance Sheet |
|---|
| Assets |
| Cash |
| Accounts Receivable |
| Total Assets |
| Liabilities |
| Accounts Payable |
| Equity |
| Retained Earnings |
| Current Year Profit |
| Total Equity |
| Liabilities + Equity |
👉 You’ll notice:
Assets ($75K) ≠ Liabilities + Equity ($65K)
— so this example needs adjustment or more accounts to balance. This is a teaching moment to ensure all accounts are included.
Example 3: Trial Balance to Cash Flow Statement
This is optional, but advanced users often go further to build a Cash Flow Statement.
Steps:
- Start with Net Income
- Adjust for non-cash items (e.g., depreciation)
- Add/Subtract working capital changes (Receivables, Payables)
- Separate into:
- Operating Activities
- Investing Activities
- Financing Activities
Automate the Process with PivotXL
Manually mapping financial statements each period is time-consuming.
With PivotXL, you can:
- Upload your trial balance
- Define reusable account groupings
- Auto-generate Income Statement, Balance Sheet, and Cash Flow in Excel
- Refresh your reports monthly with one click
Key Takeaways
| Report | What You Need from Trial Balance |
|---|---|
| Income Statement | Revenue & Expense accounts |
| Balance Sheet | Asset, Liability, Equity accounts |
| Cash Flow | P&L + Working Capital adjustments |
By understanding how each account in your trial balance feeds into the financial statements, you can build confidence and reduce reporting errors.
Summary
- A trial balance is the starting point for all core financial statements.
- By using Excel’s
SUMIFS,XLOOKUP, and grouping logic, you can create fully linked P&L, Balance Sheet, and even Cash Flow reports. - Use our Excel template to follow along, or automate the entire workflow with PivotXL.
