Build one workbook with four sheets: raw settlement data, a rate and terms sheet, a reconciliation sheet, and a bank match. In the reconciliation sheet, calculate expected payout from net sales, commission, fees, GST on fees and TCS, then compare it with the settled amount. Investigate every non-zero difference. Take all rates from your seller agreement and the latest marketplace statements.
What the template needs to do
A settlement report tells you what the marketplace paid. It does not tell you whether the payout was right. Your template should answer three questions for every settlement cycle:
- Does the sales and returns data match your own order records?
- Do the commission and fees match the terms you agreed?
- Do GST on fees and TCS add up, and does the final amount match your bank credit?
To do this, keep raw data and calculations apart. Paste each marketplace download untouched into one sheet and build every calculation in separate sheets that only read from it. That way you can re-paste next month's file without breaking formulas.
Set up the four sheets
- Raw_Settlement: the marketplace report exactly as downloaded. Do not edit or delete rows.
- Terms: commission rates, fixed fees, shipping or collection fee logic, and the GST rate applied on fees, each taken from your seller agreement or rate card, with an effective-from date.
- Recon: one row per order or order item, with expected values, reported values and differences.
- Bank_Match: one row per settlement or UTR, with the settled amount in the report and the credit in your bank statement.
Keep rates only in the Terms sheet and reference them with lookups. If a rate changes mid-year, you add a new row with a new effective date instead of overwriting history.
Columns for the Recon sheet
Use these columns as a starting point. Rename them to match your marketplace's report headings.
| Column | Source | Purpose |
|---|---|---|
| Order ID / Item ID | Report | Lookup key |
| Settlement ID / UTR | Report | Links to bank credit |
| Gross sale value | Report | Starting point |
| Return / refund value | Report | Deduct from sale |
| Net sale value | Formula | Gross minus returns |
| Commission (reported) | Report | As charged |
| Commission (expected) | Formula | Net sale value x agreed rate |
| Other fees (reported) | Report | Fixed, shipping, collection and similar |
| GST on fees | Report and formula | Reported versus recomputed |
| TCS | Report and formula | Reported versus recomputed |
| TDS (if deducted) | Report | Separate from TCS |
| Expected payout | Formula | Your calculation |
| Settled amount | Report | What the marketplace paid |
| Difference | Formula | Settled minus expected |
Handling GST and TCS columns correctly
Treat these as separate items, because they are different taxes with different treatment.
- GST on fees: the marketplace charges GST on its commission and service fees. Compute it as taxable fees multiplied by the GST rate shown on the marketplace invoice, and compare it with the reported figure. Keep the rate in the Terms sheet.
- TCS under GST: the marketplace collects it on the net value of taxable supplies made through it, after returns. Check the current rate and the base on which it applies with your tax adviser and the marketplace's latest notification or statement.
- TDS: if the marketplace deducts income tax at source, record it in its own column. Do not combine it with TCS, since the credit is claimed differently.
Do not hardcode a rate you remember from last year. Rates and rules change, so confirm them against the latest official source before you finalise the sheet.
Formulas and checks that catch errors
Build the expected payout as: net sale value minus expected commission, minus other fees, minus GST on fees, minus TCS, minus TDS if applicable. Then add these checks:
- Difference column: settled amount minus expected payout. Filter for anything not zero, allowing a small rounding tolerance such as ₹1.
- Commission variance: reported commission minus expected commission, to spot fee-rate or category mismatches.
- Duplicate check: a COUNTIFS on order ID and event type to flag the same charge posted twice.
- Return mismatch: returns in the report with no matching return in your own records, or the reverse.
- Bank match: in Bank_Match, use SUMIFS on Recon by settlement ID and compare it with the bank credit. Any gap points to a timing issue, a held amount or a deduction outside the order lines.
Add a status column with values such as Matched, Under review, Claim raised and Recovered. It turns the sheet from a one-time check into a working log.
Where Excel starts to strain
This approach works well for one marketplace and a few thousand rows a month. It gets harder when report formats differ across marketplaces, fee terms vary by category, or returns settle in later cycles than the original sale. At that point, manual pasting and lookup maintenance take more time than the checking itself.
MarketplaceMetrics by Fundle is built for this stage. It reconciles marketplace sales and settlements, checks commissions and fees against agreed terms, and tracks returns and recovery cases. Myntra Excel reports are reviewed today, and other marketplaces are in scope discussion. Pricing is a tailored quote.
Worked example
Illustrative example. The figures below are made up to show the arithmetic. They are not real rates; take your own from your agreement and settlement reports. TDS is left out to keep it short.
| Line | Amount (₹, illustrative) |
|---|---|
| Gross sale value | 1,20,000 |
| Less: returns and refunds | 18,000 |
| Net sale value | 1,02,000 |
| Less: commission | 10,200 |
| Less: other fees | 4,800 |
| Less: GST on fees (on 15,000) | 2,700 |
| Less: TCS (on net sale value) | 1,020 |
| Expected payout | 83,280 |
| Settled amount per report | 82,380 |
| Difference | -900 |
The ₹900 shortfall is not explained by any column. Next, filter the Recon sheet by order for commission variance and duplicate charges. If you find a fee above your agreed terms, mark the rows as Claim raised and attach the order IDs to your query with the marketplace.
Questions and answers
Can I use one Excel template for all marketplaces?
Use one Recon layout, but keep a separate raw sheet and a column-mapping step for each marketplace, because report headings and fee structures differ. Map each report into your standard columns before running the formulas.
Should GST on fees and TCS be in the same column?
No. GST on fees is a tax on the marketplace's services, while TCS is collected on your sales. They are treated differently in your books and returns, so keep separate columns, and add a third for TDS if it applies.
Where do I find the right GST and TCS rates?
Check your seller agreement, the marketplace's rate card and invoices, and the settlement report itself. For TCS and TDS rules, confirm the latest position with your tax adviser or the official notifications before updating the Terms sheet.
How often should I reconcile settlements?
Reconcile every settlement cycle rather than waiting for month-end. Differences are easier to trace while order data is fresh, and any claim you raise stays within the marketplace's dispute window, which you should confirm in your agreement.