Understanding Payment vs. Sales Differences in Shopify with Bookkeep
Introduction
For Shopify merchants, one of the most challenging accounting puzzles is understanding why the total payments collected in a day often doesn't match the total sales completed in the same day. These discrepancies are normal, but without proper tracking and categorization, they can lead to reconciliation headaches and inaccurate financial reporting.
Bookkeep's Shopify accounting automation specifically addresses this challenge by automatically identifying, categorizing, and tracking the key components that make up these differences. This article explores how Bookkeep handles these discrepancies and the benefits this brings to your financial operations.
The Payment vs. Sales Gap: Why It Happens
Before diving into how Bookkeep handles these differences, it's important to understand what commonly causes them. The gap between daily payments and sales in Shopify typically stems from three main sources:
1. Accounts Receivable Activity
Shopify allows transactions that create or settle accounts receivable:
- Creation of A/R:When orders are placed but not immediately paid (e.g., manual payment methods, partial payments, net terms)
- Settlement of A/R: When previously created receivables are paid off
- Refunds against A/R: When refunds are issued for orders that haven't been fully paid
2. Exchange Rate Differences
When selling in multiple currencies, exchange rate fluctuations introduce discrepancies:
- Currency Conversion: Shopify typically converts customer payments in foreign currencies to your shop's base currency. This is common with Managed Markets orders.
- Rounding Differences: Small rounding variations can occur during currency conversions
- Multiple Payments per Order: When an order is paid via multiple transactions which are converted to the shop currency, there can be multiple exchange rates leading to a larger exchange rate gain or loss.
3. Orders Marked as Paid Without Payments
Several scenarios can result in orders being marked as paid without corresponding payment transactions:
- Manual Order Management: Staff manually marking orders as paid without recording payment details
- POS Transactions: In-store purchases handled outside Shopify's payment system
- Draft Orders: Converting draft orders to paid orders without capturing payment details
- Import Errors: Errors during order imports or migrations
- Gifting: Orders which are gifted would appear here, e.g. if an order is given away without payment but marked as paid
- Order Exchanges: Some platforms, e.g. Loop Returns, which handle order exchanges may create orders marked as paid without any associated transactions as they are actually order exchanges
How Bookkeep Tracks and Categorizes These Differences
Bookkeep's Shopify integration provides automated solutions for tracking and managing these discrepancies:
Daily Sales vs. Payments Breakdown within the Ecommerce Journal Entry
Bookkeep automatically captures all sales activity and payment activity by payment type with any differences booked to the pending payments line. Below is an example with a pending payments balance.
Looking at the example above, there is a $702.24 difference between the amount collected versus the sales activity. On the surface, this $702.24 credit appears to be the repayment of accounts receivable. However, if you expand the subcategories for this line, you will see the following:
The $702.24 difference actually represents the three components mentioned above:
- $1,297.63 of settlement or repayment of accounts receivable
- $0.02 exchange rate difference which in this case represents an exchange rate gain
- $595.41 of orders marked as paid with no payment attached. This could be due to a variety of reasons as mentioned above including gifting, payments not imported, operational issues, etc.
By separating these components within one daily sales summary, Bookkeep establishes a clear relationship between sales and payments while preserving the data accuracy to understand what is happening within a single sales day.
Accounts Receivable Tracking
Bookkeep automatically:
- Identifies new receivables created by unpaid or partially paid orders
- Tracks settlements of existing receivables through payment collections
- Posts this activity under the “balance” subcategory of the pending payments line
Example Journal Entry for A/R Activity
When an order is placed with payment to come later:
| Debit | Credit | |
|---|---|---|
| Accounts Receiveable | $150.00 | |
| Revenue | $136.36 | |
| Sales Tax Payable | $13.64 |
When payment is later collected:
| Debit | Credit | |
|---|---|---|
| Shopify Payments Balance | $150.00 | |
| Accounts Receiveable | $150.00 |
Exchange Rate Difference Identification
Bookkeep's automation:
- Calculates the expected payment amount based on the original order value
- Compares this to the actual payment amount received after currency conversion
- Automatically identifies the difference as an exchange rate gain or loss
- Posts this activity under the “Exchange rate differences” subcategory of the pending payments line
Example Journal Entry for Exchange Rate Differences
When payment received is less than expected due to exchange rate changes:
| Debit | Credit | |
|---|---|---|
| Shopify Payments Balance | $148.25 | |
| Exchange Rate Gain/Loss | $1.75 | |
| Gross Sales | $150.00 |
When payment received is more than expected due to exchange rate changes:
| Debit | Credit | |
|---|---|---|
| Shopify Payments Balance | $152.50 | |
| Revenue | $150.00 | |
| Sales Tax Payable | $2.50 |
Payment Discrepancy Detection
For orders marked as paid without corresponding payment transactions, Bookkeep posts this activity under the “Orders marked paid” subcategory of the pending payments line.
Example Journal Entry for Payment Discrepancies
For orders marked paid without payment data:
| Debit | Credit | |
|---|---|---|
| Payment Discrepancy Account | $125.00 | |
| Gross Sales | $125.00 |
Benefits of Bookkeep's Approach
This comprehensive tracking system provides several key benefits:
1. Accurate Financial Reporting
- Clean distinction between sales activity and cash flow
- Proper accrual-based accounting regardless of payment timing
- Accurate accounts receivable balances at all times
- Transparent tracking of exchange rate impacts on the business
2. Improved Operational Insights
- Detection of patterns in payment discrepancies
- Identification of potential process issues in order management
- Better understanding of the impact of multi-currency selling
3. Time Savings
- Elimination of manual tracking and spreadsheets
- Automation of complex currency conversion calculations
- Reduced need for error correction and investigation
- Streamlined month-end close process
Best Practices for Using Bookkeep's Payment vs. Sales Tracking
To maximize the benefits of Bookkeep's automated tracking:
- Configure proper accounts: Set up dedicated accounts for exchange rate differences and payment discrepancies
- Review exception reports regularly: Check flagged transactions to identify systematic issues
- Verify initial setup: Ensure correct mapping of Shopify payment and sales lines
Conclusion
The disconnect between daily sales and payment figures in Shopify is inevitable due to accounts receivable activity, exchange rate variations, and order management factors. Bookkeep's accounting automation turns this potential reconciliation nightmare into clear, structured financial data through automated categorization, and journaling of these differences.
By implementing Bookkeep's Shopify integration, merchants gain accurate financial reporting, dramatically reduced reconciliation time, and valuable operational insights that would be nearly impossible to achieve with manual processes.
If you have any questions, please contact [email protected].