S'applique à
Microsoft Office Excel 2003 Microsoft Excel 2000 et 2002

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 le Solveur Microsoft Excel ?
• Comment identifier la gamme de produits qui maximise la rentabilité ?
• Comment intégrer ce modèle dans le Solveur ?
• Un modèle du Solveur offre-t-il toujours une solution ?
• Que se passe-t-il si les valeurs définies ne convergent pas ?
• Testez vos connaissances

Fichiers d'exemple  Vous pouvez télécharger les fichiers d'exemple associés aux extraits de l'ouvrage Excel pour le business et la finance disponibles sur le site Microsoft Office Online. Cet article utilise les fichiers prodmix.xls et les fichiers s25_1.xls à s25_5.xls.

Qu'est-ce que le Solveur Microsoft Excel ?

Vous pouvez faire appel au Solveur si vous êtes à la recherche de la meilleure méthode pour un objectif précis ou, pour parler de manière plus formelle, lorsque vous souhaitez rechercher dans une feuille de calcul les valeurs de cellules données permettant d'optimiser (maximiser ou minimiser) un résultat donné.

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

• La cellule cible représente l'objectif ou le résultat à atteindre (par exemple, maximiser les profits mensuels).
• 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 (par exemple, le montant de chaque produit sorti chaque mois de la chaîne de production).
• Les contraintes sont des limites imposées aux cellules variables (par exemple, ne pas utiliser plus de ressources que le nombre disponible et ne pas fabriquer un produit au-delà de la quantité susceptible d'être vendue).

Comment identifier la gamme de produits qui maximise la rentabilité ?

Les sociétés ont souvent besoin de déterminer leur planning mensuel (ou hebdomadaire) de production pour connaître la quantité à produire pour chaque produit. Dans sa définition la plus simple, le problème lié aux gammes de produits est de savoir comment définir la quantité de production mensuelle de chaque produit pour garantir des profits maximaux. Une gamme de produits doit souvent répondre aux contraintes suivantes :

• La gamme de produits ne peut pas utiliser plus de ressources que le nombre disponible.
• La demande de chaque produit est limitée. La quantité produite chaque mois pour un produit donné ne peut excéder la demande en raison du gâchis qu'implique toute surproduction (imaginez, par exemple, un médicament périssable).

Tentons maintenant de résoudre le problème de la gamme de produits décrit dans l'exemple suivant. Vous trouverez la solution à ce problème dans le fichier prodmix.xls (fourni lors du téléchargement des fichiers d'exemple) comme le montre la figure 1.

Figure 1 : Exemple de gamme de produits.

Imaginons que nous travaillons pour le compte d'une société pharmaceutique qui fabrique six produits dans son usine. La production de chaque produit exige de la main d'œuvre et de la matière première.

• La ligne 4 de la figure 1 indique le nombre d'heures de travail nécessaires à la production d'un kilo de chaque produit ; la ligne 5 précise le nombre de kilos de matière première requise pour la production d'un kilo de chaque produit. Par exemple, pour produire un kilo du produit 1, il faudra 6 heures de travail et 3,2 kilos de matière première.

• Pour chaque médicament, le prix au kilo s'affiche sur la ligne 6, le coût unitaire par kilo sur la ligne 7 et la part bénéficiaire par kilo sur la ligne 9. Par exemple, sur un prix de vente de 11,00 euros par kilo, le produit 2 implique un coût unitaire de 5,70 par kilo et une part bénéficiaire de 5,30 par kilo.

• La demande mensuelle pour chaque médicament concerné est présentée sur la ligne 8. Par exemple, la demande pour le produit 3 est de 1 041 kilos.

Ce mois-ci, 4 500 heures de travail et 1 600 kilos de matière première sont disponibles. Comment cette société peut-elle maximiser ses profits mensuels ?

