An easy to use bookkeeping spreadsheet that shows you how much profit you are making, the tax you need to pay and generates the numbers you’ll need to complete your self-assessment tax return.
- Track how much profit you are making each month;
- Log your mileage and cash expenses to reduce your tax bill;
- Check how much tax you owe;
- Raise invoices and easily see who owes you money.
Who’s this Free Bookkeeping Spreadsheet for?
This free bookkeeping spreadsheet is designed for self-employed individuals who are:
- Self-employed with a business turnover of £85,000 or less;
- Not VAT registered;
- Need a helping hand to get their finances up and running the right way;
- Ready to start budgeting for their tax bill and payments on account;
- Discover valuable insights about your business.
Only basic excel skills required
I’ve designed this free bookkeeping spreadsheet for self-employed individuals to help you:
- Stay on top of your finances;
- Be clear on your weekly, monthly and annual bookkeeping & tax responsibilities;
- Make you aware of your tax bill and when you need to pay it;
- Analyse your earnings and expenses;
- Help make tax time as simple as possible.
The spreadsheet is designed to be used for 12 months of activity at a time.
It works best if you fill it out from 1 April each year as this will match the tax year for which you will need to complete your self-assessment tax return.
Start by heading over the Business Details tab and fill out ‘Your Business Details’ and ‘Your Personal Details’.
These will be really handy when completing your HMRC personal tax return, so complete all the boxes now to make things easier later on.
Your Business Details
Complete the general details about your business, along with a brief description of what you do.
You will need to set your accounting period start date. If you are newly self-employed, then just use the date that you became self-employed.
Or if you have filed a tax return previously, then check the dates that were reported in your last tax return.
There is a box for your opening business bank account balance. This is the opening balance in your business bank account at your accounting period start date.
HMRC guidance states that keeping your business and personal banking separate, so if you haven’t opened a business bank account then consider doing so.
Not just is it preferred by HMRC but it will make managing your business finances much much easier.
If you do not have a business bank account, then just leave this blank.
Your Business Income & Expenses
Your business profits are made up of income and expenses. I have listed out the most likely types of income and expenses that will affect you when you are self-employed.
If you have other income and expense types that you feel do not fall into any of the categories, then these can be added in the rows highlighted in grey.
Your Personal Details
Fill out each of the boxes, these will need to be included when you complete your self-assessment tax return.
If you are missing anything here such as your NI number or UTR number then request them now. It will avoid stress when you need to file your tax return.
The amount of tax you will pay on your blogging earnings is based on ALL your earnings.
So in order to make an accurate estimate of the tax you need to pay on your self-employment earnings you are going to need to enter details of all your other income and any tax deducted.
For example, if you are employed and self-employed then you’ll need to enter your gross earnings and tax deducted at source (excluding National Insurance).
If you are unsure how much tax you pay across the year then make a reasonable estimate. For example by taking your last payslip and multiply the amount of tax you pay in one month by 12.
Ideally on a weekly basis, enter your business bank account transactions, either from your business account or by analysing your personal account, into this tab.
Enter the following details:
- Date of the transaction;
- Details of the transaction such as name or description of what you bought;
- Category this is selected from the drop-down list, which is generated from the business details tab;
- Amount of the transaction from the bank account. (Note: enter all amounts as a positive).
Depending on the number of transactions each week, you could choose to download your bank statement from your online bank account as a CSV or Excel file. Then just copy and paste the information into the bookkeeping spreadsheet.
There may be circumstances where you need to invoice a customer/client in order to receive payment.
Enter Your Payment Terms at the top. This is the amount of time you give your customers to pay you and are typically 7, 14 or 30 days.
Every time you raise an invoice enter it on this list to help you stay on top of your credit control and track your customer payments.
Then, I recommend each day you look at your bank account to monitor your payments in and out.
As your customers pay, mark it off with a ‘Y’ on this list.
Business mileage is an allowable expense, but one that is often not written down and easily forgotten.
If you do use your car for business travel then try and write it down as soon as possible or as part of your weekly bookkeeping.
Included within the bookkeeping spreadsheet is a tab called ‘Mileage Claim’ which contains all the information you need to log as per HMRC guidelines.
Start by entering the pence per mile you wish to claim as part of your business.
HMRC allows you to claim up to 45p on your first 10,000 business miles and 25p thereafter
Like business mileage, cash expenses are often forgotten
Included within the bookkeeping spreadsheet is a tab called ‘Cash Expenses’ where you can log all your cash expenses.
Don’t forget to keep your receipts somewhere safe or a scanned version online somewhere safe.
After you have entered all of the above information, the business results tab is automatically populated. It summarises all your self-employment income as well as your expenses from your bank account, those paid in cash and your business mileage.
Shortly after the end of each calendar month, take a look at how your business has performed.
Have you made a profit or a loss?
Were there unexpected costs or is one form of income that is going really well for you?
Reviewing your past performance will give you the information you need to shape what you do next.
The dashboard is a summary of your business results – your turnover, costs and profits.
There is also a total of your unpaid customer invoices – it is worth regularly reviewing this section so you can chase up any money owed to you.
Also included on this dashboard is an estimate of the tax you will need to pay and when to help you budget for your tax bill.
Remember everything you earn when you are self-employed is UNTAXED and it is your responsibility to declare your earnings & how much tax you need to pay to HMRC.
The figure for tax here is an estimate and your actual tax bill may differ once you claim for all the allowances you may be entitled to or record your actual income accurately.