Votre responsable est très content du tableau de bord que vous lui avez fourni ! 😀
Il vous demande maintenant de travailler sur la stratégie de la région avec lui, afin d'atteindre 150 000 € de ventes en Bulgarie en 2021. Pour travailler sur cette question, vous allez utiliser des outils d’analyse.
Répondez à un objectif précis grâce aux valeurs cibles
Vous vous rappelez vos cours de mathématiques quand vous étiez petit ? Ces fameux problèmes qui vous causaient (peut-être) des soucis ?
Eh bien rassurez-vous ! Ici, Excel va vous aider à les résoudre ! 😉
Dans un premier temps, nous allons donc poser le problème.
Sachant que :
le prix de vente moyen d’un vêtement est de 8,20 € ;
les charges de conditionnement sont fixes, 623 €.
Combien de vêtements faut-il vendre pour atteindre 150 000 € de ventes ?
Si vous entrez ces données dans un tableau Excel, le calcul du résultat revient à ceci : résultat = nombre de ventes x prix moyen d'un vêtement – charges fixes.
Pour qu’Excel trouve la valeur de la cellule jaune qui répondra à la cible à obtenir, vous allez utiliser la fonctionnalité de la valeur cible.
Grâce à cet outil, Excel va tester toutes les valeurs possibles d’une cellule, afin qu’une autre cellule atteigne une valeur précise.
Dans notre exemple :
Dans l’onglet Données, cliquez sur le bouton "Analyse scénario”
, puis sur la ligne “Valeur cible…".Dans la zone “Cellule à définir”, sélectionnez la cellule contenant la formule du résultat, B5.
Renseignez la valeur à atteindre, ici 150000.
Renseignez la cellule à modifier, ici le nombre de ventes à réaliser, la cellule B4.
Quand vous cliquez sur OK, Excel va chercher la valeur idéale de B4, afin que B5 atteigne la valeur cible.
Si vous souhaitez conserver la valeur trouvée par Excel, cliquez sur “OK”, et Excel remplit la cellule B4. Sinon cliquez sur Annuler.
Dans ce problème à une inconnue, Excel a trouvé la solution en quelques secondes ! 😊
Résolvez vos problèmes complexes grâce au solveur
Si vous avez plusieurs inconnues dans votre problème, c’est alors le Solveur qui va vous aider.
Le solveur ? Qu’est-ce que c’est ?
Le solveur est un outil d’analyse comme la valeur cible, sauf qu’il va plus loin. L’objectif à atteindre peut être :
une valeur exacte (ex. : objectif de 50 000 € de ventes) ;
une valeur minimum (ex. : au moins 50 000 € de ventes) ;
une valeur maximum (ex. : Quel montant maximum de ventes je peux atteindre avec ces critères ?).
De plus, le solveur inclut la possibilité d’ajouter des limites (appelées “contraintes” dans Excel) au problème, comme par exemple :
L’usine de production de vêtements ne peut pas produire plus de 1 000 vêtements par mois.
La matière première d’un vêtement coûte entre 5 € et 10 € selon le fournisseur.
Excel peut gérer jusqu’à 200 contraintes cumulées ! 😀
Installez le complément solveur
Le solveur n’est pas installé par défaut dans Excel, il s’agit d’un complément gratuit, qu’il faut simplement activer.
Pour l’activer :
Dans l’onglet “Fichier”, cliquez sur le bouton “Options” tout en bas de l’écran.
Cliquez sur le menu” Compléments” puis sur le bouton “Atteindre…” en bas de la fenêtre.
La liste des compléments activables apparaît.
Cochez la case Complément Solveur, et cliquez sur OK.
Et voilà ! le solveur est installé. 😊
Utilisez le solveur
Revenons à notre problématique : atteindre 150 000 € de ventes en Bulgarie en 2021.
Le prix des différents types de vêtements n’étant pas le même, vous devez en tenir compte dans votre projection de ventes. De plus, vous ne pouvez pas produire plus d’une certaine quantité de vêtements par an, c’est la limite de votre usine de production.
Ainsi,
un vêtement de la catégorie “Haut” rapporte 10 € en moyenne, et sa production maximale est de 6 000 unités ;
un vêtement de la catégorie “Bas” rapporte 7 € en moyenne, et sa production maximale est de 6 000 unités ;
un vêtement de la catégorie “Haut et bas” rapporte 15 € en moyenne, et sa production maximale est de 4 500 unités ;
les charges fixes de conditionnement pour chaque catégorie de vêtement sont de 623 €.
Intégrez ces données dans un tableau Excel, sans oublier la cible à atteindre :
Vous pouvez trouver ce tableau en téléchargeant ce fichier.
Pour vous aider à résoudre ce problème ayant de multiples conditions, utilisez le solveur :
Dans l’onglet “Données”, groupe “Analyse”, cliquez sur le bouton
.La fenêtre du solveur comporte 3 zones :
L’objectif à définir, ici la cellule E6, et en dessous, sa valeur à atteindre : 150 000.
Les cellules variables : ce sont les cellules jaunes (
B5:D5), sur lesquelles le solveur va tester différentes combinaisons jusqu’à atteindre l’objectif voulu.Les contraintes, qui sont les limites de notre problème :
les ventes de Hauts doivent être inférieures ou égales à 6 000 unités ;
les ventes de Bas doivent être inférieures ou égales à 6 000 unités ;
les ventes de Haut et bas doivent être inférieures ou égales à 4 500 unités ;
de plus, vous devez indiquer au solveur que les valeurs des cellules jaunes doivent être des chiffres entiers : vous ne pouvez pas produire 0,3 haut. 😊
Dans l’objectif à définir, sélectionnez la cellule contenant le calcul du total, soit $E$6.
Cochez l’option “Valeur”, avec l’objectif à atteindre, 150000.
Cliquez sur le bouton “Ajouter” afin de créer la première contrainte, celle disant que les ventes de Hauts (cellule B5) ne doivent pas dépasser 6 000 unités (cellule B4) :
Cliquez sur “Ajouter”, et créez les deux autres contraintes des ventes des vêtements “Bas” et “Haut et bas”.
Cliquez sur Ajouter, afin de créer la contrainte d’utiliser un nombre entier de ventes. Remplissez la fenêtre comme ceci :
Cliquez sur “Ajouter” et créez deux contraintes similaires pour les ventes des vêtements “Bas” et “Haut et bas”.
Enfin cliquez sur OK, la fenêtre du solveur doit être celle-ci :
Cliquez sur “Résoudre”
Le solveur vous indique qu’il a trouvé une solution : vous cliquez sur OK pour conserver la solution du solveur.
Vous constatez que les cellules jaunes sont maintenant remplies, et le total fait bien 150 000€ ! 😀
Pour atteindre ce chiffre en prenant en compte les contraintes, le solveur conseille de vendre 5 662 vêtements "Haut", 3 977 vêtements "Bas", et 4 494 vêtements "Haut et bas".
Testez différents scénarios de données
Votre responsable et ses collègues hésitent concernant les prix des vêtements à appliquer. Chacun a son idée de prix moyen concernant chaque catégorie de vêtement, et votre responsable vous demande quelle combinaison de prix serait la plus rentable pour l'entreprise.
Voici les différentes propositions de prix :
Proposition de votre responsable : 9 € pour les hauts, 8 € pour les bas, 14 € pour les hauts et bas.
Proposition de son collègue Tim : 10 € pour les hauts, 7 € pour les bas, 15 € pour les hauts et bas.
Proposition de son collègue Rob : 11 € pour les hauts, 8 € pour les bas, 13 € pour les hauts et bas.
Grâce à Excel, vous pouvez comparer ces propositions en créant des scénarios ! 😊
À cette étape, je vous conseille de télécharger le fichier avec les différents scénarios pour suivre la démonstration ci-dessous.
Préparez l’analyse de vos scénarios
Commencez par préparer le tableau de formules qui calcule le montant des ventes total :
Créez vos scénarios
Créez le scénario de votre responsable
Dans l’onglet “Données”, cliquez sur le bouton “Analyse scénarios”, puis sur la ligne “gestionnaire de scénarios…”
Cliquez sur le bouton “Ajouter...”.
Nommez le premier scénario, ici “Avis du responsable”.
Sélectionnez les cellules variables, ici les cellules en jaune (
B2:D2).Cliquez sur OK.
La fenêtre des variables apparaît, saisissez la première proposition.
Cliquez sur OK.
Créez le scénario de Tim
Répétez les étapes ci-dessus pour le deuxième scénario, avec comme prix moyens : 10 € pour les hauts, 7 € pour les bas, 15 € pour les hauts et bas.
Créez le scénario de Rob
Répétez les étapes ci-dessus pour le troisième scénario, avec comme prix moyens : 11 € pour les hauts, 8 € pour les bas, 13 € pour les hauts et bas.
Comparez les différents scénarios
Cliquez sur le bouton “Synthèse…”
Sélectionnez la cellule contenant la formule du résultat, soit la cellule E5, puis cliquez sur OK.
Et voilà ! Excel vient de créer un nouvel onglet, et fait tout seul la comparaison des 3 propositions :
À vous de jouer !
Téléchargez ce fichier et réalisez les opérations suivantes :
Utilisez la valeur cible afin de trouver la réponse du problème 1 (cellule verte). L’objectif est d‘arriver à 75 000 €.
Dans le problème 2, utilisez le solveur afin d’atteindre l’objectif de 70 000 € de ventes, en jouant sur le prix d'achat, le prix de vente et le nombre de ventes (les cellules orange).
Vos contraintes sont :
le prix d’achat se situe entre 4 € et 6 € ;
le prix de vente se situe entre 22 € et 26 € ;
la marge minimale doit être de 20 € ou plus.
Corrigé
Vous pouvez consulter ce fichier et regarder la vidéo ci-dessous pour vérifier votre travail.
En résumé
Excel propose différents outils d’analyse pour répondre à des problèmes complexes.
Vous pouvez résoudre des problèmes à une inconnue, grâce à la valeur cible.
Vous pouvez résoudre des problèmes complexes, avec de multiples limites, grâce au solveur.
Vous pouvez comparer des jeux de données différents en utilisant les scénarios.
Vous venez d’aider votre responsable à choisir la meilleure stratégie, bravo ! 🥳. Suivez-moi dans le chapitre suivant pour apprendre à traiter de grandes quantités de données.
