Excel - Exercice sur le Solveur

Introduction
Avant de commencer
Activer le Solveur
Ajouter des contraintes
Options du Solveur
Résoudre le problème
Références

Introduction

Le solveur est un outil complémentaire que vous pouvez utiliser pour optimiser le modèle que vous avez déjà préparé. Vous pouvez essayer de maximiser vos profits, minimiser vos pertes ou atteindre une objectif avec le moindre de ressources possibles. Il ne faut surtout pas oublier d’ajouter les contraintes qui limitent votre modèle. Sinon, votre résultat ira vers l’infini !

Avant de commencer

Pour suivre cet exercice, vous pouvez aller chercher le classeur demos-excel-3.xlsx qui se trouve sur la page des fichiers de démonstrations.

Sélectionnez la feuille de calcul Modèle.

Word 2003
Word 2007
Excel
Excel
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière

De la liste des macros complémentaires, activez l’option Complément Solver.
Appuyez sur le bouton OK.

 C’est à la fin de l’onglet Données que vous allez maintenant retrouver le bouton pour l’outil Solveur.

L’entreprise désire optimiser son profit trimestriel. Mais elle a aussi certaines contraintes qu’elle doit respecter. Le taux de croissance mensuel de l’entreprise (B21) doit être entre 15% et 150%. La capacité de vente initiale du produit 100 (B3) doit être entre 45 000 $ et 250 000 $. La capacité de vente initiale du produit 200 (B4) doit être entre 10 000 $ et 125 000 $. La capacité de vente initiale du produit 300 (B5) doit être entre 5 000 $ et 75 000 $.  Avec ces informations, utilisez le Solveur pour trouver la solution optimale.

Allez à l’onglet Données.
Appuyez sur le nouveau bouton Solveur.

La cellule à définir est celle que vous désirez optimiser. Pour cet exemple, il s’agit de la cellule D19 qui contient le profit cumulatif pour le trimestre. Les cellules variables seront B21 (taux de croissance) ainsi que B3, B4 et B5 (ventes du premier mois). Veuillez noter que les cellules variables doivent toujours être des cellules ayant que des chiffres; jamais de formules.  Il faut ensuite entrer les contraintes.

Appuyez sur le bouton Ajouter et ajoutez les contraintes suivantes.

Ces dernières vont s’assurer que le modèle n’aura pas une tendance à se diriger vers l’infini.

Ajouter des contraintes

Sans contraintes pour chaque variable que vous avez sélectionnez, le Solveur donnerait une solution allant vers l'infiniment grand ou l'infiniment petit selon votre modèle. Les contraintes forcent le Solveur à prendre en considération les limites de vos cellules variables et de votre modèle. Le Solveur peut gérer jusqu'à 200 contraintes.

Por accélérer le processus, toutes les contraintes ont déjà été ajoutées sauf celles concernant la cellule B3. Il faut déterminer la limite supérieure et inférieure de celle-ci.

Appuyez sur le bouton Ajouter et ajoutez les contraintes suivantes.

Dans la case Cellule, entrez B3.
Dans la case Contrainte, entrez B32.

Ne prenez pas en considération les "$" dans les références de cellules.

Égale à

Le contenu de la cellule doit être égal à un montant déterminé.

Supérieur ou égale à

Le contenu de la cellule soit être supérieur ou égale à un montant déterminé. Cela veut dire qu'on détermine le "plancher" de la cellule puisque la valeur ne peut être en dessous du seuil déterminé.

Inférieur ou égale à

Le contenu doit être inférieur ou égal à un montant déterminé de la case contrainte. Cette contrainte détermine le "plafond" possible puisque rien ne peut aller au-dessus.

Entier

Le contenu de la cellule doit donner un chiffre entier; sans aucune fraction.

Bin

Le contenu peut être que VRAI ou FAUX

Pour cette exercice, sélectionnez la contrainte >= .

