■■ Comment puis-je déterminer la gamme de produits mensuelle qui maximise la rentabilité?

■■ Un modèle Solver a-t-il toujours une solution?

■■ Qu’est-ce que cela signifie si un modèle de solveur donne le résultat Définir les valeurs Ne pas converger?

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Comment puis-je déterminer la gamme de produits mensuelle qui maximise la rentabilité?

Les entreprises doivent souvent déterminer la quantité de chaque produit à produire sur une base mensuelle. Dans sa forme la plus simple, le problème du mix de produits implique la façon de déterminer la quantité de chaque produit qui devrait être produite pendant un mois pour maximiser les profits. La gamme de produits doit généralement respecter les contraintes suivantes:

■■ La gamme de produits ne peut pas utiliser plus de ressources que celles disponibles.

■■ Parce que la demande pour chaque produit est limitée, vous ne voulez pas en produire plus au cours d’un mois que la demande ne l’exige car la production excédentaire est gaspillée (par exemple, un médicament périssable).

Maintenant, résolvez l’exemple suivant du problème de mélange de produits. Vous pouvez trouver une solution réalisable à ce problème dans la feuille de calcul Solution réalisable du fichier, illustrée à la figure ci-dessous. Les valeurs d’essai pour la quantité produite de chaque médicament ont été entrées dans la ligne 2.

FIGURE 1 : Cette figure montre une combinaison de produits réalisable.

Vous travaillez pour une société pharmaceutique qui fabrique six produits dans son usine. La production de chaque produit nécessite du travail et des matières premières. La ligne 4 de la figure ci-dessus montre les heures de travail nécessaires pour produire un livre de chaque produit, et la ligne 5 montre les livres de matière première nécessaires pour produire une livre de chaque produit. Par exemple, la production d’une livre de produit 1 nécessite six heures de travail et 3,2 livres de matière première. Pour chaque médicament, le prix par livre est indiqué à la ligne 6, le coût unitaire par livre est indiqué à la ligne 7, et la contribution au bénéfice par livre est indiquée à la ligne 9. Par exemple, le produit 2 se vend 11,00 $ la livre, entraîne un coût unitaire de 5,70 $ la livre, et contribue 5,30 $ de profit par livre. La demande mensuelle pour chaque médicament est indiquée à la ligne 8. Par exemple, la demande pour le produit 3 est de 1 041 livres. Ce mois-ci, 4 500 heures de travail et 1 600 livres de matières premières sont disponibles. Comment cette entreprise peut-elle maximiser son profit mensuel?

Si vous ne saviez rien du Solveur, vous pourriez attaquer ce problème en créant une feuille de calcul pour suivre les bénéfices et l’utilisation des ressources associés à la gamme de produits. Ensuite, vous pouvez utiliser les essais et les erreurs pour varier la gamme de produits afin d’optimiser les bénéfices sans utiliser plus de main-d’œuvre ou de matières premières que ce qui est disponible ou produire un médicament supérieur à la demande. Vous utilisez Solver dans ce processus uniquement lors des essais et erreurs étape. Essentiellement, Solver est un moteur d’optimisation qui effectue parfaitement la recherche par essais et erreurs.

Une clé pour résoudre le problème du mix de produits est de calculer efficacement l’utilisation des ressources et les bénéfices associés à un mix de produits donné. Un outil important que vous pouvez utiliser pour effectuer ce calcul est la fonction SOMMEPROD. Il multiplie les valeurs correspondantes dans les plages de cellules et renvoie la somme de ces valeurs. Chaque plage de cellules utilisée dans une évaluation SOMMEPROD doit avoir les mêmes dimensions, vous pouvez donc utiliser SOMMEPROD avec deux lignes ou deux colonnes mais pas avec une colonne et une ligne.

Comme exemple d’utilisation de la fonction SUMPRODUCT dans l’exemple de combinaison de produits, calculez l’utilisation des ressources. L’utilisation de la main-d’œuvre est calculée par la formule suivante:

(Travail utilisé par livre de médicament 1) * (Médicament 1 livre produit) + (Travail utilisé par livre de médicament 2)

* (Médicament produit 2 livres) + … (Main-d’œuvre utilisée par livre de médicament 6) * (Médicament produit 6 livres)

Vous pouvez calculer l’utilisation du travail d’une manière plus fastidieuse comme D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. De même, l’utilisation des matières premières pourrait être calculée comme D2 * D5 + E2 * E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Cependant, la saisie de ces formules dans une feuille de calcul pour six produits prend du temps. Imaginez combien de temps cela prendrait si vous travailliez avec une entreprise qui produisait, par exemple,

