Proper Spreadsheets

Templates › Business

Small Business Bookkeeping

Made for sole traders keeping their own books for a tax return.

A simple set of books for a one-person business: record every payment received and every business cost, tag each one with a category, and see totals by category, totals by month and profit so far this year ready for the tax return. The summary carries a chart that redraws itself as you type.

Of the 29 columns, you fill in 17 and the other 12 work themselves out, and all 14 figures on the summary do too. There is room for 1,242 rows of your own.

£4.99one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

Already have a spreadsheet of your own? Check it for mistakes, free.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic. What the film shows, in order:
  1. A new row is typed into the Money Out tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the Year Summary tab change to include the new row.
  4. The chart, Where the money goes this year, redraws with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 1,242 rows. Open any tab to see its columns.

Money In

9 columns

Every payment received by the business. One row per payment. Ready for 400 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
Date ReceivedType a date.
Invoice / RefType any text.
CustomerType any text.
CategoryPick one: Sales: Services, Sales: Goods, Other Income, Bank Interest or Grants & Refunds.
Amount InType an amount in £.
Paid ByPick one: Bank Transfer, Card, Cash, Direct Debit, PayPal or Other.
NotesType any text.
Month NoFilled in automatically. The Monthly Summary tab uses this.Fills itself in.

Worked out from this row's Date Received.

The formulaIF(ISBLANK([date]), "", MONTH([date]))
YearFilled in automatically from the date.Fills itself in.

Worked out from this row's Date Received.

The formulaIF(ISBLANK([date]), "", YEAR([date]))

Money Out

9 columns

Every business cost paid out. One row per payment, with the category you will need at tax time. Ready for 800 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
Date PaidType a date.
SupplierType any text.
CategoryPick one: Materials & Stock, Subcontractors, Travel & Motor, Office & Admin, Marketing, Software & Subscriptions, Insurance, Professional Fees, Equipment or Other Costs.
Amount OutType an amount in £.
Paid ByPick one: Bank Transfer, Card, Cash, Direct Debit, PayPal or Other.
Receipt KeptTick once the receipt or invoice is filed.Tick it when it applies.
NotesType any text.
Month NoFilled in automatically. The Monthly Summary tab uses this.Fills itself in.

Worked out from this row's Date Paid.

The formulaIF(ISBLANK([date]), "", MONTH([date]))
YearFilled in automatically from the date.Fills itself in.

Worked out from this row's Date Paid.

The formulaIF(ISBLANK([date]), "", YEAR([date]))

Monthly Summary

6 columns

Money in, money out and profit for each month of the current calendar year. Nothing to type here. The figures come from the Money In and Money Out tabs. Ready for 12 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
Month NoType a number.
MonthType any text.
Money InFills itself in.

Worked out from this row's Month No, using Amount In, Month No and Year on Money In.

The formulaIF(ISBLANK([month_no]), "", SUMIFS([Money In!amount], [Money In!month_no], [month_no], [Money In!year], YEAR(TODAY())))
Money OutFills itself in.

Worked out from this row's Month No, using Amount Out, Month No and Year on Money Out.

The formulaIF(ISBLANK([month_no]), "", SUMIFS([Money Out!amount], [Money Out!month_no], [month_no], [Money Out!year], YEAR(TODAY())))
ProfitFills itself in.

Worked out from this row's Month No, using Amount In, Month No and Year on Money In and Amount Out, Month No and Year on Money Out.

The formulaIF(ISBLANK([month_no]), "", SUMIFS([Money In!amount], [Money In!month_no], [month_no], [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!month_no], [month_no], [Money Out!year], YEAR(TODAY())))
Costs as % of IncomeShows 0% in a month with no income recorded.Fills itself in.

Worked out from this row's Month No, using Amount Out, Month No and Year on Money Out and Amount In, Month No and Year on Money In.

The formulaIF(ISBLANK([month_no]), "", IFERROR(SUMIFS([Money Out!amount], [Money Out!month_no], [month_no], [Money Out!year], YEAR(TODAY())) / SUMIFS([Money In!amount], [Money In!month_no], [month_no], [Money In!year], YEAR(TODAY())), 0))

Category Totals

5 columns

