By the Books: Step-by-Step Guide to Shopify and Xero Bookkeeping with A2X
Whether you’re getting your Shopify store off the ground or have tens of thousands of happy customers, you know that accurate Shopify bookkeeping is an important (sometimes frustrating) part of running a successful business. In addition to compliance and setting yourself up for tax success, it gives your business the financial visibility it needs to make smart decisions.
But, like most great meals, you need the right ingredients to get your Shopify bookkeeping in a good place every month—without hours of admin. From technology to process, in-house resources to outsourced accounting support, and so much more, getting your recipe right for your stage of growth is critically important to unlocking financial success.
Learn from the experts with ‘By the Books’
To help you on your journey to accurate ecommerce accounting, A2X has created the By the Book series. This series offers step-by-step guides created in partnership with expert ecommerce accountants or bookkeepers to help you accurately account for your Shopify, Amazon, eBay, Etsy, Walmart, and BigCommerce sales in QuickBooks or Xero using A2X. This series outlines processes, provides tips and tricks, and so much more—all to help you save time, money, and achieve true financial visibility.
In this episode of the By the Books series, we’ve partnered with Bean Ninjas, A2Xpert accounting and bookkeeping partners, on a step-by-step guide to Shopify and Xero bookkeeping with A2X. This guide is the process that Bean Ninjas’ accountants and bookkeepers use when working with their clients—primarily 7-figure ecommerce businesses. It has also been reviewed by A2X’s expert in-house support team, making it thoroughly expert-approved.
If you’re not working with an expert ecommerce accountant or bookkeeper you can find Bean Ninjas and others on the A2X Directory.
Why is accurate bookkeeping for my Shopify business important?
If you already know the answer to this question, feel free to skip this section. But read on if you’re looking at your Shopify dashboard, reviewing your sales, and wondering why you need accurate bookkeeping in addition to this data.
Most channels and marketplaces, like Shopify, will give you a high-level overview of your sales. But that overview lacks critical context and doesn’t tell you how your business is actually performing. To truly know your numbers and set yourself up for cashflow success, you need to account for sales, expenses, loans, payables, inventory, COGS, etc. $1,000,000 in sales might sound amazing, but not if it’s costing you $975,000, and especially if you don’t even know it.
So, while it’s important to have tidy books to make it easy to file taxes without overstating or understating your income, if you only use them for this purpose, you’re missing out on a multitude of other benefits.
For example, when you accurately account for your Shopify store in Xero using A2X, you’ll receive a comprehensive Profit & Loss Statement (P&L) that itemizes your revenue, COGS, operating expenses, and other relevant information. This report allows you to compare different periods and gain insight into the flow of your finances, enabling you to ask tough questions and address any challenges that arise. For instance, you may need to investigate if your advertising expenses are too high, why your COGS have increased, whether you should raise your prices, or if your business systems are cost-effective.
See this example P&L for a multichannel seller on Shopify, Amazon, and eBay:
The step-by-step guide to monthly Shopify bookkeeping with Xero and A2X
Monthly bookkeeping follows a standard process that requires access to certain documents and recording of information, such as sales, income, expenses, etc.
Before we jump into the high-level review of transactions, you need to have completed a few steps first. Here we’ll briefly outline what you need before you can start a month-end process before we dig in deeper. You can also access our Ecommerce Bookkeeping Checklist as a Google Sheet.
Items or access needed before starting
Whether you’re doing this yourself, working with an in-house team, or a member of an outsourced practice, you need access to a range of documents to accurately complete the month-end process. The following list is an example of what you may need for Shopify bookkeeping, though you may need different documents depending on your situation:
- Monthly Bank and/or credit card statements
- Shopify Finance summary
- Loan transactions, if any
- Merchant account statements (e.g. Sezzle or Klarna)
- Bills and/or receipts, if necessary
This is the first step of the month-end close process. This is when you start using A2X and Xero to review and record transactions.
- Record sales and income (do this by sending sales/income from A2X to Xero)
- Record bills or expenses
- Allocate / classify bank transactions
- Start bank reconciliation
- Recognize any gift cards issued or retired (if using Shopify gift cards)
If you’ve never used A2X before, take advantage of the A2X certification course. This self-paced course will teach you what you need to know about using A2X.
Update COGS / Inventory accounts
After reviewing transactions, next move on to updating COGS or inventory accounts.
- Record COGS (send COGS information to Xero from A2X, if this is set up)
- Review month-end inventory balance (if your A2X plan supports inventory, you can get the inventory report from A2X for Amazon, if inventory is being held in a FBA facility)
- Adjust inventory Shrinkage/Losses determined as of the period end.
Working paper reconciliations
Now that these tasks have been completed we can start with our high level review:
High-level review of sales
In Shopify, go to Analytics → Reports → Finances summary and verify the figures for Sales, Sales Returns, Sales Discounts and Shipping Income using the data in the Finance Summary Report in Shopify.
Confirmation of A2X sales invoices
If there are bank deposits that cannot be matched with any A2X invoices, check the Payouts section of Shopify to see if there are payouts that have not been fetched by A2X. If there are, contact A2X support to fetch the payout.
Review Order Edit orders
Order Edit transactions occur when changes are made in the Order timeline after the original payment has been processed. These are shown in A2X as Order Edit Payouts. They are mainly a correcting or adjusting entry to account for these changes so that the accounting flow is correct at the end of each period.
Order edits are generally reversed by A2X to the gateway when it was first recorded into a Pending Payment account.
These are orders that have been manually created in Shopify rather than created through the online store (or another sales channel integration within Shopify).
Manual Orders can sometimes be related to the Wholesale order transactions, particularly if the seller does not have a dedicated Shopify wholesale front-end store. They could also be payment for influencers, or sample orders.
Most often, Manual orders are manually marked as paid in Shopify. This means that the payment was received outside of the Shopify payment platform or another payment network, for example, the payment was made through check or wire transfer. These transactions should be traced and investigated further.
Cost of Goods Sold (COGS)
If the COGS feature of A2X is being utilized, you will need access to the unit costs and can then manually add or import the cost prices of all the listed SKUs in their Cost page.
Sometimes, this information is available in Shopify. In that situation, refer to the data in the Product list (or by searching the individual SKU for the unit cost) and export the data.
Cost of Goods Sold data
If you or a client want to use the COGS data in Shopify instead of using A2X bills, retrieve the figure from the Cost of Goods Sold report in Shopify and record it as a bill in Xero.
Note: This data should be reviewed monthly as products can be added to Shopify without the information for COGS, for example, if a seller is tracking cost prices elsewhere. A monthly review will ensure this does not go undetected for long.
Review Return Quantities
Return quantities are not fetched by A2X so this figure must be manually recorded. To get the data for the return each month, head to Shopify and go to Analytics → Reports and find the product orders and returns report and record this as an adjustment in Xero.
Note: Refund transactions do not necessarily mean an item has been returned. Therefore, it’s not guaranteed that these items will be made available for sale or added back to inventory. To ensure correctness, the warehouse agent should confirm any returns as stock, then they can then be reversed against COGS and inventory.
Sending of products to influencers
Tag these in Shopify to track and record as Advertising cost rather than a COGSBalance sheet
Reconcile transactions in Other Payment Gateway
Other Payment Gateways mean there is another payment method available at checkout, for example, PayPal. These gateways operate outside of the Shopify Payment network and usually charge a separate processing fee unknown by A2X.
A2X recommends sending these transactions into each of their clearing accounts for easier reconciliation and then confirming that all entries are in each clearing account as noted. If the fees are not accounted for in a different way, enter a journal entry for the monthly fees. Also confirm that the clearing accounts carry forward balances.
Verify whether all payments going to these gateways have been completely captured. To do this go to the Finance Summary Report and refer to the Payments section.
Review Gift Card Liability Balance
Gift cards issued, retired and/or devalued are not captured in A2X as they are not part of the sales/payments process. They need to be manually entered in Xero to reflect the proper balance of your gift card liability account. You can download your gift card balance report in Shopify or in the Reports section of your A2X for Shopify account.
Review pending payment
Sometimes the transaction to pay for an order or refund will appear in a different payout than the order itself.
For example, if an order is paid for after it was fulfilled, or if an order was made through one gateway but paid through another.
In these cases, A2X will add Pending Payment lines to a payout to account for money that is still owed for an order. These should be reviewed so that the expected balances are allocated to the correct clearing accounts and there is no balance in the Pending Payment account.
After reviewing the balance sheet, there may be other things you need to review. These steps are not always applicable, so skip them if they do not apply to your situation.
Chargebacks or disputes occur when a cardholder has an issue with a payment made to your store. The cardholder’s bank will inquire about it and request you refund the full amount.
Chargebacks or Disputes are fetched by A2X as Dispute within the Other category, which we recommend recording to a current liability account.
To reconcile this account and check the status of the Chargebacks, do this by going to the Order section in Shopify and filter by all chargebacks. Lost Chargebacks are adjusted in Shopify Refunds, Open and Submitted chargebacks stay in the liability account and Successful chargebacks usually have the reversing entry in the liability account.
Currency Conversion Gain or Loss
This is only applicable if you are doing the books for a store that deals with multi-currency transactions.
Just like any other cloud software, Xero has its own system accounts, also known as control accounts. These accounts are locked by Xero and therefore do not allow direct postings. Xero’s default currency conversion or bank revaluation accounts are examples of system or control accounts.
Sometimes there may be a need to post a currency conversion amount, especially if a client uses multi-currency in their Xero account. In these cases, it is best to map the currency exchange transactions into a separate expense account for currency exchange differences. Creating a separate expense account for currency exchanges means that it is easier for the accountant to clear it and reconcile at year-end.
Note: When you make a new account in Xero, return to A2X and go to Settings → Connections and click Refresh Cache. Then A2X will see the new account and you can go to your accounts and taxes pages to map those transactions.
Prepare financial statements
After reviewing everything, it’s now time for the final parts of the month-end close process: generating your financial statements in Xero. You should:
- Review and finalize Profit and Loss statement
- Review and finalize Balance Sheet
And that’s it, the Shopify bookkeeping month-end process is complete.
Overwhelmed? The experts can help
As mentioned above, these monthly financial statements are not just about staying compliant; they also contain essential information to help make financial decisions for your business.
If you think you’re missing out on crucial insights, speak with an expert ecommerce accountant, they’ll be able to analyze your statements and assist in planning your next growth phase. Bean Ninjas—who work with clients using both Xero and QuickBooks—can be found on the A2X Directory, along with many other ecommerce accounting experts.
Want to feel completely confident in your ecommerce bookkeeping?
Businesses that document their processes grow faster and make more profit. Download our free checklist to get all of the essential ecommerce bookkeeping processes you need every week, month, quarter, and year.Download it here