Gestion des remises commerciales avec Excel

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


Sujets que vous pourriez aussi aimer :


Fidélisation et remises commerciales avec Excel

Ce cas pratique Excel consiste à mettre en oeuvre les formules que nous avons apprises afin de réaliser une gestion commerciale. En fonction du chiffre des commandes passées par ses clients, une entreprise souhaite appliquer des remises commerciales, selon différents barèmes. Cette action consiste à fidéliser les clients les plus précieux. Cette gestion doit être totalement automatisée.



Chiffre d'affaires et délais de paiement
Bien sûr pour que des remises soient accordées, le client doit respecter les délais de paiement.

• Télécharger le classeur remises-commerciales-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Les clients avec leur nom sont recensés dans la colonne A du tableau. En colonne B, une information précise si le client en question respecte les délais de paiement. Puis, les quatre colonnes suivantes synthétisent les chiffres d'affaires correspondant aux commandes passées par chacun des clients, au cours des quatre trimestres de l'année. Le premier calcul à réaliser pour ensuite déduire les remises, est celui du chiffre d'affaires global de l'année par client. La méthode la plus simple pour réaliser ce calcul, consiste à sélectionner tous les chiffres d'affaires avec les cellules vides de la colonne G, pour montrer à Excel où placer les sommes en ligne.

• Sélectionner la plage de cellules C11:G16,
• Puis, réaliser le raccourci clavier ALT + =,

Alt+ = est le raccourci clavier de la somme automatique. Comme la sélection incluait les cellules vides dans lesquelles déposer les résultats, sans étape intermédiaire, Excel réalise les sommes automatiques des chiffres d'affaires par ligne, soit par client. Vous remarquez que les résultats adoptent un format numérique avec séparateur de milliers.

• Sélectionner la plage de cellules G11:G17,
• Réaliser le raccourci Alt + = ou cliquer sur le bouton Somme automatique,

Nous obtenons ainsi en G17 le chiffre d'affaires global réunissant tous les clients, au cours de l'année.

Pour que les calculs suivants puissent se faire, en colonne H doit apparaître la mention NON du titre de la colonne si le client ne paie pas dans les délais et rien dans le cas contraire. Nous devons donc utiliser la fonction Excel Si qui permet de gérer les critères.

• Sélectionner les cellules du calcul de la colonne H, soit la plage H11:H16,
• Taper le symbole = pour débuter le calcul conditionnel,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la première cellule des délais de paiement, soit B11,
• Taper le symbole = pour établir la comparaison du critère,
• Sélectionner le titre de la colonne H soit la cellule H10,
• Puis enfoncer la touche F4 pour la figer,

Ce calcul doit en effet être répercuté sur les cellules du dessous. Excel déplacera les références des cellules impliquées dans le calcul pour suivre le déplacement. Or chaque cellule de la colonne B doit être comparée à la cellule H10 qui ne doit pas suivre le déplacement. La touche F4 du clavier permet de figer cette dernière qui devient une référence absolue.

• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Sélectionner de nouveau la cellule B11,

En effet, si le critère est vérifié, cela signifie que le client ne respecte pas les délais de paiement. En conséquence, la mention NON doit s'afficher dans la colonne du résultat. Dans le cas contraire (Sinon), rien ne doit être inscrit, ce qui se traduit par un texte vide entre guillemets dans un calcul Excel. Pour ce faire :

• Taper un nouveau point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets et fermer la parenthèse,
• Puis, valider le calcul par CTRL + Entrée,

Le raccourci CTRL + Entrée permet de répliquer le calcul sur l'ensemble d'une plage de cellules, lorsque cette dernière est présélectionnée. Mais rien n'empêche de réaliser la formule seulement sur la première cellule, puis de la répliquer en tirant sa poignée.

Remise commerciale - Analyse multicritères
En colonne I, il s'agit d'afficher le pourcentage de remise accordée sur le chiffre d'affaires globale induit par le client. Tout d'abord si le client ne respecte pas les délais de paiement, aucun geste ne lui est accordé et nous devons inscrire le chiffre 0.


Tous les autres cas sont listés dans le petit tableau de bord présenté par la capture ci-dessus. Si le CA total engendré par le client est inférieur à la borne supérieure du taux 1, alors une remise de 1% lui est accordée. Si son CA est compris entre la borne supérieure du taux 1 et la borne supérieure du taux 2, on lui accorde une remise équivalente au taux 2, soit 2%. Et dans le dernier cas où son CA dépasse la borne supérieure du taux 2, c'est le taux de remise 3 qui lui est accordé, soit 5%.

Comme il y a quatre possibilités, nous devons utiliser trois fonctions Si (4-1). Et comme cette gestion commerciale doit être automatisée en considérant des variables qui peuvent changer, nous devons utiliser une fois de plus les références absolues, afin d'exploiter les données du tableau de bord.

• Sélectionner les cellules à calculer en colonne I, soit I11:I16,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la cellule H11 et taper le symbole =,
• Puis, sélectionner la cellule H10 et la figer à l'aide de la touche F4 du clavier,

