Contrôler les objectifs sur chiffres d'affaires Excel

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


Sujets que vous pourriez aussi aimer :


Objectifs sur chiffres d'affaires avec Excel

Dans cette formation, nous souhaitons monter une petite application d'entreprise permettant de faciliter le relevé des résultats et de simplifier les interprétations. La direction effectue le suivi des chiffres d'affaires de ses commerciaux au fur et à mesure des ventes. Des objectifs sont fixés pour que chacun tente de s'en rapprocher. Comme les données cumulées sont nombreuses, la direction doit pouvoir obtenir une synthèse sans équivoque à la désignation de l'un des commerciaux.



Dans l'exemple ci-dessus de l'application finalisée, à la sélection du nom d'un commercial par le biais d'une liste déroulante, les conclusions sur ses résultats surgissent instantanément avec des indicateurs de couleur. Comme les contraintes sont inscrites dans des cellules de référence, les interprétations apparaissent avec un format dynamique explicite. En un clic, la direction sait si les résultats sont satisfaisants ou insuffisants.

Source et problématique
Pour la mise en oeuvre de cette application, nous n'utiliserons pas le code VBA Excel. Nous ferons essentiellement appel aux fonctions statistiques conditionnelles, au format dynamique ainsi qu'aux graphiques. Nous proposons de débuter depuis un classeur offrant les données des chiffres d'affaires à synthétiser.

• Télécharger le classeur Excel objectifs-chiffres-affaires.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

La feuille Synthèse s'affiche par défaut. Le tableau situé entre les colonnes B et E résume les chiffres d'affaires réalisés par les commerciaux de l'entreprise, au cours du premier semestre de l'année. La colonne D représente les chiffres effectivement réalisés. La colonne E énumère quant à elle, les objectifs fixés par action. Ces objectifs ont volontairement été calibrés pour ne pas être atteints. Chaque commercial doit tenter de s'en rapprocher. Comme le suivi a été réalisé au coup par coup, ce tableau présente le détail de chacune des ventes. Il doit être synthétisé pour permettre l'interprétation des performances de chacun des commerciaux.

Malgré l'amplitude du tableau, les commerciaux ne sont que quatre. La liste déroulante située en cellule H5 le confirme. Elle a été conçue sur la plage de cellules C5:C8 située en Feuil2. C'est la fonctionnalité Excel de suppression des doublons qui a permis de lister chacun des vendeurs de façon unique.



Pour effectuer une synthèse explicite et automatisée, il convient de calculer le ratio du chiffre d'affaires réalisé par rapport à l'objectif fixé, pour le commercial sélectionné par le biais de la liste déroulante. Ce résultat doit être inscrit en cellule J3 de la feuille Synthèse. Puis il doit être reporté dans l'une des cases du tableau de bord, entre J6 et J8.



Comme l'illustre la capture ci-dessus, des contraintes sont inscrites entre G5 et G8. Comme la couleur de police a été définie comme celle de la couleur de fond, elles n'apparaissent pas mais sont bien présentes. Elles définissent les fourchettes d'interprétations automatisées des résultats.

• Entre 0 et 75%, le ratio doit être reporté en J6 (Insuffisant),
• Entre 75 et 90%, le ratio doit être reporté en J7 (A améliorer),
• Entre 90 et 100%, le ratio doit être reporté en J9 (Satisfaisant),

Dès lors, un graphique en anneau pourra être bâti sur les résultats répercutés, afin de mettre en valeur instantanément et sans équivoque, les performances du commercial désigné par la liste déroulante. Il s'agira d'adapter ses couleurs en fonction du niveau de satisfaction pour une interprétation immédiate.

Somme conditionnelle des chiffres d'affaires
En J3, nous devons donc calculer le rapport des ventes réalisées sur les objectifs fixés, pour le vendeur sélectionné. En d'autres termes, il s'agit de diviser la somme des ventes par la somme des objectifs. Ces deux sommes sont conditionnelles puisqu'il s'agit d'additionner les valeurs, seulement pour le commercial désigné. La formation Excel sur le suivi et la fidélisation des clients nous avait appris à exploiter la fonction Somme.Si. Sa syntaxe est la suivante :

=Somme.Si(Plage_de_Critère ; Critère ; Plage_pour_Somme)

