Employee needs to get a new salary on specific date in Salary Forecast Model

johan.marketoft
johan.marketoft
in Modeling

Hi,

 

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.

 

Best regards,

Johan

Tagged:

Answers

  • DavidSmith
    DavidSmith

    @johan.marketoft 

    Have you taken the new L1 training? - we cover the exact use case you describe in that training

    David

  • johan.marketoft
    johan.marketoft

    Hi @DavidSmith,

     

    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!

     

    Best regards,

    Johan

  • NB
    NB

    Hi Johan,

     

    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.

     

    Indexation:

    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.

     

    Thanks

    Naveed

     

Categories