Création d'un simulateur de calcul

Y compris Power BI, Power Query et toute autre question en lien avec Excel
t
tetelle33
Nouveau venu
Nouveau venu
Messages : 7
Inscrit le : 12 janvier 2017
Version d'Excel : 2010

Message par tetelle33 » 12 janvier 2017, 21:12

Bonjour à tous,

je suis assistante en ressources humaines et notre logiciel de paie ne calcule pas le passage à demi traitement (salaire) des agents. Simplement parce que ma collectivité n'a pas acheté le module RH (assez coûteux pour une petite commune). Pour chaque arrêt maladie je dois donc calculer manuellement le ou les jour(s) où l'agent perçois du plein traitement ou demi traitement.

Si quelqu'un pouvait m'aider à créer un simulateur, cela m'aiderait beaucoup.
Les règles dans la fonction publique sont les suivantes :
- droit à l'arrêt maladie pendant 1 an
- les 90 premiers jours sont payés à plein traitement
- les 270 jours suivants sont payés à demi-traitement
- le calcul se fait sur année glissante donc on doit calculer le traitement chaque jour d'arrêt.

Les mois de paye dans la fpt sont des mois indivisibles de 30 jours. Exemple : la paie du mois de février, l'agent est payé 30/30ème, pareil pour les mois de 31 jours.
Par conséquent, si un agent a un arrêt maladie du 1er au 29 février : il aura 29 jours de maladie de décomptés mais en paye il aura 30/30ème de salaire.
Si l'arrêt est du 1er au 31 janvier : il aura 31 jours de maladie décomptés mais en paye 30 jours de payés

Pour le calcul du passage à demi traitement, on se base à l'année de référence mobile :
exemple : arrêt maladie du 16/11/2016 au 15/12/2016 :
on calcule pour chaque jour d'arrêt de cette période (le 16/11, le 17/11, le 18/11 ....) si l'agent a obtenu 90 jours de plein traitement ou non dans l'année précédent ce jour d'arrêt (du 16/11/2015 au 15/11/2016 pour le calcul du 16/11/2016, du 17/11/2015 au 16/11/2016 pour calculer le jour du 17/11/2016 etc).

on ne tient pas compte des jours fériés, de la durée de travail de l'agent, des congés annuels.

Voilà, j'ai quelques notions en tableur mais vraiment minimes, je suis assez novice avec excel surtout avec des formules.
Merci pour votre attention et votre aide.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 12 janvier 2017, 22:53

Bonsoir,

Je n'aurais guère le temps de travailler sur ce type de calculs en ce moment... :D Mais je te livre le dernier fichier, résultant du dernier travail sur le sujet : cela devrait pouvoir t'aider...

Les seules colonnes à servir sont A et B début et fin de CM, ainsi que C2 (date d'entrée en service, si l'acquisition de droits se fait progressivement, selon tableau G4:K7 qui peut le cas échéant être modifié).
Pour initialiser, seules les colonnes A et B du tableau principal (et C2) sont à effacer (toutes les autres colonnes contiennent des formules, y compris des colonnes masquées).

Les dates se mettant en grisé indiquent des prolongations de CM (MFC qui opère cette mise en forme).
Des alertes (par MFC) peuvent apparaître : cellule se colorant en rouge si un CM est à cheval sur un période de changement de droits ; il convient alors de le scinder sur la date de changement pour qu'il soit correctement comptabilisé. Cellule se colorant en orange si on a fait une erreur manifeste de saisie de date.
La ligne 11 doit rester vide...

Le dernier ajout à ce fichier réside dans les colonnes P et Q : calcul pour chaque congé des jours pris en charge à 100% ou à 50%. Ce calcul est fait au moyen d'une fonction personnalisée matricielle (j'avoue qu'elle m'a fait un peu transpirer ! :D ).
La gestion des congés maladie étant dépendante de la situation antérieure (à un an), il importe de ne pas modifier le point de départ (ligne 12) et de ne pas sauter de lignes dans l'inscriptions des CM successifs...

A toi de voir !
Cordialement
GestionCM_Fonc.xls
(150.5 Kio) Téléchargé 4660 fois
t
tetelle33
Nouveau venu
Nouveau venu
Messages : 7
Inscrit le : 12 janvier 2017
Version d'Excel : 2010

Message par tetelle33 » 13 janvier 2017, 00:56

merci beaucoup MFerrand pour ce classeur.
En effet, il m'aide beaucoup.

J'ai mis les arrêts successifs d'un agent et je trouve un résultat différent avec ton classeur que ce qu'on a payé à l'agent.
Il semble que le classeur ne prend pas en compte les bonnes dates de référence.
Pour un arrêt du 16/11/2016 au 15/12/2016, l'année de référence mobile est du 16/11/2015 au 15/11/2016 (1 an juste avant le premier jour d'arrêt).
Il se trouve que mon agent a été en cm du 30/11/2015 au 29/02/2016 au cours duquel il a déjà obtenu 90 jours de PT, il devrait donc être en DT du 16/11 au 30/11/2016.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 janvier 2017, 01:17

Bien sûr que non !
Le calcul est glissant au jour le jour.
A partir du 30/11 il repasse à plein traitement.
Par contre, la fonction d'identification des jours ne donne pas le bon détail... Là il faudra que je m'y repenche.
t
tetelle33
Nouveau venu
Nouveau venu
Messages : 7
Inscrit le : 12 janvier 2017
Version d'Excel : 2010