50 produits dans son usine. Un moyen beaucoup plus simple de calculer la main-d’œuvre et l’utilisation des matières premières consiste à SOMMAIRE ($ D $ 2: $ I $ 2, D4: I4) formule du D14 au D15. Cette formule calcule D2 * D4 + E2 * E4 + F2* F4 + G2 * G4 + H2 * H4 + I2 * I4 (qui est l’utilisation de main-d’œuvre) mais est beaucoup plus facile à saisir. Utilisez le signe $ avec la plage D2: I2 pour que lorsque vous copiez la formule, vous capturez toujours le mélange de produits de la ligne 2. La formule de la cellule D15 calcule l’utilisation des matières premières.

De manière similaire, le profit est déterminé par la formule suivante:

(Profit du médicament 1 par livre) * (Profit du médicament 1 livre) + (Bénéfice du médicament 2 par livre) * (Médicament 2 livres produit) + … (Médicament 6 bénéfice par livre) * (Médicament 6 livres produit) Le profit est facilement calculé dans la cellule D12 avec la formule SUMPRODUCT (D9: I9, $ D $ 2: $ I $ 2).

Vous pouvez maintenant identifier les trois composants du modèle de solveur de mix de produits:

■■ cellule cible L’objectif est de maximiser le profit (calculé dans la cellule D12). 

■■ Changer les cellules Le nombre de livres produites pour chaque produit est répertorié dans la gamme de cellules D2: I2.

■■ Contraintes Vous avez les contraintes suivantes:

• • N’utilisez pas plus de main-d’œuvre ou de matière première que ce qui est disponible. C’est-à-dire que les valeurs dans les cellules D14: D15 (les ressources utilisées) doivent être inférieures ou égales aux valeurs dans les cellules F14: F15 (les ressources disponibles).
• • Ne produisez pas plus d’un médicament que ce qui est demandé. C’est-à-dire que les valeurs dans les cellules D2: I2 (livres produites de chaque médicament) doivent être inférieures ou égales à la demande pour chaque médicament (répertoriée dans les cellules D8: I8).
• • Vous ne pouvez pas produire de quantité négative de médicament.

Ce chapitre vous montre comment entrer la cellule cible, modifier les cellules et les contraintes dans Solver.

Ensuite, tout ce que vous avez à faire est de cliquer sur le bouton Résoudre pour trouver un mix de produits maximisant les profits.

Pour commencer, cliquez sur l’onglet Données et, dans le groupe Analyse, cliquez sur Solveur.

        Remarque Comme expliqué au chapitre précédent, «Présentation de l’optimisation avec Excel Solver», Solver est activé en cliquant sur l’onglet Fichier, puis en choisissant Options et compléments. Dans la gestion

dans la liste, cliquez sur Compléments Excel et cliquez sur OK. Cochez la case Complément Solveur, puis cliquez sur OK.

La boîte de dialogue Paramètres du solveur s’ouvre, comme illustré à la figure ci-dessous.

FIGURE 2 :Cette figure montre la boîte de dialogue Paramètres du solveur.

Cliquez sur la case Définir l’objectif, puis sélectionnez la cellule de profit (cellule D12). Cliquez sur la case En changeant les cellules variables, puis pointez sur la plage D2: I2, qui contient les kilos produits de chaque médicament. La boîte de dialogue devrait maintenant ressembler à la figure 3.

FIGURE 3:Cette figure montre la boîte de dialogue Paramètres du solveur avec la cellule cible et les cellules changeantes définies.

Vous êtes maintenant prêt à ajouter des contraintes au modèle. Cliquez sur le bouton Ajouter. La contrainte d’ajout

La boîte de dialogue s’ouvre, comme le montre la figure ci-dessous.

FIGURE 4 : Cette figure montre la boîte de dialogue Ajouter une contrainte.

Pour ajouter les contraintes d’utilisation des ressources, cliquez sur la case Référence de cellule, puis sélectionnez la plage D14: D15. Après avoir confirmé la sélection de <= dans la liste du milieu, cliquez sur la case Contrainte, puis sélectionnez la plage de cellules F14: F15. La boîte de dialogue Ajouter une contrainte devrait maintenant ressembler à la figure .

FIGURE 5:Cette figure montre la boîte de dialogue Ajouter une contrainte avec les contraintes d’utilisation des ressources entrées.

Vous avez maintenant veillé à ce que lorsque le solveur essaie différentes valeurs pour les cellules changeantes, seules les combinaisons qui satisfont à la fois D14 <= ​​F14 (la main-d’œuvre utilisé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 égal à la matière première disponible) sont pris en compte. Cliquez sur Ajouter pour saisir les contraintes de demande, en remplissant la boîte de dialogue Ajouter une contrainte, comme illustré à la figure 6.

FIGURE 6 : Cette figure montre la boîte de dialogue Ajouter une contrainte avec les contraintes de demande entrées.

