Build a Price Protection and Stock Compensation Simulator in Excel

Price protection and stock compensation both value what a partner was holding at a specific moment — the date a price dropped, or the date a compensation event was declared. The rate is the easy part. The work is establishing the holding: opening stock plus purchases minus sales, up to that date, per SKU per partner.

A disambiguation first, because this phrase collides: stock compensation here means the channel claim — compensating a distributor or dealer for inventory that has become harder to sell. It has nothing to do with employee stock or equity compensation, which is a form of staff remuneration.

This article builds the model. For the commercial detail — how these clauses are written and why they exist — see price protection in sales and the stock compensation rebate process. The workbook structure assumed throughout is in how to build a claim and scheme simulator in Excel.

The worked example

Three illustrative lines at one event date. All figures illustrative.

Partner	SKU	Opening stock	Purchases to event date	Sales to event date	Holding at event date	Price differential (₹)	Claim value (₹)
P-1001	SKU-A	100	400	350	150	120	18,000
P-1002	SKU-A	200	100	260	40	120	4,800
P-1003	SKU-B	0	500	500	0	120	0

Look at P-1002. Sales in the window (260) exceed purchases in the window (100), so the holding depends entirely on opening stock — without it the model returns a negative holding and either pays nothing or, worse, produces a negative claim that someone nets off elsewhere. That case is where most of these models break, and it is why opening stock is a required input rather than a nice-to-have.

P-1003 shows the other end: a partner who sold everything holds nothing and is owed nothing, which the model should state plainly rather than omit.

What the two claims have in common

Both are point-in-time inventory claims. Price protection compensates for a list-price reduction on stock the partner already bought at the higher price — the rate-difference situation common in consumer electronics and any category where prices fall during the selling cycle. Stock compensation covers stock that has become harder to sell: ageing, seasonal, or displaced by a new version.

Commercially they answer different questions. Mechanically they are the same model: establish a holding at a date, apply a rate, produce a claim value. That is why one workbook serves both, and why both fail the same way — not on the rate, which is usually written down, but on the holding, which usually is not. Both sit inside the wider channel claims process alongside the rate-difference and damage claims that behave similarly.

The reason the holding is contentious is that neither party can see it directly. The brand knows what it shipped; the partner knows what it sold. Only a reconstruction from both sides establishes what was actually sitting in the channel at the moment the price moved.

Establishing the holding is the whole problem

The model reconstructs stock on hand at the event date, per partner per SKU.

Purchases up to the event date:

=SUMIFS(Data!$G:$G, Data!$C:$C, $A2, Data!$E:$E, $B2, Data!$F:$F, "Purchase", Data!$B:$B, "<="&Terms!$B$5)

Sales up to the event date:

=SUMIFS(Data!$G:$G, Data!$C:$C, $A2, Data!$E:$E, $B2, Data!$F:$F, "Sale", Data!$B:$B, "<="&Terms!$B$5)

Holding:

=C2 + D2 - E2

where C2 is opening stock, D2 purchases, E2 sales, column C is the partner code, E the SKU code, F the document type and Terms!$B$5 the event date.

Two things to notice. The event date lives on the Terms sheet, so re-running the model for a different event means changing one cell rather than editing every formula. And the document type does the separating — purchases and sales are distinguished by what kind of document they are, not by the sign of the quantity.

This reconstruction is only as good as the completeness of the transaction data — which is decided by the invoice-level fields you collect, not by the model. A missing month of sales overstates the holding and therefore the claim; a missing month of purchases understates it. Neither produces an error — the formulas complete perfectly and return a confident wrong number. That is precisely why the control totals from the pillar matter more in this model than in any other: run them before you look at a single claim value, and treat a mismatch as a stop condition.

Declared stock versus calculated stock

In practice many of these claims are settled on a stock statement the partner declares, rather than a reconstruction. Both routes are legitimate and both have a real weakness.

Declared is fast. The partner states what they hold, the rate is applied, the claim settles. It is also unverified — the brand is accepting a number it has no independent basis for, and any error or optimism in the declaration flows straight into the payout.

Calculated is verifiable. It derives from transactions both sides can point at. It also depends on complete data, which is exactly what channel transaction feeds often are not, particularly for secondary sales the distributor captures and the brand receives late.

Left unreconciled, the gap between the two is a quiet form of revenue leakage — paid out on a number nobody checked. The strongest position is neither alone: calculate, then reconcile against the declaration. Where the two agree, the claim is well-founded and settles quickly. Where they differ, treat the gap as an exception to investigate rather than an accusation — most gaps are timing (a sales file that had not arrived) or scope (a SKU mapped differently), not misstatement. Handling it that way keeps the relationship intact and still gives finance a defensible number, which is the same posture that works for contested deductions.

Applying the rate

Once the holding is established, the rate is arithmetic.

For a price differential:

=F2 * (Terms!$B$7 - Terms!$B$8)

where F2 is the holding, and Terms!$B$7 and Terms!$B$8 hold the old and new prices.

Where compensation is declared as a percentage of value rather than a per-unit differential, the claim is simply the holding value multiplied by the declared rate — with that rate, again, read from the Terms sheet.

The rule that matters here: the rate must come from the Terms sheet and never be typed into the formula. A rate inside a formula cannot be audited, cannot be changed in one place, and — when a second price event follows — cannot be told apart from the first one.

Avoiding double-counting

This is the specific risk in point-in-time claims, and it has two shapes.

The same units attracting two benefits. Stock that earns price protection may also sit inside the qualifying base of a volume scheme, so the same inventory is paid for twice under two different mechanisms. The stock compensation process treats this directly: compensated stock should reduce the eligible base before the scheme is computed.

A second event before the first settles. Two price drops in one quarter, each generating a claim over an overlapping holding, is the classic way a partner is compensated twice for the same units — and where units later come back, the return has to reverse the right claim or the overpayment simply persists as a clawback nobody raises.

The practical controls are unglamorous: record which units have been claimed and under which event, and never run two event models over the same period without reconciling them against each other first.

Be honest about why this error recurs. A spreadsheet has no natural way to remember what was already claimed — each workbook is a fresh calculation over the same data, with no memory of the previous one. The discipline has to live in a person, and people running month-end miss things. That is not a formula flaw; it is a structural limit of the tool.

Where this model stops coping

Multiple price events inside one period, each with overlapping holdings. Batch or serial-level tracking, where the claim depends on which units are held rather than how many. Partner data arriving late, so the holding at the event date keeps changing after the claim is filed. And reconciling declared against calculated across hundreds of partner-SKU combinations, where the exception list becomes longer than the claim.

At that point the constraint is memory and reconciliation rather than arithmetic, and moving the work off spreadsheets is the honest answer. ClaimDS reconstructs the holding from transaction data at each event date and records which units were claimed under which event, so a second price drop reconciles against the first instead of silently overlapping it. <!-- TODO: confirm capability wording with founder -->

This article is general information about commercial and spreadsheet practice, not tax, legal or accounting advice. Where a price-protection or compensation settlement is passed through a credit note, the GST treatment is a separate question — see price protection and rate-difference credit notes and confirm the position with a qualified professional.

Read next

• How to build a claim and scheme simulator in Excel — the workbook structure this model assumes.
• Price protection in sales — the clause, the triggers and the windows.
• The stock compensation rebate process — how compensation interacts with a slab rebate.
• How to calculate FMCG distributor claims — the other common claim types.
• Settlement factsheets for rebate claims — showing the working behind a holding-based claim.
• How to move rebate and claim work off spreadsheets — when the workbook stops being enough.

Book a demo to see how ClaimDS establishes the holding at an event date from your own transaction data.