sheetsmith

Templates › Business and freelance

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.

Tabs
5
Columns
29
Fill themselves in
26

£7one 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.

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.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

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.

Money In

Every payment received by the business. One row per payment. Room for 400 rows.

ColumnTypeWorked out as
Date Receiveddate
Invoice / Reftext
Customertext
Categorychoice (list)
Amount Incurrency
Paid Bychoice (list)
Notestext
Month NonumberIF(ISBLANK([date]), "", MONTH([date]))
YearnumberIF(ISBLANK([date]), "", YEAR([date]))

Money Out

Every business cost paid out. One row per payment, with the category you will need at tax time. Room for 800 rows.

ColumnTypeWorked out as
Date Paiddate
Suppliertext
Categorychoice (list)
Amount Outcurrency
Paid Bychoice (list)
Receipt Keptbool
Notestext
Month NonumberIF(ISBLANK([date]), "", MONTH([date]))
YearnumberIF(ISBLANK([date]), "", YEAR([date]))

Monthly Summary

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. Room for 12 rows.

ColumnTypeWorked out as
Month Nonumber
Monthtext
Money IncurrencyIF(ISBLANK([month_no]), "", SUMIFS([Money In!amount], [Money In!month_no], [month_no], [Money In!year], YEAR(TODAY())))
Money OutcurrencyIF(ISBLANK([month_no]), "", SUMIFS([Money Out!amount], [Money Out!month_no], [month_no], [Money Out!year], YEAR(TODAY())))
ProfitcurrencyIF(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 IncomepercentIF(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

This year's total for every income and cost category. Add a row if you start using a new category. Room for 30 rows.

ColumnTypeWorked out as
Categorychoice (list)
Money In This YearcurrencyIF(ISBLANK([category]), "", SUMIFS([Money In!amount], [Money In!category], [category], [Money In!year], YEAR(TODAY())))
Money Out This YearcurrencyIF(ISBLANK([category]), "", SUMIFS([Money Out!amount], [Money Out!category], [category], [Money Out!year], YEAR(TODAY())))
Share of Total CostspercentIF(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))
EntriesnumberIF(ISBLANK([category]), "", COUNTIFS([Money In!category], [category], [Money In!year], YEAR(TODAY())) + COUNTIFS([Money Out!category], [category], [Money Out!year], YEAR(TODAY())))

Year Summary

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

MeasureWorked out as
Total money in this yearSUMIFS([Money In!amount], [Money In!year], YEAR(TODAY()))
Total money out this yearSUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY()))
Profit so far this yearSUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())) - SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY()))
Money in this monthSUMIFS([Money In!amount], [Money In!month_no], MONTH(TODAY()), [Money In!year], YEAR(TODAY()))
Money out this monthSUMIFS([Money Out!amount], [Money Out!month_no], MONTH(TODAY()), [Money Out!year], YEAR(TODAY()))
Profit this monthSUMIFS([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 farIFERROR((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 incomeIFERROR(SUMIFS([Money Out!amount], [Money Out!year], YEAR(TODAY())) / SUMIFS([Money In!amount], [Money In!year], YEAR(TODAY())), 0)
Biggest cost categoryIFERROR(INDEX([Category Totals!category], MATCH(MAX([Category Totals!money_out]), [Category Totals!money_out], 0)), "")
Spend in that categoryIFERROR(MAX([Category Totals!money_out]), 0)
Suggested tax set-aside (20% of profit)ROUND(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 yearCOUNTIFS([Money In!year], YEAR(TODAY()))
Payments out recorded this yearCOUNTIFS([Money Out!year], YEAR(TODAY()))
Costs still missing a receiptCOUNTIFS([Money Out!amount], ">0", [Money Out!receipt_held], FALSE)