Nous devons donc désigner en premier argument, la colonne C comme plage sur laquelle doit être recherché le critère qui définira la somme. En deuxième argument, le critère devra désigner la cellule du vendeur défini par la liste déroulante. Dès lors, nous désignerons la colonne D en troisième argument comme plage de cellules correspondante pour réaliser la somme des chiffres réalisés. Pour la somme des objectifs, nous désignerons la colonne E. Pour gérer le cas où aucun commercial n'est précisé, nous proposons d'inclure ce calcul conditionnel de ratio, dans une fonction Excel Si.

• Cliquer dans la cellule J3 pour la sélectionner et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Sélectionner la cellule de la liste déroulante, soit H5,
• Saisir alors le critère suivant : <>'', pour vérifier qu'elle n'est pas vide,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir le nom de la fonction pour la somme conditionnelle suivi d'une parenthèse, soit Somme.Si(,
• Désigner intégralement la colonne C en tapant la référence C:C suivi d'un point-virgule,
• Sélectionner alors la cellule du critère, soit H5 et taper un nouveau point-virgule,
• Désigner intégralement la colonne D en tapant la référence D:D et fermer la parenthèse,
• Taper le symbole su slash (/) pour la division,
• Saisir de nouveau la somme conditionnelle suivie d'une parenthèse, soit Somme.Si(,
• Désigner de nouveau la colonne C (C:C) et taper un point-virgule,
• Sélectionner de nouveau la cellule H5 pour le critère suivi d'un point-virgule,
• Désigner alors intégralement la colonne E en tapant la référence E:E et fermer la parenthèse,
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets ('') pour conserver la cellule vide lorsque le commercial n'est pas spécifié,
• Fermer la parenthèse de la fonction Si et valider le calcul par la touche Entrée,

La formule que nous avons bâtie est la suivante :

=SI(H5<>''; SOMME.SI(C:C;H5;D:D)/SOMME.SI(C:C;H5;E:E); '')

Le fait de désigner une colonne entièrement permet de rendre le calcul évolutif, si d'aventure de nouvelles saisies de ventes étaient ajoutées à la suite du tableau. Comme la cellule de titre est fusionnée entre B2 et E2, nous n'avons pas pu les spécifier à la souris. Dans ces conditions, il suffit de taper les références, comme nous l'avons fait.

Quoiqu'il en soit, nous avons bien divisé la somme des ventes par la somme des objectifs pour le commercial désigné. Comme le critère est dynamique, cette somme conditionnelle l'est aussi.

• Sélectionner par exemple le vendeur Céhef à l'aide de la liste déroulante,

Nous obtenons un ratio de 82%.

• Sélectionner le vendeur Hamalibou toujours par le biais de la liste,

Le ratio passe à 91%. Nous obtenons bien une synthèse dynamique qu'il s'agit de mettre en valeur pour des exploitations rapides et sans ambiguïté des résultats.



Classement des résultats par tranches
Comme nous l'avons évoqué dans la présentation, il s'agit de reporter le résultat de ce calcul dans la cellule correspondante, entre J6 et J8. Pour vérifier les fourchettes imposées par les contraintes en colonne G, nous devons exploiter la fonction Excel ET dans la zone de critère de la fonction SI. Nous pourrons ainsi vérifier que le ratio est à la fois supérieur à la valeur seuille et inférieur à la valeur plafond.

• En J6, saisir la formule suivante :

=SI(ET($H$5<>''; $J$3>=G5; $J$3<G6); $J$3; '')

La fonction ET impose de vérifier ensemble trois conditions, dans la zone de critère de la fonction SI. Tout d'abord, pour reporter ce résultat, encore faut-il qu'un commercial soit désigné en H5 ($H$5<>''). Nous figeons cette cellule dans le calcul pour pouvoir le répliquer sur les deux lignes du dessous, en continuant de faire référence à cette cellule fixe. Dans le même temps, le taux (J3) doit être supérieur à la plus petite valeur de la tranche inscrite en G5 et inférieur à la plus grande, inscrite en G6 ($J$3>=G5; $J$3<G6). Le taux du commercial est figé car toujours inscrit dans la même cellule. Les bornes des tranches quant à elles restent libres de se déplacer. Ainsi le résultat du commercial sera comparé à l'ensemble des cas. Lorsque tous ces critères sont vérifiés ensemble, le résultat du commercial ($J$3) est reporté dans la tranche correspondante. Dans le cas contraire, la cellule est laissée vide ('').

