Utilisation du solveur pour déterminer la gamme de produits optimale

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

Les sociétés doivent souvent déterminer la quantité de chaque produit à produire sur une base mensuelle. Dans sa forme la plus simple, le problème de Mix de produit consiste à déterminer le volume de chaque produit qui doit être produit pendant un mois pour optimiser les bénéfices. La combinaison de produits doit généralement respecter les contraintes suivantes :

• La gamme de produits ne peut pas utiliser plus de ressources qu’il n’en existe.

• Chaque produit est limité à la demande. Il n’est pas possible de générer un plus grand nombre de produits au cours d’un mois à la demande, car la production excédentaire est gaspillée (par exemple, un médicament Perishable).

À présent, nous allons résoudre l’exemple suivant du problème de la gamme de produits. La solution à ce problème se trouve dans la Prodmix.xlsx de fichier, illustrée dans la figure 27-1.

Imaginons que nous puissions travailler pour une société pharmaceutique qui produit six produits différents au niveau de leur établissement. La production de chaque produit nécessite une main-d’œuvre et des matières premières. La ligne 4 de la figure 27-1 indique les heures consacrées au travail d’une livres de chaque produit, et la ligne 5 montre la livres de matières premières nécessaires à la création d’une livres de chaque produit. Par exemple, la création d’un livre de produit 1 nécessite 6 heures de main-d’œuvre et 3,2 livres de matières premières. Pour chaque médicament, le prix par livre est fourni dans la ligne 6, le coût unitaire par livre est fourni dans la ligne 7 et la contribution bénéficiaire par livre est indiquée dans la ligne 9. Par exemple, le produit 2 se vend pour $11,00 par livre, génère un coût unitaire d' $5,70 par kilo et apporte $5,30 profit par livre. La demande du mois de chaque médicament est indiquée dans la ligne 8. Par exemple, la demande de produit 3 est 1041 livres. Ce mois-ci, 4500 heures de main-d’œuvre et 1600 livres de matières premières sont disponibles. Comment cette société peut-elle optimiser son bénéfice mensuel ?

Si nous savions qu’il n’y a aucune information sur le solveur d’Excel, nous aurions pu attaquer ce problème en créant une feuille de calcul pour suivre les bénéfices et l’utilisation des ressources associées à la gamme de produits. Nous utiliserons ensuite la version d’évaluation et l’erreur pour faire 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 qu’il n’y en a, sans pour autant produire de médicaments dépassant la demande. Nous utilisons le solveur dans ce processus uniquement lors de l’étape d’évaluation et d’erreur. Le solveur est essentiellement un moteur d’optimisation qui exécute parfaitement la recherche d’évaluation et d’erreur.

Pour résoudre ce problème, il est essentiel de calculer efficacement l’utilisation des ressources et les bénéfices associés à toute combinaison de produits donnée. Un outil important que nous pouvons utiliser pour effectuer ce calcul est la fonction SOMMEPROD. La fonction SOMMEPROD multiplie les valeurs correspondantes dans des 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, ce qui signifie que vous pouvez utiliser SOMMEPROD avec deux lignes ou deux colonnes, et non avec une colonne et une seule ligne.

Pour illustrer l’utilisation de la fonction SOMMEPROD dans notre exemple de composition de produit, nous allons essayer de calculer notre utilisation des ressources. Notre utilisation est calculée par

(Main-d’oeuvre utilisée par kilo de drogues 1) * (drogues 1 livres produits) +
(Main-d’oeuvre utilisée par kilo de drogues 2) * (drogues 2 livres produits) +...
(Main-d’oeuvre utilisée par kilo de drogues 6) * (médicaments 6 livres produits)

Il est possible de calculer l’utilisation de la main d’œuvre pour une utilisation plus fastidieuse : D2 * D4 + E2 * E4 + F2 * F4+. De la même façon, l’utilisation de matériel brut peut être calculée comme D2 * D5 + E2 *E5 + F2 * F5 + G2 * i5. Toutefois, la saisie de formules dans une feuille de calcul pour six produits prend du temps. Imaginez combien de temps il s’agissait de travailler avec une société qui a produit, par exemple, des produits 50 au niveau de leurs usines. Un moyen plus facile de calculer la main-d’œuvre et l’utilisation des ressources brutes consiste à copier la formule SOMMEPROD sur D15 dans la formule SOMMEPROD ($D $2 : $I $2, D4 : I4). Cette formule calcule D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (qui est notre utilisation du travail), mais il est beaucoup plus facile à entrer ! Vous remarquerez que j’utilise le symbole $ avec la plage D2 : I2 de telle sorte que lorsque je copie la formule, je capture la composition de produit à partir de la ligne 2. La formule dans la cellule D15 calcule l’utilisation de matériel brut.

De la même façon, notre profit est déterminé par

