View original (French)

Apprentice salary calculator tool

kinedimo Posted messages 26 Registration date   Status Member Last intervention   -  
kinedimo Posted messages 26 Registration date   Status Member Last intervention   -
Hello everyone,

I work in human resources and I would like to create a tool to calculate an apprentice's salary by entering certain data.

Here is the 2012 apprentice scale.

Age of the youth | 1st year | 2nd year | 3rd year

Under 18 years: 25% 356.42 | 37% 527.50€ | 53% 755.60€

From 18 to 21 years: 41% 584.52€ | 49% 698.58€ | 65% 926.68€

21 years and over: 53% 755.60€ | 61% 869.66€ | 78% 1112.02€

I would like to create a table in which I will enter the apprentice's birth date, the start and end dates of the contract, and the software would calculate what would be his salary in the 1st year, the 2nd year, and the 3rd year, if applicable.

Since I'm not a specialist in Excel, I have very little or no command of formulas.

Thank you very much for your help.

Configuration: Windows 7 / Firefox 14.0.1

9 answers

  1.
    kinedimo Posted messages 26 Registration date   Status Member Last intervention   6
     
    thank you again tichacha for taking the time .... i didn’t expect so much ... you did a wonderful job, THANK YOU

    have a good evening.
    4
  2. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Hi kinedimo !

    It took me many hours spread over a dozen days, but I’m pleased to finally present to you the tool you wanted:
    https://www.cjoint.com/?BHEgRndLJDN

    Just one sheet, no macro: You enter three pieces of information, the birth date, the hiring date and the minimum wage amount, and you see the three (or four) base salary ranges display. In fact, only columns A and B matter to you.
    I didn’t hide the rest so you can have fun seeing how the logic is built, how the calculations are performed and how the results are derived.

    There’s no password, but I strongly advise you not to modify anything... The 13 scenarios (age at entry into apprenticeship) are all taken into account.

    * If someday you have an apprentice who directly enters the second year, you cut one year from their start date and start paying them as if they were in the second year.

    Best regards to Eric, to tichacha and to you.
    --
    It’s nice, retirement! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    1
  3. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Hello everyone.

    And here is a lighter version of the same file: https://www.cjoint.com/?BHFgtexIU91

    --
    It’s great, retirement! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    1
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    And if you indicated how you calculate it?
    We don’t know what to do with your percentages and your sums...
    And 21 years exactly, is it case 2 or case 3?

    eric

    --
    You will never respond to an unsolicited message...
    Well, that’s done.
    0
    1. tichacha14 Posted messages 302 Status Member 56
       
      Hello Eric

      for your information, an apprentice's salary is based on the gross minimum wage (hence the %).
      actually kinedimo gave us the values in order: for under 18, in the first year an apprentice earns 25% of the minimum wage, i.e. 356€; in the second year, 37% i.e. 527€ etc.
      0
  6. tichacha14 Posted messages 302 Status Member 56
     
    Hello,

    Here is a file to model as you like

    https://www.cjoint.com/?BHrvO2kryJ8

    does that work for you?
    0
    1. tichacha14 Posted messages 302 Status Member 56
       
      you will need to enter the exact amounts and I didn’t work on the formatting but you have the automatic calculations
      there might be an easier way but I kind of like to complicate things ;-)
      0
  7. kinedimo Posted messages 26 Registration date   Status Member Last intervention   6
     
    Thank you very much, tichacha. It’s super nice of you.
    I’m going to model it.

    Have a good evening
    0
  8. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Hello kinedimo.

    I must assume:
    - that it is an apprentice who did not go directly into the second year of apprenticeship, or who is not on their second contract, and who does not have the status of a disabled worker?
    - that your company does not apply agreements (collective agreement or professional branches, enterprises...) offering a minimum wage higher than the SMIC?
    - that the company is not part of the Construction branch nor of the public sector?

    --
    That's good, retirement! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
  9. kinedimo Posted messages 26 Registration date   Status Member Last intervention   6
     
    Hello,

    I am embarrassed to respond to you only now, I am even ashamed.

    I thank you all for your efforts, your help, your availability. you are very nice.
    I will look at all of this closely and I will keep you posted ...

    really sorry again for not giving any news ....
    have a good evening
    0
  10. kinedimo Posted messages 26 Registration date   Status Member Last intervention   6
     
    thank you therefore to Mr. Raymond PENTIER, tichacha and eric ....
    0