Small Business Spreadsheet for Income and Expenses + P&L

Quick Answer: A small business spreadsheet for income and expenses works best as two registers, one for money in and one for money out, filled from your business bank and card statements, plus a P&L sheet that totals each category by month with SUMIFS. Income minus expenses for the year to date is the simple profit and loss statement lenders ask self-employed borrowers for. Keep categories fixed so every month is comparable.

The quickest way to know whether your business is making money is to list what came in, list what went out, and subtract. A spreadsheet does that well if you feed it from the bank rather than from memory. The design below keeps income and expenses on separate sheets, which makes each easier to check, and builds a profit and loss statement that updates itself.

Why separate income and expense sheets?

One combined list works, but two registers have practical advantages for a small business:

• Income rows need different details from expense rows: who paid and for which invoice, versus who you paid and for what.
• You check them differently. Income is matched against invoices or sales reports; expenses against receipts.
• A missing deposit stands out on a short income sheet. It disappears in a list of four hundred card purchases.

The IRS's page on what kind of records to keep lists deposit information and invoices among the supporting documents for gross receipts, and account statements, credit card statements and invoices for expenses. The two registers point at exactly those documents.

What goes on the Money In sheet?

Streams are your income categories: Consulting, Product sales, Retainers, Interest, Other income. Keep a separate stream called Not income for owner contributions, loan proceeds and transfers from savings. They are deposits, but counting them as revenue overstates your profit, and that is the mistake lenders and preparers catch first.

What goes on the Money Out sheet?

Record amounts as positive numbers on this sheet; the P&L subtracts them. A starter category list: Advertising, Contractors, Cost of goods, Software, Office, Phone and internet, Insurance, Professional fees, Rent, Travel, Meals, Vehicle, Bank fees, Other. Add Not expense for owner draws, card payments and transfers to savings, for the same reason as above.

How do you fill the registers from bank statements?

Rather than typing, start from the statement rows:

1. Download each month's statement for every business account and card.
2. Turn them into rows. Many banks offer a CSV download; if you only have PDFs, QuickBankConvert converts statements to Excel or CSV with date, description and a signed amount, and lists the statement's opening and closing balance with the rows.
3. Sort the rows by amount. Positive amounts go to Money In, negative amounts to Money Out (flip the sign as you paste).
4. Fill in Stream or Category, and the invoice or receipt details.

For categorizing large batches, our guide to bank statement categorization covers rules that keep it consistent, and tracking business expenses from bank statements covers receipts.

How do you build a monthly profit and loss sheet?

On a sheet named P&L, put the year in B1 (for example 2026) and month numbers 1 to 12 across C3:N3. List income streams in A5:A9 and expense categories in A12:A25.

For an income stream in C5:

=SUMIFS('Money In'!$E:$E, 'Money In'!$D:$D, $A5, 'Money In'!$A:$A, ">="&DATE($B$1,C$3,1), 'Money In'!$A:$A, "<"&EDATE(DATE($B$1,C$3,1),1))

For an expense category in C12:

=SUMIFS('Money Out'!$D:$D, 'Money Out'!$C:$C, $A12, 'Money Out'!$A:$A, ">="&DATE($B$1,C$3,1), 'Money Out'!$A:$A, "<"&EDATE(DATE($B$1,C$3,1),1))

Fill each across to column N and down the list. Then add the rows that make it a P&L:

Add a column O headed Year to date with =SUM(C5:N5) for each row. The same SUMIFS syntax works in Google Sheets; Google's SUMIFS page documents it. If you'd rather not write formulas, an Excel PivotTable of each register, with category in Rows and date grouped by month in Columns, gives the same totals.

What profit and loss statement do lenders ask for?

Self-employed mortgage and business-loan applicants are often asked for a year-to-date profit and loss statement, especially when the last tax return is several months old. The lender's checklist decides the details, but the usual expectations are:

• The period: from 1 January to the end of the most recent month.
• The layout: business name, the period, total income, expenses by category, and net profit. Your YTD column already has this.
• A signature and date from you, and sometimes a statement that it is accurate. Some lenders require it to be prepared or reviewed by a CPA or tax preparer.
• Supporting bank statements for the same months, which the underwriter compares with the P&L. Deposits on the statements should be explained by your income figures.

Before you send it, check that total income on the P&L is close to the total of business deposits on the statements, minus the deposits you marked Not income. A big gap is the first question an underwriter will ask. Lenders also look at cash flow on the statements themselves; see cash flow analysis from bank statements.

Why doesn't my profit match the change in my bank balance?

Because several things move cash without being income or expense, and the P&L leaves them out by design. Over a year, the gap between net profit and the change in your bank balance is usually explained by:

• Owner draws and contributions. Money you take out lowers the balance but isn't an expense; money you put in raises it but isn't revenue.
• Loan proceeds and repayments. Borrowed money arrives as a deposit. The principal part of each repayment leaves as a payment but isn't an expense; only the interest is.
• Equipment purchases. A laptop or vehicle bought for the business is often handled as an asset and depreciated, rather than expensed in one month. Your preparer decides how; in the spreadsheet, give it its own category so it is easy to find.
• Card timing. Purchases on a business card are expenses when you make them, but cash only leaves checking when you pay the card.
• Sales tax collected. Tax you charge customers and pay over to the state isn't your income.

Add a small reconciliation block under the P&L: start with net profit, add contributions and loan proceeds, subtract draws, principal repayments and equipment, and you should land close to the year's change in cash. If you don't, a row is miscategorized.

How do you check the numbers each month?

• For each account, the month's money in minus money out should equal the statement's closing balance minus its opening balance.
• Every row has a category, and nothing new has landed in Other.
• Every expense over a threshold you choose, say $75, has a receipt marked Y.
• Net profit looks plausible against the last few months. A sudden jump usually means a loan or transfer slipped into income.

Related guides

• Excel bookkeeping template
• Track business expenses from bank statements
• PDF to Excel