Message par tetelle33 » 13 janvier 2017, 01:19

un exemple de calcul des arrêts que je vous joins pour mieux me faire comprendre.
Bien lire les quelques lignes sous le tableau car c'est de là qu'en découlent les bons calculs.

Merci encore.
calcul_arrets_mo.pdf
(8.49 Kio) Téléchargé 2493 fois
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 janvier 2017, 01:31

Je regarderais en détail plus tard (il est 4h30 cheez moi !), les règles de calcul indiquées en bas, au jour le jour, correspondent bien à ce que j'ai dit.
L'exemple que tu as donné est simple :du 30/11/2015 au 27/02/2016, ton agent à eu 90 jours à PT.
Du 16/11/2016 au 29/11/2016, il y a toujours 90 jours. A partir du 30, il n'y en a plus que 89, le 30 devient le 90e, et ainsi de suite par glissement jusqu'à la fin de ce congé.
t
tetelle33
Nouveau venu
Nouveau venu
Messages : 7
Inscrit le : 12 janvier 2017
Version d'Excel : 2010

Message par tetelle33 » 13 janvier 2017, 01:36

oups désolé je ne savais pas pour l'heure. Tu es de quel pays ?

Vu l'heure chez moi aussi, peut être que la fatigue m'embrouille, je regarderai demain.

Merci de ton aide.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 janvier 2017, 02:11

Je réside à La Réunion : +3h avec la métropole en ce moment.
Je suis volontiers nocturne mais il m'arrive de dormir (le problème c'est que dormir le jour m'ampute sérieusement d'autres activités...)

J'ai finalement mis tes exemples dans mon tableau : le premier donne rigoureusement les mêmes résultats, le second fait apparaître 2 petites erreurs de ton document :
-congé du 05/09/2002 au 30/06/2002 ! sans conséquence car rectifié en dessous dans le détail (mais j'ai tapé bêtement et ma cellule s'est colorée en orange, j'ai donc rectifié en 12/11...)
-congé du 03/01/2003 au 16/03/2003 : ton document compte globalement 74 jours alors qu'il n'y en a que 73. Quelqu'un a dû compter un 29 février alors que pour 2003, pas possible. Tu as donc un jour de plus dans les DT mais le décompte des PT est identique.

On applique donc bien les mêmes règles.
GestionCM_FoncEx2.pdf
(281.58 Kio) Téléchargé 1733 fois
GestionCM_FoncEx1.pdf
(281.91 Kio) Téléchargé 1185 fois
t
tetelle33
Nouveau venu
Nouveau venu
Messages : 7
Inscrit le : 12 janvier 2017
Version d'Excel : 2010

Message par tetelle33 » 13 janvier 2017, 07:51

Bonjour MFerrand

Ton fichier est génial et ça fonctionne. On a vraiment tous les éléments, le nombre de jours décomptés en maladie et le nombre de jours pour la paye, les périodes avec le % de traitement, tout quoi. Je te remercie vraiment.
Juste une question pourquoi avoir mis la date du 24/05/1990 ? J'ai rentré la date de l'agent en C2 mais ça ne change rien vu que l'agent a 14 ans d'ancienneté.

Il me reste maintenant à essayer de créer un fichier de calcul de paie pour agent CNRACL mais ça c'est à titre perso, pas pour le boulot.

Excel c'est génial quand on sait s'en servir, si j'avais plus de temps pour apprendre, ça me dirait bien. :D

Merci pour tout et bonne journée.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 janvier 2017, 12:55

Bonjour,

S'il y a des paliers d'ancienneté pour l'acquisition des droits (durée à PT), tu peux adapter l'échelle... En tous cas, l'adapter pour que ça colle avec ta situation (sans supprimer des paliers [il faut faire en sorte qu'ils n'aient pas d'effet s'ils ne doivent pas en avoir], car certaines formules risquent de ne pas aimer... !) Le premier original remonte à plusieurs années, je l'ai repris au moins 3 fois pour le compléter selon les demandes faites. Il faudrait que je fasse une vérification pour le rendre adaptable facilement à toutes situations... mais ça attendra un peu. En C2, il faut une date pour que le reste fonctionne : si la date réelle ne permet pas de cadrer la situation de droits, tu peux toujours en mettre une fictive qui fera cadrer...

Dans l'immédiat, tu m'as fait mettre le doigt sur une insuffisance de la fonction de calcul des jours pour les répartir en PT et DT.
J'ai découvert une petite anomalie réparée mais dont la réparation ne corrige pas l'erreur sur ton premier exemple. En fouillant j'ai découvert que tous les cas de chevauchements n'étaient pas pris en compte (le hasard a fait que dans tous les tests précédents ces cas n'étaient pas apparus...). J'ai donc reventilé les situations en 6 cas. Je pensais pouvoir apporter un correctif rapide, sans toucher à la structure mais 2 cas ne me permettent d'affecter des jours pris dans la période d'une année écoulée sans remonter aux dates de répartition concernée. Dans ces conditions il me faut modifier légèrement la structure du calcul pour tout aligner à partir de ces dates (pour que l'ensemble reste cohérent), ce qui va demander un peu plus de temps et que je ne pourrais y consacrer aujourd'hui.

Je reviendrai donc vers toi avec le correctif, une fois fait.
Cordialement.
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message