Commissions conditionnelles sur objectifs

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Commissions sur chiffres d'affaires

Nous attaquons le sixième exercice Excel dédié aux utilisateurs avancés. Il est question d'analyser des contextes pour enclencher des actions et calculs correspondants.



La société T.K.D. Industries effectue le suivi des chiffres d'affaires développés par ses commerciaux. Pour les motiver, chacun bénéficie d'une prime conditionnelle qui lui est propre. Le tableau de la capture ci-dessus offre une représentation de ce cas professionnel.

Source et présentation de la problématique
Le contexte doit être analysé par des formules conditionnelles afin d'offrir des résultats dynamiques, exploitables pour toute société. Et pour focaliser notre attention sur leur mise en oeuvre, nous proposons de réceptionner le classeur de ce tableau. Comme toujours, il est offert au téléchargement depuis le site Bonbache.fr.

• Télécharger le classeur commissions-et-objectifs-chiffres-affaires.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Le tableau, prêt à recevoir nos formules, est construit sur l'unique feuille de ce classeur. Elle est nommée Comissions_CA.

La société T.K.D. Industries effectue le suivi des chiffres d'affaires réalisés par ses commerciaux, pour chacun des mois de l'année. Ces résultats sont dévoilés dans une première colonne, C pour le commercial Hamalibou, E pour le vendeur Doeuf et G pour Braltar. Dans une seconde colonne voisine respective, il s'agit de calculer le montant de la commission accordée. Ce montant dépend bien sûr du chiffre réalisé pour chacun des mois, mais pas seulement. Des contraintes sont inscrites dans un tableau de bord fragmenté en deux parties. Et ces contraintes sont propres à chaque commercial.

Chaque mois, le chiffre réalisé par le vendeur Hamalibou doit dépasser le palier de 160000 Euros, inscrit en D5, pour bénéficier d'une prime de 10% inscrite en H6. Le cas échéant, sa commission est calculée sur le taux inférieur de 8%, mentionné en cellule H7.

Il en va de même pour les deux autres vendeurs Doeuf et Braltar. Mais ces taux se déclenchent en fonction de paliers qui leur sont propres, inscrits respectivement en cellules D6 et D7.

Les calculs des commissions sont donc conditionnels. Il s'agit de comparer chaque chiffre d'affaires avec la contrainte correspondant au commercial dans le panneau de contrôle. Si le palier est franchi, le taux le plus élevé est appliqué. Dans le cas contraire, le taux inférieur est utilisé. Vous l'avez compris, la fonction Excel Si est nécessaire. C'est elle qui permet d'analyser des situations pour enclencher les actions correspondantes. Comme cette analyse est basée sur des valeurs de références variables, inscrites dans des cellules isolées, les références absolues vont surgir.

L'objectif final, comme toujours avec Excel, est de livrer un modèle parfaitement dynamique et déclinable. Et à ce titre, ces contraintes du panneau de contrôle, font office de leviers. Il suffit de modifier un palier ou un taux pour conduire à des résultats différents, selon la politique de la société à l'instant T.

Pourcentages conditionnels de commissions
Le raisonnement est identique pour chaque commercial. Mais la contrainte leur est propre. Il va donc s'agir de vérifier un critère comparant chaque chiffre d'affaires avec chaque palier respectif. Selon le résultat de cette comparaison, l'un ou l'autre calcul devra être enclenché. La fonction Excel Si avance la solution. Elle requiert précisément les trois paramètres nécessaires, selon la syntaxe suivante :

=Si(Critère_à_vérifier; Action_alors ; Action_sinon)

Le critère à vérifier consiste donc à savoir si le chiffre d'affaires en cours et supérieur ou égal au palier à franchir, inscrit dans le panneau de contrôle. S'il est satisfait, le calcul de la commission doit être enclenché sur le taux supérieur (Action_alors). Dans le cas contraire, la commission doit être déduite sur la base du taux inférieur (Action_sinon).

• Sélectionner la première commission à calculer, soit la cellule D11,
• Taper le symbole égal (=) pour débuter la formule de l'analyse,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, Si(,

Cette parenthèse permet d'accueillir les arguments à renseigner. Et à ce titre, vous remarquez l'apparition immédiate d'une info-bulle, destinée à vous aiguiller. Elle confirme ce que nous avons expliqué. Nous devons premièrement construire le critère en fonction duquel les actions seront entreprises.

