Rebates Calculation Logic Examples
In this section we will show four examples of logics that calculate different types of rebates:
- Tiered rebate – based on quantities or amounts purchased.
- Stepped rebate – tiered rebate where the rebate is calculated separately for each tier.
- Growth rebate – based on a specified increase in purchases.
- Fixed rebate – not dependent on the purchased amounts.
See also the Rebate Variants section below for detailed explanation of the rebate types.
Tiered Rebate
The complete calculation logic will have the following elements:
Type | Element Name | Display Mode | Format Type |
---|---|---|---|
Library | Never | ||
RetainGlobal | Never | ||
CustomerGroup | Never | ||
ProductGroup | Never | ||
SalesTarget | Never | ||
ActualsQuery | Never | ||
ActualSales | Never | ||
TotalSalesRebate | Quoting | Money (EUR) | |
ASFFactor | Never | ||
SalesForecast | Quoting | Money (EUR) | |
RebateForecast | Quoting | Money (EUR) | |
RebateRecords | Never |
Library – Stores the actual calculation of the rebate.
// Tiered discount calculation function: when revenue falls in specific tier, %discount of that tier is applied. // If the base for the discount calculation is different from the actual, provide a discountBase argument. // Target values are % values (i.e. 5% is represented by 5 rather than 0.05) def calcDiscount(actual, tieredTarget){ return calcDiscount(actual, tieredTarget, null) } def calcDiscount(actual, tieredTarget, discountBase){ api.log("calcDiscount: actual", actual.inspect()) api.log("calcDiscount: target", tieredTarget.inspect()) if (actual != null && tieredTarget != null){ if (tieredTarget.size()>0){ for (i = tieredTarget.size()-1; i>=0; i--){ def tier = tieredTarget.get(i) def target = tier.target as BigDecimal def value = tier.value as BigDecimal api.log("Tier" +i + " target", target.inspect()) api.log("Tier" +i + " value", value.inspect()) if (target != null){ if (actual > target){ if (value == null) value=0 def discount = (discountBase == null? actual : discountBase) * value api.log(" Discount", discount) return discount } } } } } return 0.0 }
RetainGlobal – The usage of global variables must be explicitly enabled.
api.retainGlobal
can be set to true
by default in General Settings.
api.retainGlobal=true
CustomerGroup user input – Displays a field where users select the customer group for which the rebate is calculated.
def cg = api.customerGroupEntry() api.log("customer group", cg.inspect()) return cg
ProductGroup user entry – Displays a field where users select the product group for which the rebate is calculated.
def pg = api.productGroupEntry() api.log("product group", pg.inspect()) return pg
SalesTarget user entry – Displays a field where users can define the number of tiers and their thresholds.
def tieredVal = api.multiTierEntry("Revenue Target", "€","%") if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck // 5% --> 0.05 conversion if (tieredVal != null){ tieredVal = tieredVal.multiplyValues(0.01) } api.log("sales target", tieredVal.inspect()) api.global.target = tieredVal return tieredVal
Actuals – Datamart Query
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def line = api.currentItem() if (line != null){ // is null on input generation/formula save def customerGroup = api.getElement("CustomerGroup") def productGroup = api.getElement("ProductGroup") // actuals for the period specified in the rebate line item def timeFilter = api.filter("InvoiceDate", line?.startDate, line?.endDate) if (timeFilter != null){ api.datamartQuery("TX", "Transaction DM", customerGroup, productGroup, timeFilter, "InvoicePrice\$") } }
ActualSales – Datamart Lookup
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.datamartLookup("TX", "InvoicePrice\$") api.log("actual", sales.inspect()) api.global.actual = sales as BigDecimal return api.global.actual
TotalSalesRebate – Calculation of the rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual if (sales<0) sales=0 def rebate = Library.calcDiscount(sales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
ASF Factor – The expected growth of sales (example).
1.75
SalesForecast – Actual sales multiplied by the expected growth rate.
ActualSales*ASFFactor
RebateForecast – Expected rebate based on the forecasted sales.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual * api.getElement("ASFFactor") if (sales<0) sales=0 def rebate = Library.calcDiscount(sales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
RebateRecords – Creates a new rebate record for the calculated rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck rebateRecords.add()
Stepped Rebate
The complete calculation logic will have the following elements:
Type | Element Name | Display Mode | Format Type |
---|---|---|---|
Library | Never | ||
RetainGlobal | Never | ||
CustomerGroup | Never | ||
ProductGroup | Never | ||
SalesTarget | Never | ||
ActualsQuery | Never | ||
ActualSales | Never | ||
TotalSalesRebate | Quoting | Money (EUR) | |
ASFFactor | Never | ||
SalesForecast | Quoting | Money (EUR) | |
RebateForecast | Quoting | Money (EUR) | |
RebateRecords | Never |
Library – Stores the actual calculation of the rebate.
//Stepped discount calculation function: revenue falls in a specific tier, %discount of each tier up to this tier is applied to the part of the revenue. if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck // target values are % values (i.e. 5% is represented by 5 rather than 0.05) def calcSteppedDiscount(actual, tieredTarget){ api.log("calcSteppedDiscount: actual", actual.inspect()) api.log("calcSteppedDiscount: target", tieredTarget.inspect()) def cumulativeDiscount = 0.0 if (actual != null && tieredTarget != null){ if (tieredTarget.size()>0){ def unusedActual = actual for (i = tieredTarget.size()-1; i>=0; i--){ def tier = tieredTarget.get(i) def target = tier.target as BigDecimal def value = tier.value as BigDecimal api.log("Tier" +i + " target", target.inspect()) api.log("Tier" +i + " value", value.inspect()) if (target != null){ if (unusedActual > target){ if (value == null) value=0 def discountable = unusedActual - target def discount = discountable * value api.log("Discountable", discountable) api.log("Discount", discount) cumulativeDiscount += (discountable * value) api.log(" Cumulative discount", cumulativeDiscount) unusedActual = target } } } } } return cumulativeDiscount }
RetainGlobal – The usage of global variables must be explicitly enabled.
api.retainGlobal
can be set to true
by default in General Settings.
api.retainGlobal=true
CustomerGroup user input – Displays a field where users select the customer group for which the rebate is calculated.
def cg = api.customerGroupEntry() api.log("customer group", cg.inspect()) return cg
ProductGroup user entry – Displays a field where users select the product group for which the rebate is calculated.
def pg = api.productGroupEntry() api.log("product group", pg.inspect()) return pg
SalesTarget user entry – Displays a field where users can define the number of tiers and their thresholds.
def tieredVal = api.multiTierEntry("Revenue Target", "€","%") if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck // 5% --> 0.05 conversion if (tieredVal != null){ tieredVal = tieredVal.multiplyValues(0.01) } api.log("sales target", tieredVal.inspect()) api.global.target = tieredVal return tieredVal
Actuals – Datamart Query
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def line = api.currentItem() if (line != null){ // is null on input generation/formula save def customerGroup = api.getElement("CustomerGroup") def productGroup = api.getElement("ProductGroup") // actuals for the period specified in the rebate line item def timeFilter = api.filter("InvoiceDate", line?.startDate, line?.endDate) if (timeFilter != null){ api.datamartQuery("TX", "Transaction DM", customerGroup, productGroup, timeFilter, "InvoicePrice\$") } }
ActualSales – Datamart Lookup
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.datamartLookup("TX", "InvoicePrice\$") api.log("actual", sales.inspect()) api.global.actual = sales as BigDecimal return api.global.actual
TotalSalesRebate – Calculation of the rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual if (sales<0) sales=0 def rebate = Library.calcSteppedDiscount(sales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
ASF Factor – The expected growth of sales.
1.75
SalesForecast – Actual sales multiplied by the expected growth rate.
ActualSales*ASFFactor
RebateForecast – Expected rebate based on the forecasted sales.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual * api.getElement("ASFFactor") if (sales<0) sales=0 def rebate = Library.calcDiscount(sales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
RebateRecords – Creates a new rebate record for the calculated rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck rebateRecords.add()
Growth Rebate
The complete calculation logic will have the following elements:
Type | Element Name | Display Mode | Format Type |
---|---|---|---|
Library | Never | ||
RetainGlobal | Never | ||
CustomerGroup | Never | ||
ProductGroup | Never | ||
GrowthTarget | Never | ||
ActualsQuery | Never | ||
ActualSales | Never | ||
PreviousYearSales | Never | ||
GrowthRebate | Quoting | Money (EUR) | |
ASFFactor | Never | ||
SalesForecast | Quoting | Money (EUR) | |
RebateForecast | Quoting | Money (EUR) | |
RebateRecords | Never |
Library – Stores the actual calculation of the rebate.
//When increase to the pervious period's actual is higher than a given target percentage //target levels and values are % values (i.e. 5% is represented by 5 rather than 0.05) def growthBonus(actual, prevPeriodActual, tieredTarget){ api.log("growthBonus: actual", actual.inspect()) api.log("growthBonus: prevPeriodactual", prevPeriodActual.inspect()) api.log("growthBonus: target", tieredTarget.inspect()) if (actual != null && prevPeriodActual != null && prevPeriodActual != 0.0 && tieredTarget != null){ actual = actual as BigDecimal // to be safe (DatamartQuery can return doubles) prevPeriodActual = prevPeriodActual as BigDecimal def growth = 100* (actual - prevPeriodActual) / prevPeriodActual // as % api.log("growthBonus: growth", growth.inspect()) return calcDiscount(growth, tieredTarget, actual) } }
RetainGlobal – The usage of global variables must be explicitly enabled.
api.retainGlobal
can be set to true
by default in General Settings.
api.retainGlobal=true
CustomerGroup user input – Displays a field where users select the customer group for which the rebate is calculated.
def cg = api.customerGroupEntry() api.log("customer group", cg.inspect()) return cg
ProductGroup user entry – Displays a field where users select the product group for which the rebate is calculated.
def pg = api.productGroupEntry() api.log("product group", pg.inspect()) return pg
GrowthTarget user entry – Displays a field where users can define the number of tiers and their thresholds.
def tieredVal = api.multiTierEntry("Growth Target", "%","%") if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck //rebate % conversion if (tieredVal != null){ tieredVal = tieredVal.multiplyValues(0.01) } api.log("growth target", tieredVal.inspect()) api.global.target = tieredVal return tieredVal
ActualsQuery – Datamart query to find out actual and last year's sales.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def line = api.currentItem() if (line != null){ // is null on input generation/formula save def customerGroup = api.getElement("CustomerGroup") def productGroup = api.getElement("ProductGroup") // actuals for the period specified in the rebate line item def timeFilter = api.filter("InvoiceDate", line?.startDate, line?.endDate) if (timeFilter != null){ api.datamartQuery("TX", "Transaction DM", customerGroup, productGroup, timeFilter, "InvoicePrice\$") } // previous year (copy and paste for other past periods) def cal = api.datamartCalendar() def startDate = line?.startDate?.toString() // is a string def endDate = line.endDate.toString() // is a string if (startDate != null){ startDate = cal.add(startDate, -1, "YEAR"); } if (endDate != null){ endDate = cal.add(endDate, -1, "YEAR"); } timeFilter = api.filter("InvoiceDate", startDate, endDate) if (timeFilter != null){ api.datamartQuery("TX-1yr", "Transaction DM", customerGroup, productGroup, timeFilter, "InvoicePrice\$") } }
ActualSales – Datamart Lookup
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.datamartLookup("TX", "InvoicePrice\$") api.log("actual", sales.inspect()) api.global.actual = sales as BigDecimal return api.global.actual
PrevYearSales – Datamart Lookup
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.datamartLookup("TX-1yr", "InvoicePrice\$") api.log("actual", sales.inspect()) api.global.actualPrevYear = sales as BigDecimal return api.global.actualPrevYear
GrowthRebate – Calculation of the rebate based on the sales growth.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual def lastYearsSales = api.global.actualPrevYear if (sales<0) sales=0 if (lastYearsSales<0) lastYearsSales=0 def rebate = Library.growthBonus(sales, lastYearsSales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
ASF Factor – The expected growth of sales.
1.75
SalesForecast – Actual sales multiplied by the expected growth rate.
ActualSales*ASFFactor
RebateForecast – Expected rebate based on the forecasted sales.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck def sales = api.global.actual * api.getElement("ASFFactor") if (sales<0) sales=0 def rebate = Library.calcDiscount(sales, api.global.target) api.global.rebate = rebate as BigDecimal return api.global.rebate
RebateRecords – Creates a new rebate record for the calculated rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck rebateRecords.add()
Fixed Rebate
The complete calculation logic will have the following elements:
Type | Element Name | Display Mode | Format Type |
---|---|---|---|
CustomerGroup | Never | ||
ProductGroup | Never | ||
FixedRebate | Quoting | Money (EUR) | |
RebateRecords | Never |
CustomerGroup user input – Displays a field where users select the customer group for which the rebate is calculated.
def cg = api.customerGroupEntry() api.log("customer group", cg.inspect()) return cg
ProductGroup user entry – Displays a field where users select the product group for which the rebate is calculated.
def pg = api.productGroupEntry() api.log("product group", pg.inspect()) return pg
FixedRebate user entry – Displays a field where the user can enter a fixed rebate value.
UserEntry("Fixed rebate")
RebateRecords – Creates a new rebate record for the calculated rebate.
if (api.isInputGenerationExecution()) return //isInputGenerationExecution supported from version 10.0, in older versions use isSyntaxCheck rebateRecords.add()
Rebate Variants
The following types are only examples, in Pricefx you can set up any Condition Type you need.
Tiered Rebate
This is the simplest rebate type that is based on quantities or amounts purchased by the customer. The tiered rebate type defines several tiers with corresponding rebate percentages. The paid out rebate depends on the highest threshold level that has been reached.
Example: The final sales is $17,200 and the bonus is calculated based on three agreed tiers.
Threshold | Rebate Percentage | Threshold Reached | Calculation | Bonus |
---|---|---|---|---|
$10,000 | 1.0% | Yes | ||
$15,000 | 1.5% | Yes | 17,200 * 0.015 | $258 |
$20,000 | 2.0% | No |
Growth Rebate
The growth rebate type is based on a specified increase in purchases. Again, several tiers are defined and the paid out rebate depends on the threshold that has been reached.
Example: The previous year's sales was $14,000 and this year's sales has reached $17,200, so the increase is 22.86%. The bonus is calculated in the following way:
Threshold | Rebate Percentage | Threshold Reached | Calculation | Bonus |
---|---|---|---|---|
10% | 1.0% | Yes | ||
20% | 1.5% | Yes | 17,200 * 0.015 | $258 |
30% | 2.0% | No |
Stepped Rebate
The bonus is paid for each tier whose lower threshold has been exceeded but only from the value that is within the tier limits and the percentage specified for that tier is applied.
Example: The final sales is $17,200. The first tier has been reached and exceeded, so the bonus is paid from the full interval, which is $5,000. The second tier has been reached but not exceeded, so the bonus is paid only from the value above the $15,000 threshold, i.e. $2,200.
Threshold | Rebate Percentage | Threshold Reached | Unused Actual | Discount Base | Calculation | Bonus |
---|---|---|---|---|---|---|
$10,000 | 1.0% | Yes | 5,000 | 5,000 * 0.01 | $50 | |
$15,000 | 1.5% | Yes | 2,200 | 2,200 * 0.015 | $33 | |
$20,000 | 2.0% | No |
The total paid out rebate is $83.
Found an issue in documentation? Write to us.