Nous venons de poser le premier critère de la première fonction conditionnelle (=SI(H11=$H$10). Il consiste à savoir si le client ne respecte pas les délais (valeur NON). La cellule H10 est donc figée pour qu'elle reste l'élément de comparaison de référence en répliquant la formule sur les lignes du dessous. Dans ce cas, aucune remise ne doit être accordée.

• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Saisir le chiffre 0 et taper un nouveau point-virgule pour accéder à la partie Sinon,

Comme il reste à vérifier tous les seuils indiqués par le tableau de bord, la formule ne peut se terminer ainsi. Il s'agit d'imbriquer de nouvelles fonctions conditionnelles à l'intérieur de la première pour augmenter le nombre de critères possibles.

• Saisir de nouveau la fonction suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner le premier CA, soit G11 et saisir le symbole inférieur (<),
• Cliquer alors sur la cellule du plafond du taux 1, soit H5,
• Puis, enfoncer la touche F4 pour la figer,

Le critère consiste à savoir si le CA en cours est inférieur au seuil du taux 1 (SI(G11<$H$5). Pour qu'il puisse être utilisé comme élément de référence sur toute la colonne, nous le figeons. Si ce critère est vérifié, nous devons accorder la valeur du taux 1, soit 1%, dans le cas contraire nous devons le comparer au plafond du taux 2.

• Taper un point-virgule pour passer dans la branche Alors de la deuxième fonction SI,
• Sélectionner la valeur du taux 1, soit I5 et la figer (F4),
• Taper un point-virgule pour passer dans la branche SINON de la deuxième fonction SI,
• Saisir de nouveau le nom de la fonction suivi d'une parenthèse ouvrante soit SI(,
• Sélectionner de nouveau le premier CA, soit G11 pour le comparer au deuxième seuil,
• Ajouter le symbole inférieur (<) et cliquer sur la cellule du deuxième plafond, soit H6,
• Figer cette cellule à l'aide de la touche F4,

Dans cette dernière fonction Si, nous vérifions si le CA est inférieur au deuxième plafond (SI(G11<$H$6). Dans la chronologie, cela sous-entend que le premier critère n'a pas été vérifié et que le CA est nécessairement supérieur au plafond du premier taux. Si cette deuxième condition est vérifiée, c'est le taux 2 qui doit être appliqué, dans le cas contraire, il s'agit du taux 3. Le cas contraire correspond au dernier Sinon. Il s'agit de la branche qui induit qu'aucun des critères jusqu'alors n'a été vérifié. Nous en déduisons que le CA en cours est nécessairement supérieur au plafond du deuxième taux. En conséquence, c'est le dernier taux qui est choisi.

• Taper un point-virgule et cliquer la cellule I6 du taux 2 puis la figer,
• Saisir un dernier point-virgule pour accéder au dernier Sinon,
• Sélectionner la cellule du taux 3 soit 7 et la figer,
• Fermer trois parenthèses pour boucler toutes les fonctions SI imbriquées,
• Valider le calcul par CTRL + Entrée pour le répliquer sur toutes les cellules présélectionnées,

Tous les pourcentages de primes accordés s'inscrivent dans les cellules correspondant aux clients, en fonction des critères auxquels ils répondent. Comme ces calculs ont été bâtis sur les variables du tableau de bord à l'aide des références absolues, la gestion commerciale est parfaitement automatisée. A l'avenir, il s'agira pour les années suivantes d'inscrire les nouveaux CA par trimestre, éventuellement de changer les conditions des taux, et tous les calculs se mettront à jour dynamiquement. Si ce tableau Excel devait servir de modèle pour la gestion client, il conviendrait alors de protéger les cellules des formules comme nous l'avions fait dans la facturation client automatisée.

Il ne reste plus qu'à calculer les montants des remises accordées dans la colonne J. Il s'agit d'une simple multiplication du pourcentage de remise par le chiffre d'affaires annuel du client. Mais comme l'indique la mention du titre de la colonne, ce résultat doit être arrondi à deux décimales. En conséquence le calcul de la multiplication doit être intégré dans la fonction Excel Arrondi dont la syntaxe est la suivante : =ARRONDI(nombre_a_arrondir; nombre_decimales_a_conserver). Le nombre à arrondir est le résultat de la multiplication. Le nombre de décimales est 2.

• Sélectionner l'ensemble des cellules des remises à calculer, soit J11:J16,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Arrondi(,
• Sélectionner la cellule du pourcentage de remise pour le client, soit I11,
• Taper le symbole de l'étoile pour la multiplication (*),
• Sélectionner la cellule du CA pour le client, soit G11,
• Saisir un point-virgule pour passer à l'argument du nombre de décimales,
• Saisir le chiffre 2 pour ne conserver que 2 décimales après calcul,
• Fermer la parenthèse de la fonction et répliquer le calcul par CTRL + Entrée,

Chaque remise ainsi arrondie à deux décimales s'affiche dans la ligne du client en fonction des critères auxquels il correspond. Aucune référence absolue n'était ici nécessaire dans la mesure où chaque cellule impliquée dans le calcul, devait bien suivre le déplacement, en changeant de ligne pour correspondre à chaque client. Nous aurions pu utiliser les fonctions Excel Arrondi.Inf ou Arrondi.Sup pour respectivement arrondir, au nombre inférieur ou au nombre supérieur, selon les décimales précisées.

• Sélectionner la plage de cellules J11:J17,
• Réaliser le raccourci clavier Alt + =,

Nous calculons ainsi instantanément en J17, la somme de toutes les remises accordées aux clients.

Le dernier calcul en J18 consiste à savoir quel est la part des remises accordées par rapport à l'ensemble des chiffres d'affaires réalisés. Il s'agit donc de diviser la somme des remises accordés par la somme des chiffres d'affaires réalisés par les clients.

• En J18, réaliser le calcul suivant :=J17/G17,

Au final nous constatons donc que l'entreprise accorde 3,04% de remise par rapport à l'ensemble des chiffres d'affaires apportés par les clients. Notre gestion commerciale est terminée et peut s'avérer fort utile à toute entreprise désireuse de suivre ses clients et de les fidéliser. En effet, comme tous nos calculs sont dynamiques et automatisés, chaque société peut à sa guise, modifier les conditions de remise dans le tableau de bord, pour que les résultats se mettent à jour en fonction de ces nouvelles contraintes.