Bonus/Variable Pay Proration including Absence/leave types

[RRKUMAR]

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: 01^st January 2021 to 14^th 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: 01^st January 2021 to 14^th 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: 01^st January 2021 to 14^th 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: 15^th April 2021 to 31^st 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: 15^th April 2021 to 31^st 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:15^th April 2021 to 31^st 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

[Fernando_Silva]

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:

1. Adding/Mapping [Country] and our custom Leave of Absence code ([LOA]) field from jobInfo as part of the background element fields tracked by VarPay. Luckily, we already had the LOA code available in JobInfo!
2. Create a Lookup table with two inputs ([Country] and [LOA]) and two outputs ([Factor] and [Constant]) with the combinations of inputs that should be recognized (which could change by country). The [Factor] will proportionally reduce the assignments' standard proration percentage (ranging between [0,1]). The [Constant] number of days will substitute the assignment's original calendar days (e.g. 'recognize only the first five days of absenteeism for this [Country]&[LOA] combination'). It is always either a [Factor] or a [Constant] (never both). [Factor] should always be <= 1.0, and the final/default record at the end ("*") for no matches should be zero for both outputs: all unmapped combinations' prorations are nullified (converted to zero).
3. Create assignment-level custom fields to [1] Bring the required [Country] and [LOA] from the history file, [2] bring back the outputs ([Factor] and [Constant]) of the LU table we created, [3] Formulate the impact of the [Factor] and [Constant]: if [LOA] is blank, use the standard assignmentProration field as a result, if not, do [Factor]* assignmentProration + [Constant] / 365. 365 is a hard-coded parameter to adjust on each leap year. [4] The final adjusted proration to use will be the minimum value between the assignment proration and the calculation we just did ([3]).
4. You can associate an entry-level field as [4]'s aggregate to see each employee's total or use the assignments' results according to your requirements.

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

[RRKUMAR]

Thanks so much Fernando for such an elaborate explanation. I am analysing your solution and will come back to you soon with any questions, if any. Kind regards Ritesh

[RRKUMAR]

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

[Fernando_Silva]

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.

[RRKUMAR]

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

[mattpreece]

Hi, excuse me if this is a silly question, but how does the factor get applied to the proration percentage?