Pour la mise en évidence des résultats, afin de livrer des interprétations évidentes et instantanées, nous souhaitons créer un graphique en anneau. Ce type de graphique permet de représenter les proportions d'un tout. En d'autres termes, à l'instar des secteurs, la somme des éléments à représenter doit conduire à une valeur totale de 100%. Or dans la colonne J, entre les lignes 6 et 8, une seule valeur est représentée, selon la classification définie par la formule précédente. En J9, nous devons donc calculer la différence pour atteindre 100%. Ainsi nous aurons les données nécessaires pour ensuite initier la création du graphique.

• En J9, saisir la formule suivante :

=SIERREUR(100%-SOMME(J6:J8); '')

Nous exploitons la fonction Excel SiErreur pour gérer l'exception dans le cas où aucun commercial n'est spécifié. Dans ce contexte, comme le spécifie son second argument, nous conservons la cellule vide (''). Sinon le calcul consiste à retrancher la somme des résultats entre J6 et J8 sur 100%. Grâce à cette donnée, la somme des valeurs des cellules comprises entre J6 et J9 conduit nécessairement à 100%.



Comme l'illustre la capture ci-dessus avec les résultats des calculs précédents, il existe une cellule fusionnée sur plusieurs colonnes (I, J et K) et sur plusieurs lignes (13 à 18). Elle est prévue pour reproduire le pourcentage du commercial au centre du graphique, dans la même couleur que ce dernier. Nous allons donc devoir reproduire l'égalité des cellules et y placer une mise en forme conditionnelle afin de faire réagir automatiquement la cellule en fonction du résultat.

• Cliquer dans la cellule fusionnée, soit en I13,
• Taper le symbole = et sélectionner le ratio du commercial en J3 (=J3),

Nous reproduisons ainsi à l'identique le pourcentage du commercial désigné. Vous remarquez la mise en forme atypique prédéfinie sur la cellule avec une taille de police réglée sur 44pt. Sa couleur doit également varier en fonction de la tranche, pour accompagner le graphique et ainsi mettre en évidence les résultats.

• Entre 0 et 75%, couleur de texte rouge,
• Entre 75 et 90%, couleur de texte jaune,
• Entre 90 et 100%, couleur de texte verte,

Ces trois cas peuvent être vérifiés avec une formule simple pour déclencher la mise en forme conditionnelle. Si le résultat en I13 est égal à la valeur de la cellule J6 (Tranche 1), alors le texte doit apparaître en rouge. S'il faut J7, le texte doit apparaître en jaune et en vert s'il vaut J8.

• Sélectionner la cellule I13,
• Cliquer sur le bouton Mise en forme conditionnelle dans le ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le dernier type : Utiliser une formule pour...,
• Puis cliquer dans la zone de saisie de la formule située juste en dessous,
• Cliquer sur la cellule J6 pour définir la condition de la première tranche,
• Cliquer ensuite sur le bouton Format pour définir la mise en forme associée,
• Dans l'onglet Police de la boîte de dialogue qui suit, définir une couleur de texte dans un rouge assez vif ainsi qu'une police grasse,
• Valider ces réglages par Ok,
• De retour sur la première boîte de dialogue, cliquer de même sur Ok pour confirmer la règle,

Si vous sélectionnez le vendeur Houda, le résultat en I13 apparaît en effet dans la couleur rouge de symbolique négative. Il s'agit de reproduire désormais les mêmes opérations sur la même cellule mais en définissant le jaune en cas d'égalité avec J7 et le vert en cas d'égalité avec J8.

• Créer les deux autres règles de mise en forme conditionnelle illustrées par la capture ci-dessous,

Désormais, à la sélection d'un commercial, selon ses résultats, la mise en valeur s'adapte dynamiquement pour fournir un indicateur de performance sans équivoque et exploitable instantanément.

Représentation graphique des résultats
Il s'agit désormais de construire le graphique en anneau afin de renforcer la synthèse dynamique ainsi produite. L'interprétation des résultats s'en trouvera grandement simplifiée. Pour atteindre la cible finalisée présentée en préambule de cette formation, nous devrons réaliser certains réglages et paramétrages sur le graphique. Pour cela, nous nous inspirerons des techniques enseignées par la formation sur la présentation des graphiques dans Excel.

• Choisir le vendeur Houda dans la liste déroulante pour proposer les valeurs de référence,
• Sélectionner la plage de cellules J6:J9,
• Cliquer sur l'onglet Insertion en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Graphiques, cliquer sur la flèche des graphiques en Secteurs,
• Dans la liste proposée, choisir Anneau,
• Le positionner au-dessus de la cellue I13,