• Désigner le chiffre d'affaires correspondant pour le commercial, soit la cellule C11,
• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour l'inégalité à vérifier,
• Désigner alors la contrainte correspondante dans le panneau, soit la cellule D5,
• Enfoncer la touche F4 du clavier pour figer ses références,

Le critère à ce stade est le suivant : C11>=$D$5. Le chiffre d'affaires réalisé chaque mois doit être comparé avec la même valeur seuille. Nous autorisons donc la cellule C11 à se déplacer. Ainsi, lorsque nous répliquerons le calcul, l'analyse se poursuivra naturellement en fonction des résultats situés sur les lignes du dessous. Mais bien sûr, chacun de ces chiffres doit toujours être confronté au même palier, situé de façon inamovible en cellule D5. Cette contrainte ne devra donc pas suivre le déplacement lors de la réplication. De fait, nous la figeons.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner de nouveau le chiffre d'affaires, soit C11 pour initier le calcul de la prime,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Désigner alors le plus grand des taux pour intégrer sa référence H6 dans la syntaxe,

Si le critère est vérifié en effet, nous savons que le CA du commercial a franchi le palier invoqué. En conséquence, nous exploitons le taux supérieur pour le calcul de la commission.

• Enfoncer la touche F4 du clavier pour figer ce taux,

La raison est similaire à la précédente. Nous allons répliquer cette logique de calcul sur les lignes du dessous. De fait, tous les chiffres d'affaires vont être considérés tour à tour. Mais les taux servant au calcul de la commission sont placés dans des cellules uniques. Ils ne doivent donc pas suivre ce déplacement.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

Nous l'avons expliqué, dans le cas contraire, le chiffre d'affaires n'étant pas suffisant, le calcul de la commission doit être effectué à partir du taux inférieur.

• Désigner de nouveau la cellule du chiffre d'affaires ou saisir ses références, soit C11,

Il est possible en effet que la formule déborde sur cette cellule voisine, empêchant de la désigner à la souris.

• Taper le symbole de l'étoile (*) du pavé numérique pour initier la multiplication,
• Désigner le taux inférieur pour intégrer sa référence H7 dans le calcul,
• Enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la fonction Si,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Cette combinaison de touches est importante. Tout en validant le calcul, elle permet de conserver active la cellule du résultat. Nous souhaitons en effet le reproduire sur la hauteur du tableau.

Le premier résultat tombe. Et comme vous le constatez, la commission correspond à 10% du chiffre réalisé. Le CA du commercial pour ce premier mois est en effet supérieur à la contrainte du panneau de contrôle. Notre fonction conditionnelle semble avoir parfaitement analysé la situation pour adapter automatiquement le calcul.

Nous devons répercuter sa logique sur les lignes du dessous. Et pour cela, nous devons exploiter la poignée de la cellule du résultat.



Cette poignée est matérialisée par le petit carré noir placé en bas à droite de la case active. En plaçant la souris au-dessus, vous remarquez que son curseur se transforme en une croix noire. Elle indique qu'Excel est prêt à répliquer la formule.

• Placer la souris au-dessus de la poignée jusqu'à visualiser la croix noire,
• Puis, double cliquer à l'aide du bouton gauche de la souris,

Le calcul est instantanément reproduit sur la hauteur du tableau, dont la borne a été automatiquement détectée par Excel. Nous aurions aussi pu accompagner le mouvement en cliquant et glissant cette poignée à la verticale jusqu'à la ligne 22. Mais cette méthode est à la fois plus efficace et confortable.

La formule que nous avons construite sur la base du premier chiffre d'affaires, est la suivante :

=SI(C11>=$D$5;C11*$H$6;C11*$H$7)

Si vous sélectionnez le dernier résultat en cellule D22, sa barre de formule indique que la syntaxe a évolué de la façon suivante :

=SI(C22>=$D$5;C22*$H$6;C22*$H$7)

Le raisonnement est parfaitement identique. Le critère est propre à chaque ligne en revanche, dans la mesure où la condition est vérifiée sur le chiffre d'affaires en cours (C22). Cependant, chacun est comparé au même palier ($D$5), grâce aux références absolues. De même, chacun est bien multiplié par les taux uniques ($H$6 et $H$7).



Ce raisonnement doit être plagié pour les deux autres commerciaux, soit respectivement pour les colonnes F et H. Il ne peut pas être répliqué sans adaptation à cause de la contrainte propre à chacun, située sur une ligne différente.

• Sélectionner le tout premier calcul de prime de commission, soit la cellule D11,
• Réaliser le raccourci clavier CTRL + C pour copier sa formule,

