Hello Pigment Community,
I need your help on calculating merit increase for existing employees.
To give you more context, our merit increase calculation is made of:
- 1. The annual package of an employee → this is the basis for our calculation.
- 2. The merit increase % : it will be applied to the annual package and is cumulative through the years.
- 3. The merit increase eligibility: we calculate it based on the start date of the employee.
- 4. The timing / effective date of the merit increase: it is the month the merit increase will be effective.
We are currently stuck on the last part, which is the timing of the merit increase.
I will explain quickly each component below and will explain more in details why we are stuck on the effective date and what we try to achieve.
1 : The annual package of an employee.
Let’s say we have an employee making 40,000 EUR a year.
We have a metric in which we have 40,000 EUR on every month for this employee.
2 : The merit increase %.
It is defined by year and it is cumulative.
Here we input 4% of merit increase for each year, and then we calculate what is the cumulative % that we need to apply on these 40,000 EUR.
3 : The merit increase eligiblity.
We calculate it by checking if the employee started before a given number of months.
4 : The timing / effective date of the merit increase.
This is where we need your help.
In our current formula we say:
IF(Merit Increase Eligibility = TRUE,
Annual package * Merit Increase% cumulated [BY CONSTANT: Month.Year],
Currently, we are just testing if the merit increase eligibility is true, and if it is, we are applying the merit increase % that we defined for the year.
In this formula, the effective date of the merit increase is the beginning of the year by default as we are doing a [BY CONSTANT: Month.Year].
I want to improve that formula and to be able to also set the timing / effective month of the increase.
I created a metric “Month of Increase” where I can chose “January” , “February”, etc. for each year.
Could you please help us integrate this effective date in our formula for each year?
One of the complexity of it, is that if we chose to set the increase in FY25 in June, we still need to apply the % of FY24 to FY25 salaries until June, and then take into account FY25 increase starting in June.
Please let me know if you need any detail.
Best answer by GawainView original