Ce type de graphique permet de révéler les proportions des valeurs sélectionnées entre elles. Dans notre cas, il n'existe nécessairement que deux données non nulles sur les quatre sélectionnées. Pour fermer la boucle des 100%, l'anneau commence toujours par représenter la valeur la plus importante. En fonction des résultats du vendeur, il ne s'agira pas de la même section de l'anneau. Pourtant elle figurera au même emplacement. Donc nous pourrons modifier les couleurs selon les tranches, pour donner l'illusion d'un graphique avec mise en forme conditionnelle. Remarque : Nous avons exclu de la sélection les cellules de titres. Nous ne souhaitons conserver en effet ni le titre du graphique ni la légende.

• A l'aide du bouton symbole + situé en haut à droite du graphique sélectionné, décocher Titre du graphique et Légende,

Pour des versions antérieures d'Excel, il est possible de réaliser les mêmes réglages en passant par le ruban Disposition d'un graphique sélectionné.

• Cliquer sur l'onglet Format en haut à droite de la fenêtre Excel, pour activer le ruban contextuel d'un graphique sélectionné,
• Dans la section Styles de formes, cliquer sur le bouton Remplissage,
• Dans la liste, choisir Aucun remplissage,

Désormais nous visualisons les informations à travers la zone du graphique. De fait, la donnée de synthèse du commercial réapparait en arrière-plan. Il convient d'ajuster l'emplacement du graphique pour donner l'illusion que le pourcentage est positionné parfaitement au centre. Comme le fond n'existe plus, il convient de sélectionner le graphique par ses bords. Les flèches du pavé directionnel permettent des ajustements précis.

• Cliquer ensuite sur l'anneau situé au centre du graphique,
• Cliquer cette fois sur le bouton Contour du ruban Format,
• Dans la liste, choisir Sans contour,
• Puis cliquer sur la plus petite portion de l'anneau pour l'isoler dans la sélection,
• Avec le bouton Remplissage du ruban Format, choisir Aucun remplissage,

Comme nous l'évoquions précédemment, il convient désormais d'adapter la couleur de la portion restante de l'anneau. En réalité, ces portions sont au nombre de quatre. Nous venons d'en masquer une. Les deux autres le sont naturellement puisque leur cellule correspondante est vide. Lorsque nous changerons de commercial, une nouvelle section apparaîtra au profit de l'actuelle qui se masquera. C'est la raison pour laquelle nous allons leur attribuer des couleurs différentes, dont la symbolique mettra en exergue les performances de chacun.

• A l'aide de la liste déroulante, choisir le commercial Houda,
• Cliquer sur les anneaux de sa représentation graphique,
• Puis, cliquer une seconde fois sur la portion de l'anneau en couleur pour l'isoler,
• A l'aide du bouton Remplissage du ruban Format, lui appliquer un rouge identique à celui de la mise en forme conditionnelle de son résultat en pourcentage,
• A l'aide du bouton Effets, lui appliquer une ombre portée, peu importe la variante,
• Choisir maintenant le vendeur Céhéf avec la liste déroulante,
• Sélectionner la partie en couleur de son anneau,
• Lui appliquer un remplissage jaune-orangé identique au format dynamique de son résultat,
• Lui attribuer une ombre portée identique à la précédente,
• Enfin, choisir le commercial Hamalibou et resélectionner la portion de son anneau,
• Lui appliquer un remplissage vert identique au format dynamique de son résultat,
• Puis, réaliser une ombre portée comme pour les deux autres,

Désormais, en choisissant un vendeur dans le but d'afficher ses résultats synthétisés, la couleur du graphique s'adapte en fonction de ses performances. Nous donnons bien l'illusion d'une mise en forme conditionnelle sur le graphique. Les synthèses sont on ne peut plus probantes. La dominante de couleur évoque d'un seul coup d'oeil la qualité des résultats obtenus.



Comme l'illustre la capture ci-dessus, le jaune orangé pour le vendeur Céhef, présente sans équivoque des résultats passables. A la lecture instantanée, la direction sait que le vendeur doit améliorer ses ventes mais qu'il semble être sur la bonne voie.

Synthèse des résultats par vendeur
Pour renforcer la pertinence du rendu que nous avons conçu jusqu'alors, nous souhaitons greffer les chiffres synthétisés du vendeur, en dessous de la zone de graphique. Dans un deuxième temps, à la sélection d'un commercial, nous souhaitons que les lignes qui le concernent dans le tableau, ressortent en couleur dynamique.