Si nous ne connaissions pas le Solveur, nous aborderions le problème en créant une feuille de calcul dans laquelle il nous faudrait gérer pour chaque produit l'utilisation des ressources et les profits associés à la gamme de produits. Nous appliquerions ensuite la méthode « essai et erreur » pour varier la gamme de produits et optimiser les profits sans utiliser plus de main d'œuvre ou de matière première que la quantité disponible et sans produire une quantité de médicaments supérieure à la demande. Dans ce processus, nous utilisons uniquement le Solveur à l'étape « essai et erreur ». Le Solveur est principalement un moteur d'optimisation chargé d'exécuter de manière infaillible une recherche de type « essai et erreur ».

Une solution clé pour résoudre le problème de la gamme de produits est de calculer avec efficacité l'utilisation des ressources et les profits associés à une gamme de produits donnée. Un outil important auquel il est possible de faire appel est la fonction SOMMEPROD. Cette fonction multiplie les valeurs correspondantes des plages de cellules et renvoie la somme de ces valeurs. Chaque plage de cellules employée dans une évaluation SOMMEPROD doit présenter les mêmes dimensions, ce qui implique d'utiliser la fonction SOMMEPROD avec deux lignes ou deux colonnes et non avec une colonne et une ligne.

Pour donner un exemple de la manière dont nous pouvons exploiter la fonction SOMMEPROD dans notre exemple de gamme de produits, essayons de calculer l'utilisation des ressources. L'utilisation de la main d'œuvre peut être évaluée par le calcul suivant :

