Introduction
When transactional data is imported from an external ERP system into a data mart, information about the rebates is typically missing. But without taking rebates into consideration, the gross margin will be over estimated. Thus, unless rebate data is added to the transactional data mart, it will not be possible to get the full picture when analyzing the data with the PriceAnalyzer module.
...
Figure 2. Screenshot of a transactional data mart where the information about the rebates is included, and where the rebate is included in the calculation of the margin.
The Mathematics of
...
Rebate Allocation
Before it is possible to configure a rebate allocation, it is necessary to understand the mathematics behind the rebate component in the price waterfall. For this, consider a transaction t, which has a corresponding row in the transaction data mart.
As discussed in the introduction, customers may want to include the rebate in price waterfall, or include rebates in the calculation of pocket margins. Thus, a field in the transaction data mart is reserved for a transaction rebate amount trebate, which will hold the value of the rebate bar in the price waterfall.
Special Case — A Single Rebate
To figure out how to calculate the transaction rebate amount trebate, simplify the problem by considering the special case where each transaction is only associated to a single rebate r. Let rrebate denote the rebate amount (the amount of money which is paid back to the buyer).
...
trebate=tinvoicerrebaterΣinvoice (4)�rebate=�invoice�rebate�Σinvoice (4)
Interpretation
It is natural for the transaction rebate amount to be proportionate to tinvoice�invoice and rrate�rate: if the rebate rate increases, the transaction rebate amount must increase with it (linearly). And if the transaction invoice price increases, the transaction rebate amount must increase (linearly) too — because rrate�rate can be considered as a component of rrebate�rebate.
...
Since rebates are typically not linearly dependent on the transaction total invoice price, the rebate trebate�rebate can not be said to actually “exist” for the individual transaction; it is only derived in order to allow end-users to include rebates in price waterfalls, to calculate meaningful margins, and similar analyses. |
The General Case — Multiple Rebates
A single transaction can also be encompassed by multiple rebates. In this case, tmargin�margin is simply calculated as a sum over all the rebates that includes tinvoice�invoice in the rebate calculation. Let Rt�� denote the set of all rebates that the transaction t� is encompassed by. This results in
...
The factor tinvoice�invoice is independent of the rebate, and can thus be broken out of the sum. |
Implementing the Rebate Allocation
With (5), a calculation data load can be constructed that performs the calculation and stores the output back into the transactions datamart. The greatest challenge here is to find out which transactions are encompassed by which rebates — that is, which data mart rows are associated with which rebate records. To solve this problem, a Feeder Logic needs to be utilized.
Feeder
...
Logics
On the screen where logics can be configured, it is possible to select a feeder logic.
...
But how does the application determine whether a given transaction is encompassed by a given rebate record? This problem is solved with a so-called calculation base.
Calculation Base
Each rebate record has a property called calculationBase; which, in short, is a filter for rows in data marts. During the processing of a calculation data load job, the calculation base of the emitted rebate records are used to filter out rows from the transaction data mart. The rows that matches the calculation base filter will result in executions of the PriceAnalyzer logic in the row calculation context phase.
Click to see example
| Code Block |
|---|
The calculation base is be defined in a rebate logic during the rebate record calculation context phase, with the binding variable calculationBase. By default, the calculation base inherits some rebate record’s properties — such as: startDate, endDate, customerGroup and productGroup. Thus, many properties do not necessarily have to be set explicitly in the logic.
...
| Code Block |
|---|
calculationBase.clear() |
Allocation
...
Logic
With an understanding of how feeder logics and calculation bases can be used to associate rebate records with its transactions, it is now possible to construct the PriceAnalyzer logic that will perform the allocation. This logic is referred to as an allocation logic. The logic will perform the calculation according to the formula that was derived in the above section (see The Mathematics of the Rebate Allocation):
...
Figure 7. Datamart with where trebate�rebate is stored in the Rebate column.
Row Calculation Context Phase
Firstly, the transaction invoice price (tinvoice�invoice) and the incomplete sum (Rebate) will have to be read from the transaction row. The input parameters will also have to be declared during the Input Generation, otherwise the row values can’t be mapped to the logic inputs:
...
To improve readability, the mandatory null checks were omitted from the example. |
Init Calculation Context Phase
When the job enters the row calculation context phase, it assumes that the amount stored in the field Rebate is 0. But if the logic was executed at least one time previously, the Rebate field may already store a value greater than 0. Therefore, the execution of the row phase element must be preceded by the execution of an init calculation context element, which that resets the Rebate amount to 0 for all data mart rows.
...
1 | Iterate over all the transaction lines, in the source table. |
2 | Update the source row. The source rows must be updated, because these are the values that will be mapped to the |
3 | Those transactions that cannot be matched with a rebate record will be “skipped” by the allocation logic (i.e. the allocation logic will not execute in the row calculation context phase for these transactions). As such, for these rows, the row calculation context elements will not add rows to the target table. This means that these data mart rows would remain unchanged — unless they are updated in the init phase. |
Summary
For Pricefx solutions that involves the Rebate Manager, it is common to include data about the rebates in the transaction data mart. In a process that is often referred to as rebate allocation, a value that represents the transaction’s total proportional contribution to all rebates is added to the transaction row (in the data mart). This allows the rebates to be taken into account when calculating the pocket margin, and it allows the end-users to analyze the rebates in the PriceAnalyzer module. The amount can be expressed as follows:
...