Why Your Bank Deposit Never Matches Your Sales Dashboard
You made $10,000 in sales yesterday. You check your bank account. The deposit is $9,347.23. Where did $652.77 go?
This is the daily frustration of e-commerce accounting. Shopify (and every other platform) deducts multiple fees before sending your payout. They also hold reserves, deduct refunds from previous periods, and apply varying fee structures based on your plan and payment methods.
Without proper reconciliation, you're flying blind. You might think you're profitable when fees are eating your margins. You might miss unauthorized charges. Your accountant will hate you at tax time.
This guide will show you exactly how to build a reconciliation system in Excel that accounts for every cent.
Understanding the Payout Anatomy
Before we dive into Excel, you need to understand what goes into (and out of) a Shopify payout.
Components of a Payout:
IN (adds to payout):
- Gross sales from orders
- Shipping charged to customers
- Taxes collected
OUT (reduces payout):
- Shopify subscription fees
- Transaction fees (0.5-2% depending on plan and payment method)
- Payment processing fees (2.9% + 30¢ for Shopify Payments in US)
- Chargeback fees ($15-25 per incident)
- Refunds issued (both order amount and fees)
- Adjustments and corrections
- Reserve holds (if applicable)
The Critical Concept: Payout ID
Don't try to match daily sales to daily deposits. It doesn't work. Instead, every payout has a unique Payout ID that groups all transactions that will arrive in that specific bank deposit.
An order from Monday might be in Wednesday's payout. A refund from last month might be deducted from today's payout. The Payout ID is your source of truth.
Step-by-Step Excel Reconciliation Process
-
3
Export Shopify Payout Data
In Shopify Admin, go to Finances > Payouts. Click on the payout you want to reconcile. Export the 'Transactions' CSV. This file contains every order, refund, fee, and adjustment in that payout.
-
3
Calculate Gross Sales by Payout ID
In Calculations sheet, use =SUMIFS(RawData!Amount, RawData!PayoutID, PayoutID, RawData!Type, "charge") to sum all charges. This gives you gross sales before any deductions.
-
3
Sum All Fee Categories
Create separate calculations for each fee type: transaction fees (=SUMIFS for "transaction_fee"), processing fees ("payment_processing_fee"), refunds ("refund"), and chargebacks ("chargeback"). Each has its own row in your Summary sheet.
-
3
Build the Reconciliation Waterfall
Start with Gross Sales. Subtract each fee category line by line. The final number should equal your Expected Payout. Formula: =GrossSales - TransactionFees - ProcessingFees - Refunds - Chargebacks - Adjustments
-
3
Compare to Actual Bank Deposit
In your Summary sheet, enter the actual deposit amount from your bank statement. Calculate variance: =ExpectedPayout - ActualDeposit. If variance is more than $0.01, you have a problem to investigate.
-
3
Investigate Discrepancies with Pivot Tables
If there's a variance, create a pivot table from Raw Data with Type as rows and Amount as values. This quickly shows if you missed a fee category or if Shopify made an adjustment you didn't account for.
Excel Formulas You Need to Know
SUMIFS for Conditional Summing
```excel
=SUMIFS(amount_range, payout_id_range, "po_123456", type_range, "charge")
```
This sums all amounts where the payout ID matches AND the type is "charge".
VLOOKUP for Fee Rate Lookup
```excel
=VLOOKUP(payment_method, fee_table, 2, FALSE)
```
If you have different fee rates by payment method (credit card vs. debit), use a lookup table.
COUNTIFS for Transaction Count Verification
```excel
=COUNTIFS(payout_id_range, "po_123456", type_range, "charge")
```
Count how many orders are in this payout. Cross-check against Shopify's payout details.
Advanced: Power Query for Automation
If you reconcile payouts regularly, use Power Query to connect directly to your exported CSV folder. New files automatically refresh your workbook.