Employee needs to get a new salary on specific date in Salary Forecast Model
The employee list consists of all current employees in the company and I have their salary among other informaiton in a DATA module. I have created a Salary Forecast based on FTE, Salary, Start Date and End Date. The client wants to be able to select a certain date for a new salary. Is the most convenient way to get this new salary into the forecast by including a formula like "if new salary date passed then include that one as well"? Or should is it more common to build an import function based on date?
Similar additional question - they also want to have a percentage salary increase for entire company regardless of person. For example once a year 3% increase. Would be great on input on how to also do this / same way or different.
Have you taken the new L1 training? - we cover the exact use case you describe in that training
Thanks for replying, the L1 training has workforce planning yes but they don't have this uplift aspect to the salary as far as I can see? Anyways, I managed to resolve this with a if then else formula so all good!
New employee salary:
I am assuming the "Data Module", is not dimensioned by time.I would suggest creating two modules dimensioned by Employees, Time, and versions (if required).
1. Input Module: For example named as "New employee salary": This could be model user input driven and can also be import driven ( you would have to build the import action).
2. Calculation module: This would compare base salary with the new salary and use the higher of the two to calculate cost.
I would suggest creating two modules dimensioned by Time and versions (assumptions around indexation may differ from version to version).
1. Input module: % formatted line item that model users can input the desired indexation in the relevant month.
2. Calculation module: This module could have two line items
A. Number formatted line item: 1+ the indexation rate from the input module
B. Number formatted line item: Indexation factor calc: Using a conditional if statement along with previous, if the factor needs to be compounded, to arrive at an indexation factor. (For e.g. if IF A * PREVIOUS(A) = 0 THEN A ELSE A * PREVIOUS(B))
Finally multiply Line item B from above with the base salary.
This should allow increase in base salary at a point in time as well as indexation to be applied globally to all employees.
Note: You might have to add additional logic around not applying indexation to employees that have been given a new base salary over and above the indexation assumption, else they will receive an uplift along with a higher base salary.