Template · Accounting
Trial balance to income statement and balance sheet
Map a trial balance to an income statement and balance sheet, with checks that the books balance.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Income statement and balance sheet from the trial balance | |||
| Example data — replace the blue input cells with your own. | |||
| NET REVENUE | NET INCOME | TOTAL ASSETS | BALANCE CHECK |
| 1,663,850.00 | 50,120.00 | 1,089,990.00 | Balanced |
| Income statement, year to date | |||
| Sales revenue | 1,686,300.00 | SALES | |
| Less: sales returns and allowances | (22,450.00) | SRET | |
| Net revenue | 1,663,850.00 | ||
| Cost of goods sold | 1,108,220.00 | COGS | |
| Freight in | 61,380.00 | FRTIN | |
| Total cost of sales | 1,169,600.00 | ||
| Gross profit | 494,250.00 | ||
| Operating expenses | |||
| Salaries, wages and payroll taxes | 216,270.00 | SAL |
Showing the first 16 of 71 rows and 4 of 4 columns. Cells with formulas show the formula on hover.
| Trial balance | |||||||
| Year-to-date balances by account. Enter debits and credits as positive numbers; the line code links each account to the Map tab. | |||||||
| Account no. | Account name | Debit | Credit | Net (debit - credit) | Line code | Statement line | Statement |
| 1010 | Cash, operating account | 186,400.00 | 0.00 | 186,400.00 | CASH | Cash | BS |
| 1020 | Cash, payroll account | 12,850.00 | 0.00 | 12,850.00 | CASH | Cash | BS |
| 1100 | Accounts receivable | 143,260.00 | 0.00 | 143,260.00 | AR | Accounts receivable | BS |
| 1190 | Allowance for doubtful accounts | 0.00 | 7,160.00 | (7,160.00) | ALLOW | Allowance for doubtful accounts | BS |
| 1200 | Inventory | 418,900.00 | 0.00 | 418,900.00 | INV | Inventory | BS |
| 1300 | Prepaid insurance | 9,600.00 | 0.00 | 9,600.00 | PREP | Prepaid expenses | BS |
| 1310 | Prepaid software subscriptions | 4,380.00 | 0.00 | 4,380.00 | PREP | Prepaid expenses | BS |
| 1500 | Warehouse equipment | 286,000.00 | 0.00 | 286,000.00 | PPE | Property, plant and equipment, at cost | BS |
| 1510 | Delivery vehicles | 164,500.00 | 0.00 | 164,500.00 | PPE | Property, plant and equipment, at cost | BS |
| 1590 | Accumulated depreciation | 0.00 | 128,740.00 | (128,740.00) | ACCDEP | Accumulated depreciation | BS |
| 2010 | Accounts payable | 0.00 | 231,480.00 | (231,480.00) | AP | Accounts payable | BS |
| 2100 | Accrued wages | 0.00 | 18,650.00 | (18,650.00) | ACCR | Accrued liabilities | BS |
Showing the first 16 of 157 rows and 8 of 8 columns. Cells with formulas show the formula on hover.
| Statement line map | |||||
| Each line code, the statement it belongs to, its section and the sign that shows it as a positive figure. | |||||
| Sign: -1 for credit-normal lines so they show positive; +1 for debit-normal lines. Spare rows at the bottom. | |||||
| Line code | Line name | Statement | Section | Sign | Sort order |
| SALES | Sales revenue | IS | Revenue | -1 | 1 |
| SRET | Sales returns and allowances | IS | Revenue | -1 | 2 |
| COGS | Cost of goods sold | IS | Cost of sales | 1 | 3 |
| FRTIN | Freight in | IS | Cost of sales | 1 | 4 |
| SAL | Salaries, wages and payroll taxes | IS | Operating expenses | 1 | 5 |
| RENT | Rent | IS | Operating expenses | 1 | 6 |
| UTIL | Utilities | IS | Operating expenses | 1 | 7 |
| INS | Insurance | IS | Operating expenses | 1 | 8 |
| DEP | Depreciation | IS | Operating expenses | 1 | 9 |
| MKT | Marketing and advertising | IS | Operating expenses | 1 | 10 |
| PROF | Professional fees | IS | Operating expenses | 1 | 11 |
| OFF | Office supplies | IS | Operating expenses | 1 | 12 |
Showing the first 16 of 40 rows and 6 of 6 columns. Cells with formulas show the formula on hover.
| Trial balance to financial statements |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Turns a year-to-date trial balance into an income statement and a balance sheet. Each trial balance row carries a statement line code. The Map tab defines the lines, the statement and section each belongs to, and its sign. The Statements t… |
| How to use it |
| 1. On the Trial balance tab, enter each account number, name, debit and credit (positive numbers), and its statement line code from the Map tab. |
| 2. Read the Net column and the totals. The check reads OK: debits equal credits when the trial balance is in balance. |
| 3. Check that every row shows a statement line name. A row marked Not in map or No code is left out of the statements until it has a valid code. |
| 4. On the Statements tab, read the income statement, the balance sheet and the checks. The balance check reads Balanced when assets equal liabilities plus equity. |
| 5. To add a line, use a spare row on the Map tab, then add a matching statement line on the Statements tab. |
| Formulas and method |
| Net = debit - credit for each account. A line amount = SUMIF of the net on the line code, multiplied by the sign from the Map tab. |
| Income statement: net revenue = sales revenue + sales returns (negative); gross profit = net revenue - cost of sales; operating income = gross profit - operating expenses; income before tax = operating income + other income (expense), net;… |
Showing the first 16 of 32 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook turns a year-to-date trial balance into an income statement and a balance sheet. Each trial balance row has a debit or a credit and a statement line code. The Map tab defines each line, the statement it belongs to, its section and the sign that shows it as a positive figure. The Statements tab adds up the net balances for each line with SUMIF on the code and multiplies by the sign.
The income statement runs from net revenue through gross profit, operating income and income before tax to net income. The balance sheet includes the current-year net income in equity, so total assets should equal total liabilities and equity. Checks confirm that debits equal credits, that every trial balance row has a valid line code, and that the balance sheet balances.
The example is a fictional distributor, Example Distribution Co., with 36 accounts for the year to September 30, 2026. Its figures are invented. The trial balance accepts up to 150 accounts, and the Map tab has spare rows for new line codes.
What’s inside
- Net balance per account, mapped to statement lines by code with SUMIF
- Income statement from net revenue to net income, with gross profit and operating income
- Balance sheet with current-year net income in equity
- Checks for debits equal to credits, valid line codes and a balanced balance sheet
- Editable line map with the sign and sort order for each line
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Statements | Income statement, balance sheet, KPI tiles and checks. |
| Trial balance | One row per account with debit, credit, net, line code and the statement it maps to. |
| Map | Statement lines with code, name, statement, section, sign and sort order. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 512 formulas in 1,025 cells across 4 tabs, so 50% of its cells calculate. They use 8 distinct functions; the longest formula is 104 characters and 333 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 754 | one result when a test is true, another when false |
VLOOKUP | 331 | looks a value up in a column |
IFERROR | 300 | swaps an error for a fallback value |
SUMIF | 31 | adds values meeting one condition |
SUM | 7 | adds numbers |
ABS | 3 | absolute value |
COUNTIF | 2 | counts cells meeting one condition |
ROUND | 2 | rounds to a number of digits |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- Enter each account's number, name, debit and credit on the Trial balance tab.
- Assign each account a line code from the Map tab.
- Confirm the trial balance check reads OK and every row has a valid line name.
- Read the income statement and the balance sheet on the Statements tab.
- Confirm the balance check reads Balanced.
What is it good for?
- Preparing annual statements from a trial balance for a small business
- Checking a trial balance before a lender or investor review
- Building a monthly profit and loss from a ledger export
- Tracing which accounts make up each statement line
Questions about this sheet
Why are some balance sheet lines negative?
Contra accounts such as the allowance for doubtful accounts and accumulated depreciation are shown as negative amounts that reduce their parent line.
What happens if an account has no valid line code?
The trial balance check flags the row, and the row is left out of the statements. The balance check then fails until the code is fixed.
Does the workbook need a closing entry?
No. The opening retained earnings are kept as a separate line, and the current-year income is added to equity on the balance sheet.
Can I add a new statement line?
Yes. Add the line to the Map tab with its sign, then add a matching row on the Statements tab.