Shopify Bookkeeping 101

Shopify Bookkeeping 101


What should you be accounting for?

Sales & Refunds

If you’re VAT registered then you will need to be accounting for your sales revenue split out by destination and also by tax rate.

You should account for sales to customers in the UK separately to sales to customers outside the UK. This is because they incur different VAT rates.

Sales to the UK will typically incur 20% VAT, whereas sales outside the UK are classed as exports and are usually zero rated.

If you’re selling any products that are zero rated or reduced rated then those will also need to be separated out.

You should end up with the following groups of sales effectively:

Sales group

Typical VAT rate

Sales of standard rated products to the UK

20% VAT on Income

Sales of reduced rated products to the UK

5% VAT on Income

Sales of zero rated products to the UK

Zero Rated Income

Sales shipped outside the UK

Zero Rated Income

If you’re shipping goods outside of the UK then, although these will be zero rated for UK VAT purposes, you may also need to consider your tax liabilities for those countries too. Each country's rules may be slightly different, so we would strongly recommend speaking with an advisor in the country you are planning on shipping to. This might seem like a lot of headache if you are shipping all around the world, however, it is better to be safe than sorry. A good starting resource can be found here: https://www.quaderno.io/digital-tax-guides/tax-registration-filing-guides

This guide will only be focusing on the UK side of VAT compliance.

Fees

Shopify charges a monthly fee for using their platform plus payment processing fees and sometimes you’ll also be billed for other things like installed apps through Shopify too.

You’ll need to account for these fees and ensure that you apply the correct VAT rates too.

If your business is established in the UK then Shopify invoices its fees to you via their Shopify International Limited legal entity which is incorporated in Ireland.

Different rules apply depending on whether or not you have provided Shopify with your VAT number. The reason for this is that the place of supply for services and digital services are usually based on whether the supplier is working with a business (B2B) or consumer (B2C).

The general rule for supplies is they treat their end customer as a business if they have a VAT number and a consumer if they don’t.

This means that if you have not provided Shopify with your VAT number then they treat you as a “consumer” under VAT regulations, meaning any supplies of services to you must have VAT applied to them. However, if you are not VAT registered you won’t be able to reclaim this VAT. So in general If this is the case then the VAT rate you should use will be ‘no vat’

If however, you have provided Shopify with your VAT number then they will treat you as a “business” customer and as such will not apply any VAT to their fees and instead, they will apply the reverse charge rules.

What does Reverse Charge mean?

Good question. So when VAT is reverse charged it essentially means that the supplier does not charge any VAT, the customer does not pay any VAT but both parties account for it.

In Xero and QuickBooks there are specific tax rates for this type of expense:

  • Xero - Reverse Charge Expenses (20%)
  • QuickBooks - 20.0% RC SG (Reverse Charge Services 20%)

How can I double check the tax rate Shopify is charging me?

Inside your Shopify Admin you can check if you have previously entered your VAT number and if Shopify has verified it using the below steps:

  1. Navigate to your Billing page:
  2. From your Shopify admin, go to Settings > Billing.
  3. In the VAT number section, provide your VAT information:
  4. If you have a VAT number, then click Add VAT number, enter it in the space provided, and click Save.
  5. If you don't have a VAT number, then click I don't have a VAT number, and click Confirm. The VAT number is displayed as N/A.
  6. Click Save.

Shopify verifies your VAT number through the VAT Information Exchange System (VIES) and changes its status from pending to verified once the verification is complete.

You could also download one of your monthly bills which would show clearly if any VAT has been charged.

Cost of Goods Sold

The next piece of the puzzle is Cost of Goods Sold or COGS for short.

Until now you’ve accounted for your sales revenues and any fees that Shopify has charged you but in order to work out your gross profit, you also need to account for the cost to you of the items you sold.

Your gross profit is calculated as:

Total Revenue - Cost Of Goods Sold

In other words, your sales minus and costs that are directly incurred as part of those sales.

Your Shopify fees would be one such cost as without selling the item you would not incur the Shopify payment processing fee.

Cost of Goods Sold is the inventory cost side of things. For each item you sold you need to account for the purchase price of that item that you paid.

Now at this point it’s important to note that there are two main ways of accounting for COGS:

Option 1

As you purchase inventory, you account for it directly as a cost of goods sold.

The advantage of this method is it’s easy.

The disadvantage is that it will render your monthly profit and loss reports inaccurate. Why? Because you’re potentially accounting for the cost of a bulk quantity of inventory in one month but then selling it across multiple months, meaning your month to month P&L is basically useless.

