Volume Commitment & Rebate Modeller – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Value a tiered rebate at what it will actually pay across your own volume scenarios, and see the breakage against the headline rate the deal is announced at.
2. Handle retrospective and incremental rebate bases separately - they are routinely confused and one is worth roughly half the other.
3. Calculate the break-even volume on a commitment, below which the committed price plus the shortfall charge costs more than buying without one.

PRICING STRATEGY EXCEL DESCRIPTION

Volume Commitment & Rebate Modeller is an Excel template (XLSX) available for immediate download upon purchase.

A tiered rebate is quoted at its top tier and earned at its expected tier.
The difference is breakage, and the supplier priced the arrangement
knowing you would land in it. A volume commitment is agreed on a forecast
and paid for whether the forecast arrives or not.

Both structures are common in indirect categories. Both are announced
internally at their headline number. Both are modelled far less often than
they should be.

THE REBATE SHEET

Enter the tiers exactly as offered, including the thresholds, then three
volume scenarios with the probabilities you privately believe rather than
the ones in the business case. The model finds the tier each scenario
reaches, calculates the rebate earned under that scenario, and produces a
probability-weighted expected rebate in money and as an effective discount
percentage.

Breakage is then stated explicitly. In the worked example a headline five
per cent becomes an expected two and a half. That expected figure, not the
headline, is the number to compare against a simple unconditional price
reduction – and a smaller certain discount frequently beats a larger
conditional one, because it is certain and because it does not distort
buying behaviour at year end.

The sheet also handles retrospective and incremental bases separately. A
retrospective rebate pays the tier rate on total spend once a threshold is
passed; an incremental one pays only on the spend above it. The schedule
is often ambiguous about which applies, the two are worth materially
different amounts, and resolving that ambiguity before signature is worth
doing on its own.

THE COMMITMENT SHEET

The same problem from the other side. Enter the committed volume, the
price with the commitment, the price without it, and the shortfall charge
per unit not taken. The model shows the total cost with and without the
commitment at five volume outcomes – at plan, ten, twenty and thirty per
cent below, and ten per cent above – and highlights in red every case
where the commitment loses.

It then calculates the break-even volume, and expresses it as a share of
the commitment. That percentage is usually what stops the conversation.
Above roughly ninety per cent, a single site buying locally, one cancelled
project or one acquisition arriving with its own supplier puts you under
water. In indirect categories you rarely control the volume you have just
promised, and the sheet closes with the four questions to answer before
signing anything of the kind. WHO THIS IS FOR

Category managers and procurement leads who negotiate commercial terms and
have to defend the outcome to a finance function. Consultants who need a
defensible model in the first week of an engagement. Finance business
partners who validate what procurement claims. It assumes professional
judgement and supplies the arithmetic, not the other way round.

HOW THE FILE IS BUILT

One Excel workbook, self-contained. A read me sheet stating what the model
does, how to use it and what it is not. The working sheets, with a worked
example filled in throughout and designed to be overwritten. A licence
sheet.

Every cell is unlocked. Every formula is visible and editable. Nothing is
password-protected, because a model a professional cannot adapt into their
own situation is worth nothing. The colour convention is consistent across
the whole catalogue: a yellow fill with blue text is an input you supply,
black text is a formula, green text is a reference to another sheet.

WHAT IS DELIBERATELY NOT IN IT

No benchmarks. No market prices, no index forecasts, no industry margin
ranges, no assumed cost of capital held as a constant. Every field of that
kind is an input, left empty or left as an example, and labelled as an
input. A fabricated benchmark is worse than no benchmark, because a
fabricated number gets reported and then defended.

This is educational and commercial material. It is not legal, tax,
financial or accounting advice and it creates no professional
relationship. Where the subject touches contract wording, classification
or regulation, the file says plainly where your own adviser has to take
over.

LICENCE

Use inside one organisation by up to ten named users, with unlimited
internal adaptation. No resale, redistribution or use as the basis of a
training or consultancy product supplied to third parties. The author
terms inside the file prevail over any more permissive default marketplace
terms.

Source: Best Practices in Pricing Strategy, Procurement Strategy Excel: Volume Commitment & Rebate Modeller Excel (XLSX) Spreadsheet, Davide Sferrazza