(Médicament 1 profit par livre) * (drogues 1 livres produits) +
(Profit 2 du profit par livre) * (médicament 2 livres produits) +...
(Médicament 6 profit par livre) * (médicament 6 livres produits)

Le bénéfice est facilement calculé dans la cellule D12 avec la formule SOMMEPROD (D9 : i9, $D $2 : $I $2).

Nous sommes désormais en mesure d’identifier les trois composantes de notre modèle de solveur de Mix.

• Cellule cible. Notre objectif est d’optimiser profit (calculé dans la cellule D12).

• Modification de cellules. Nombre de livres produits pour chaque produit (figurant dans la plage de cellules D2 : I2)

• Aux. Nous avons les contraintes suivantes :

  • Ne pas utiliser plus de main-d’œuvre ou de mat. Prem. Autrement dit, les valeurs des cellules D14 : D15 (les ressources utilisées) doivent être inférieures ou égales aux valeurs des cellules F14 : F15 (ressources disponibles).

  • Ne produisez pas plus de médicaments qu’à la demande. Autrement dit, les valeurs des cellules D2 : I2 (livres produits pour chaque médicament) doivent être inférieures ou égales à la demande de chaque médicament (énumérées dans les cellules D8 : i8).

  • Il n’est pas possible de produire un montant négatif de médicament.

Je vais vous montrer comment entrer la cellule cible, modifier des cellules et des contraintes dans le solveur. Il vous suffit alors de cliquer sur le bouton résoudre pour rechercher un produit de gain maximal.

Pour commencer, cliquez sur l’onglet données, puis, dans le groupe analyse, cliquez sur Solveur.

La boîte de dialogue Paramètres du solveur s’affiche, comme illustré dans la figure 27-2.

Cliquez sur la zone cellule cible définie, puis sélectionnez notre cellule de profit (cellule D12). Cliquez sur la zone modifier les cellules, puis pointez sur la plage D2 : I2, qui contient les livres produits pour chaque médicament. La boîte de dialogue doit maintenant ressembler à la figure 27-3.

Nous sommes désormais prêts à ajouter des contraintes au modèle. Cliquez sur le bouton Ajouter. La boîte de dialogue Ajouter une contrainte apparaît dans la figure 27-4.

Pour ajouter les contraintes d’utilisation des ressources, cliquez sur la zone référence de cellule, puis sélectionnez la plage de cellule D14 : D15. Sélectionnez <= dans la liste du milieu. Cliquez sur la zone contrainte, puis sélectionnez la plage de cellules F14 : F15. La boîte de dialogue Ajouter une contrainte doit maintenant ressembler à figure 27-5.

Nous avons vérifié que lorsque le solveur tente d’utiliser différentes valeurs pour la modification de cellules, seules les combinaisons qui répondent à la fois à la valeur D14<= F14 (la main-d’œuvre utilisée est inférieure ou égale à la main-d’oeuvre disponible) et D15<= F15 (la matière brute utilisée est inférieure ou égale à la quantité de données brute disponible) est prise en compte. Cliquez sur Ajouter pour entrer les contraintes de demande. Renseignez la boîte de dialogue Ajouter une contrainte, comme illustré dans la figure 27-6.

En ajoutant ces contraintes, vous vous assurez que lorsque le solveur tente d’utiliser des combinaisons différentes pour modifier les valeurs de cellule, seules les combinaisons qui répondent aux paramètres suivants sont prises en compte :

• D2<= D8 (le montant produit pour médicament 1 est inférieur ou égal à la demande de médicament 1).

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

• F2<= F8 (le montant produit pour le médicament 3 est inférieur ou égal à la demande pour le médicament 3)

• G2<= G8 (le montant produit pour le médicament 4 fabriqué est inférieur ou égal à la demande de médicament 4)

• H2<= H8 (le montant obtenu pour le médicament 5 fabriqué est inférieur ou égal à la demande pour le médicament 5)

• I2<= i8 (le montant obtenu pour le médicament 6 fabriqué est inférieur ou égal à la demande de médicament 6)

Cliquez sur OK dans la boîte de dialogue Ajouter une contrainte. La fenêtre solveur doit se présenter comme figure 27-7.

Dans la boîte de dialogue Options du solveur, nous devons indiquer que la modification de cellules ne doit pas être négative. Cliquez sur le bouton Options dans la boîte de dialogue Paramètres du solveur. Activez la case à cocher modèle linéaire et la zone supposé non-négatif, comme illustré dans la page suivante de l’illustration 27-8. Cliquez sur OK.

Le fait de cocher la case supposer qu’il n’est pas négatif permet de s’assurer que le solveur considère uniquement les combinaisons de cellules variables pour lesquelles chaque cellule variable utilise une valeur non négative. Nous avons vérifié la zone modèle linéaire, car le problème de la combinaison de produits est un type spécial de problème de solveur appelé modèle linéaire. Fondamentalement, un modèle de solveur est linéaire dans les conditions suivantes :

