Here is the next post (following the previous one) dealing with price allocation, focused today on median price usage. After using the Revenue Type (Essential, Nonessential and Post contract support), we are going to explain how works the median price algorithm. Be prepared, it’s far from easy.
First, you need to disable the « Enable discount allocation method » checkbox in the Global ledger parameter of the Revenue recognition module, or force to Yes the « Apply residual method » in the sales order header.
According to item setup, « Is revenue allocation active » has to be ticked as it makes all the price allocation policy available, but you have to activate the « Median price » checkbox. About Minimum and Maximum tolerance, we’ll deal with that later on.
The Revenue schedule used for the current demo is based on a 3 occurrences schedule : this will have impacts for what’s next to come.
Like we did before on the previous post, I’ve created a sales order, adding the 3 items for a sales price of 1000, 100 et 150 USD (without any discount this time).
I ran the confirmation, and then analysing the results in the Revenue price allocation form.
Allocation price done
Well well… Let’s start by the easy part. The unit price and Total, we retrieve the price applied on the sales order lines.
The allocation price retrieves the price setup on the Revenue allocation price form.
But what the hell has happened for explaining the -304.06 allocation amount on the first line and the split of 960.34, 126,44 and 63,22 in the Revenue to recognize column for the 3 items ?
Let’s start the tricky explanation.
First thing : the Revenue type does not interfer here. What’s really happened is described in the Excel spreadsheet below.
3 items in the sales orders, so the algorithm is run 3 times and the Revenue to recognize is updated 3 times.
Iteration 1 : focus on the first item
For the Laptop, the system calculates the difference between the List price and the Allocation price. If positive (which is), the ratio between the List price / ∑ Liste price * Difference between List price and allocation price is calculated. In our case, the result is 434.78. Adding to the Allocation price, the result is 1434.78.
The system is doing the same for the 2 other items, using the difference between the List price and Allocation price of the first item.
If the difference would have been negative, the result would have been the same times -1. (x-1).
Iteration 2 : Focus on the second item
Here is the same calculation that applies but with a difference. Instead of comparing the List price against the Allocation price, the system compares the new value of Revenue to recognize minus Allocation price for the second item…. Which results of -128.26.
From there, the same calculation is applied than iteration 1.
Iteration 3 : Focus on the last item
The algorithm is moving on as many times as needed depending of the number of sales order lines. In my example I have 3 lines. At the end, the total calculated to recognize is 1454.06 USD. Don’t forget we need to have the same total as ordered, meaning 1150 USD. For some (strange) reason, the system is making the adjustement for the first item, that’s why the Allocation amount of the first item is here with -304.06 USD.
Important notes : the quantity and number of occurrences have also some impacts on the result. Here I’ve taken a simple test case with a quantity of 1 for each item, and only 3 occurrences for the periods (number of occurrences of the sales order = number of occurrences of the Revenue schedule = 3).
For the first item, if I’m changing the occurrences to 2, by updating the end date, we would have this result :
Clicking then on Revenue price allocation :
The result is the following : for the first iteration, the Allocation price field has been adjusted by the rate : number of applied occurrences / number of occurrences setup on the Revenue schedule (e.g 2/3).
Usage of tolerance
You can use the minimum and maximum tolerance for each item. If the List price is between the Min and Max tolerance against the Allocation price, the algorithm is not triggered.
In detail, the following conditions describe when the algorithm calculation is triggered :
– List price < Allocation price – Minimum tolerance
– List price > Allocation price + Maximum tolerance
To illustrate this, I’ve setup a 1000% tolerance for the Minimun and Maximum on the Warranty and Support item here. When running the confirmation I’ve got this :
In other words, the system has calculated only the first iteration of the previous example. The 2 other items (Warannty and Support) have not been taken into account for the allocation price calculation.
That’s it for the technical explanation. For a business use case on this, not sure in France this will have some usage. Maybe more on the US market, but if someone has ideas, comments are here for that below.