Il est aussi possible de cliquer sur le bouton Copier, dans la section Presse-papiers du ruban Accueil.

• Sélectionner la première prime pour le deuxième commercial, soit la cellule F11,
• Réaliser le raccourci clavier CTRL + V pour coller la formule,
• Sélectionner la première prime pour le troisième commercial, soit la cellule H11,
• Réaliser de nouveau le raccourci clavier CTRL + V,

Il est aussi possible de cliquer sur le bouton Coller dans la section Presse-papiers du ruban Accueil. Les calculs semblent être reproduits sans difficultés. Mais ils ne sont pas corrects et nous allons le constater.

• Double cliquer sur la cellule F11 pour afficher la syntaxe de sa formule,

Cette technique offre aussi l'intérêt d'identifier les cellules impliquées par des jeux de couleurs. Le copier-coller implique les mêmes conséquences que la poignée. Nous avons reproduit la logique deux colonnes plus à droite. De fait, toutes les cellules non figées ont suivi ce déplacement. C'est la raison pour laquelle le chiffre d'affaires utilisé pour le critère et le calcul n'est plus C11 mais E11. Et c'est exactement ce que nous souhaitons. Comme les taux sont figés, ils sont bien exploités pour le calcul sur indication du critère. Une fois encore, la réplication est parfaite. Le critère en revanche doit être adapté. Aucune astuce de référence absolue ne permet cette réplication automatique du fait de la configuration différente (Contrainte en ligne et déplacement en colonne). Pour le deuxième commercial, la cellule de la contrainte pour la comparaison doit descendre d'une ligne.

• Dans le critère de la fonction Si, remplacer $D$5 par $D$6,

Aussitôt vous remarquez que le fond rouge identifiant la cellule dans le panneau de contrôle, descend d'une case.

• Valider cette modification par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour reproduire la logique sur toute la colonne,

Le dernier résultat en cellule F22 confirme bien que la commission est calculée sur le chiffre d'affaires de la même ligne en tenant compte de la contrainte adaptée pour ce commercial.



Une rapide vérification des données confirme que les 10% ne sont appliqués que lorsque le CA dépasse les 215 000 Euros de cette nouvelle contrainte.

Nous devons adapter le calcul de la prime du dernier commercial de la même façon.

• Double cliquer sur la cellule H11 pour afficher la syntaxe du calcul,
• Dans le critère de la fonction Si, remplacer $D$5 par $D$7,
• Valider la modification par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la formule,

Grâce aux références absolues judicieusement combinées avec la fonction Excel Si, nous avons bâti un modèle parfaitement dynamique. En cas de changement de politique, il suffit à la société d'actionner les leviers du panneau de contrôle pour influer instantanément sur tous les calculs de primes.

• En H6, remplacer le nombre 10 par le nombre 15 et valider par Entrée,

Chaque commercial se voit désormais gratifié d'une prime de 15%, dès lors que le chiffre d'affaires correspondant a dépassé sa contrainte qui lui est propre. De la même façon, un changement de l'un de ces paliers, affecterait tous les résultats dans la colonne du commercial concerné.

Synthétiser les résultats et objectifs
Les données statistiques requises entre les lignes 24 et 26 sont précieuses. Elles doivent simplifier la compréhension des résultats.

En ligne 24, nous devons tout d'abord rendre compte du total des commissions versées par vendeur. Une simple somme automatique sur la colonne dédiée fera l'affaire. Il ne restera plus qu'à dupliquer la formule pour les autres commerciaux afin de la répliquer sur les colonnes appropriées.

En ligne 25, il s'agit de comptabiliser pour chaque commercial, chaque objectif atteint. Ce dénombrement est conditionnel. Il s'effectue selon un critère qui consiste à comparer chaque CA avec la contrainte propre à atteindre. Cette donnée résultante sera directement utilisée pour fournir une idée de la proportion du succès, en ligne 26. Nous pourrons en déduire si le vendeur est un bon élément.

• Sélectionner le premier total à calculer, soit la cellule D24,
• A droite du ruban Accueil, cliquer sur le bouton Somme automatique,

La proposition d'Excel est quasiment correcte. Nous pourrions d'ailleurs la valider. Mais comme la cellule du résultat à livrer ne touche pas les données du tableau, la cellule vide intermédiaire est incluse dans la suggestion. Il est préférable de l'exclure.

• Sélectionner uniquement les montants de commissions, soit la plage de cellules D11:D22,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Copier sa formule par le raccourci CTRL + C,
• Sélectionner le total suivant, soit la cellule F24,
• Coller la formule à l'aide du raccourci CTRL + V,