Option 2

As you purchase inventory, you account for it in an inventory asset account which appears on your balance sheet rather than your profit and loss report. Then as you sell through inventory, you move the cost of that inventory from the inventory asset account to your cost of goods sold account on your P&L report.

The advantage of this method is that it produces a much more accurate monthly P&L.

The disadvantage is that it’s a bit more time consuming.

If you are looking for a more accurate monthly P&L then below we will outline the process for option 2.

Getting a more accurate monthly P&L

Step 1 - Account for inventory purchases to an inventory asset account

In order to keep your monthly P&L as accurate as possible you’ll need to be accounting for your inventory purchases to an inventory asset account.

The account should be set up as a current asset type account so that it appears in the correct section on your balance sheet.

As you account for inventory purchased to that account it’s value will increase reflecting that you have an increase in “assets”, in this case inventory.

Costs to include are:

  • Purchase price of the item
  • Packaging costs
  • Cost of shipping to your warehouse
  • Import duties paid

All of these items make up the landed cost of your inventory. The landed cost of an item is essentially the price you paid for the item from your supplier plus the cost to prepare that item for sale. All the costs associated with getting the item onto a shelf ready to sell effectively.

Step 2 - Account for inventory sold to a cost of goods sold account

You’ll need to work out how many units of each product you have sold each month and what the cost of those items were, that would give you your total COGS for the month.

To work out your cost per item you should sum up all of the costs we detailed above in step 1 and divide by the number of units you purchased.

For example if you purchased 1,000 units of an item and your costs were as follows:

Purchase price of 1,000 units £2,500

Packaging costs £100

Cost of shipping to your warehouse £300

Import duties paid £600

The total cost was £3,500 so per item that works out at £3.50 each.

Therefore your landed cost per item is £3.50 and this is the cost you’d use to work out your cost of goods sold each month.

The calculation would be:

Number of units sold x landed cost per unit = cost of good sold

As you can see this quickly can become quite a handful, separating out your sales revenues by destination and tax rate and working out your cost of goods sold.

To do all of this yourself manually and get it all right takes patience and a lot of time. That’s why most Shopify sellers use accounting integrations like Link My Books or work with e-commerce specialist accountants such as ecommerceaccountants.co.uk.

Which reports to use from Shopify

If you’re wanting to do all of this manually then you’ll need to know what data to use and where to get it from. Shopify does have some pretty good reports that we can use.

You can use the Shopify Sales by billing location report to get a snapshot of your sales for each month split by country.

Shopify sales by billing location report screenshot

The sales by billing location report contains the following columns which we will use:

  • Country
  • Total Sales

You will also need to grab your Shopify Fees figures which are available via the Payouts page on Shopify.

Export all your payouts for a month and then total up the Fees column.

Shopify sales export layout

What to do with these figures?

Now that you have the figures from the payout report you can begin to enter them into your accounting software or spreadsheet.


Sales

You will need to sum up your sales into the categories we discussed earlier:

Sales group

Typical VAT rate

Sales of standard rated products to the UK

20% VAT on Income

Sales of reduced rated products to the UK

5% VAT on Income

Sales of zero rated products to the UK

Zero Rated Income

Sales shipped outside the UK

Zero Rated Income

Fees

Total up the fees for each month using the Payouts file you exported.

If you have provided your VAT number to Shopify then Shopify is NOT charging VAT on their fees. More on that in this article Do You Pay VAT On Shopify Fees?

Alternatives to doing this manually?

As you may be seeing by now, doing this manually is going to take time and patience and even so it is going to be pretty susceptible to human error.

Most Shopify sellers decide that this task is something they don't want to spend much time on each month. So what are your options if you too decide this?

  1. Get your accountant/bookkeeper to do it manually
  2. Use software to automate it yourself
  3. Get Ecommerce Accountants to do it using software

Using an accountant or bookkeeper

If you use an accountant or bookkeeper then this will definitely save you some time. The downside is that they will charge you per hour for doing so. The likelihood is that it will take them just as much time as it would have taken you and this could easily end up costing you a few hundred pounds per month just for data entry.

Use software to automate it yourself

Using software would be most Shopify sellers' preferred choice. This is quick, accurate and cost effective. One such tool is Link My Books.

Link My Books hooks up directly to your Shopify account and accounting software such as Xero or QuickBooks and posts summaries of each of your Shopify payouts following the same format as we discussed above. It also handles all other payment gateways if you're making sales via PayPal, Klarna, etc. or via Shopify POS for example.

