I have a few clients who use Shopify for their website’s shopping cart. They are selling services, not products. My clients are using QuickBooks Online, and the Shopify sales arrive directly in the bank feed. Due to the nature of these small businesses, we are posting the Shopify deposits directly to an Income account called “Shopify Revenue.” We are not creating individual invoices or sales receipts for each customer sale, as we do not need that level of detail for items or income (only services are being sold, no products). Note: If your client needs more detail, the approach outlined below may not be the right solution.
Before I came on the scene, the clients did not realize that Shopify was deducting their fee from each deposit, so only the net income was being posted from the bank feed, not the gross. As a result, gross sales were being under-reported. In order to correct for this, I create a Zero Dollar Check (ZDC) at the end of each month to ‘gross up’ the Shopify income.
If this scenario works for your client, here are the steps:
Step 1. Create an Income account called: Shopify Income or Shopify Revenue
Step 2. Create an Expense or Cost of Sales account for the Shopify fees.
Step 3. Post Shopify income in the bank feed directly to the Shopify Revenue account. You can do this manually, or create a Rule.
Step 4. At month-end, log in to Shopify and pull a Daily Payouts report. Set the report dates to the month just ended. The columns in the report show: Payout Date, Status, Charges, Refunds, Adjustments, Reserved Funds, Fees, Retried Amount, Total (Net). Save the .csv data to an Excel workbook with a separate tab for each month’s Shopify raw data. This is good backup documentation for the year.
Step 5. Add a tab to the Excel workbook which summarizes the raw data for each month. My summary chart looks like this:
Screenshot: Shopify Summary Data in Excel
Next, look at the QuickBooks P&L (Cash Basis) for the month just ended, and note the Shopify Revenues posted directly in QB. Add them to a second summary chart in Excel, which includes a column for the Shopify fees from the raw data, to compare. Mine looks like this:
Screenshot: Comparison Shopify Report to QB
This shows the Gross Revenues in Shopify compared to the Net Revenues posted to QB. The difference should be the missing Shopify fees. When the numbers exactly match up, you know how much to ‘gross up’ the Shopify revenues in QB.
Step 6. Create and use a memorized Zero Dollar Check to gross up the revenues. My entry for August looks like this:
Screenshot: Zero Dollar Check to Gross Up Revenues and add Shopify Fees
As you can see, this ZDC grosses up the Shopify revenues and adds the Shopify fees to the P&L. While the explanation is long, the work is short – it usually takes about 5-10 minutes for the entire process.
This is another great example of using Zero Dollar Checks for simple tasks, instead of Journal Entries. I’d like to close with this acknowledgment. While I have been touting the benefits of ZDC, I did not invent the idea. I first learned about ZDCs from Doug Sleeter, formerly of the Sleeter Group.