Comme vous le constatez, les références s'adaptent automatiquement dans la syntaxe :

=SOMME(F11:F22)

De fait, l'addition des primes est bien réalisée dans la colonne du deuxième commercial.

• Sélectionner le troisième total, soit la cellule H24,
• Coller la formule par le raccourci CTRL + V,

Une première indication intéressante surgit. Le premier des trois commerciaux est celui qui a bénéficié du plus gros montant de primes reversées. Il s'agit vraisemblablement du meilleur vendeur. Encore faut-il replacer les éléments dans leur contexte. En effet, les contraintes ne sont pas identiques pour chacun. En tout état de cause, cette conclusion est beaucoup plus difficile à obtenir sans la présence de ces premiers résultats statistiques.

En ligne 25, nous devons désormais comptabiliser tous les objectifs atteints, pour chaque commercial. Nous devons exploiter la fonction Excel Nb.Si dont la syntaxe est la suivante :

=Nb.Si(Plage_où_compter; Critère)

Le premier argument est la plage de cellules des chiffres d'affaires sur laquelle le décompte doit être réalisé. Mais ce décompte dépend d'une condition à fournir en second argument. Pour chaque ligne, elle consiste à vérifier que ce chiffre est bien supérieur à la contrainte imposée. C'est seulement dans ce cas, que la ligne pourra être comptabilisée.