• La cellule cible est calculée par l’ajout des termes du formulaire (modification de la cellule) * (constante).

• Chaque contrainte satisfait la « spécification de modèle linéaire ». Cela signifie que chaque contrainte est évaluée par l’ajout des termes du formulaire (modification de la cellule) * (constante) et comparaison des sommes à une constante.

Pourquoi ce problème de solveur est-il linéaire ? Notre cellule cible (profit) est calculée comme s’il s’agissait de

(Médicament 1 profit par livre) * (drogues 1 livres produits) +
(Profit 2 du profit par livre) * (médicament 2 livres produits) +...
(Médicament 6 profit par livre) * (médicament 6 livres produits)

Ce calcul suit un modèle dans lequel la valeur de la cellule cible est dérivée en ajoutant des termes de formulaire (cellule variable) * (constante).

Notre contrainte de main-d’œuvre est évaluée en comparant la valeur dérivée de (main d’œuvre utilisée par kilo de drogues 1) * (de la main-d’oeuvre utilisée par kilo-produits produits) + (main-d’oeuvre utilisée par kilo de drogues 2) * (2 livres produits produits) +... (Main-d’oeuvre américaineEd par kilo de drogues 6) * (médicament 6 livres produits) au travail disponible.

C’est la raison pour laquelle la contrainte de main-d’oeuvre est évaluée en ajoutant les termes du formulaire (cellule variable) * (constante) et en comparant les sommes à une constante. Les contraintes de main-d’oeuvre et de matériel brut répondent à la condition de modèle linéaire.

Nos contraintes de demande prennent la forme

(Produit pharmaceutique 1) <= (demande de médicament 1)
(Médicaments 2 produits) <= (demande de médicament 2)
§
(Produits médicament 6 produits) <= (exigence de médicament 6)

Chaque contrainte de requête remplit également l’exigence du modèle linéaire, car chacune d’elles est évaluée en ajoutant les termes du formulaire (cellule variable) * (constante) et en comparant les sommes à une constante.

Après avoir démontré que notre modèle de composition de produits est un modèle linéaire, pourquoi devrais-je m’intéresser ?

• S’il s’agit d’un modèle de solveur linéaire et que nous sélectionnerons modèle linéaire, le solveur est sûr de trouver la solution optimale pour le modèle de solveur. S’il ne s’agit pas d’un modèle de solveur linéaire, il est possible que le Solveur ne trouve pas la solution optimale.

• S’il s’agit d’un modèle de solveur linéaire et que nous sélectionnant l’option modèle linéaire, le solveur utilise un algorithme très efficace (méthode simplex) pour rechercher la solution optimale du modèle. S’il s’agit d’un modèle de solveur linéaire qui n’est pas sélectionné supposer que modèle linéaire, le solveur utilise un algorithme très inefficace (méthode GRG2) et peut rencontrer des difficultés pour trouver la solution optimale du modèle.

Après avoir cliqué sur OK dans la boîte de dialogue Options du solveur, nous revenons à la boîte de dialogue solveur principale, présentée plus haut dans la figure 27-7. Lorsque nous cliquons sur résoudre, le solveur calcule une solution optimale (le cas échéant) pour notre modèle d’éventail de produits. Comme indiqué au chapitre 26, une solution optimale pour le modèle de Mix de produit est un ensemble de valeurs de cellules variables (livres produits pour chaque médicament) qui maximise les bénéfices sur l’ensemble de solutions réalisables. Là encore, une solution faisable est un ensemble de valeurs de cellule variables qui répondent à toutes les contraintes. Le changement de valeurs de cellule affiché dans Figure 27-9 est une solution faisable, car tous les niveaux de production ne sont pas négatifs, les niveaux de production ne dépassent pas la demande et l’utilisation des ressources ne dépasse pas les ressources disponibles.

Les valeurs de cellule variables affichées dans la figure 27-10 sur la page suivante représentent une solution irréalisable pour les raisons suivantes :

• Nous produisons une plus grande substance pour le médicament 5 à la demande.

• Nous utilisons plus de main-d’œuvre que ce qui est disponible.

• Nous utilisons des documents plus bruts que ce qui est disponible.

Après avoir cliqué sur résoudre, le solveur trouve rapidement la solution optimale illustrée dans la figure 27-11. Pour conserver les valeurs de solution optimales dans la feuille de calcul, vous devez sélectionner l’option conserver la solution du solveur.

Notre société pharmaceutique peut optimiser son profit mensuel à un niveau de $6 625,20 en produisant 596,67 livres de drogues 4, 1084 livres de drogues 5 et aucune des autres drogues. Nous ne sommes pas en mesure d’obtenir le bénéfice maximal de $6 625,20 d’une autre manière. Tout ce que nous pouvons faire pour nous faire savoir qu’avec nos ressources limitées et votre demande, il n’est pas possible de faire plus de $6 627,20 ce mois.