(Main d'œuvre par kilo de médicament 1) *
(Kilos produits pour médicament 1) +
(Main d'œuvre par kilo de médicament 2) *
(Kilos produits pour médicament 2) +
...
(Main d'œuvre par kilo de médicament 6) *
(Kilos produits pour médicament 6)

Dans notre feuille de calcul, nous pouvons calculer l'utilisation de la main d'œuvre avec la formule D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (méthode la plus laborieuse). De la même manière, la formule D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 peut être envisagée pour calculer l'utilisation de la matière première. La saisie de ces formules dans une feuille de calcul peut prendre du temps avec six produits. Imaginez ce que cela prendrait si vous travailliez dans une société produisant une cinquantaine de produits dans son usine.

Une manière beaucoup plus simple pour calculer l'utilisation de la main d'œuvre et de la matière première est de copier la formule de la cellule D14 vers la cellule D15 :

SOMMEPROD($D$2:$I$2;D4:I4)

La formule utilisée pour le calcul, D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (utilisation de la main d'œuvre), s'avère bien plus facile à saisir !

Notez l'utilisation du signe $ dans la plage D2:I2. Ainsi, lorsque je copie la formule, j'extrais toujours la gamme de produits de la ligne 2. La formule placée dans la cellule D15 calcule l'utilisation de la matière première.

De manière identique, le calcul des profits s'effectue comme suit :

(Profit par kilo de médicament 1) *
(Kilos produits pour médicament 1) +
(Profit par kilo de médicament 2) *
(Kilos produits pour médicament 2) +
...
(Profit par kilo de médicament 6) *
(Kilos produits pour médicament 6)

Les profits sont facilement calculés dans la cellule D12 avec la formule suivante :

SOMMEPROD(D9:I9;$D$2:$I$2)

Nous pouvons à présent identifier les trois parties de notre modèle Solveur de gamme de produits :

Cellule cible	Cellules variables	Contraintes
Notre objectif est de maximiser les profits (calculés dans la cellule D12).	Nombre de kilos produits pour chaque produit (affiché dans la plage de cellules D2:I2).	N'utilisez pas une quantité de main d'œuvre et de matière première plus importante que la quantité disponible. Les valeurs inscrites dans les cellules D14:D15 (ressources utilisées) doivent donc être inférieures ou égales aux valeurs des cellules F14:F15 (ressources disponibles). Ne produisez pas un volume de médicaments supérieur à la demande. Les valeurs inscrites dans les cellules D2:I2 (kilos produits pour chaque médicament) doivent donc être inférieures ou égales à la demande de chaque médicament (affichée dans les cellules D8:I8). Il n'est pas possible de produire une quantité négative pour un médicament donné.

Comment intégrer ce modèle dans le Solveur ?

Je vais maintenant vous montrer comment saisir la cellule cible, les cellules variables et les contraintes dans le Solveur. Tout ce que vous avez à faire ensuite est de cliquer sur le bouton Résoudre afin que le Solveur recherche une gamme de produits capable de maximiser vos profits !

1. Pour commencer, sélectionnez Solveur dans le menu Outils. (Pour obtenir des instructions sur l'installation du Solveur, consultez la rubrique Introduction à l'optimisation à l'aide du Solveur Microsoft Excel.)

   La boîte de dialogue Paramètres du solveur apparaît.

   

2. Pour saisir la cellule cible, cliquez dans la zone Cellule cible à définir, puis sélectionnez la cellule des profits (cellule D12). Pour saisir les cellules variables, cliquez dans la zone Cellules variables, puis pointez sur la plage D2:I2 répertoriant les kilos produits pour chaque médicament. La boîte de dialogue doit désormais apparaître comme dans la figure suivante.

   

3. Nous sommes maintenant prêts à ajouter des contraintes au modèle. Cliquez sur le bouton Ajouter. La boîte de dialogue Ajouter une contrainte apparaît.

   

4. Pour ajouter les contraintes d'utilisation des ressources, cliquez dans la zone intitulée Cellule, puis sélectionnez la plage D14:D15. Choisissez <= dans la liste située au milieu de la boîte de dialogue. Cliquez dans la zone intitulée Contrainte, puis sélectionnez la plage de cellules F14:F15.

   

   Nous nous sommes maintenant assurés qu'au moment où le Solveur évalue différentes valeurs pour les cellules variables, il prend uniquement en compte les combinaisons respectant à la fois les paramètres D14 <= F14 (la main d'œuvre employée est inférieure ou égale à la main d'œuvre disponible) et D15 <= F15 (la matière première utilisée est inférieure ou égale à la matière première disponible).

5. Cliquez à présent sur le bouton Ajouter dans la boîte de dialogue Ajouter une contrainte pour saisir les contraintes de demande. Remplissez seulement la boîte de dialogue Ajouter une contrainte tel qu'il vous l'est démontré dans la figure suivante.

   

   L'ajout de ces contraintes garantit qu'au moment d'évaluer différentes combinaisons pour les valeurs des cellules variables, le Solveur tient uniquement compte des combinaisons répondant aux critères suivants :

   • D2 <= D8 (la quantité produite pour le médicament 1 est inférieure ou égale à la demande enregistrée pour le médicament 1)
   • E2 <= E8 (la quantité produite pour le médicament 2 est inférieure ou égale à la demande enregistrée pour le médicament 2)
   • F2 <= F8 (la quantité produite pour le médicament 3 est inférieure ou égale à la demande enregistrée pour le médicament 3)
   • G2 <= G8 (la quantité produite pour le médicament 4 est inférieure ou égale à la demande enregistrée pour le médicament 4)
   • H2 <= H8 (la quantité produite pour le médicament 5 est inférieure ou égale à la demande enregistrée pour le médicament 5)
   • I2 <= I8 (la quantité produite pour le médicament 6 est inférieure ou égale à la demande enregistrée pour le médicament 6)

6. Cliquez sur OK dans la boîte de dialogue Ajouter une contrainte. La boîte de dialogue Paramètres du solveur doit apparaître comme dans la figure suivante.

   

7. Entrons à présent une contrainte précisant que toutes les cellules variables doivent être non négatives dans la boîte de dialogue Options du solveur (accessible lorsque vous cliquez sur le bouton Options dans la boîte de dialogue Paramètres du solveur).

   

   Sélectionnez les options Modèle supposé linéaire et Supposé non-négatif, puis cliquez sur OK.

   Pourquoi sélectionner ces options ?

   L'option Supposé non-négatif garantit que le Solveur tient uniquement compte des combinaisons de cellules variables dans lesquelles chaque cellule variable admet une valeur non négative.

   Nous avons choisi l'option Modèle supposé linéaire parce que le problème de la gamme de produits est un type de problème du Solveur spécial appelé modèle linéaire. Un modèle du Solveur apparaît principalement linéaire dans les conditions suivantes :

   • La cellule cible est calculée en ajoutant ensemble les conditions du formulaire (cellule variable)*(constante).
   • Chaque contrainte répond aux exigences du modèle linéaire, ce qui signifie qu'elle est évaluée en ajoutant ensemble les conditions du formulaire (cellule variable)*(constante) et en comparant les sommes obtenues à une constante.

   Pourquoi ce problème du Solveur est-il linéaire ?

   Notre cellule cible (profits) est calculée de la manière suivante :

   (Profit par kilo de médicament 1) *
   (Kilos produits pour médicament 1) +
   (Profit par kilo de médicament 2) *
   (Kilos produits pour médicament 2) +
   ...
   (Profit par kilo de médicament 6) *
   (Kilos produits pour médicament 6)

   Ce calcul suit un modèle dans lequel la valeur de la cellule cible est extraite en ajoutant ensemble les conditions du formulaire (cellule variable)*(constante).

   Notre contrainte de main d'œuvre est évaluée en comparant la main d'œuvre disponible à la valeur extraite de :

   (Main d'œuvre par kilo de médicament 1) *
   (Kilos produits pour médicament 1) +
   (Main d'œuvre par kilo de médicament 2) *
   (Kilos produits pour médicament 2) +
   ...
   (Main d'œuvre par kilo de médicament 6) *
   (Kilos produits pour médicament 6)

   La contrainte de main d'œuvre est donc évaluée en ajoutant ensemble les conditions du formulaire (cellule variable)*(constante) et en comparant les sommes obtenues à une constante. La contrainte de main d'œuvre et la contrainte de matière première répondent toutes les deux aux exigences du modèle linéaire.

   Nos contraintes de demande prennent la forme suivante :

   (Qté médicament 1 produite)<=(Demande médicament 1)
   (Qté médicament 2 produite)<=(Demande médicament 2)
   ...
   (Qté médicament 6 produite)<=(Demande médicament 6)

   Chaque contrainte de demande satisfait également les exigences du modèle linéaire puisque chacune d'entre elles est évaluée en ajoutant ensemble les conditions du formulaire (cellule variable)*(constante), puis en comparant les sommes calculées à une constante.

   Ayant démontré que notre modèle de gamme de produits est linéaire, de quoi avons-nous maintenant à nous soucier ?

   • Si un modèle du Solveur est linéaire et que nous sélectionnons l'option Modèle supposé linéaire, le Solveur est certain de trouver la solution optimale au modèle. Si le modèle du Solveur n'est pas linéaire, les chances du Solveur de trouver la solution optimale sont aléatoires.
   • Si un modèle du Solveur est linéaire et que nous sélectionnons l'option Modèle supposé linéaire, le Solveur utilise un algorithme très efficace (méthode simplex) pour rechercher la solution optimale du modèle. Si un modèle du Solveur est linéaire mais que nous décidons de ne pas sélectionner l'option Modèle supposé linéaire, le Solveur utilise un algorithme très inefficace (méthode GRG2) et risque d'avoir des difficultés à trouver la solution optimale du modèle.
   
   

8. Après avoir cliqué sur OK dans la boîte de dialogue Options du solveur, nous revenons à la boîte de dialogue Solveur principale. Dès que nous cliquons sur le bouton Résoudre, le Solveur calcule une solution optimale (le cas échéant) pour notre modèle de gamme de produits.

Une solution optimale pour un modèle de gamme de produits peut se résumer à un ensemble de valeurs de cellules variables (kilos produits par médicament) qui maximise les profits par rapport à l'ensemble de toutes les solutions réalisables. Encore une fois, une solution réalisable est un ensemble de valeurs de cellules variables qui satisfont toutes les contraintes. Les valeurs de cellules variables dévoilées dans la figure 2 désignent une solution réalisable puisque tous les niveaux de production sont non négatifs, qu'aucun niveau de production n'excède la demande et que les ressources utilisées ne dépassent pas le nombre des ressources disponibles.

Figure 2 : Solution au problème de gamme de produits réalisable et conforme aux contraintes.

Les valeurs de cellules variables présentées dans la figure 3 constituent une solution irréalisable pour les raisons suivantes :

• Notre production du médicament 5 est supérieure à la demande.
• Nous utilisons une main d'œuvre plus importante que la main d'œuvre disponible.
• Nous utilisons une quantité de matière première plus importante que la quantité disponible.

Figure 3 : Solution au problème de gamme de produits irréalisable et inadaptée aux contraintes définies.

Dès que nous cliquons sur le bouton Résoudre, le Solveur recherche rapidement la solution optimale décrite dans la figure 4. Vous devez sélectionner l'option Garder la solution du solveur pour conserver les valeurs de la solution optimale dans la feuille de calcul.

Figure 4 : Solution optimale au problème de gamme de produits.

Notre société pharmaceutique peut maximiser ses profits mensuels à hauteur de 6 625,20 € en produisant 596,67 kilos pour le médicament 4, 1 084 kilos pour le médicament 5 et zéro kilo pour les autres médicaments ! Il est impossible de savoir si nous pouvons atteindre ce profit maximal de 6 625,20 € avec d'autres méthodes. Tout ce dont nous sommes certains, c'est qu'en raison des limites de nos ressources et de notre demande, nous ne pouvons pas espérer plus de 6 625,20 € ce mois-ci.

Un modèle du Solveur offre-t-il toujours une solution ?

Supposons que la demande pour chaque produit doit être satisfaite. Il nous faut donc modifier nos contraintes de demande de D2:I2 <= D8:I8 à D2:I2 >= D8:I8. Pour modifier cette contrainte :

1. Ouvrez le Solveur.
2. Cliquez sur la contrainte D2:I2 <= D8:I8, puis sur Modifier.

   La boîte de dialogue Modifier une contrainte apparaît.

   

3. Dans la zone centrale, sélectionnez >=, puis cliquez sur OK.

   Nous avons désormais la garantie que le Solveur tiendra uniquement compte des valeurs de cellules variables qui satisfont à toutes les demandes.

Lorsque vous cliquez sur Résoudre, un message indiquant que Le solveur ne peut pas trouver de solution réalisable apparaît. Ce message signifie qu'en raison de nos ressources limitées, nous ne pouvons satisfaire la demande de tous les produits. Nous n'avons commis aucune erreur dans notre modèle ! Le Solveur nous signale simplement que si nous souhaitons satisfaire la demande pour chaque produit, nous devons ajouter davantage de main d'œuvre, de matière première ou des deux.

Que se passe-t-il si les valeurs définies ne convergent pas ?

Voyons ce qui se passe si nous autorisons une demande illimitée pour chaque produit ainsi que des quantités négatives à produire pour chaque médicament. Pour trouver la solution optimale à cette situation :

1. Ouvrez le Solveur.
2. Cliquez sur le bouton Options, puis désactivez la case à cocher Supposé non-négatif.
3. Dans la boîte de dialogue Paramètres du solveur, cliquez sur la contrainte de demande D2:I2 <= D8:I8, puis sur Supprimer pour supprimer la contrainte.

Lorsque vous cliquez sur le bouton Résoudre, le Solveur renvoie un message signalant que Les valeurs de la cellule définie ne convergent pas. Ce message signifie que si la cellule cible doit être maximisée (comme dans notre exemple), il existe donc des solutions réalisables avec des valeurs de cellules cibles plus ou moins grandes. (Si la cellule cible doit être minimisée, ce message signifie que des solutions réalisables existent avec des valeurs de cellule cible plus ou moins petites.)

Dans notre situation, en autorisant une production négative pour un médicament donné, nous « créons » en réalité des ressources que nous pouvons utiliser pour produire de manière arbitraire de grandes quantités d'autres médicaments. Étant donné notre demande illimitée, cette solution nous garantit des profits illimités. Dans une situation réelle, nous pouvons dégager une quantité infinie de bénéfices. Pour résumer, si le message indiquant que les valeurs ne convergent pas apparaît, c'est que votre modèle comporte bel et bien une erreur.

Testez vos connaissances

Des solutions à ces problèmes sont fournies dans les fichiers s25_1.xls à s25_5.xls disponibles dans le cadre du téléchargement des fichiers d'exemple.

1. Supposons que notre société pharmaceutique peut acheter jusqu'à 500 heures de main d'œuvre à un coût d'un euro par heure. Qui raterait une telle occasion ?
2. Dans une usine de fabrication de puces électroniques, quatre techniciens (A, B, C et D) sont chargés de la production de trois produits (produits 1, 2 et 3). Le fabricant peut vendre 80 unités du produit 1 ce mois-ci, 50 unités du produit 2 et au maximum 50 unités du produit 3. Le technicien A peut uniquement fabriquer les produits 1 et 3. Le technicien B peut uniquement fabriquer les produits 1 et 2. Le technicien C peut uniquement fabriquer le produit 3. Le technicien D peut uniquement fabriquer le produit 2. Pour chaque unité produite, les produits participent aux profits suivants : 6 € pour le produit 1, 7 € pour le produit 2 et 10 € pour le produit 3. Le temps (en heures) nécessaire à chaque technicien pour la fabrication d'un produit est présenté dans le tableau ci-dessous.

   Produit	Technicien A	Technicien B	Technicien C	Technicien D
   1	2	2,5	Non concerné	Non concerné
   2	Non concerné	3	Non concerné	3,5
   3	3	Non concerné	4	Non concerné

   
   

   Chaque technicien peut effectuer jusqu'à 120 heures par mois. Comment le fabricant de puces électroniques peut-il maximiser ses profits mensuels ?

3. Une usine de produits informatiques fabrique des souris, des claviers et des manettes de jeux vidéo. Le profit par unité, la main d'œuvre utilisée par unité, la demande mensuelle et l'utilisation de temps machine sont répertoriés dans le tableau suivant :

   	Souris	Claviers	Manettes
   Profit/unité	8 €	11 €	9 €
   Main d'œuvre utilisée/unité	0,2 heure	0,3 heure	0,24 heure
   Temps machine/unité	0,04 heure	0,055 heure	0,04 heure
   Demande mensuelle	15 000	25 000	11 000

   
   

   Un volume total de 13 000 heures de travail et 3 000 heures de temps machine est disponible chaque mois. Comment le fabricant peut-il maximiser la contribution mensuelle de l'usine aux profits de la société ?

4. Il nous faut résoudre notre exemple pharmaceutique en partant du principe qu'une demande minimale de 200 unités doit être satisfaite pour chaque médicament.
5. Jason est joaillier et fabrique des bracelets, des colliers et des boucles d'oreille en diamant. Il souhaite travailler 160 heures maximum par mois et dispose d'une quantité de 113 400 carats. Les profits, le temps de travail et le nombre de carats produits par unités sont répertoriés dans le tableau ci-dessous. Si la demande pour chaque produit est illimitée, comment Jason peut-il maximiser ses profits ?

   Produit	Profit par unité	Heures de travail par unité	Carats par unité
   Bracelet	300 €	0,35	170,1
   Collier	200 €	0,15	106,3
   Boucles d'oreille	100 €	0,05	70,9