How to Build a Claim and Scheme Simulator in Excel

A claim simulator is a workbook that recomputes what a distributor claim or scheme payout should be, from transaction data and the agreed terms — rather than accepting the figure a partner claimed. Build it as four tabs, keep every term off the formulas, match on codes rather than names, and reconcile with control totals before trusting any number.

This article is the structure the other simulator articles build on. It covers the workbook, the data discipline and the checks. The claim-type specifics — chargebacks and billbacks, price protection and stock compensation, and buyback and expiry returns — each get their own model, and each assumes this one.

The four tabs

Tab	What it holds	The rule
Data	Raw transactions exactly as exported — invoices, returns, stock movements	Never edited by hand. Re-export instead
Terms	Every rate, threshold, window date, price and band, as labelled cells	The only place a number is typed
Calc	One row per claim line, with the formulas	Formulas live here and nowhere else
Output	The summary, the line detail to share, and the control totals	Read-only; built from Calc

Almost every broken claim model in practice is a workbook where these four jobs have been merged into one sheet — rates typed into formulas, data edited in place, and no independent check that anything reconciles.

Why terms never belong inside a formula

A formula like =B2*0.035 works and is wrong. It works because it produces a number; it is wrong because the 3.5% now exists only inside a cell nobody will think to look at. When the scheme rate changes, or someone asks why a partner was paid at that rate, the answer is buried in the formula bar of one row — and if the rate was typed into some rows and not others, the model silently pays two different rates for the same scheme.

Put the rate on the Terms sheet as a labelled cell, and point the formula at it. Then the rate is visible, changeable in one place, and auditable. The same applies to window start and end dates, thresholds, band boundaries and reference prices. This is the discipline that makes the difference between a workbook you can defend at settlement and one you can only apologise for — the same reasoning behind writing rates, bases and windows into the rebate agreement rather than leaving them implicit. The payout lookup table is the same idea in scheme design: one authoritative table, referenced rather than retyped.

Codes, not names

Match on the customer code and the SKU code, never on names.

Names are not stable data. The same distributor appears as "Sharma Traders", "Sharma Traders." and "SHARMA TRADERS " across three exports; a product is renamed mid-year; a trailing space survives a copy-paste. Every one of those makes a criteria match fail — and a failed match in a conditional sum does not raise an error. It just returns a smaller number.

That is what makes it dangerous. A claim that is 8% short because forty rows silently failed to match looks exactly like a claim that is correct. Nobody investigates a number that seems plausible. Codes are issued once, by a system, and do not vary by typist — which is why the invoice-level fields you collect matter more than any formula technique.

Dates must be dates

The second silent failure is dates arriving as text. An exported date can look identical to a real one and behave nothing like it: window comparisons admit or drop the wrong rows, and date arithmetic returns an error or a meaningless number.

The quickest tell is alignment — Excel right-aligns real dates and left-aligns text by default. If a column of dates is hugging the left of its cells, it is text. Convert the whole column before any calculation, and re-check after every fresh export, because the export will do it again. This one failure accounts for more wrong claim numbers than any formula mistake, precisely because some rows still calculate and the workbook looks like it is working.

Control totals

A control total is a figure computed independently and compared against the source, to prove nothing was lost between the system and the model. Two are enough for most claim work:

• Row count — =COUNTA(Data!$A:$A)-1 (the -1 drops the header), compared against the row count the source system reported.
• Total quantity or value — =SUM(Data!$G:$G), compared against the total the source reported.

Then flag the comparison rather than eyeballing it:

=IF(ROUND(B2-C2,2)=0,"OK","CHECK")

where B2 is your computed total and C2 the figure from the source.

The point is not arithmetic; it is knowing whether the model is working on complete data. A missing month of sales or a truncated export produces a workbook that computes flawlessly and answers the wrong question. Run the control totals before looking at any claim number, and treat a mismatch as a stop condition rather than a rounding curiosity.

One authoritative copy

The last structural rule is about people, not formulas. The moment a workbook is emailed, it forks — and two versions of a claim model produce two payouts, both defensible by whoever holds that file. Keep one authoritative copy, record which version settled which period, and never let a settlement be recomputed from someone's local edit. The same principle runs through the settlement factsheet: the number is only as good as the ability to show where it came from.

Where a simulator stops coping

A spreadsheet is the right tool for designing and testing claim logic, and the wrong one for running settlement month after month. The honest breaking points: many concurrent schemes with different windows and bases; several people needing the same version at once; history that must be preserved for audit rather than overwritten; returns arriving after a payout with no way for the workbook to remember what was already claimed; and settlements that must reconcile against credit notes.

None of those is a formula problem, which is why no formula fixes them. When they start to bite, the question becomes how to move the work off spreadsheets — and that is a different article. ClaimDS holds each scheme's terms as the authoritative record and recomputes entitlement from transaction data at settlement, so the model and the settlement are the same thing rather than two versions of it. <!-- TODO: confirm capability wording with founder -->

Build the workbook this way and the claim-type models that follow are mostly arithmetic. Build it any other way and the arithmetic is the least of the problems.

This article is general information about commercial and spreadsheet practice, not tax, legal or accounting advice. Where a claim, return or credit note has GST consequences, confirm the treatment for your business with a qualified professional.

Read next

• Build a chargeback and billback simulator in Excel — price difference × qualifying units.
• Build a price protection and stock compensation simulator in Excel — valuing the holding at an event date.
• Build a buyback and expiry return simulator in Excel — banding returned stock by age.
• How to calculate FMCG distributor claims — the arithmetic behind the common claim types.
• How to move rebate and claim work off spreadsheets — when the workbook stops being enough.

Book a demo to see how ClaimDS recomputes claims from your own transaction data against the agreed terms.