The spreadsheet only the buying director understands
You have volume rebates with a brick manufacturer, a plasterboard supplier, an insulation brand and your buying group. Some pay on total spend, some on tonnage, some on growth over last year, some only on particular product families. There are tiers, retrospective steps and claim deadlines. The buying director keeps it all in a spreadsheet, updated quarterly from a purchase report.
Then the buying director is on holiday at the quarter end, the report misses the purchases from a branch that codes things differently, and a claim goes in late or not at all.
Why rebates are hard to track
The difficulty is not the maths. It is the matching. Each agreement defines what counts in its own terms, and your purchase data is not organised around those terms.
| Agreement detail | Why it goes wrong |
|---|---|
| Qualifying products | Product families don't match your product groups |
| Measure | Spend, units, tonnes or pallets, depending on the supplier |
| Tiers | Retrospective steps change the value of earlier purchases |
| Direct deliveries | Supplier-to-site orders may count but are coded differently |
| Claim deadline | Missed if the reminder is in someone's diary |
| Group agreements | Buying group terms overlap with direct ones |
Nobody sees the running position, so the business can't tell whether it is close to the next tier and should concentrate buying.
Staff changes make it worse. When the person who negotiated an agreement moves on, the reasoning behind their spreadsheet often goes with them, and the next person inherits formulas that nobody can explain.
What unclaimed rebates cost
Rebate income is often a large part of a merchant's margin. A missed claim is profit you earned and gave back. An inaccurate accrual makes your management accounts wrong until the true figure arrives. And not knowing where you stand against tiers means buying decisions are made without the information that would change them.
Rebate tracking that runs on your purchase data
- Each agreement is set up once: qualifying products or groups, the measure, tiers, periods, claim dates and who receives the claim.
- Purchase lines, including direct-to-site orders, are read from your merchant system daily and mapped to agreements. Lines that could belong to more than one are flagged for a decision.
- Running positions are calculated per agreement: what you have bought, which tier you are in, how far to the next one, and the accrual at today's position.
- Reminders go out ahead of each claim date, with a claim pack ready: qualifying purchases, totals and the calculation.
- When the rebate is received, it is matched to the claim so shortfalls show up.
- A monthly summary goes to finance for the accrual journal, in the format your accounts package takes.
The agreements themselves, and what they mean where the wording is unclear, are for your buying team and the supplier to settle. The system records the interpretation you choose and applies it consistently.
How the buying team works afterwards
The buying director opens a dashboard rather than a spreadsheet. Anyone in finance can see the accruals and the claims due. Near a tier threshold, buyers can see it and make a deliberate choice about where to place the next orders. Claims go in on time with the working attached, and underpayments are spotted.
Does this describe your rebates?
- Rebate tracking lives in one person's spreadsheet.
- Accruals are adjusted substantially when claims are settled.
- Direct-to-site purchases are not always counted.
- You have discovered a missed claim after the deadline.
- Buyers don't know how close you are to the next tier.