This year's total for every income and cost category. Add a row if you start using a new category. Ready for 30 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
CategoryPick one: Sales: Services, Sales: Goods, Other Income, Bank Interest, Grants & Refunds, Materials & Stock, Subcontractors, Travel & Motor, Office & Admin, Marketing, Software & Subscriptions, Insurance, Professional Fees, Equipment or Other Costs.
Money In This YearFills itself in.

Worked out from this row's Category, using Amount In, Category and Year on Money In.

The formulaIF(ISBLANK([category]), "", SUMIFS([Money In!amount], [Money In!category], [category], [Money In!year], YEAR(TODAY())))
Money Out This YearFills itself in.

Worked out from this row's Category, using Amount Out, Category and Year on Money Out.

The formulaIF(ISBLANK([category]), "", SUMIFS([Money Out!amount], [Money Out!category], [category], [Money Out!year], YEAR(TODAY())))
Share of Total CostsFills itself in.

Worked out from this row's Category, using Amount Out, Category and Year on Money Out.

The formulaIF(ISBLANK([category]), "", IFERROR(SUMIFS([Money Out!amount], [Money Out!category], [category], [Money Out!year], YEAR(TODAY())) / SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY())), 0))
EntriesFills itself in.

Worked out from this row's Category, using Category and Year on Money In and Category and Year on Money Out.

The formulaIF(ISBLANK([category]), "", COUNTIFS([Money In!category], [category], [Money In!year], YEAR(TODAY())) + COUNTIFS([Money Out!category], [category], [Money Out!year], YEAR(TODAY())))

Year Summary

14 figures

The headline figures for this calendar year, ready for the tax return.

FigureWorked out as
Total money in this year

Worked out from Amount In and Year on Money In.

The formulaSUMIFS([Money In!amount], [Money In!year], YEAR(TODAY()))
Total money out this year

Worked out from Amount Out and Year on Money Out.

The formulaSUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY()))
Profit so far this yearMoney in less money out, for the current calendar year.

Worked out from Amount In and Year on Money In and Amount Out and Year on Money Out.

The formulaSUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY()))
Money in this month

Worked out from Amount In, Month No and Year on Money In.

The formulaSUMIFS([Money In!amount], [Money In!month_no], MONTH(TODAY()), [Money In!year], YEAR(TODAY()))
Money out this month

Worked out from Amount Out, Month No and Year on Money Out.

The formulaSUMIFS([Money Out!amount], [Money Out!month_no], MONTH(TODAY()), [Money Out!year], YEAR(TODAY()))
Profit this month

Worked out from Amount In, Month No and Year on Money In and Amount Out, Month No and Year on Money Out.

The formulaSUMIFS([Money In!amount], [Money In!month_no], MONTH(TODAY()), [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!month_no], MONTH(TODAY()), [Money Out!year], YEAR(TODAY()))
Average profit per month so far

Worked out from Amount In and Year on Money In and Amount Out and Year on Money Out.

The formulaIFERROR((SUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY()))) / MONTH(TODAY()), 0)
Costs as a share of income

Worked out from Amount Out and Year on Money Out and Amount In and Year on Money In.

The formulaIFERROR(SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY())) / SUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())), 0)
Biggest cost category

Worked out from Category and Money Out This Year on Category Totals.

The formulaIFERROR(INDEX([Category Totals!category], MATCH(MAX([Category Totals!money_out]), [Category Totals!money_out], 0)), "")
Spend in that category

The largest Money Out This Year on Category Totals.

The formulaIFERROR(MAX([Category Totals!money_out]), 0)
Suggested tax set-aside (20% of profit)A rough cushion only. Your actual tax and National Insurance will differ.

Worked out from Amount In and Year on Money In and Amount Out and Year on Money Out.

The formulaROUND(MAX(SUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY())), 0) * 0.2, 2)
Payments in recorded this year

Worked out from Year on Money In.

The formulaCOUNTIFS([Money In!year], YEAR(TODAY()))
Payments out recorded this year

Worked out from Year on Money Out.

The formulaCOUNTIFS([Money Out!year], YEAR(TODAY()))
Costs still missing a receiptTick the Receipt Kept box on the Money Out tab once each one is filed.

Worked out from Amount Out and Receipt Kept on Money Out.

The formulaCOUNTIFS([Money Out!amount], ">0", [Money Out!receipt_held], FALSE)