L’ajout de ces contraintes garantit que lorsque le solveur essaie différentes combinaisons pour les valeurs de cellule changeantes, seules les combinaisons qui satisfont aux paramètres suivants sont prises en compte:

■■ D2 <= D8 (la quantité produite de médicament 1 est inférieure ou égale à la demande de médicament 1)

■■ E2 <= E8 (la quantité produite de médicament 2 est inférieure ou égale à la demande de médicament 2)

■■ F2 <= F8 (la quantité produite de médicament 3 est inférieure ou égale à la demande de médicament 3)

■■ G2 <= G8 (la quantité produite de médicament 4 est inférieure ou égale à la demande de médicament 4)

■■ H2 <= H8 (la quantité produite de médicament 5 est inférieure ou égale à la demande de médicament 5)

■■ I2 <= I8 (la quantité produite de médicament 6 est inférieure ou égale à la demande de médicament 6)

Cliquez sur OK dans la boîte de dialogue Ajouter une contrainte. La boîte de dialogue Paramètres du solveur doit ressembler à Figure .

FIGURE 7:Il s’agit de la dernière boîte de dialogue Paramètres du solveur pour le problème de mélange de produits.

La sélection de la case à cocher Rendre les variables non contraintes non négatives garantit que toutes les cellules changeantes sont forcées d’être supérieures ou égales à 0.

Ensuite, choisissez Simplex LP dans la liste Sélectionnez une méthode de résolution. Vous choisissez le moteur Simplex LP car le problème de mélange de produits est un type spécial de problème de solveur appelé modèle linéaire. Essentiellement, un modèle de solveur est linéaire dans les conditions suivantes:

■■ La cellule cible est calculée en additionnant les termes de la (cellule changeante) * (constante)

forme.

■■ Chaque contrainte satisfait l’exigence du modèle linéaire. Cela signifie que chaque contrainte est évaluée en additionnant les termes de la forme (cellule changeante) * (constante) et en comparant les sommes à une constante.

Pourquoi ce problème du solveur est-il linéaire? Votre cellule cible (profit) est calculée comme suit:

(Profit du médicament 1 par livre) * (Profit du médicament 1 livre) + (Bénéfice du médicament 2 par livre) * (Médicament 2 livres produit) + … (Médicament 6 bénéfice par livre) * (Médicament 6 livres produit) Ce calcul suit un modèle dans lequel la valeur de la cellule cible est dérivée en additionnant les termes de la forme (cellule changeante) * (constante).Votre contrainte de travail est évaluée en comparant la valeur dérivée de (Travail utilisé par livre de Médicament 1) * (Médicament 1 livre produit) + (Main-d’œuvre utilisée par livre de Médicament 2) * (Médicament 2 livres produit   d) + . . . (Main-d’œuvre utilisée par livre de médicament 6) * (Médicament produit 6 livres) au travail disponible.

Par conséquent, la contrainte de travail est évaluée en additionnant les termes de la forme (cellule changeante) * (constante) et en comparant les sommes à une constante. La contrainte de main-d’œuvre et la contrainte de matière première satisfont toutes deux à l’exigence du modèle linéaire.

Les contraintes de demande prennent la forme suivante:

(Médicament 1 produit) <= (Demande de médicament 1) (Médicament 2 produit) <= (Demande de médicament 2) … (Médicament 6 produit) <= (Demande de médicament 6)

Chaque contrainte de demande satisfait également l’exigence du modèle linéaire car chacune est évaluée en additionnant les termes de la forme (cellule changeante) * (constante) et en comparant les sommes à une constante.

Sachant que le modèle de mix de produits est un modèle linéaire, pourquoi vous en soucier?

■■ Si un modèle de solveur est linéaire et que vous sélectionnez Simplex LP, le solveur est assuré de trouver la solution optimale au modèle de solveur. Si un modèle de solveur n’est pas linéaire, le solveur peut ou non trouver la solution optimale.

■■ Si un modèle Solver est linéaire et que vous sélectionnez Simplex LP, Solver utilise un algorithme très efficace (la méthode simplex) pour trouver la solution optimale du modèle. Si un modèle Solver est linéaire et que vous ne sélectionnez pas Simplex LP, Solver utilise un algorithme très inefficace (la méthode GRG2) et peut avoir des difficultés à trouver la solution optimale du modèle.

Après avoir cliqué sur Résoudre, le solveur calcule une solution optimale (le cas échéant) pour le modèle de combinaison de produits. Comme indiqué à l’article 28, une solution optimale au modèle de combinaison de produits serait un ensemble de valeurs cellulaires changeantes (livres produites de chaque médicament) qui maximise le profit par rapport à l’ensemble de toutes les solutions viables. Encore une fois, une solution réalisable est un ensemble de valeurs de cellule changeantes satisfaisant toutes les contraintes. Les valeurs changeantes des cellules illustrées à la figure ci-dessous sont une solution réalisable car tous les niveaux de production sont non négatives, les niveaux de production ne dépassent pas la demande et l’utilisation des ressources ne dépasse pas les ressources disponibles.

