Shopify Bookkeeping in QuickBooks Online: Step-by-Step Guide
Struggling to reconcile your Shopify store in QuickBooks Online?
You’re not alone! Bookkeeping for Shopify is complicated.
But, it’s important to do it correctly so that you can get accurate financials, which will help to ensure you’ve received all your payouts, enable you to make better business decisions, and even put your business in a better position to get investment or a loan.
Read on for a step-by-step guide for how to do Shopify bookkeeping in QuickBooks Online.
Key takeaways
- Shopify bookkeeping is complicated, but it’s important to do it correctly so you get accurate numbers for your store (and avoid an accounting nightmare).
- Know what reports and tools you need to have on hand to complete the monthly bookkeeping process.
- Download our free checklist and watch our webinar (linked below) to get the tools you need to build your monthly Shopify-QBO bookkeeping process.
Special thanks to Brittany Brown, CEO of LedgerGurus, for providing the subject matter expertise in this article. LedgerGurus is an A2X Gold Partner.
Table of Contents
Integrate Shopify and QuickBooks Online for accurate accounting
A2X auto-categorizes your Shopify sales, fees, taxes, and more into accurate summaries that make reconciliation in QuickBooks Online a breeze.
Try A2X today
Common hurdles with Shopify bookkeeping
Before we dive into the steps, it’s important to understand some of the challenges you might run into when closing your books for Shopify.
Anatomy of a Shopify payout
Shopify payouts are not just “sales”. They’re actually a combination of sales, fees, refunds, gift cards, and other transactions.
So, coding the entire payout amount to a “sales” or “income” account is actually incorrect. Doing so might cause you to over or under-report on revenue, sales tax, and expenses.
Fortunately, distinguishing what types of transactions make up a payout is exactly what A2X was designed to do. Learn how to integrate Shopify and QuickBooks Online with A2X.
Payout periods
Shopify payouts may not align perfectly with monthly bookkeeping periods. This is important to note for Shopify bookkeeping because it means that the timing of when sales are made and when funds are actually deposited can vary.
If there’s a mismatch between tracking revenue and tracking COGS, it will skew profit margins, which can impact business decision-making.
Payment gateways
Using payment gateways – such as Sezzle, Klarna, Afterpay, etc. – is a great way for Shopify merchants to provide different payment options and improve their store experience.
However, payment gateways add complexity to Shopify bookkeeping due to their varied processing fees and different transaction timelines, which can make it challenging to reconcile accounts accurately.
Each gateway might have its own unique way of reporting sales, refunds, and fees, requiring careful attention to detail to ensure correct financial recording in QuickBooks.
Source: LedgerGurus
Getting started with Shopify bookkeeping
Looking for someone to walk you through how to do Shopify bookkeeping?
Watch this webinar recording featuring Brittany Brown, CEO of LedgerGurus.
Make your Shopify-QBO bookkeeping process your own – download our free Shopify QuickBooks Online Bookkeeping Checklist. You’ll get access to a Google spreadsheet that you can download or copy, then modify according to your unique processes.
What you need to complete monthly Shopify bookkeeping
Before you dive in, make sure you have the following items, access, tools, etc.
Access to Shopify store, A2X, QuickBooks Online | A2X is an ecommerce accounting automation app designed to get Shopify transaction data into QuickBooks Online accurately and automatically. Sign up for a free trial here. |
Monthly bank and/or credit card statements | To be used for reconciling bank transactions in QuickBooks Online to confirm that closing balances match once you’ve completed the monthly bookkeeping process. |
Shopify Finance Summary | A2X will get the Shopify data you need to reconcile transactions in QBO, but the Shopify Finance Summary can help you better understand any discrepancies that might have occurred. |
Loan transactions (if applicable) | If you’re borrowing money through PayPal, Shopify Pay, Amazon Pay, etc., you might have loans that are factoring into the deposits you receive from these payment gateways and will need to take this into consideration when bookkeeping. |
Merchant account statements (e.g., Sezzle or Klarna) | These statements detail the transactions processed through specific payment gateways or financing options, such as Sezzle or Klarna. They're key to tracking fees, refunds, and payouts related to these services. |
Pending payments status | Visibility into pending payments status from payment processors is essential for reconciliation (and ultimately, understanding the timing of cash flows). Knowing the status of pending payments helps in accurate revenue recognition and cash management. |
Bills and/or receipts | For recording expenses in QBO and for understanding COGS. |
Logins to additional apps | Access to any additional Shopify apps (e.g., return apps) is necessary for tracking transactions related to returns, discounts, or other special cases that might impact financial records. |
Product cost catalog | A summary of all the SKUs you have on hand, and the costs associated with each. Important for accurately calculating the Cost of Goods Sold (COGS). |
Units sold by SKU | Essential for COGS calculation and inventory management. |
List of manual orders | Manual orders are sales orders that might not be automatically captured by Shopify's system. This list will help capture the details you need for accurate records. |
Step-by-step guide: How to complete Shopify bookkeeping in QuickBooks Online
1. Send sales/income/other transactions from A2X to QuickBooks Online (QBO)
If you see a deposit in QuickBooks Online from Shopify: this amount is not just “sales”, and should not be coded to a “sales” or “income” account.
This is where A2X can help. A2X will automatically code the transactions that make up each Shopify deposit to the appropriate account in QuickBooks.
Shopify Payments
Any payout labeled as “Shopify Payments” should match perfectly with A2X.
Once you’ve set up your A2X account, go to the ‘Payouts’ tab. Click ‘Review’ beside the payout you wish to reconcile.
Review how A2X has coded the payout, then click ‘Send to QuickBooks’.
Go back to the bank feed in QuickBooks. You’ll now see that the payout has a corresponding journal entry (with a settlement report attached).
Click ‘Match’ to reconcile.
Watch this video to better understand how to connect A2X to Shopify and QuickBooks, and then how to send Shopify transactions from A2X to QuickBooks.
Other payment gateways
Payments from other payment gateways (Afterpay, PayPal, Klarna, etc.) will need to be treated slightly differently than Shopify Payments, since A2X cannot fetch fees that are charged by the payment processor.
You can follow the process outlined above to post deposits from other payment gateways to a clearing account in QuickBooks. Entries from A2X will typically appear as a debit in the clearing account.
When the payment gateway deposit comes through to QuickBooks, it will be posted as a credit in the bank account. You will then need to transfer it to the clearing account (note: you can set up a rule in QuickBooks to make this happen automatically).
Any remaining balances (likely payment processor fees) will then need to be entered manually into QuickBooks to balance the account.
2. Record bills and/or expenses
Recording bills and expenses involves coding all transactions that appear in your bank and credit card accounts, regardless of whether they are related to Shopify sales or ecommerce activities.
During this step, it’s also important to account for accrual-based activities, ensuring that bills which are tracked and subsequently paid are accurately reflected in your financial records.
3. Allocate/classify bank transactions
Bank accounts and credit cards are called “source accounts”, and they’ll make up 95% of what’s happening financially in your business.
Make sure any remaining transactions have been captured and coded correctly.
4. Start bank reconciliation
Bank reconciliation is incredibly important as it ensures that all financial activity is captured, and nothing is duplicated or missed.
Use the QBO bank feed to match transactions. Check that your balances are correct using any relevant monthly bank/credit card statements.
5. Review/update product costs
To get a complete financial picture, it’s incredibly important to engage in the cost accounting element of your Shopify store – but keep in mind that this part of the process can be incredibly difficult, so try not to be a perfectionist.
You need to have some sort of resource to track your product costs, whether it’s a spreadsheet or an inventory management system. Review it and make sure that your product costs are correct.
Then, upload the latest version of your product costs to A2X to help calculate COGS.
Note: A2X’s COGS feature is a static system, which means that the costs that are initially inputted will be used until they are updated. Keep this in mind if your product costs change frequently (e.g., if you experience regular cost changes with vendors).
6. Record COGS
Send COGS from A2X to QuickBooks Online. ( Get more details on how to set this up here.)
7. Review month-end inventory balance
Think of COGS and inventory as two sides of the same coin. COGS is an expense line item on your Profit and Loss. Inventory is an asset line item on your Balance Sheet. Inventory that sits on your Balance Sheet will eventually move to Profit and Loss as items are sold.
In your Shopify bookkeeping process, regular inventory counts are necessary to ensure that the recorded value of your inventory aligns with the actual on-hand amount, and to adjust for any discrepancies, such as shrinkage or losses.
If your inventory asset value goes negative, it may indicate a disconnect between how COGS and inventory levels are being tracked and managed.
8. Adjust inventory shrinkage/losses
Adjust your inventory shrinkage and losses in QBO after completing step 7.
This is incredibly important to do regularly, as sometimes inventory can go missing – which might seem like no big deal, but could have a major impact on your business in the long run.
9. Record Shopify COGS in QBO
If you’re not using A2X’s COGS feature, record your COGS as a journal entry in QBO at this time.
If you’re using an inventory management system, this involves simply verifying the data that automatically transfers into your accounting system.
If you’re using a spreadsheet, a more manual inventory process would involve checking sales channels, calculating costs based on a product cost catalog, and manually entering this data.
10. Review returned quantities
After (or alongside) your inventory count, review returned quantities to ensure inventory records are accurate.
11. Record marketing products
If you’re using products for marketing purposes (e.g., if you’re sending products to influencers), track these as marketing expenses or inventory outflows as appropriate.
12. Review sales in Shopify Finance Summary
If you’ve used A2X to push transaction data over to Shopify, this is a simple verification step – check to see if all records look accurate and consistent.
If you notice any differences, make sure you have sent all of your A2X entries to your Shopify accounting software.
If you’re still noticing differences, then you can use the A2X Shopify Reconciliation Report to investigate further.
13. Verify unmatched A2X summaries
Occasionally, discrepancies in bank deposits and A2X records occur. This is often due to duplicate entries sent by A2X, and simply need to be deleted in QBO.
However, you know your business best – if something doesn’t look right, now is the time to take a closer look.
14. Review Order Edit orders
Edited orders in Shopify can impact revenue tracking, inventory management, tax, transaction fees, and more.
Make sure you review and adjust for any orders in Shopify that have been edited.
15. Review manual orders
Review orders that have been placed manually or might not have been automatically captured by Shopify.
16. Reconcile other payment gateways
This is the process that we touched on briefly in Step 1 – reconciling other payment gateways.
If you have sent over the A2X entry to a clearing account for a particular payment gateway, and then have received the sales amount from that payment gateway, it’s time to balance the difference.
A common discrepancy in these accounts is often due to payment processor fees, which are typically detailed in the statements. Use your payment gateway statements to review and code accordingly.
17. Review pending payments
Check for any payments that are pending receipt and follow up as necessary.
18. Review chargebacks
Review and record any chargebacks (disputed or returned transactions).
19. Review currency conversion
If operating in multiple currencies, review and record any gains or losses due to currency conversion.
20. Review and finalize Profit and Loss statement and Balance Sheet
Don’t skip this step! This is why you’ve done all of your hard work reconciling your accounts.
Perhaps the most important part of your monthly Shopify bookkeeping process is to thoroughly review your financial statements. This review isn’t just a formality for tax purposes, but a fundamental practice for informed business owners to truly understand their financial health.
Take a look at your Profit and Loss statement and Balance Sheet (accessible in the ‘Reports’ section in QuickBooks) to see your revenue, expenses, and overall financial standing for the period. Analyzing these reports provides insights into each transaction, ensuring everything is correctly categorized and reflecting the true financial picture of your store.
A simple but in-depth review will help you make informed decisions based on the financial trends and health of your business, with every aspect from sales to inventory accurately accounted for in your balance sheet.
Shopify bookkeeping is easier (and more accurate) with A2X.
Start a free trial today!
Integrate Shopify and QuickBooks Online for accurate accounting
A2X auto-categorizes your Shopify sales, fees, taxes, and more into accurate summaries that make reconciliation in QuickBooks Online a breeze.
Try A2X today
