Reconcile in three layers. First, build a net sales figure for the month from marketplace order and return reports. Second, compare it with outward supplies and credit notes in your GST returns. Third, bridge net sales to the bank by deducting commission, fees, GST on fees, TCS, TDS and other adjustments, then compare expected payout with actual receipts. Log every difference as timing, error or recovery.
What three-way reconciliation actually checks
A three-way reconciliation ties three independent records for the same period: what the marketplace says you sold, what you declared in GST returns, and what reached your bank. Each pair catches a different problem.
- Sales vs GST returns: catches unreported or double-reported invoices, missed credit notes and wrong state tagging.
- Sales vs bank: catches short settlements, unagreed deductions and unpaid orders.
- GST returns vs bank: shows timing gaps and tax collected at source that affect your cash and ledgers.
Gather the right documents first
Pull these for one marketplace and one period before you open a spreadsheet:
- Marketplace sales or order report with invoice number, date, ship-to state, taxable value, tax and order status.
- Returns, RTO and credit note report for the same orders.
- Settlement or payout reports showing order-level deductions.
- Filed GSTR-1 and GSTR-3B, plus the e-commerce operator data visible on the GST portal.
- Bank statement lines for marketplace payouts.
- Your seller agreement and current rate card.
Use invoice date for sales and GST, and settlement date for bank. Rules on how supplies through e-commerce operators are reported change, so confirm the latest treatment with your tax adviser.
Step-by-step: reconcile the three records
- Fix the period and cut-off. Decide whether you reconcile by invoice month or settlement month, and stay consistent.
- Build net sales. Gross invoiced value less returns and credit notes, by invoice, with taxable value and tax split out.
- Compare with GST returns. Match net taxable value and tax to what you reported, state by state where relevant. List invoices present in one place only.
- Compute expected payout. Start from net sales (including tax collected) and deduct every charge shown in the settlement report. See the bridge below.
- Match to bank. Tie each payout reference to a bank credit. Differences become your exception list.
- Classify every exception as timing, data error, or recovery claim, and assign an owner and a date.
Build the settlement bridge
The bridge converts sales into the cash you should receive. Take each line from the settlement report, not from memory.
| Line | Where to find it | Check against |
|---|---|---|
| Net sales incl. tax | Sales and returns reports | Your sales ledger |
| Commission and fixed fees | Settlement report | Rate card in seller agreement |
| GST on fees | Settlement report | Fee invoices from marketplace |
| TCS under GST | Settlement report | GST portal credit entries |
| TDS under Income Tax | Settlement report | Form 26AS or AIS |
| Penalties, ads, adjustments | Settlement report | Agreement and notices |
| Expected payout | Calculated | Bank credit |
Do not assume rates. Read them from your agreement and rate card, and confirm the latest terms with the marketplace and your tax adviser.
Common mismatches and how to treat them
- Returns processed after the return is filed: the credit note sits in a later period. Document it and handle the adjustment as your tax adviser advises.
- Orders delivered but unpaid at month-end: timing difference. Carry forward and confirm it clears next cycle.
- Fee higher than the rate card: raise a dispute with order IDs and the clause you rely on.
- TCS or TDS not visible: check the GST portal and Form 26AS or AIS before the next filing.
- Bundled bank credits: one credit covering several payouts. Split it by payout reference.
Keep it routine, not a year-end scramble
Run the bridge every settlement cycle and close the GST comparison before each return. Keep one exception register with age, amount and status so recoveries do not go stale.
If order-level matching in Excel has become the bottleneck, MarketplaceMetrics by Fundle reconciles sales and settlements, checks commissions and fees against agreed terms, and tracks returns and recovery cases. Myntra Excel reports are reviewed; other marketplaces are in scope discussion.
Worked example
Illustrative example (all ₹ figures are made up for explanation, not rates or benchmarks):
| Item | ₹ (illustrative) |
|---|---|
| Gross invoiced sales | 10,00,000 |
| Less returns and credit notes | 1,50,000 |
| Net sales (ledger) | 8,50,000 |
| Net outward value in GST return | 8,70,000 |
| Difference | 20,000 |
The ₹20,000 gap is a return credit note recorded on the marketplace but not yet reflected in the return. Log it as a timing item and correct it as your tax adviser advises.
| Payout bridge | ₹ (illustrative) |
|---|---|
| Net sales | 8,50,000 |
| Commission, fees and GST on fees | (1,20,000) |
| TCS deducted | (8,000) |
| TDS deducted | (800) |
| Expected payout | 7,21,200 |
| Bank receipts | 6,95,200 |
| Shortfall | 26,000 |
Of the ₹26,000, ₹18,000 relates to orders delivered late in the month and unpaid, so it is timing. The remaining ₹8,000 is a penalty line not found in the agreement, so it becomes a recovery case with order IDs attached.
Questions and answers
How often should I run a three-way reconciliation?
Run the sales-to-bank bridge every settlement cycle, weekly or fortnightly depending on the marketplace. Run the sales-to-GST comparison at least monthly before filing returns, so corrections happen while the period is still open.
Why does my GST return not match the marketplace sales report?
Common causes are returns or credit notes recorded in different periods, invoices cancelled after filing, wrong state tagging and cut-off differences. Match invoice by invoice and keep a list of items present in only one record.
Where do I find TCS and TDS amounts deducted by the marketplace?
Order-level or payout-level amounts appear in the settlement report. Verify them against credit entries on the GST portal for TCS and Form 26AS or AIS for TDS. Confirm applicable rates and treatment with your tax adviser.
Can I do this reconciliation in Excel?
Yes, for low volumes. Use lookups on order ID or invoice number across the three datasets. As order counts and fee lines grow, manual matching becomes slow and error-prone, and a dedicated reconciliation tool saves time.