• Entre les cellules H23 et J24, reproduire le tableau de synthèse comme proposé sur la capture,

En H24 et I24, il s'agit de synthétiser respectivement la somme des chiffres réalisés et la somme des objectifs, pour le vendeur sélectionné. Le vendeur est donc la condition du calcul. Il s'agit d'exploiter comme précédemment, la fonction Excel Somme.Si, à deux reprises donc. Dans les deux cas, le critère doit être vérifié sur la colonne C. Dans le premier cas, l'addition doit se faire sur la colonne D, puis sur la colonne E dans le deuxième cas.

• En H24, saisir la formule suivante :

=SI($H$5<>''; SOMME.SI($C:$C;$H$5;D:D);'')

Nous exploitons tout d'abord la fonction Si comme un garde-fou, afin de ne générer aucune erreur. Si un commercial est bien désigné ($H$5<>''), alors nous réalisons le calcul, sinon nous conservons la cellule vide (''). Nous figeons la colonne sur laquelle le critère doit être cherché ($C:$C). En effet, le calcul des objectifs utilise cette même rangée. Pour la même raison, nous figeons la cellule du critère ($H$5). En revanche, nous laissons libre la colonne D. Le calcul suivant doit se faire sur la colonne E. En tirant la poignée, elle se déplacera donc.

• Tirer la poignée de ce calcul sur la cellule I24 pour le reproduire,

Nous obtenons bien des résultats de synthèse dynamiques. Si vous changez le commercial, les calculs s'adaptent puisque le critère change. Le dernier calcul en J24 consiste simplement à comptabiliser le nombre de ventes par commercial. Nous devons exploiter la fonction Excel Nb.Si. Elle permet de réaliser un dénombrement selon un critère à vérifier sur une plage de cellules. Sa syntaxe est la suivante :

=Nb.Si(Plage_de_cellules ; Critère_à_vérifier)

• En conséquence, en J24, taper la formule suivante :

=SI(H5<>''; NB.SI(C:C;H5);'')

Nous souhaitons maintenant appuyer les résultats en insistant sur la dominante de couleur. Pour cela nous proposons d'appliquer trois règles de mise en forme conditionnelle sur le tableau du détail des ventes. Si la vente est située dans la fourchette 0 à 75%, toute la ligne du vendeur doit ressortir en rouge, en jaune-orangé pour la fourchette 75-90% et en vert entre 90 et 100%.

• Sélectionner la plage de cellules B6:E53,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue, sélectionner le type : Utiliser une formule pour...,
• Cliquer dans la zone de saisie de la formule juste en-dessous pour l'activer,
• Saisir la formule suivante :

=ET($C6=$H$5; $D6/$E6>=$G$5; $D6/$E6<$G$6)

Le premier critère consiste à vérifier que le commercial pour la ligne en cours est bien celui désigné par la liste déroulante ($C6=$H$5). La cellule du tableau n'est figée qu'en colonne. Le nom du commercial est toujours situé en colonne C. Mais comme il faut vérifier ce critère sur l'ensemble du tableau, nous la laissons se déplacer en ligne. Les deux critères suivants consistent à vérifier que le rapport Vente/Objectif ($D6/$E6>=$G$5; $D6/$E6<$G$6) est bien compris dans la fourchette des contraintes, la toute première ici. Les cellules du tableau sont figées seulement en colonne pour la même raison que précédemment. Les cellules des contraintes quant à elles sont intégralement figées puisqu'elles ne doivent pas bouger.

• Cliquer sur le bouton Format et choisir un remplissage rouge identique à celui du graphique,
• De même, choisir une police grasse de couleur claire,
• Valider ces réglages de mise en forme conditionnelle,

Il s'agit de reproduire deux fois cette règle mais en adaptant les cellules des tranches de contraintes et les couleurs associées. Les formules respectives sont les suivantes :

• Pour le jaune : =ET($C6=$H$5; $D6/$E6>=$G$6; $D6/$E6<$G$7),
• Pour le vert : =ET($C6=$H$5; $D6/$E6>=$G$7; $D6/$E6<$G$8).

Nous obtenons une synthèse instantanée remarquablement simple à exploiter au premier coup d'oeil. La partie droite de la feuille résume les performances avec le niveau de satisfaction par indicateur de couleur. La partie gauche révèle le détail mis en surbrillance pour l'élément en cours d'étude dans la partie droite.