FIGURE 8 : Une solution réalisable au problème du mix de produits s’inscrit dans les contraintes.

Les valeurs de cellule changeantes illustrées à la figure ci-dessous représentent une solution irréalisable pour les raisons suivantes:

■■ Vous produisez plus de Drug 5 que la demande.

■■ Vous utilisez plus de main-d’œuvre que ce qui est disponible.

■■ Vous utilisez plus de matières premières que ce qui est disponible.

FIGURE : Une solution irréalisable au problème du mix de produits ne rentre pas dans les contraintes définies.

FIGURE : Il s’agit de la solution optimale au problème de mélange de produits.

Votre compagnie pharmaceutique peut maximiser son bénéfice mensuel à un niveau de 6 625,20 $ en produisant 596,67 livres de médicament 4; 1 084 livres de médicament 5; et aucun des autres médicaments. Vous ne pouvez pas déterminer si vous pouvez réaliser le profit maximal de 6 625,20 $ par d’autres moyens. Tout ce dont vous pouvez être sûr, c’est qu’avec vos ressources et votre demande limitées, vous ne pouvez pas gagner plus de 6 625,20 $ ce mois-ci.

Un modèle Solver a-t-il toujours une solution?

Supposons que la demande pour chaque produit doit être satisfaite. (Voir la feuille de calcul No Feasible Solution dans le fichier .) Vous devez ensuite modifier vos contraintes de demande de D2: I2 <= D8: I8 à D2: I2> = D8: I8. Pour ce faire, ouvrez le solveur, sélectionnez la contrainte D2: I2 <= D8: I8, puis cliquez sur Modifier. La boîte de dialogue Modifier la contrainte, illustrée à la figure ci-dessous, s’ouvre.

FIGURE 9:Il s’agit de la boîte de dialogue Modifier la contrainte.

Comme le montre la figure ci-dessous, sélectionnez> = puis cliquez sur OK. Vous êtes désormais assuré que Solver envisagera de ne modifier que les valeurs de cellule qui répondent à toutes les demandes. Lorsque vous cliquez sur Résoudre, le message suivant s’affiche: «Le solveur n’a pas pu trouver de solution réalisable». Ce message ne signifie pas que vous avez fait une erreur dans votre modèle, mais plutôt qu’avec des ressources limitées, vous ne pouvez pas répondre à la demande pour tous les produits. Le solveur et vous dire simplement que si vous voulez répondre à la demande pour chaque produit, vous devez ajouter plus de main-d’œuvre, plus de matière première ou les deux.

FIGURE 10 : La contrainte de demande garantit maintenant que la demande est satisfaite.

Qu’est-ce que cela signifie si un modèle de solveur donne le résultat Définir les valeurs ne pas converger?

Que se passe-t-il si vous autorisez une demande illimitée pour chaque produit et des quantités négatives produites pour chaque médicament? (Vous pouvez voir ce problème du solveur dans la feuille de calcul Définir les valeurs ne pas converger dans le fichier Prodmix.xlsx.) Pour trouver la solution optimale pour cette situation, ouvrez le solveur et désactivez la case à cocher Rendre les variables non contraintes non négatives. Dans la boîte de dialogue Paramètres du solveur,

sélectionnez la contrainte de demande D2: I2 <= D8: I8, puis cliquez sur Supprimer pour supprimer la contrainte. Lorsque vous cliquez sur Résoudre, le solveur renvoie le message Définir les valeurs de cellule ne pas converger. Ce message signifie que si la cellule cible doit être maximisée (comme dans cet exemple), il existe des solutions réalisables avec des valeurs de cellule cible arbitrairement grandes. Par exemple, si vous vouliez faire un milliard de dollars de profit, Solver pourrait trouver une solution réalisable pour réaliser ce niveau de profit . (Si la cellule cible doit être minimisée, le message Définir les valeurs de cellule ne converge pas signifie qu’il existe des solutions  Mall ciblent les valeurs des cellules.) Dans cette situation, en autorisant la production négative d’un médicament, vous créez des ressources qui peuvent être utilisées pour produire arbitrairement de grandes quantités d’autres médicaments. Compte tenu de votre demande illimitée, vous pouvez réaliser des bénéfices illimités. Dans une situation réelle, vous ne pouvez pas gagner une somme infinie d’argent. En bref, si vous voyez Set Values ​​Do Not Converge, votre modèle a une erreur.