• Cliquer sur la cellule D25 pour la sélectionner,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse, soit Nb.Si(,
• Sélectionner les CA du premier commercial, soit la plage de cellules C11:C22,
• Taper un point-virgule (;) pour passer à l'argument du critère à vérifier,
• Saisir le symbole supérieur suivi du symbole égal entre guillemets, soit '>=',

L'opérateur est nécessaire pour la comparaison du critère à valider. Il doit nécessairement être inscrit entre guillemets. Puis, il doit être concaténé à la variable pour finaliser la construction de la condition.

• Enfoncer la touche 1 en haut à gauche du clavier pour inscrire l'opérateur de concaténation (&),
• Sélectionner alors la contrainte propre à ce commercial, soit la cellule D5,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, valider le calcul par le raccourci CTRL + Entrée,

Comme le relate le résultat, le premier vendeur a atteint 8 fois son objectif sur les 12 mois de l'année. D'ores et déjà, nous pouvons en conclure qu'il s'agit d'un commercial performant.



Nous devons adapter cette formule aux autres vendeurs comme nous l'avons fait précédemment.

• Copier ce calcul (CTRL + C),
• Sélectionner la cellule F25 et le coller (CTRL + V),
• Sélectionner la cellule H25 et le coller de nouveau (CTRL + V),
• Double cliquer sur la cellule F25 pour accéder à sa syntaxe,
• Remplacer la contrainte F5 du critère par D6,
• Puis, valider la modification par la touche Entrée par exemple,
• Double cliquer sur la cellule H25 pour accéder à sa syntaxe,
• Remplacer la contrainte H5 du critère par D7,
• Puis, valider la modification par la touche Entrée,

Les cellules des paliers avaient naturellement suivi le déplacement en colonne, imposé par le copier-coller. La modification de formule peut aussi être opérée dans la barre de formule de la cellule. Cette technique permet d'éviter de double cliquer sur le résultat à modifier.

Le deuxième commercial semble donc loin du compte avec seulement deux objectifs atteints. Mais en replaçant les choses dans leur contexte, nous savons aussi qu'il a hérité du palier le plus élevé à franchir.

Pour calculer les taux en ligne 26, il suffit de diviser le résultat précédent par le nombre total de chiffres d'affaires réalisés. Nous savons qu'ils sont renseignés sur les 12 mois de l'année. Mais réaliser la division par le nombre 12 n'est pas une solution satisfaisante. Nous le répétons à chaque occasion, il n'est pas conseillé d'introduire des constantes dans les formules.

Nous proposons donc d'exploiter la fonction NbVal sur la colonne respective des chiffres d'affaires. Souvenez-vous, cette fonction permet de compter toutes les cellules non vides sur une plage définie. En outre cette technique permettra de conserver un modèle parfaitement dynamique avec des calculs bâtis uniquement sur des variables.

• Sélectionner le premier taux à calculer, soit la cellule D26,
• Taper le symbole égal (=) pour débuter la formule,
• Désigner le nombre d'objectifs atteints, soit la cellule D25,
• Taper le symbole slash (/) du pavé numérique pour enclencher la division,
• Saisir le nom de la fonction suivi d'une parenthèse, soit NbVal(,
• Désigner tous les chiffres d'affaires du commercial, soit la plage de cellules D11:D22,
• Fermer la parenthèse de la fonction NbVal,
• Valider le calcul par le raccourci CTRL + Entrée pour garder la cellule active,

Le résultat obtenu est un très bon résumé des performances du commercial, selon les objectifs fixés. Ils sont atteints dans 67% des cas.

• Copier la cellule du résultat (CTRL + C),
• Puis, la coller (CTRL + V) en F26 et H26,

C'est judicieusement que nous n'avons pas exploité les références absolues dans ce calcul. De fait, toutes les cellules impliquées suivent le déplacement imposé en colonne.



La formule d'origine : =D25/NBVAL(D11:D22), s'est adaptée pour le dernier commercial, dans sa colonne : =H25/NBVAL(H11:H22).

Alertes visuelles sur objectifs
Il est toujours intéressant voire important de renforcer la pertinence d'un tableau de suivi avec des indicateurs de couleurs dynamiques. Et à ce titre, pour chaque commercial, nous proposons de mettre automatiquement en évidence toutes les lignes pour lesquelles l'objectif est atteint. Dès la première lecture, la densité de couleur renseignera de façon évidente sur les performances des uns et des autres.

Nous devons donc exploiter la mise en forme conditionnelle d'Excel. Elle doit faire réagir les cellules en fonction d'un critère particulier. Ce dernier doit vérifier que le chiffre d'affaires dépasse l'objectif. Un tel critère doit être bâti avec une formule de comparaison. Trois règles doivent être construites pour honorer les trois différents objectifs associés aux commerciaux.

• Sélectionner les données numériques du premier commercial, soit la plage C11:D22,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Sélectionner le premier chiffre d'affaires à comparer avec l'objectif, soit la cellule C11,

Comme vous le constatez, elle apparaît naturellement figée : $C$11. Or pour chaque ligne que la règle doit passer en revue, chaque chiffre d'affaires doit être comparé à la contrainte associée du panneau de contrôle. Donc, la cellule doit être libérée en ligne. En revanche, sur chaque ligne, le critère doit nécessairement être vérifié par rapport au CA. En conséquence cette cellule doit rester figée en colonne.

• Enfoncer deux fois la touche F4 du clavier,

Un seul dollar persiste. Il est désormais placé devant l'indice de colonne : $C11. Donc seul le déplacement à l'horizontale est interdit.

• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour l'inégalité à vérifier,
• Désigner la contrainte associée dans le panneau de contrôle, soit la cellule D5,

Elle aussi apparaît complètement figée : $D$5. Cette fois, c'est bien ce que nous souhaitons. Chaque chiffre d'affaires passé en revue doit être comparé à cette même cellule de référence. Elle ne doit donc pas bouger.

Le critère de la règle est donc le suivant : =$C11>=$D$5.

Il consiste à vérifier, ligne à ligne, que le chiffre réalisé a atteint ou dépassé l'objectif. Si tel est le cas, l'apparence des cellules concernées doit réagir.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un vert clair dans la palette de couleurs,
• Puis, valider ce choix en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui offre un résumé de la situation. Un remplissage vert doit être appliqué aux cellules désignées lorsque le CA atteint l'objectif.

• Valider la création de cette règle en cliquant sur le bouton Ok,

Instantanément, les repérages dynamiques se déclenchent. La proportion de vert domine pour ce premier commercial. Dès la première lecture, l'interprétation des résultats est positive. Une fois encore, nous avons construit un critère dynamique, pour un modèle de suivi des commerciaux dynamique.

• En D5, remplacer la contrainte 160000 par 180000,

Instantanément, tous les calculs s'actualisent ainsi que la mise en valeur. La proportion de vert est beaucoup plus faible indiquant que le taux de réussite a largement chuté.

La même règle doit être construite pour chacun des deux commerciaux restants. Il s'agit d'abord de bien sélectionner leurs valeurs numériques respectives. Ensuite, il faut adapter le critère. Pour le premier, la comparaison doit être réalisée par rapport à la contrainte en cellule D6 : =$E11>=$D$6. Pour le deuxième, il faut se baser sur la référence en D7 : =$G11>=$D$7.



Tous les objectifs sont désormais repérés dynamiquement. Nous avons finalisé un modèle de tableau Excel parfaitement dynamique pour effectuer le suivi des performances réalisées par les commerciaux de l'entreprise.