How to set up an income and expense spreadsheet for a small business

Plenty of freelancers and small business owners keep their books by "checking the bank balance at the end of the month". That works until tax time, a client who hasn't paid the balance, or the simple question "how much did I actually make this month?"

A spreadsheet with the right columns and a fixed month-end routine solves most of that. Here is a layout that is enough for a one-person business or a small studio.

The columns you need

Column	Example	Why
Date	2026-10-05	The day money actually came in or went out
Type	Income / Expense	Lets you filter
Category	Design fees, materials, software	Keep it to about 10 categories
Description	Client A website redesign — deposit	Clear enough for you in three months
Party	Client A	Customer or supplier
Amount	30,000	Always positive; the Type column says which way
Tax	1,500	When an invoice or receipt shows tax
Method	Bank transfer, cash, card	Needed when you reconcile
Reference	Invoice or receipt number	So you can find the document later
Status	Received / Awaiting payment	The column that gets you paid

Status is the one people skip. Record balances and instalments as Awaiting payment and switch them to Received when the money lands. At month end, one filter shows who still owes you.

Categories

Too many categories and you'll stop maintaining them. Start with this and adjust later:

• Income: services, product sales, other income
• Expenses: subcontractors, materials and stock, software and subscriptions, equipment, travel, advertising, rent and utilities, taxes and fees, other

Each entry gets one category. If one purchase covers two purposes, split it into two lines.

Monthly totals with SUMIFS

With one transaction per row, SUMIFS does the totals. Assuming column A is Date, B is Type, C is Category, F is Amount and J is Status:

• Income this month: =SUMIFS(F:F, B:B, "Income", A:A, ">="&DATE(2026,10,1), A:A, "<"&DATE(2026,11,1))
• One expense category: =SUMIFS(F:F, B:B, "Expense", C:C, "Software and subscriptions")
• Total awaiting payment: =SUMIFS(F:F, J:J, "Awaiting payment")

Add a summary sheet with income, expenses and the difference for each month, and you have a simple profit-and-loss view.

A 30-minute month-end routine

1. Export your bank statement for the month as CSV or Excel from online banking.
2. Match line by line: anything in the bank but not in your sheet, add it; anything in your sheet but not in the bank, check whether it was cash or hasn't cleared yet.
3. Chase what's overdue: filter Awaiting payment, and message anyone past the agreed date that same day.
4. File the paperwork: photograph or scan invoices and receipts, name them date_party_amount, keep them in one folder.
5. Look at three numbers: income this month, expenses this month, total awaiting payment.

Do it on the same day every month — say the 5th — and at tax time you only need to combine twelve months.

One month, filled in

A small design studio's October:

Date	Type	Category	Description	Amount	Status
10/03	Income	Services	Client A website redesign — deposit	30,000	Received
10/05	Expense	Software and subscriptions	Design software, monthly	1,680	Paid
10/12	Expense	Subcontractors	Illustration	8,000	Paid
10/20	Income	Services	Studio B poster design	12,000	Received
10/31	Income	Services	Client A website redesign — balance	30,000	Awaiting payment

At a glance: 42,000 received, 9,680 spent, and a 30,000 balance to chase. Looking only at the bank, you'd think the month brought in a little over thirty thousand.

Three common mistakes

1. Mixing personal and business money. One card for everything makes month end painful. Use at least one account just for the business.
2. Recording income but not expenses. For tax and for knowing your real margin, expenses matter as much — small subscriptions and subcontractor fees add up.
3. Looking for receipts later. Photograph them the day you get them; it beats digging through bags and inboxes at month end.

Let Docu do the sorting

If you'd rather not type every line, NEXUPIRA's Docu reads a bank statement you upload (CSV) or photos of receipts, categorises the entries and returns an Excel income/expense sheet with category subtotals. Anything it can't read clearly is marked to confirm for you.

FAQ

Do freelancers need to keep books?

Formal bookkeeping requirements depend on your country, your status and your type of income — ask your tax office or an accountant. Whatever the rules, recording every payment and every deduction makes tax time easier and stops balances from slipping through.

Is this the same as accounting?

No. An income/expense sheet tracks cash in and out, which is enough for a one-person business or a small studio. A company's formal accounts are best left to an accountant.

Excel or a bookkeeping app?

Either works. What matters is fixed columns and a monthly routine. Excel is flexible and easy to hand to an accountant.