# Distributor Rebate Tracking Worksheet > Source: https://netray.co/tools/distributor-rebate-tracking-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Track vendor rebate programs by period: purchases, rebate rate, amount earned, claimed and received, and what is still outstanding. ## Category supply-chain ## Industry Wholesale Distribution ## Audience - Purchasing manager - Controller - Vendor manager ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Vendor **Key:** vendor **Type:** text #### Program **Key:** program **Type:** text #### Period **Key:** period **Type:** select **Options:** - Monthly - Quarterly - Semi-annual - Annual #### Qualifying purchases **Key:** purchases **Type:** number **Format:** currency #### Rebate rate (%) **Key:** pct **Type:** number #### Rebate earned **Key:** earned **Type:** number **Expr:** purchases * pct / 100 **Format:** currency #### Amount claimed **Key:** claimed **Type:** number **Format:** currency #### Amount received **Key:** received **Type:** number **Format:** currency #### Outstanding **Key:** outstanding **Type:** number **Expr:** purchases * pct / 100 - received **Format:** currency #### Claim due date **Key:** due **Type:** date **Rows:** **Vendor:** Northfield Fasteners **Program:** Volume tier 2 **Period:** Quarterly **Purchases:** 182000 **Pct:** 3.5 **Claimed:** 6370 **Received:** 6370 **Due:** 2026-10-31 **Vendor:** Apex Bearings **Program:** Growth rebate **Period:** Annual **Purchases:** 640000 **Pct:** 2 **Claimed:** 0 **Received:** 0 **Due:** 2027-01-31 **Vendor:** Summit Hydraulics **Program:** Stocking incentive **Period:** Quarterly **Purchases:** 96500 **Pct:** 4 **Claimed:** 3860 **Received:** 2000 **Due:** 2026-10-31 **Vendor:** Delta Electrical **Program:** Prompt pay **Period:** Monthly **Purchases:** 58200 **Pct:** 1.5 **Claimed:** 873 **Received:** 873 **Due:** 2026-11-10 **Vendor:** Ridge Tool Supply **Program:** Mix and match **Period:** Semi-annual **Purchases:** 121000 **Pct:** 5 **Claimed:** 6050 **Received:** 0 **Due:** 2026-11-15 **Totals:** #### Total qualifying purchases **Key:** purchases **Agg:** sum **Format:** currency #### Total rebate earned **Key:** earned **Agg:** sum **Format:** currency #### Total rebate received **Key:** received **Agg:** sum **Format:** currency #### Total outstanding **Key:** outstanding **Agg:** sum **Format:** currency **Summary:** #### Blended rebate rate **Key:** blended **Expr:** sum_earned / max(sum_purchases, 1) * 100 **Format:** percent #### Collection rate **Key:** collected **Expr:** sum_received / max(sum_earned, 1) * 100 **Format:** percent **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use the worksheet **Body:** 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 **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### How do I track rebates that have tiers? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms distributor rebate tracking spreadsheet, vendor rebate tracking template, supplier rebate tracker excel, p21 vendor rebates, eclipse rebate tracking, rebate accrual worksheet, volume rebate tracker, purchase rebate reconciliation --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json