Distributor Rebate Tracking Worksheet
Vendor rebates are real money that distributors regularly leave uncollected because nobody tracks claim deadlines or short payments. This worksheet gives each program a row, calculates what you have earned and shows what is still owed, so your purchasing and finance teams work from the same numbers.
| Vendor | Program | Period | Qualifying purchases | Rebate rate (%) | Rebate earned | Amount claimed | Amount received | Outstanding | Claim due date | |
|---|---|---|---|---|---|---|---|---|---|---|
$6,370 | $0 | |||||||||
$12,800 | $12,800 | |||||||||
$3,860 | $1,860 | |||||||||
$873 | $0 | |||||||||
$6,050 | $6,050 |
Rebate programs differ in how tiers, exclusions and timing work. Replace the sample rows with your own program terms and keep the vendor agreement alongside this sheet. Treat it as a tracking aid, and follow your finance team's accounting treatment for accruals.
Rebate leakage review
Optional: we can outline a read-only comparison of purchase history to rebate terms that finds unclaimed amounts.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Add one row per vendor program and period. Enter the qualifying purchases from your purchase history, the rebate percentage from the agreement, and the amount you have claimed and received. The sheet computes the earned amount and the outstanding balance. Sort by outstanding amount to chase the largest gaps first, and by due date to avoid missing a claim window. Export to Excel for monthly review with finance.
Qualifying purchases
The hardest part of rebate tracking is agreeing what counts. Agreements often exclude freight, taxes, returns, certain product lines or items bought on special pricing. Pull purchase history by vendor and date range, then remove exclusions before entering the figure. If your ERP records vendor rebates or contracts, reconcile the system's figure against the vendor's own statement. Differences between the two are a common source of short payments.
- Net out returns and credits
- Exclude freight and tax unless the agreement includes them
- Use the vendor's period definition, which may not match your fiscal month
- Keep the agreement version that applied to the period
Reading the summary
The blended rebate rate shows what you earn across all programs as a share of qualifying purchases. The collection rate shows how much of what you earned has actually been received. A low collection rate usually means unclaimed rebates or disputed amounts rather than a bad program. Review programs with an outstanding balance past their claim date, because many vendors enforce deadlines and will not pay late claims.
Common mistakes
Treating rebate income as earned when only a tier threshold has been hit in part is a frequent accounting error, so coordinate accruals with your controller. Another is missing tier step-ups: a purchase that pushes you into the next tier may retroactively raise the rate for the whole period. Finally, never rely on the vendor to compute and send the rebate. Track it yourself, and compare the vendor's number to yours every period.
Frequently Asked Questions
How do I track rebates that have tiers?
Create a row for the program and enter the rate that applies at current volume. Check the agreement for whether a higher tier applies retroactively to all purchases or only to those above the threshold, and adjust the rate and purchases accordingly.
Should I accrue rebates before receiving them?
Accounting treatment varies, and many companies reduce cost of goods sold as rebates are earned when collection is probable. Follow your finance team's policy and auditor guidance. This worksheet supports the tracking, not the accounting decision.
What if the vendor pays less than I calculated?
Compare their statement with your qualifying purchase figure, looking for excluded items, returns, or period differences. Keep the outstanding balance on the sheet until the difference is resolved or formally written off.
How often should I update it?
Monthly is a good rhythm, aligned to month-end close. Review claim due dates weekly in the last month of each period so deadlines are not missed.
If you want rebate earnings calculated automatically from your purchase history, we can build a read-only comparison between your ERP data and each vendor's program terms.
Related Tools
Prophet 21 Pricing Library Design Worksheet
Plan each price page: who it applies to, which product group, the cost basis and multiplier, and the resulting gross margin, before anyone keys it into the system.
Supply Chain & InventoryDistribution ERP Fill Rate Calculator
Calculate line, unit and complete-order fill rate from your ERP counts and see the revenue and gross margin put at risk by unfilled lines.
EpicorProphet 21 Inventory Replenishment Calculator
Work out order point, safety stock, EOQ and a max level for a distribution item, then compare against what your P21 replenishment parameters say.
Supply Chain & InventoryBranch Transfer Planning Worksheet
Decide how much stock to move between branches by comparing what the destination needs with what the source can spare, and see the transfer value.
More Supply Chain & Inventory tools
See all- Distribution KPI Formula Reference
- Distributor Pricing and Rebate Terms Glossary
- Distributor EDI X12 856 ASN Skeleton Generator
- Distributor ABC Inventory Classification Worksheet
- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator