on 2023 Mar 30 12:24 PM
Hi
We have a requirement to calculate Bonus/Variable Pay taking into account not only the time spent by a person on a bonus plan/scheme in a calendar year but also incorporating the time/absence that was characterised as Unpaid Absence/Leave or relevant for Bonus proration. SF out of box offering does a simple proration based on the duration of time spent on Bonus Scheme and not on Absence/Leave Types.
A Sample scenario as follows:
Scenario ( Service Proration-Yes and Absence Proration-Yes, AI Target Change-Yes)
BONUS SCHEME-1
Effective Date: 01st January 2021 to 14th April 2021
Base Salary (effective as of 31st December)(including FTE)=105000 EUR
AI Target=5%
Service Proration=104days /365 days=28.49%
Absence Proration/Presence Factor=85% ( This means out of the 104 days of service proration for this bonus bonus scheme person has been on certain leave/absence types for 15% of 104 days (16 days) which need to be discounted for calculating the actual days worked)
On Target Amount=105000*0.05*0.2849*0.85=1216.8791 EUR
Effective Date: 01st January 2021 to 14th April 2021
Bonus Scheme1-Part A
Weighing=50%
Business Performance=95%
Part A Realisation=1216.8791 *0.5*0.95=578.0176 EUR
Effective Date: 01st January 2021 to 14th April 2021
Bonus Scheme 1-Part B
Weighting=50%
Business Performance=120%
Part B Realisation=1216.8791 *0.5*1.2=730.1275 EUR
BONUS SCHEME-2
Effective Date: 15th April 2021 to 31st December 2021
Base Salary (effective as of 31st December)(including FTE)=105000 EUR
AI Target=10%
Service Proration= 261/365=71.51%
Absence Proration/Presence Factor=60%
On Target Amount=105000*0.10*0.7151*0.6=4505.13 EUR
Effective Date: 15th April 2021 to 31st December 2021
Bonus Scheme2-Part A
Weighing=50%
Business Performance=95%
Part A Realisation=4505.13 *0.5*0.95=2139.9368 EUR
Effective Date:15th April 2021 to 31st December 2021
Bonus Scheme2-Part B
Weighting=50%
Business Performance=120%
Part B Realisation =4505.13 *0.5*1.2=2703.078 EUR
Total system calculated Bonus = 578.0176 + 730.1275 +2139.9368 +2703.078 =6151.1599 EUR
Summary of the requirement is as follows:
1. To calculate the Absence/Presence Factor for each of the staff based on relevance of these Absence Types/Leave types for Bonus Proration. The Absence/Presence Factor should be available in the System at any point in time so that correct Bonus proration is possible.
2. The Absence/Leave Types that are relevant for Bonus proration differ from country to country. For eg in Country A, Absence Type 1 and Absence Type 2 are relevant for Bonus proration and for Country B, only Absence Type 3 is to be taken into account for Bonus proration
3. Third, in a few countries there is a threshold limit that dictates when the relevant Absence types are to be taken for Bonus calculation. For example in Country A, if Absence Type 1 is relevant for Bonus proration then it will only be counted if the Absence Type 1 is taken for 10 or more days. If the Absence Type 1 is taken upto 9 days then there will be no Absence/Presence proration and Presence factor will be considered as 100%. This threshold limit will differ based on Absence Types on a country-basis.
Look forward to your thoughts on how to achieve this.
Thanks
R Kumar
Request clarification before answering.
Hi Ritesh,
We dealt with a similar issue: reducing the standard proration/assignment % based on leave codes.
These are the general steps we followed to get the Absence % impacting our proration:
I hope my experience can be useful/helpful to you. I think I saw a proposed idea to have a standard functionality to manage these, as it's a common requirement in multiple companies.
Regards,
Fernando
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Fernando
Apologies for bothering you on this.
Could you please help elaborate Look up table part with some sample examples? Essentially if have people spread over say five countries with different Leave Types(LOA) relevant for AI/Bonus proration, then the Input will be Country and LOA combination say Spain and Leave Type-X(Spain) and for the rest of the 4 countries with relevant combination of Leave Types(LOA) combination. As far as output is concerned, I am not very sure about the way or how the FACTOR and CONSTANT output will come into play for each individual employee. The part about FACTOR being <= 1.0 is clear. Not sure but are you able to share some sample made up scenarios to explain the solution? Thanks a ton for your help. Ritesh
Hi Ritesh,
Glad to help. It would be best to build the LU table based on your requirements. If, by default, your LOA field is empty, the logic in your formulas should exclude those assignments from being evaluated.
All possible combinations must match against the LU table for those assignments with LOA codes. Use the default functionality in the LU tables to nullify the proration (make it zero) for non-valid/unmapped combinations to keep controlled your LU table size and reduce the impact on the processing performance.
The adjusted proration can never be larger than the original proration, standard value; hence, FACTOR must always be lower than or equal to 1.00. The same restriction applies when using CONSTANT to replace the actual proration for a constant amount of calendar days instead. That's why the result of "FACTOR x $original_proration + CONSTANT/365" has to be compared and limited according to the $original_proration value.
Now, regarding the made-up scenario:
LU Table Contents
COUNTRY|LOA_CODE |FACTOR|CONSTANT
Spain |TypeX_SPA| 1.00 | 0.00
India |TypeX_GEN| 0.50 | 0.00
Italy |TypeY_ITA| 0.00 |10.00
Japan |TypeX_GEN| 0.00 | 5.00
* | * | 0.00 | 0.00<br>
According to the matching inputs: (1) The first record should keep the original proration without altering its value; (2) the 2nd record will reduce the proration to 50% of its original value; (3) the 3rd one should replace whatever proration comes from the history with 10/365; (4) the 4th one will replace the proration with 5/365; and (5) by default, any unmatching combinations will have 0.00 proration (FACTOR=0, CONSTANT=0). After this, the adjusted value should be limited to the minimum between this calculation and the original proration. The final proration should be the entry, a summarized version of this final calculation.
I hope I've been clear enough with my explanation.
Thanks very much Fernando. The illustration and explanation indeed help. I assume when you say the 2nd record will reduce the proration to 50% it means that based on the Bonus scheme start and end date(Assignment) and Leave TypeX_GEN during the duration of the bonus scheme; the system after seeing the person was on this leave type for half the duration of the bonus scheme(Assignment), the output of the FACTOR value should be 50% and so on and so forth upto 100%. This may be the case for one employee. We may have multiple employees who have been on say this leave type(TypeX_GEN) for certain durations during the bonus scheme. What I am not able to understand is how does this LU table store different FACTOR for multiple employees for prorating their bonuses. Are we saying that we may need to have another input parameter in the LU table- User ID?
Could you please also help explain how the 3rd and 4th one i.e CONSTANT will work. Sorry but I am not able to fully understand how the CONSTANT is working.
Ritesh
User | Count |
---|---|
15 | |
9 | |
7 | |
4 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.