KHIZHNYAKOVCalcul des primes des commerciaux sous Excel : objectif, barème et entonnoir
Suivi des ventes des commerciaux et calcul des primes en Excel : objectif, entonnoir, barème — Modèle Excel — Voir le modèle →
Chaque fin de mois, la même scène : le commercial pense avoir vendu davantage, le dirigeant a un autre chiffre, et le comptable attend une valeur définitive. Quand les primes sont calculées à la main à partir d'exports hétérogènes, les contestations sont inévitables. La solution tient en deux idées : des règles écrites une fois pour toutes, et un tableau qui les applique de manière identique à tout le monde.
Voici comment mettre en place ce calcul dans Excel, avec un exemple chiffré, les formules à connaître et les pièges à éviter.
Étape 1 : définir ce qui compte comme « réalisé »
C'est la décision la plus importante, et celle qui provoque le plus de litiges. Trois options courantes : les ventes signées, les ventes facturées, les ventes encaissées. Les règles de calcul doivent être claires : par exemple, seules les affaires payées dans le mois comptent, quelle que soit la date de signature.
Pour que cela fonctionne dans un tableau, chaque affaire est une ligne avec : date de création, commercial, client, produit, montant, étape (nouveau, négociation, signé, payé…) et date de paiement. Le réalisé du mois est la somme des montants dont l'étape est « Payé » et dont la date de paiement tombe dans le mois.
Avec le commercial en B, le montant en E, l'étape en F et la date de paiement en G :
=SOMME.SI.ENS(E:E;B:B;"Dupont";F:F;"Payé";G:G;">="&DATE(2026;10;1);G:G;"<="&DATE(2026;10;31))
Étape 2 : mesurer la réalisation de l'objectif
Chaque commercial a un objectif mensuel. Le taux de réalisation est le réalisé divisé par l'objectif.
Exemple. Camille a un objectif de 40 000 € et a réalisé 46 000 € : 46 000 / 40 000 = 115 %. Antoine a le même objectif et 29 000 € de réalisé : 72,5 %.
Étape 3 : un barème par paliers
Le plus lisible est un tableau de paliers : à partir de tel pourcentage de réalisation, tel taux de prime. Exemple purement illustratif :
| À partir de... | Taux de prime |
|---|---|
| 0 % | 0 % |
| 80 % | 2 % |
| 100 % | 3 % |
| 120 % | 4 % |
Avec ce barème, Camille (115 %) relève du palier 100 %, donc 3 % : prime = 46 000 × 3 % = 1 380 €. Antoine (72,5 %) reste sous 80 % : prime nulle. Dans Excel, la fonction qui retient « le palier le plus élevé atteint » est la recherche en correspondance approximative :
=RECHERCHEV(H2;Bareme!A2:B5;2;VRAI)
Le barème doit être trié par ordre croissant de seuil, c'est la condition pour que VRAI fonctionne. La prime est ensuite =réalisé*taux.
Attention : ce barème est un exemple. Vos paliers, taux et conditions d'attribution relèvent de votre règlement de rémunération et de votre contrat de travail.
Étape 4 : le montant à verser
Si le commercial a un fixe, le total à verser est : fixe + prime. Camille, avec un fixe de 1 800 €, recevrait 1 800 + 1 380 = 3 180 €. Antoine : 1 800 €. Un feu tricolore sur le taux de réalisation (rouge sous 80 %, orange jusqu'à 100 %, vert au-delà) rend le classement lisible d'un coup d'œil, avec une simple mise en forme conditionnelle.
Étape 5 : garder un œil sur l'entonnoir
Une prime calculée sur le passé ne dit rien de l'avenir. Pour anticiper, comptez combien d'affaires se trouvent à chaque étape, et pour quel montant :
=NB.SI.ENS(F:F;"Négociation";A:A;">="&DATE(2026;10;1))
Rapportez ensuite le nombre d'affaires payées au nombre d'affaires créées pour obtenir un taux de conversion. Si 50 affaires sont créées et que 11 sont payées, la conversion est de 22 %. Si votre portefeuille en cours est trop faible, vous savez dès le milieu du mois que l'objectif sera difficile.
Erreurs fréquentes
- Compter une affaire sans date de paiement : elle disparaît du réalisé ou, pire, est comptée au mauvais mois. Ajoutez une colonne de contrôle qui signale les affaires « Payé » sans date.
- Barème non trié :
RECHERCHEVenVRAIdonne des résultats faux. - Modifier les règles en cours de mois : changez-les seulement pour la période suivante et gardez la trace.
- Oublier les avoirs et annulations : saisissez-les en montant négatif ou changez l'étape.
Un modèle de calcul de primes
Le modèle Excel de suivi des ventes des commerciaux et des primes est un fichier payant qui met tout cela en place. La feuille « Références » contient les commerciaux avec objectif et fixe, le barème de primes, les produits et le mois de calcul. La feuille « Ventes » accepte jusqu'à 1000 affaires avec contrôle de saisie. La feuille « Primes » affiche, pour chaque commercial, objectif, réalisé, réalisation, feu tricolore, taux du barème, prime, salaire fixe, montant à verser, portefeuille en cours et conversion.
La « Synthèse » présente l'entonnoir par étape, les ventes payées par mois et trois graphiques. Le fichier est en français, sans macros, compatible Excel 2007 et versions ultérieures et LibreOffice. Il s'adresse aux responsables des ventes, directeurs commerciaux, dirigeants de petites entreprises et comptables. Précision importante : il calcule la prime selon votre barème, mais ni les impôts ni les cotisations.
FAQ
Faut-il verser la prime sur les ventes signées ou encaissées ?
Les deux se pratiquent. Les ventes encaissées protègent l'entreprise contre les impayés. Les ventes signées motivent davantage. L'essentiel est de le préciser clairement dans le règlement et de s'y tenir.
Comment calculer une prime d'équipe ?
Ajoutez un objectif collectif et un taux séparé, que vous répartissez ensuite selon une clé fixée à l'avance, par exemple à parts égales ou au prorata du réalisé.
Un barème progressif est-il plus juste qu'un taux unique ?
Il récompense davantage la performance, mais il crée des effets de seuil : une affaire de plus peut faire changer de palier. Testez vos paliers sur les résultats des derniers mois avant de les adopter.
Comment éviter les contestations ?
Donnez à chaque commercial l'accès à la liste de ses affaires et à la règle de calcul. Quand tout le monde voit les mêmes chiffres, les discussions durent deux minutes.
