A free Amazon and Flipkart reconciliation template is easy to build in Excel. Use one tab per marketplace for settlement data, one for your order or sales data, one for agreed fee terms, and a summary tab. Match by order ID, compare expected against charged fees, and list every variance with a status and owner. Always check column names against your latest reports.
What a reconciliation template must actually do
Most free downloads are formatted tables with a few totals. A useful template does three jobs: it proves that every order you sold is accounted for in a settlement, it shows whether the fees charged match what you agreed, and it tracks what is still pending or disputed.
Amazon and Flipkart both publish settlement or payment reports in the seller dashboard, but their layouts, column names and fee breakdowns differ and change over time. That is why a rigid template copied from the internet often breaks on the first real file. Build a structure you control, then map each marketplace's columns into it.
- Completeness: sales, returns and cancellations on your side against what the settlement shows.
- Accuracy: commission, fixed or closing fees, shipping or weight fees, and other deductions against your terms.
- Follow-up: a list of differences with status, owner and date raised.
The tabs to include in your Excel workbook
Keep the workbook simple enough that anyone on the finance team can refresh it each month.
| Tab | Purpose | Key contents |
|---|---|---|
| Orders | Your source of truth for sales | Order ID, SKU, order date, selling price, status |
| Amazon_Settlement | Raw settlement data pasted as is | Order ID, transaction type, amounts, fee lines, settlement date |
| Flipkart_Settlement | Raw payment data pasted as is | Order item ID, sale amount, fee lines, settlement value, date |
| Fee_Terms | Agreed commercial terms | Category, fee type, rate or slab, effective dates |
| Recon | Matched view per order | Expected vs charged fees, variance, status |
| Returns_Claims | Open items | Return ID, reason, amount, claim status, follow-up date |
| Summary | Month-end view | Totals by marketplace, unreconciled balance |
Never edit the raw settlement tabs. Paste new data in, and let formulas on the Recon tab do the work. That keeps an audit trail.
Columns to map from each marketplace report
Open your latest settlement or payment report from each seller dashboard and map the columns into a common set. Names differ, so confirm them against your own files rather than a template written for an older format.
- Order identifier: the key for matching. Check whether the marketplace uses an order ID, order item ID or both.
- Transaction type: sale, refund, adjustment, reversal or claim.
- Gross sale amount: the value the fees are calculated on.
- Each fee line separately: commission, fixed or closing fee, shipping or weight-based fee, and any others listed.
- Taxes collected or deducted: keep these in separate columns. Confirm current GST, TCS and TDS treatment with your tax adviser and the marketplace's latest documentation.
- Net settlement amount and date: what actually reached your bank and when.
Add a Marketplace column to the Recon tab so Amazon and Flipkart rows can sit in one table and be filtered with a pivot.
A monthly reconciliation routine
- Download the settlement or payment reports for the same period from each marketplace and your own order export.
- Paste each into its raw tab without changing values.
- On the Recon tab, use XLOOKUP or INDEX-MATCH on the order identifier to pull the settlement lines against each order.
- Pull the applicable rate from Fee_Terms using category and effective date, and calculate the expected fee.
- Subtract expected from charged for each fee line to get the variance.
- Flag orders with no settlement, settlement with no matching order, and any variance above a tolerance you set.
- Move genuine issues to Returns_Claims with an owner and a follow-up date.
- Tie the net settlement total to your bank credits on the Summary tab.
Watch the date logic. Orders sold in one month often settle in the next, so reconcile by settlement period and keep an ageing list of unsettled orders.
Checks that catch real leakage
These are the checks that tend to find money, so build each one as its own filter or flag column.
- Commission mismatch: charged commission differs from the rate in your agreement or the marketplace's current rate card. Find the rate in your seller agreement or rate card, and confirm the latest version before you dispute.
- Duplicate or unexpected fees: the same fee line appearing twice for one order, or fee types you do not recognise.
- Shipping or weight discrepancies: fees based on a weight slab that differs from your product master.
- Return handling: refunds processed without the item coming back, or returned items not reflected in your stock. Track return status against the amount held.
- Missing settlements: delivered orders with no payout after the expected window in your terms.
- Tax lines: TCS or TDS amounts that do not match what you see in your tax credit records. Confirm with your tax adviser.
When Excel stops being enough
Excel works well for one or two marketplaces and a manageable order count. It struggles when files run to hundreds of thousands of rows, when fee terms change mid-month, or when several people edit the same workbook and formulas get overwritten.
If you are spending days each month on matching rather than resolving disputes, consider a tool. MarketplaceMetrics by Fundle 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 Amazon and Flipkart coverage is a scope discussion, so ask about your exact setup. Pricing is a tailored quote.
Worked example
Illustrative example only. The figures below are assumed to show the method and are not real rates or results. Suppose your Fee_Terms tab gives an expected commission for a category, and the settlement shows what was charged.
| Order | Sale value (₹) | Expected commission (₹) | Charged commission (₹) | Variance (₹) |
|---|---|---|---|---|
| A | 1,000 | 120 | 120 | 0 |
| B | 1,500 | 180 | 225 | 45 |
| C | 800 | 96 | 96 | 0 |
| Total | 3,300 | 396 | 441 | 45 |
Order B is flagged because the charged commission is ₹45 above expected. You would check whether the category mapping, the effective date of the rate or the marketplace's fee calculation caused it, then raise it with evidence from your agreement and the settlement line. On the Returns_Claims tab it carries a status, owner and follow-up date until credited or closed.
Questions and answers
Is there a free Amazon and Flipkart reconciliation Excel template to download?
Generic templates exist, but they rarely match current settlement report columns. A better approach is to build your own workbook using the tabs and columns in this guide, then map each marketplace's latest report into it. That way the sheet fits your actual files and fee terms.
Which key should I use to match orders with settlements?
Use the order identifier in the settlement report, such as the order ID or order item ID, and check which one appears in both your order export and the marketplace file. Test on a small sample first, because one order can have several settlement lines.
How often should I reconcile marketplace settlements?
Most brands do a full reconciliation monthly, with a lighter weekly check on unsettled orders and returns. Weekly checks let you raise disputes sooner. Always check the dispute or claim time limits in your seller agreement and the marketplace's latest policy.
Can Excel check commissions against my agreed rates?
Yes, if you keep a Fee_Terms tab with category, rate and effective dates, and look up the expected fee per order. It becomes hard to maintain when terms change often or volumes are very large. Confirm current rates in your agreement or rate card.