KHIZHNYAKOVCréer un tableau des effectifs avec masse salariale sous Excel
Tableau des effectifs sous Excel : postes, masse salariale et affectation — modèle Excel — Voir le modèle →
Le tableau des effectifs dort souvent dans un document, la liste du personnel dans un autre, et la masse salariale dans la tête du comptable. Résultat : personne ne sait précisément combien de postes sont vacants, ni ce que coûterait leur recrutement.
La méthode consiste à séparer deux listes et à laisser Excel les rapprocher : d'un côté les postes (ce que l'organisation prévoit), de l'autre les affectations (qui occupe quoi). Les écarts apparaissent alors d'eux-mêmes.
Les deux listes de base
Liste des postes : service, intitulé, effectif budgété, salaire de base mensuel, primes en pourcentage, primes en montant.
Liste des affectations : nom, poste (choisi dans la liste), quotité de temps de travail, date d'embauche. La quotité est de 1 pour un temps plein, 0,5 pour un mi-temps.
Les calculs essentiels
- Coût mensuel d'un poste = salaire de base × (1 + prime en %) + prime en montant.
- Masse salariale mensuelle prévue = coût du poste × effectif budgété.
- Masse salariale annuelle = mensuelle × 12.
- Postes pourvus = somme des quotités des salariés affectés.
- Postes vacants = effectif budgété − pourvus.
- Taux de couverture = pourvus ÷ budgétés.
Exemple chiffré
Service « Accueil » : poste d'agent d'accueil, 3 postes budgétés, salaire de base 1 800 €, prime de 10 %, prime fixe de 50 €.
| Élément | Calcul | Résultat |
|---|---|---|
| Coût mensuel d'un poste | 1 800 × 1,10 + 50 | 2 030 € |
| Masse mensuelle prévue | 2 030 × 3 | 6 090 € |
| Masse annuelle | 6 090 × 12 | 73 080 € |
| Postes pourvus | 1 + 1 + 0,5 | 2,5 |
| Postes vacants | 3 − 2,5 | 0,5 |
| Taux de couverture | 2,5 ÷ 3 | 83 % |
| Masse mensuelle réelle | 2 030 × 2,5 | 5 075 € |
Les charges et cotisations ne sont pas incluses dans cet exemple : ajoutez-les avec un taux de paramètre, après avoir vérifié le taux applicable localement.
Les formules Excel
- Coût du poste :
=D2*(1+E2)+F2 - Pourvus :
=SOMME.SI(Affectation[Poste];A2;Affectation[Quotité]) - Vacants :
=B2-pourvus - Statut :
=SI(vacants>0;"Vacant";SI(vacants<0;"Dépassement";"Pourvu")) - Masse réelle par service :
=SOMME.SI(Service;"Accueil";MasseRéelle) - Taux de couverture :
=SIERREUR(pourvus/budgétés;0)
Un menu déroulant (validation de données) pour le choix du poste dans la liste des affectations évite les fautes de frappe, qui sont la cause principale d'erreurs de rapprochement.
Lecture des résultats
- Un service à 60 % de couverture avec une forte charge de travail est un candidat au recrutement.
- Un dépassement d'effectif (plus de personnes que de postes) mérite un contrôle : erreur de saisie ou décision non régularisée ?
- La différence entre masse prévue et réelle donne l'économie liée aux vacances de poste, mais aussi le coût du recrutement à budgéter.
Erreurs fréquentes
- Compter des personnes, pas des quotités : deux mi-temps ne font qu'un poste.
- Oublier les primes : le coût réel d'un poste dépasse le salaire de base.
- Mélanger montants mensuels et annuels dans la même colonne.
- Modifier la liste des postes sans reporter les affectations : un poste renommé ne correspond plus aux lignes existantes.
Modèle Excel prêt à l'emploi
Le modèle « Tableau des effectifs : postes, masse salariale et affectation » applique cette logique. La feuille « Paramètres » contient l'organisation, la décision d'approbation, le coefficient de majoration locale, le taux de cotisations et le référentiel des services. Le « Tableau des effectifs » gère jusqu'à 120 postes : effectifs budgétés, salaire de base, primes en pourcentage et en montant, masse salariale mensuelle et annuelle, pourvus, vacants et statut.
La feuille « Affectation » accepte jusqu'à 300 lignes : nom, poste de la liste, quotité, date d'embauche, service, salaire de base et éléments de rémunération, avec contrôle du dépassement des effectifs. La « Synthèse » présente par service les effectifs budgétés, pourvus et vacants, le taux de couverture, la masse salariale prévue et réelle, les charges et un graphique.
Le fichier est en français, fonctionne sous Excel 2007 et versions ultérieures et LibreOffice, sans macros, et il est payant. Il aide à préparer et analyser le tableau des effectifs ; le document approuvé reste établi selon la décision de votre direction et le formulaire requis. Vérifiez les taux et coefficients auprès des règles applicables.
FAQ
À quelle fréquence réviser le tableau des effectifs ?
Au moins une fois par an, lors du budget, et à chaque création ou suppression de poste. Les affectations, elles, se mettent à jour à chaque embauche ou départ.
Comment gérer un salarié sur deux postes ?
Saisissez deux lignes d'affectation avec chacune sa quotité (par exemple 0,6 et 0,4) : le total par personne reste de 1.
Faut-il inclure les cotisations dans la masse salariale ?
Pour un pilotage de budget, oui : indiquez un taux de charges dans les paramètres et calculez un coût global. Vérifiez le taux auprès des règles locales.
Comment présenter les postes vacants à la direction ?
Un tableau par service avec effectifs budgétés, pourvus et vacants, plus le coût mensuel du recrutement des postes vacants, suffit à lancer la discussion.