It's trusted by thousands of sellers and is rated 4.98 out of 5 on average across hundreds of reviews.

You can try Link My Books for free here.

Get Ecommerce Accountants to do it using software

Ecommerce Accountants partnered with Link My Books to bring you the best of both worlds. You get the knowledge and experience of Joe and his team at Ecommerce Accountants and then the automation super power that is Link My Books.

Link my Books and Ecommerce Accountants have been working together since November 2020 to help alleviate the headache of not just Shopify but Amazon, eBay and Etsy accounting too.

With Link My Books automating your Shopify bookkeeping and Ecommerce Accountants taking care of everything else you can sit back and relax knowing that your accounts will be spot on and efficient.

What accounting software should you use?

First up, we'll talk about how you can start off using basic spreadsheets.

Shopify seller accounting spreadsheet

This is a great place to start for Shopify sellers who are just getting started or who want a completely free option for their bookkeeping. We have put together a Free Shopify Seller Accounting Spreadsheet which will help you as a new Shopify seller to keep track of your sales, refunds and fees.

Of course, the spreadsheet doesn't pull the data from Shopify automatically like some of the software options we'll discuss later on, but if you are happy to manually download the required reports from Shopify and input the data yourself then it could be a good option.

When to move away from spreadsheets

A good rule of thumb is when you become VAT registered then it's probably time to ditch the spreadsheet and start using accounting software. The main reason for this is that to submit a VAT return to HMRC (the UK's VAT authority) you need to follow the Making Tax Digital or MTD rules.

MTD came about in 2020 and in a nutshell, it means that you have to submit your UK VAT returns via an approved software partner. The idea is that HMRC wants to be able to see the data behind the figures included on your VAT return to see that things add up essentially.

Once you're VAT registered, you should start to think about using a software provider to account for your Shopify sales.

You might also consider the switch if you find the manual process of downloading and processing the Shopify reports tedious. Most of the software options we will discuss later are pretty good value for money when you consider just how much time you will save plus they are often more accurate than doing it manually due to the lower risk of human error.

Shopify Seller Accounting Software Options

For Shopify sellers who want to streamline and manage their accounts in an effective way, there are a couple of well-renowned options available.

Xero

Accounting Software

Xero is one such example which has been getting great feedback from its users since it was first launched in 2006 by Australian Tim Lucas.

Xero has a great host of built in reports meaning you can manually enter your Shopify sales and fees and then produce profit and loss reports, balance sheets and the all important VAT Returns.

You still need to manually download and enter the Shopify sales information each month with Xero but if you use an integration app such as A2X or Link My Books then that process could be completely automated too. See the integration section below for more detail.

It's MTD compliant and you can submit your VAT returns straight to HMRC from inside Xero with the click of a button.

Pricing
Xero has 3 price plans available. Stater, Standard and Premium. Starter is not really suitable for most Shopify sellers as you are limited to the number of invoices and bills you can enter on it. Most Shopify sellers choose Standard with some choosing Premium, mainly those who need the added multi-currency features.

Starter - £12/month

Standard - £26/month

Premium - £33/month

All pricing is in GBP and excludes VAT.

QuickBooks Online

Accounting Software

Another established choice for Shopify sellers is QuickBooks which was launched in 1998 by Intuit Inc but now also offers its accounting services via the cloud with its QuickBooks Online product.

Similarly to Xero, QuickBooks Online is a full solution meaning that you enter all your income and expenses and it does all the hard work for you in producing reports and VAT returns.

Pricing

QuickBooks Online has 3 price plans available. Simple, Essentials and Plus. Unlike Xero QuickBooks Online pricing is mainly focused on additional users. You can only add additional users on the Essentials and Plus plans. Most Shopify sellers choose Essentials with some choosing Plus, mainly those who need the added stock features.

Simple - £12

Essentials - £20

Plus - £30

All pricing is in GBP and excludes VAT.

Shopify bookkeeping automation integrations

Assuming you now have Xero or QuickBooks, the next thing to consider is probably going to save you the most time overall. That is an integration app.

The two main players in this market are A2X Accounting and Link My Books.

These apps help you to automate your Shopify bookkeeping completely.

They hook up directly to your Shopify account and Xero or QuickBooks and form a connection between the two. But more than that, they help you to accurately account for your Shopify sales, refunds, fees and VAT completely on autopilot.

A2X Accounting Pricing

Mini - $19/month - Max 200 orders/month

Basic - $39/month - Max 500 orders/month

