The work of tax prep is rarely the arithmetic. It is knowing that the numbers are complete and that each one means what you think it means. A statement-derived workbook earns trust in two ways: every month ties to its statement, and every line is classified once. This guide covers workbook preparation, not tax advice; how each line is treated depends on the entity, the jurisdiction and the preparer’s judgment.
Which lines are not income or expense
The most common error in a bank-derived workbook is counting a movement of money as if it were earnings or spending. Mark these before categorizing, because a category assigned to a transfer is wasted work.
| Statement line | Usually treated as | Watch for |
|---|---|---|
| Customer or client deposits | Business income | Payment processors may deposit net of fees, understating sales |
| Transfers between your own accounts | Neither income nor expense | Both legs appear; mark both |
| Credit card payments | Neither; the card’s purchases are the expense | Counting payment and purchases double counts |
| Loan proceeds and payments | Proceeds are a liability; a payment splits into principal and interest | Only the interest is typically an expense |
| Owner deposits and draws | Equity, not income or expense | A draw is not a salary |
| Sales tax collected | A liability, not income | Deposits may include tax |
| Vendor refunds | A reduction of the original expense | Not income |
| Cash withdrawals | Unknown until supported | Needs a receipt or an explanation |
Workbook layout
Keep three working tabs. Transactions holds every row with Account, Source, Date, Description, Money out, Money in, Amount, Type and Category. Type takes one of a few values: Income, Expense, Transfer, Card payment, Loan or Owner. Summary totals by category and month. Tie-out lists each statement’s opening, closing and net change, with a link check that closing equals the next opening. A monthly category total on the Summary tab looks like this:
=SUMIFS(Transactions!G:G, Transactions!I:I, $A2,
Transactions!C:C, ">="&B$1,
Transactions!C:C, "<"&EDATE(B$1,1))Here column G is Amount, column I is Category, column C is Date, and B1 holds the first day of the month.
Three checks before you hand it over
First, the Amount column over every row must equal the sum of each statement’s closing balance minus its opening balance. If a year of statements opens at 12,400.00 and closes at 15,210.40, the rows must total 2,810.40. Second, Amount where Type is Transfer must total 0.00 across accounts; a remainder means a missing leg, or a transfer that straddles the year end. Third, the Uncategorized total should be small and itemized on the handover tab.
Step by step: build the workbook
- Tie out every statement first. Convert each statement and confirm opening + deposits − withdrawals = closing before its rows join the workbook. Check that closing balances chain from month to month.
- Stack the rows on one sheet. Add Account and Source columns. Keep one header row, no merged cells, no blank rows, and delete each file’s TOTAL row so it is not counted again.
- Make dates and amounts real. Convert text dates to true dates. Add Amount = money in − money out, so deposits are positive and payments negative, and format it with two decimals.
- Mark the lines that are not income or expense. Flag transfers between your accounts, card payments, loan principal and owner deposits or draws in a Type column.
- Categorize. Use the category list your preparer works from. Apply it by payee with a lookup table, and keep an Uncategorized bucket whose total stays visible.
- Build the summary tab. Total by category and month. With transfers included, the grand total must equal the sum of every statement’s closing balance minus its opening balance.
- Compare with other records. Match deposits against sales reports, invoices and any information returns the client received, such as 1099-K or 1099-NEC forms in the US.
- Hand over with a note. List missing statements, open questions and assumptions on a separate tab.
What can go wrong
- A year with a hole in it. One missing statement understates income or expense for that month. Run the continuity check before categorizing anything.
- Totals counted twice. Each exported sheet ends with a TOTAL row. Left in place, it is summed again in a pivot table.
- Text instead of numbers or dates. Pivot tables cannot group text dates by month, and text amounts are skipped by SUMIFS. Left-aligned cells are the tell.
- Year-end cut-off. A statement that runs from the 20th to the 19th spans two tax years. Assign rows to a year by transaction date, not by statement.
- Rows read from a scan. Dates and payees are the part a balance check cannot verify. Spot-check them against the page.
- Sorting part of the sheet. Sorting one column alone separates amounts from their descriptions. Select the whole table, or sort an Excel table.
- Mixed personal and business activity. Do not guess. Mark the line and ask the client; a one-line Type value is cheaper than amended work.
- Categories that drift. Free-typed category names create near-duplicates. Use a drop-down list taken from the preparer’s chart.
Where SheetParser fits
SheetParser converts each statement into rows and checks opening + deposits − withdrawals against the closing balance. The Excel export has a Bank Transactions sheet with Date, Description, Debit (Money Out), Credit (Money In) and Balance, plus a Reconciliation Audit sheet. A batch of up to 12 statements downloads as one workbook with a Consolidated Transactions sheet that carries a Source Statement column and a Batch Reconciliation sheet. Start with the bank statement to Excel tool; 1 file a day is free up to 10 pages, and paid plans open files up to 150 pages. Categories and tax treatment are yours to apply.