Build a Chargeback and Billback Simulator in Excel

A chargeback and a billback are the same arithmetic — a price difference multiplied by the units that qualify. The difficulty is never the multiplication; it is defining which units qualify, at which two prices, in which window. Get those three definitions from the agreement and the model is straightforward.

This article builds the model. For what these instruments are and how they behave commercially, see what is a billback and the billbacks, chargebacks and deductions glossary. The workbook structure it assumes — four tabs, codes not names, control totals — is set out in how to build a claim and scheme simulator in Excel and is not repeated here.

One disambiguation before anything else: a chargeback here is a channel price-difference claim between trading partners. It is not a card-payment chargeback, where a cardholder reverses a transaction through their bank. Everything below is the distribution sense.

The worked example

Three illustrative claim lines. All figures are illustrative — the point is the adjustments, not the multiplication.

Claim line	Reference price (₹)	Actual/contract price (₹)	Difference (₹)	Qualifying units	Claim value (₹)	Evidence required
A — clean line	1,000	880	120	500	60,000	Contract reference + invoices
B — window boundary	1,000	880	120	420 (80 fell outside the window)	50,400	Invoices with dates at the boundary
C — return adjusted	1,000	900	100	300 (340 less 40 returned)	30,000	Invoices + the return documents

Line A is the calculation everyone expects. Lines B and C are where the money actually moves: 80 units excluded by a window boundary and 40 units removed by a return are the difference between a claim that settles and a claim that gets queried — and, left unnoticed, a steady source of revenue leakage on claims that were simply never computed properly. The fields you capture at invoice level decide whether either adjustment is even visible.

Chargeback and billback: same maths, opposite direction

A chargeback, in the distribution sense, is typically claimed where a distributor sold to a contracted customer at an agreed lower price and recovers the difference from the supplier — the mechanism behind contracted and customer-specific pricing. A billback is typically claimed where a partner sold at a promoted price on the brand's instruction and bills back the gap.

The direction and the paperwork differ. The arithmetic does not. Both compute:

(reference price − actual price) × qualifying units

Which is why one model serves both, and why the disputes on both look identical. Neither side argues about multiplication; they argue about which units counted and which two prices applied. When a claim of this shape is disputed, the disagreement almost always traces back to a definition that was never written down — the same root cause behind most deductions taken against these claims.

The three definitions that decide the number

Everything contentious in this claim type lives in three definitions the agreement must make explicit.

Which two prices. "The difference" is meaningless until both sides of it are named. The reference price might be the list price, a standard price, or the price actually invoiced — these are different numbers, and the price waterfall is precisely the sequence they sit in. The agreed price is whatever the contract or promotion set. Name both in the agreement, and hold both on the Terms sheet.

Which units qualify. Three sub-questions, each of which quietly changes the total: which customer or outlet the sale must have gone to; which SKUs are in scope; and what the trigger is — the sale to the end customer, or the original purchase from the supplier. The last one is the one most often left implicit, and it is the difference between a claim computed on primary and one computed on secondary sales.

Which window. The promotion or contract dates, and — critically — which date field governs at the boundary. Document date and delivery date give different answers for every unit shipped near a period end, and both are defensible until the agreement picks one.

Most disputes on chargeback and billback claims trace to one of these three being implicit rather than wrong. The arithmetic is agreed; the definitions were never agreed in the first place.

Building the model

With the workbook structured as the pillar describes, two formulas do the work.

Qualifying units for one claim line:

=SUMIFS(Data!$G:$G, Data!$D:$D, $A2, Data!$E:$E, $B2, Data!$B:$B, ">="&$C2, Data!$B:$B, "<="&$D2)

where Data column G is quantity, D is the customer or outlet code, E is the SKU code, B is the document date, and $C2/$D2 hold the window start and end for that line.

Read it in plain words: sum the quantity column, for rows where the customer code matches this line's customer, and the SKU code matches this line's SKU, and the document date is on or after the window start, and on or before the window end. Each criteria pair is one condition; adding a condition means adding a pair.

Claim value:

=F2 * (Terms!$B$4 - G2)

where F2 is qualifying units, Terms!$B$4 is the reference price and G2 the agreed price for that line.

Note where the reference price comes from: the Terms sheet, not the formula. That is what lets you answer "which reference price did we claim at?" months later.

The single most important detail is in the criteria. The customer and SKU arguments must point at code columns. Matching on names is the most common cause of a claim understating itself, because one mistyped name silently drops units from the total — and a smaller number raises no error and looks entirely plausible.

Netting returns

Units returned after the claim period should reduce qualifying quantity. Otherwise the claim is made on units the partner did not ultimately keep or sell, and the correction arrives later as a clawback or a deduction.

The approach: build a second conditional sum over the return documents, using the same customer, SKU and window criteria, and subtract it from the qualifying units before applying the price difference. Structurally it is the same formula shape as above, pointed at return rows instead of sale rows.

The trap is how you identify a return. Do it by document type, not by negative quantities. Negative-quantity conventions vary between systems — some post returns as negative lines against the original document type, others as positive lines under a separate return type, and some do both depending on the transaction. A model built on one convention breaks silently on the other: it either double-counts the return or misses it entirely, and in both cases the arithmetic still completes. The returns and reversals treatment is worth settling before the model is built rather than after.

Evidence: what makes the claim settle

The model produces a number. The claim needs proof: the contract or promotion reference that establishes the agreed price, the invoices behind the qualifying units, and the end-customer detail where the claim depends on who bought.

Design the Output tab to emit the supporting line detail, not only the total. A total with no line detail is what turns a claim into a query cycle — the counterparty cannot verify it, so they ask, and the exchange consumes more time than the claim is worth. Line detail attached to the claim converts a negotiation into a check. This is the same discipline as the settlement factsheet, and it is what separates claims that settle from claims that age inside a claims process.

Where this model stops coping

Honestly: a spreadsheet handles one contract, one window and one price pair very well. It struggles when there are many concurrent contracts and promotions with different windows; when prices change mid-window so a single reference price no longer applies; when the end-customer data sits with the distributor and the brand cannot verify it; and when claims span periods, so the same units could be claimed twice under two overlapping agreements.

Each of those is a data and memory problem rather than a formula problem, which is why the workbook cannot solve them — it has no way to remember what was already claimed. That is the point at which moving the work off spreadsheets becomes the real answer. ClaimDS holds each contract and promotion as an authoritative record and recomputes qualifying units from transaction data, so overlapping agreements resolve against one set of terms rather than several workbooks. <!-- TODO: confirm capability wording with founder -->

This article is general information about commercial and spreadsheet practice, not tax, legal or accounting advice. Where a claim or its settlement has GST consequences — including whether it settles through a financial or a tax credit note — confirm the treatment for your business with a qualified professional.

Read next

• How to build a claim and scheme simulator in Excel — the workbook structure this model assumes.
• What is a billback — the instrument, commercially.
• The chargeback dispute process — what happens when the number is contested.
• How to calculate FMCG distributor claims — the other common claim types.

Book a demo to see how ClaimDS computes price-difference claims from your own invoice data.