- Promotions and settlements · TPM
- CFO / Finance, Accounting / Analytics, Operations Manager
- Retail and branch networks, Manufacturing, Logistics and warehousing
Quarterly rebate settlement template: a printable sheet and the steps behind it
In short
A quarterly rebate settlement is the routine in which a supplier checks purchases against agreed thresholds, calculates the rebate due and approves the payment or credit. A good settlement sheet records the customer, period, base, tier reached, rate, amount and approvals so another person can repeat the calculation. There is no single official form, so use a consistent internal sheet. Ask your accountant how the rebate is booked.
Why quarterly settlement goes wrong
Most rebate problems appear in the first week after quarter end. Sales data comes from one system, the agreement lives in a folder, returns arrive late and the person calculating the rebate holds the rules in their head. The result is a number nobody can reproduce, and a customer who calls to ask why it differs from their own.
A settlement sheet makes the calculation a record. It forces the same questions every quarter, and it gives finance a document to approve instead of a figure in an email.
Printable settlement sheet
QUARTERLY REBATE SETTLEMENT no. ……… / ………
Prepared on: ……………………………… by: ………………………………
| Customer (legal name) | …………………………………………… |
|---|---|
| Agreement no. and version | …………………………………………… |
| Period | from ……………… to ……………… |
| Related companies included | …………………………………………… |
| Data source and extraction date | …………………………………………… |
| Line | Value |
|---|---|
| Gross purchases in the period | |
| Less: returns and credit notes | |
| Less: excluded items (list below) | |
| Rebate base | |
| Tier reached | |
| Rate | |
| Rebate due | |
| Advances or earlier credits already given | |
| Amount to settle now |
Excluded items and reason: ……………………………………………………………………………
Form of settlement: credit note / payment / other: ………………………… Due date: ………………………
| Prepared by | Checked by (finance) | Approved by |
|---|---|---|
How to fill it in
- Identify the agreement and its version. If terms changed mid-year, record which version applies to this period.
- Record the data source and the date of extraction. Late returns change the figures. The date shows which data was used.
- Calculate the base. Start from gross purchases and subtract returns, credit notes and items excluded by the agreement. List the exclusions so a reader can check them.
- Find the tier. Compare the base with the thresholds. Write the tier and the rate, not only the result.
- Apply the rate as the agreement says. If the rate applies to all purchases, multiply the base. If only above the threshold, calculate the part above.
- Subtract earlier advances. Quarterly settlements often follow earlier partial credits. Show them so the amount to settle is clear.
- Route for approval. One person prepares, a second checks, and an approver signs. Do not let one person do all three.
A five-day routine after quarter end
| Day | Task | Owner |
|---|---|---|
| 1 | Freeze the sales data and extract purchases per customer | Sales operations |
| 2 | Add returns and credit notes, apply exclusions | Sales operations |
| 3 | Calculate the rebate for every customer with an agreement | Sales operations or finance |
| 4 | Check a sample and all large or unusual amounts | Finance |
| 5 | Approve, issue credits or payments and archive the sheets | Finance, approver |
The schedule is an example. Adjust the days to the number of customers, but keep the order: data, adjustments, calculation, check, approval.
Handling corrections after settlement
Returns and invoice corrections sometimes arrive after the sheet is approved. Decide the rule in advance. One approach is to settle on the data as it stood on the cutoff date and carry later corrections into the next quarter's sheet as a separate line. Another is to reopen the sheet if the correction exceeds a set amount. Either works, if it is written down and applied to every customer in the same way. Customers notice when one is treated differently from another.
Whichever rule you choose, keep the corrected sheet and the original together. The history of changes is what resolves a dispute quickly.
What to check before approval
- Is the agreement the current version?
- Does the base match the sales report after returns?
- Is the tier consistent with the thresholds in the agreement?
- Are related companies included if the agreement covers them?
- Were earlier advances subtracted?
- Does the amount look reasonable against the previous quarter?
Common mistakes
- Recalculating from memory. Without a sheet, the rules change slightly each quarter.
- Using different data cutoffs. The customer's report and yours must use the same dates.
- Forgetting related companies. A group threshold is not reached if part of the group is missing.
- No archive. A dispute six months later needs the sheet, the data and the agreement together.
- Settling and accruing separately. The accrual during the quarter should match the final figure, within a reason you can explain.
There is no single official form for such a settlement. Use a layout that fits your agreements, and ask your accountant how rebates are recorded in your books.
Reducing the manual work
A sheet per customer works for a few agreements. With dozens of customers, several tiers and group conditions, the calculation becomes the bottleneck. In Remanently Trade Marketing, turnover can be imported from Excel, bonuses with thresholds are defined in the system and, in the QUICK START scope, the fulfillment of conditions and tiers is calculated from sales data. Reports show the settlement of terms and target fulfillment. The sheet above remains useful as the approval document.
For the wording of the agreement the sheet applies, see volume rebate agreement. For tier design, see bonus for purchase structure. For the system perspective, read rebate management software and trade promotion management. Details are on the Trade Marketing page, and you can book a demo with your own quarter's data.
Frequently asked questions
Is there an official form for rebate settlement?
No single official form applies. Use a consistent internal sheet that lets another person repeat the calculation, and ask your accountant how the rebate is booked.
Who should approve a rebate settlement?
Separate the roles: one person prepares, a second checks and an approver signs. The same person should not do all three.
How soon after quarter end should settlement be done?
As soon as the data is final. A routine of about a week works for many teams, but the main point is a fixed sequence of data, adjustments, calculation, check and approval.
What data do I need for the calculation?
Purchases per customer for the period, returns and credit notes, the list of exclusions, the agreement with its thresholds and rates, and any advances already given.
Can the calculation be automated?
Yes. In Remanently Trade Marketing, turnover can be imported and, in the QUICK START scope and above, the fulfillment of conditions and tiers is calculated from sales data.
Settle one quarter on your own data
Bring last quarter's sales and one or two agreements. In a short demo we run the calculation in Remanently Trade Marketing and compare it with your sheet.
Module: Remanently Trade Marketing (TPM) · Pricing · Contact · Get started