Pro - $69/month - Max 2,000 orders/month

Advanced - $99/month - Max 5,000 orders/month

Standard 10K - $149/month - Max 10,000 orders/month

All pricing is in USD and excludes VAT. Larger plans are also available.

Link My Books Pricing

Starter - £13/month - Max 200 orders/month

1K - £26/month - Max 1,000 orders/month

5K - £39/month - Max 5,000 orders/month

10K - £65/month - Max 10,000 orders/month

All pricing is in GBP and excludes VAT. Larger plans are also available.

Choosing between A2X and Link My Books

A2X and Link My Books are both very good at what they do. They both make your life as a Shopify seller much easier when it comes to automating your bookkeeping.

Link My Books is well renowned for having a super easy setup process with guided wizards holding your hand along the way. Being a UK based company with a great understanding of VAT they provide default account and VAT rate selections that you can simply approve which makes setting up a doddle. With A2X your tax mappings must be manually selected which can be tricky if you are not sure of what you are doing.

A2X is based in New Zealand whilst Link My Books is a British company so if you are keen on backing British businesses then this may sway your choice.

Ultimately whichever you choose you will end up saving a huge chunk of time on your Shopify bookkeeping each month. Plus by getting your VAT right you might even end up saving money too as your VAT liabilities could be reduced due to correctly separating out sales which don't incur VAT such as exports, zero rated goods and marketplace VAT responsible goods.

This is a common mistake that we see many sellers making before the move to using Link My Books.

How to reconcile payments into the bank from Shopify & other payment gateways

With Shopify merchants have the ability to accept payments via Shopify Payments and also through a whole host of other payment gateways too.

The good news is that Link My Books works for all payment gateways through one single connection to your Shopify account.

For sales paid for via Shopify Payments, Link My Books generates one settlement summary report for each payout you received from Shopify. This means that the invoices you send to Xero or QuickBooks from Link My Books will match exactly the amount Shopify deposited into your bank account. So these are nice and easy to reconcile directly on your bank reconciliation page in Xero or QuickBooks.

If you are accepting other payment gateways such as PayPal, Klarna and Clearpay via your Shopify store, Link My Books will generate a settlement summary report once per month for each gateway summarising the sales and refunds.

These monthly summaries will be cleared through a clearing account (one clearing account per payment gateway).

What is a clearing account?

A clearing account (sometimes referred to as a suspense or holding account) is an account where funds from payment providers that are in transit or as yet undeposited into your bank account are accounted for.

We use these clearing accounts to record the funds that are expected from each third-party payment gateway, which have yet to be deposited into your bank account.

How many clearing accounts do I need?

One for each payment gateway you accept payments through. Don't worry we create these in your chart of accounts for you automatically as they are needed so no action is required from your side.

How do I reconcile these payment gateway clearing accounts?

STEP 1: Send the Link My Books settlement summary report to Xero or QuickBooks. This will debit the clearing account with the net value of your sales minus any refunds via that payment gateway.

STEP 2: Allocate any deposits from the payment gateway to the relevant payment gateway clearing account. This will credit the clearing account.

(We name all payment gateway accounts clearly so it should be easy to identify which is which)

STEP 3: Download your monthly fees invoice from each payment gateway and account for those directly in Xero or QuickBooks separately.

👉 There can sometimes be a balance left in the clearing account at the end of each month, nothing to worry about - this is usually down to funds yet to be deposited into your bank account for sales that occurred during that month.

Full walkthrough of the process

Below is an example of a PayPal settlement summary report produced by Link My Books. We will use this example to walk through the entire process from start to finish.

Some payment processors have bank feeds into Xero or QuickBooks which can be utilised using Option 1 below, for those that don't (or if you'd prefer to skip the bank feeds) follow Option 2 below.

👉 Note that even though this example is for PayPal, the same general process applies to any other payment gateway too.

  • Option 1 – Using the PayPal bank feed (Xero only)
  • Option 2 – using a manual journal entry

Option 1 – Using the PayPal bank feed

If you’re using the PayPal bank feed with Xero already then this is the option for you.

Let’s look at an example for one month:

Unlike Shopify Payments, payouts from Shopify-PayPal will not have a matching deposit into your bank account.

This is because the funds from these sales go into your PayPal balance.

To counter this Link My Books allocates these payout balances to the Shopify Gateway Clearing account. So in our example, the £239.38 amount would be allocated to that account as below:

On the PayPal bank feed in Xero the corresponding payments would look like this:

And the PayPal fees like this:

