If you're a Shopify seller based in the UK looking for a free Shopify seller bookkeeping spreadsheet then you're in luck... You can download our Shopify accounting spreadsheet for free and use this article to walk you through it.
DISCLAIMER*
We are not Tax Advisors and so our advice and suggestions on the application of tax rules cannot be construed as tax advice. We highly recommend that users seek advice from qualified accountants for their tax compliance.
Who should use this spreadsheet?
As we discussed in our article on the best Shopify seller accounting software, it's important to know if using a spreadsheet is right for your Shopify business.
Essentially if you are just starting out or you are still a relatively small business then using a spreadsheet could be perfect for you.
If you're VAT registered then you probably shouldn't be using a spreadsheet for your bookkeeping because HMRC now requires all VAT returns to be submitted through Making Tax Digital approved software such as Xero or QuickBooks.
👉 Download our Shopify seller accounting spreadsheet here
How to use the spreadsheet
The file contains 3 sheets. Transactions, VAT Rates and Transaction Types.
Step 1 - Confirm the Transaction Types
By default the following transaction types are in this sheet:
- Shopify Sales
- Shopify Refunds
- Shopify Fees
It's up to you to decide how you want to account for your Shopify revenues but a clean approach would be to use these defaults. You could of course add more such as Shopify Shipping and Shopify Shipping Refunds or break down your fees into individual fee types.
👉 If you add any transaction types be sure to update the conditional formatting dropdown used on the main Transactions sheet.
Step 2 - Confirm VAT rates (if VAT registered)
By default the following VAT rates are in this sheet:
- 20%
- 5%
- 0%
- Reverse Charged VAT
Typically you would use the 20% rate for the sale of standard goods. The 5% rate would be for the sale of reduced rated goods, the 0% rate would be for the sale of zero rated goods and the Reverse Charged VAT rate would be for Shopify fees.
Again if you need other VAT rates you can add them, just be sure to update the conditional formatting dropdown so that you can select them on the main transactions sheet.
Step 3 - Adding transactions
You could either add individual orders to this sheet or you could enter summaries for each Shopify Payments payout. The latter is what most sellers choose to do and then they keep a copy of the payout report just in case they need it should HMRC request to see the individual transaction level data. We will focus on entering payout level data.
On the main transactions sheet there are 7 columns:
- Date - In this column, you will add the date of the payout
- Description - Enter a brief description such as "Sales shipped to UK"
- Type - Select from the dropdown the transaction type
- Gross Amount - Enter the gross amount for the transaction
- VAT Rate - Select from the dropdown the VAT rate
- Net Amount - This field is calculated automatically
- VAT Amount - This field is calculated automatically
👉 Top tip - if you enter sales as a positive number and refunds and fees as a negative number then you will be able to easily sum up the values later.
Where to get the figures to populate the spreadsheet
You will need to download the Shopify Payouts report and do a bit of filtering to gather all of the information you need to fill out this spreadsheet. Check out our article on which Shopify reports to use for seller bookkeeping for more information.
How to find out your profits
Once you have filled in the spreadsheet for a period of time, say a financial year, then you may want to work out your profits for that period.
Since this spreadsheet does not take into account inventory purchases or stock firstly you would work out how much stock you sold during the period (your cost of goods sold).
To do that take a snapshot of your opening stock balance, add any stock purchases and then remove your closing stock balance. You should be left with the value of the stock you physically sold during this period.
Once you have your cost of goods sold figure then you can add that as the final line in the transactions sheet (as a negative value since it is a cost to your business).
Then just sum up the Net Amount column to see your bottom line net profits.
Alternatives to using a spreadsheet
As you can see, even a simple enough spreadsheet becomes quite a lot of work when you consider the whole task in hand.
Most Shopify sellers decide that this task is better to either outsource or automate.
Our advice would be this...if you're spending more than 10 minutes per month on your Shopify accounting, then stop wasting your time. Spend your time on tasks within your business that grow it not ones that manage it.
Using something like Link My Books with Xero would enable you to automate the whole process and have you spending just a few minutes each month on your bookkeeping.
To start a free trial of Link My Books click here.
.png)