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.
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!
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.