We will allocate the PayPal deposit amounts to the Shopify Gateway Clearing account and PayPal fee line items should be allocated directly to an account you use for PayPal payment processing fees:

This will clear off the 239.38 amount Link My Books allocated to the same account via the settlement invoice, which you can validate by running an Account Transactions report for the Shopify Gateway Clearing account:

Next, you should read our help article on Using bank rules in Xero to speed up Shopify-PayPal reconciling

Option 2 – Using a manual journal entry

The second option is if you are not using the PayPal bank feed. In this section, we will show you how to reconcile your Shopify-PayPal settlement invoices generated by Link My Books with the PayPal Activity Download report, as well as how to account for your PayPal fees.

Link My Books generates Shopify-PayPal settlements based on the information available from Shopify. Since PayPal does not pass the information about their fees to Shopify, we are unable to include PayPal fees directly in the settlements either.

Contents of this section

  • Step 1 – On PayPal, download the report of your activity for the month
  • Step 2 – Confirm the Link My Books report matches PayPal
  • Step 3 – Allocate PayPal deposits to the clearing account
  • Step 4 – Account for your PayPal fees

Step 1 – On PayPal, download the report of your activity for the month

Log in to your PayPal account and navigate to:

Activity > Reports > All Reports > Activity download

👉 Important - Once you download the CSV file from PayPal you will need to filter the column “Custom Number” to show only “Shopify” transactions.

The columns we are otherwise interested in are the Gross, Fee and Net columns, so sum those up to show the totals at the bottom like this:

Step 2 – Confirm the Link My Books report matches PayPal

Since Link My Books generates one Shopify-PayPal settlement report per month, all we need to do next is confirm that the figure shown in the PayPal report matches the value of the settlement in Link My Books for that month.

In our example the £49.95 amount shown on Link My Books matches the 49.95 value in the PayPal data:

Step 3 – Allocate PayPal deposits to the clearing account

When you post a Shopify-PayPal settlement to your accounting platform, Link My Books adds a line to allocate the total amount to the Shopify Gateway Clearing account.

When you withdraw funds from PayPal, those deposits should be allocated to the Shopify Gateway Clearing account (assuming that you are only using the PayPal account for Shopify and nothing else).

At the end of the month, the balance of the Shopify Gateway Clearing account should reflect your total PayPal fees.

In our example, £49.95 was allocated to the Shopify Gateway Clearing account for the invoice generated by Link My Books and £47.00 from deposits into the bank from PayPal, leaving £2.95 – which is the amount of the PayPal fees.

Step 4 – Account for your PayPal fees

Now you need to account for the PayPal Fees and balance off the Shopify Gateway Clearing account, luckily you do that in one entry in your accounting software.

In Xero, this can be achieved via a Bill entry:

In QuickBooks, this can be achieved via a Journal entry:

Once you have done this your Shopify Gateway Clearing account balance should be showing as £0.00.

Outsource to a professional

As you can hopefully see from this guide, setting up your bookkeeping system can be fairly technical and there are a lot of considerations to think about. If you’re a business owner you might be learning all of this content and only using it once. If you would prefer to leverage your time then we would recommend outsourcing this to a professional.

If you are a Shopify seller and would prefer to outsource the set-up and maintenance of Link My Books, alongside your bookkeeping solution, then we would strongly recommend speaking with Ecommerce Accountants.

Ecommerce Accountants are the leading accountancy in the UK for ecommerce businesses and we have been working closely alongside their friendly team for a number of years. Because of this, we believe that they understand Link My Books inside out and should be the go-to people for handling your bookkeeping and accounting needs.

NB: If you do get in touch with Ecommerce Accountants, please let them know that Dan sent you. Joe (the founder of Ecommerce Accountants) owes me a bottle of Dom Perrignon if more than 10 clients read this and get in touch with him off the back of it.

About the author

Dan Little
Dan Little
Dan Little - Co-Founder & CEO of Link My Books. When he discovered how frustrating bookkeeping can be when selling on Amazon he sought to create a solution.
View more posts →
Woman Typing on Laptop

Is manual bookkeeping stressing you out?

Are you wasting time calculating sales, fees, VAT, and taxes? Run your business more efficiently by automating your Amazon bookkeeping to Xero or Quickbooks.

Latest Amazon and bookkeeping news straight to your inbox!

Email Icon
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No spam. Unsubscribe anytime.
More Posts

You Might Also Like

View All Posts
WANT TO TALK TO AN EXPERT BEFORE GETTING STARTED?