Le contenu de la cellule B32 est la limite inférieure déterminée pour ce modèle ou son "plancher". Il est possible de mettre une valeur dans la cellule. Mais cela n'est pas recommandé. Il est plus facile de changer une limite dans une cellule que d'avoir à changer une contrainte dans le Solveur.

Appuyez sur le bouton Ajouter et ajoutez les contraintes suivantes.

Dans la case Cellule, entrez B3.
Sélectionnez la contrainte <=.
Dans la case Contrainte, entrez C32.

Le solveur vous offre plusieurs options pour vous aider à le gérer. Vous pouvez placer un temps maximum pour trouver la solution optimale. Vous pouvez aussi déterminer le nombre d’essais, ou d’itérations, que le solveur peut prendre pour trouver la solution. Le degré de précision, de tolérance et convergence vous aide à déterminer à quel point vous serez proche de la solution optimale selon le type de modèle que vous avez. Le modèle supposé linéaire optimise le solveur pour des modèles simples. Vous pouvez aussi choisir parmi plusieurs types d’estimations, de dérivées et de types de recherches pour retrouver la solution optimale qui varie d’efficacité selon les équations à résoudre.

Activez l'option Échelle automatique.

L'échelle automatique est nécessaire lorsque vous avez plusieurs variables. Sinon, le Solveur va optimiser modèle seulement selon la première variable au leiu d'optimiser chaque variable individuellement. TRÈS IMPORTANT.

Vous pouvez aussi déterminer le nombre d'itérations (essaies) et le temps maximum en secondes. Certains modèles plus complexes peuvent prendre plusieurs heures même sur un ordinateur performant pour compléter. Une limite de teps peut être un avantage.

Le complément Solveur offre trois méthodes pour optimiser votre modèle. Une apporche peut atteindre la solution optimale plus rapidement selon le type de modèle que vous avez créé. Le second onglet vous offre des options pour des modèles non-linéaires dont l'option Multistart.

Le dernier onglet offre les options pour une approche évolutionnaire dont le degré de convergence et le taux de mutation.

Pour sortir des options du Solveur, appuyez sur le bouton OK.

Résoudre le problème

Avant de demander au Solveur de "résoudre" ou d'optimiser votre modèle, vous dever réaliser les étapes suivantes.

Déterminer la cellule à optimiser.
Choisir si vous désiez maximiser ou minimiser le modèle.
Choisir les cellules que vous permetez au Solveur de modifier afin de l'optimiser.
Inclure les critères pour chaque variables choisie.
Déterminer les options du Solveur.

Appuyez sur le bouton Résoudre.

Le Solveur va prendre quelques instranst pour Optimiser votre modèle. Cet exemple est très simple et rapide.

Sélectionnez l’option Rétablir les valeurs d’origines ainsi que choisir tous les rapports (Réponses, Sensibilité, Limites).
Appuyez sur le bouton OK.

Excel va générer trois nouvelles feuilles de calcul avec les résultats d’analyse du degré de réponse et de sensibilité des variables ainsi que leurs limites. Cela vous aidera à déterminer quelles sont les variables les plus importantes selon leur impact sur votre modèle.

La nouvelle feuille de calcul avec le rapport de réponses montre l'approche qui a été utilisée, la solution optimale selon les critères, les valeurs initiales et finales pour les variables que vous aviez choisies ainsi que de la liste des contraintes que vous avez déterminé. Cela vous permettra de voir les résultats et d'apporter des changements si requis.

La feuille de calcul du rapport de sensibilité vous montre l'impact de chaque variables choisies dans votre modèle. On constate ici que la cellule B21, le taux de croissance, a un impact très important par rapports aux autres cellules (1 154 999 vs 6,825 )

La dernière feuille de calcul indique les limites de chaque variable utilisées dans le modèle.

Conclusion

Le Solveur est un excellent outil pour optimier votre modèle ainsi que pour déterminer l'importance de chaque variable de votre modèle. Vous saurez ainsi sur quel élément vous devez mettre plus d'importance.

Abonnez-vous à l'infolettre

Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.

Cliquez ici pour vous abonner à l'infolettre