Comment utiliser le solveur d’Excel ?

Aujourd’hui, je vous propose de découvrir une fonctionnalité avancée d’Excel : le solveur qui permet, un peu à la manière de l’outil de détermination de la valeur cible, d’obtenir automatiquement la valeur à affecter à une cellule pour obtenir un résultat donné sur une autre cellule. Nous verrons également que le solveur va permettre de définir des contraintes qui vont afin d’ajuster très précisément le résultat retourné par celui-ci.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Le solveur

Dans ce tutoriel, nous allons découvrir le fonctionnement du solveur d’Excel.

Il s’agit d’un outil de simulation extrêmement pratique et puissant proposé par Excel qui permet, à l’instar du calcul de la valeur cible que nous avons découvert récemment, d’effectuer une série de simulations pour déterminer la valeur à donner à une ou plusieurs cellules pour arriver à un résultat.

En outre, la force du solveur par rapport à la valeur cible est que nous allons pouvoir définir des contraintes à appliquer sur l’analyse, ce qui permet de mettre en place des simulations bien plus complexes.

Avant même de pouvoir utiliser le solveur, il convient de l’activer dans Excel.

En effet, il s’agit d’une fonctionnalité bien présente dans Excel, mais celle-ci est cachée, et disponible sous la forme d’un complément Excel.

Pour l’activer, nous devons nous rendre dans le menu Fichier > Options :

Puis dans la fenêtre des options, choisir la rubrique « Compléments », puis tout en bas dans le menu Gérer, choisir l’options « Compléments Excel » et cliquer sur le bouton [Atteindre] :

Dans la fenêtre suivante, nous pouvons activer l’option « Complément Solveur » et valider en appuyant sur le bouton [OK].

Le solveur est maintenant activé, et nous le retrouverons dans menu « Données », tout à droite :

2. Cas d’étude

Pour illustrer le fonctionnement du Solveur, nous allons utiliser l’exemple suivant :

Nous retrouvons ici l’analyse de la rentabilité de la production d’une entreprise qui vend deux types de produits : un produit A vendu 167€ et un produit B vendu 268€.

Chacun de ces produits nécessite de passer en atelier et évidemment une matière première :

• Le produit A nécessite 14 minutes d’atelier et 7kg de matière
• Le produit B nécessite 32 minutes d’atelier et 4kg de matière

En outre, l’atelier peut fonctionner 6h par jours, pour un total de 7200 minutes par mois pour un coût de fonctionnement global de 40€ de l’heure.

Quant à la matière, le stock auquel l’entreprise peut prétendre est de 1500kg, dont le coût d’achat est estimé à 14€/kg.

Pour finir, l’entreprise est déjà engagée à livrer 100 unités de produits A.

L’objectif va donc être de déterminer le nombre de produits A et B à produire pour maximiser sa marge, en supposant que tous les produits sortis de l’atelier seront immédiatement vendus.

Il est évidemment possible de déterminer les nombres de produits A et B manuellement, mais nous préfèrerons laisser Excel réaliser ce calcul pour nous en utilisant le solveur.

Dans le tableau d’analyse, nous retrouvons pour chaque produit une cellule avec la quantité à produire :

Ces quantités sont ici définies à une unique unité, mais nous allons souhaiter définir les valeurs qui permettent d’optimiser la marge.

À partir du prix de vente unitaire, nous allons retrouver le CA TTC correspondant, ainsi que le CA HT compte tenu d’un taux de 20% :

Les coûts des matières et de l’atelier sont ensuite calculés à partir des temps d’occupations et de matières consommées :

Ce qui permet de déterminer la marge, laquelle est égale au CA HT duquel les coûts sont retranchés :

3. Utiliser le solveur

Nous pouvons maintenant lancer le solveur :

La première chose à faire va être de sélectionner la cellule dans laquelle se trouve se trouve l’élément que nous allons souhaiter optimiser en l’occurrence, la marge :

Dans la zone suivante nous pouvons spécifier si nous souhaitons :

• Maximiser le résultat de cette cellule,
• Le minimiser,
• Ou atteindre une valeur donnée (comme pour la valeur cible)

Ensuite, nous allons sélectionner les cellules variables que le solveur va pouvoir modifier pour optimiser la marge (les cellules dans lesquelles se trouvent les quantités) :

Nous allons maintenant saisir les contraintes qui vont permettre de guider le solveur dans sa recherche d’optimisation en cliquant sur le bouton [Ajouter] :

• Tout d’abord, vouloir que les cellules à définir ne puissent avoir que des valeurs entières (nous ne pouvons produire que des produits entiers). Nous sélectionnons donc la cellule C21 et dans le menu déroulant, nous choisissons « ent », pour « entier » :

• Ensuite, nous appuyons sur le bouton [Ajouter] pour insérer une autre contrainte,
• Nous répétons l’opération avec la cellule D21 :

• Puis, nous fixons le nombre de minutes maximum d’occupation de l’atelier à 7200 au maximum (« F31 <= 7200 ») :

• Et que le poids des matières utilisées ne peut dépasser 1500kg (« F32 <= 1500 ») :

• Et enfin, nous allons fixer le nombre minimal de produits A vendus à 100 unités (« C21 >= 100 »):

Lorsque la dernière contrainte est enregistrée, nous pouvons appuyer sur le bouton [OK] pour la valider et revenir sur la fenêtre précédente, dans laquelle nous retrouverons chacune d’entre elles :

Pour finir, nous choisissons le type de résolution à utiliser :

Il s’agit ici d’une résolution linéaire, en utilisant l’algorithme de résolution simplexe (SIMPLEX PL).

Nous pouvons maintenant demander à Excel de résoudre notre problématique en appuyant sur le bouton [Résoudre] :

Le solveur nous présente alors le résultat de son analyse : pour générer une marge maximale, définie à 46k€, l’entreprise doit produire et vendre 114 articles A et 175 articles B.

Nous pouvons ici :

• Valider la modification proposée en sélectionnant l’option « Conserver la solution du solveur »,
• Rétablir les données d’origine en sélectionnant l’option « Rétablir les valeurs d’origine »,
• Choisir de revenir sur la fenêtre de paramétrage du solveur pour le modifier, en cochant l’option « Retourner dans la boite de dialogue Paramètres du solveur »,
• Afficher un « Rapport du plan » après validation (dans ce cas il faut sélectionner le rapport dans la fenêtre au-dessus)
• Enregistrer le scénario pour pouvoir l’appeler par la suite depuis la fenêtre

Ici, nous choisissons de conserver la solution, et d’afficher le rapport de plan, puis nous appuyons sur la touche [OK] :

Nous retrouvons le détail des calculs dans un nouvel onglet :