Cet article est adapté de l'ouvrage Excel pour le business et la finance écrit par Wayne L. Winston. Ce livre, qui se présente sous la forme d'un manuel de classe, a été élaboré à partir d'une série de présentations rédigées par Wayne Winston, statisticien et professeur d'économie de renom spécialisé dans le développement d'applications créatives et pratiques pour Excel. Alors soyez prêt à faire fonctionner vos méninges ! Visitez le site Microsoft Press pour acheter cet ouvrage.

Contenu de cet article

• Qu'est-ce que l'optimisation ?

• Définition d'un modèle d'optimisation
• Installation et exécution du Solveur
• Testez vos connaissances

Qu'est-ce que l'optimisation ?

• Comment une importante société pharmaceutique peut-elle déterminer chaque mois la gamme de produits qui permet d'augmenter la rentabilité de son usine d'Indianapolis ?
• Si Microsoft produit des consoles Xbox dans trois endroits différents, comment cette société peut-elle minimiser ses coûts pour répondre à la demande ?
• Quel est le prix des consoles et des jeux Xbox qui permettra d'augmenter les bénéfices générés par la vente de Xbox ?
• Microsoft souhaite mettre en place 20 initiatives stratégiques qui lieront des ressources financières et des programmeurs expérimentés durant les cinq prochaines années. Microsoft ne dispose pas de ressources suffisantes pour mener à bien les 20 projets. Quels projets doivent être mis en place ?
• Comment les bookmakers trouvent-ils les meilleures « cotations » pour les équipes de la ligue américaine de football (NFL) afin de définir des répartitions de points fiables ?
• Comment puis-je constituer un portefeuille de retraite entre les actions des nouvelles technologies, les actions de valeurs, les obligations, les liquidités et l'or ?

Dans toutes ces situations, nous voulons trouver la meilleure manière de procéder. Pour parler de manière plus formelle, nous voulons trouver dans une feuille de calcul les valeurs de certaines cellules permettant d'optimiser (maximiser ou minimiser) un objectif donné. Le Solveur Microsoft Excel vous permet de trouver des solutions aux problèmes d'optimisation.

Définition d'un modèle d'optimisation

Un modèle d'optimisation se compose de trois éléments : une cellule cible, des cellules variables et des contraintes.

Cellule cible

La cellule cible représente l'objectif ou le résultat à atteindre. Nous voulons minimiser ou maximiser la cellule cible. Dans l'exemple de la gamme de produits d'une société pharmaceutique, le directeur de l'usine souhaitera vraisemblablement augmenter la rentabilité de l'usine chaque mois. La cellule qui mesure la rentabilité sera la cellule cible. Les cellules cibles correspondant à chaque situation décrite au début de l'article sont répertoriées dans le tableau suivant.

N'oubliez pas que certaines situations impliqueront plusieurs cellules cibles. Par exemple, Microsoft peut avoir un objectif secondaire pour optimiser la part de marché des Xbox.

Cellules variables

Les cellules variables désignent les cellules de la feuille de calcul qu'il est possible de modifier ou d'adapter pour optimiser la cellule cible. Dans l'exemple précédent de la société pharmaceutique, le directeur de l'usine peut modifier la quantité à fabriquer pour chaque produit pendant un mois. Les cellules dans lesquelles ces valeurs sont enregistrées sont les cellules variables de ce modèle. Le tableau suivant répertorie les définitions de cellules variables appropriées pour les modèles décrits au début de l'article.

Contraintes

Les contraintes sont des limites imposées aux cellules variables. Dans notre exemple de gammes de produits, il n'est pas possible d'utiliser une quantité de ressources supérieure à la quantité de ressources disponible (par exemple, matières premières et main d'oeuvre). Qui plus est, la quantité produite ne doit pas excéder la quantité que les personnes souhaitent acheter. Dans la plupart des modèles du Solveur, il existe une contrainte implicite stipulant que toutes les cellules variables doivent être non négatives. Les contraintes liées à la non-négativité seront traitées en détail dans les chapitres suivants. Gardez en mémoire qu'un modèle du Solveur ne doit pas nécessairement inclure des contraintes. Le tableau suivant répertorie les contraintes associées aux problèmes présentés au début du chapitre.

Installation et exécution du Solveur

Pour installer le Solveur, cliquez sur Macros complémentaires dans le menu Outils, puis activez la case à cocher Complément Solver. Cliquez sur OK ; Excel installe alors le Solveur. Une fois la macro complémentaire installée, vous pouvez exécuter le Solveur en cliquant sur Solveur dans le menu Outils.

La figure suivante présente la boîte de dialogue Paramètres du solveur, laquelle vous permet d'entrer les valeurs de la cellule cible, des cellules variables et des contraintes qui s'appliquent à votre modèle d'optimisation.

Remarque  La procédure à suivre sera détaillée dans chacun des articles sur les modèles du Solveur qui sont répertoriés dans la section Voir aussi de cet article.

Une fois que vous avez entré les valeurs de la cellule cible, des cellules variables et des contraintes, que fait le Solveur ? Pour répondre à cette question, il est nécessaire de connaître la terminologie liée au Solveur. Toute spécification des cellules variables qui répond aux contraintes du modèle est appelée solution réalisable. Ainsi, dans notre exemple de gammes de produits, toute gamme de produits qui répond aux trois conditions suivantes constitue une solution réalisable :

• La gamme de produits n'utilise pas une quantité de matières premières et de main d'oeuvre supérieure à la quantité disponible.
• La gamme de produits ne fabrique pas une quantité supérieure à la demande pour chaque produit.
• La quantité fabriquée pour chaque produit est non négative.

En fait, le Solveur recherche toutes les solutions réalisables et trouve la solution réalisable qui dispose de la « meilleure » valeur dans la cellule cible (la valeur la plus grande pour une optimisation maximale, et la valeur la plus petite pour une optimisation minimale). Une solution de ce type est appelée solution optimale. Certains modèles du Solveur n'offrent aucune solution optimale, alors que d'autres offrent une seule solution. D'autres encore offrent plusieurs solutions optimales (ou plus précisément, un nombre infini de solutions optimales).

Pour comprendre le fonctionnement du Solveur, la meilleure approche consiste à consulter des exemples détaillés. La section Voir aussi de cet article inclut des liens vers d'autres articles qui décrivent comment utiliser le Solveur pour résoudre plusieurs problèmes professionnels (ou non professionnels) importants.

Testez vos connaissances

Pour chaque situation décrite ci-dessous, identifiez la cellule cible, les cellules variables et les contraintes.

• Je fais un emprunt de 100 000 € pour un prêt sur 15 ans. Le taux d'intérêt annuel est de 8 %. J'effectue des versements mensuels. Comment puis-je déterminer le remboursement mensuel de mon emprunt ?
• Comment un concessionnaire automobile doit-il répartir son budget publicitaire entre les différents formats publicitaires ?
• Quel emplacement une ville doit-elle choisir pour un seul hôpital ?
• Comment une société pharmaceutique doit-elle orienter sa force de vente pour ses produits ?
• Une société pharmaceutique dispose d'un budget de 2 milliards d'euros pour l'acquisition de sociétés de biotechnologie. Quelles sociétés doit-elle acquérir ?
• Le taux d'imposition appliqué à une société pharmaceutique dépend du pays de fabrication du produit. Comment une société pharmaceutique peut-elle déterminer le lieu de fabrication de chaque médicament ?