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

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Cet article traite de l'utilisation du solveur, un programme de complément Microsoft Excel que vous pouvez utiliser pour l'analyse de scénarios afin de déterminer une 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. Vous pouvez trouver la solution à ce problème dans le fichier Prodmix. xlsx, présenté dans la figure 27-1.

Image représentant un livre

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 pour le poids) * (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.

Remarque :  Comme décrit dans le chapitre 26, «présentation de l'optimisation avec le solveur Excel», le solveur est installé en cliquant sur le bouton Microsoft Office, sur Options Excel, puis sur compléments. Dans la liste gérer, cliquez sur compléments Excel, activez la case à cocher complément Solver, puis cliquez sur OK.

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

Image représentant un livre

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.

Image représentant un livre

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.

Image représentant un livre

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.

Image représentant un livre

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 à D14< = F14 (la main-d'œuvre utilisée est inférieure ou égale à la main-d'œuvre disponible) et D15< = F15 (la matière brute utilisée est inférieur ou égal à le matériau brut disponible) est considéré. 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.

Image représentant un livre

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 (le volume de produit de médicament 2 est inférieur ou égal à la demande pour le médicament 2)

  • F2< = F8 (le montant produit pour le médicament 3 réalisé 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 produit pour le médicament 5 fabriqué est inférieur ou égal à la demande pour le médicament 5)

  • I2< = i8 (le montant produit par 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.

Image représentant un livre

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.

Image représentant un livre

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 pour le poids) * (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édicament 2 produit) < = (demande de médicament 2)
§
(médicament 6 produit) < = (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.

Image représentant un livre

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.

Image représentant un livre

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.

Image représentant un livre

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.

Supposez que la demande de chaque produit doit être remplie. (Pour en savoir plus, voir la feuille de calcul de solution pour le fichier Prodmix. xlsx.) Nous devons alors changer nos contraintes de demande de D2: I2< = D8: i8 vers 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, présentée dans la figure 27-12, s'affiche.

Image représentant un livre

Sélectionnez > =, puis cliquez sur OK. Nous sommes maintenant certain que le solveur va envisager de changer uniquement les valeurs de cellule qui répondent à toutes les demandes. Lorsque vous cliquez sur résoudre, le message «le Solveur n'a pas pu trouver une solution faisable» s'affiche. Ce message ne signifie pas que nous avons commis une erreur dans notre modèle, mais plutôt qu'avec nos ressources limitées, nous ne sommes pas en mesure de répondre à la demande de tous les produits. Le solveur nous dit simplement que si nous voulons répondre à la demande de chaque produit, nous devons ajouter de plus en plus de main-d'œuvre, de matières premières, ou bien plus encore.

Voyons ce qui se passe si nous autorisons une demande illimitée pour chaque produit et que nous autorisons la production de quantités négatives pour chaque médicament. (Vous pouvez constater ce problème de solveur sur la feuille de calcul Set valeurs do not converge dans le fichier Prodmix. xlsx.) Pour trouver la solution optimale pour cette situation, ouvrez le solveur, cliquez sur le bouton Options, puis décochez la case supposer comme non négatif. 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 convergent pas». Ce message signifie que si la cellule cible doit être agrandie (comme dans notre exemple), il existe des solutions possibles avec des valeurs de cellules cibles de niveau arbitraire. (Si la cellule cible doit être réduite, le message «définir les valeurs de cellule ne convergent» signifie qu'il existe des solutions possibles avec des valeurs de cellules cibles arbitrairement petites.) Dans notre situation, en autorisant la production négative d'un médicament, nous avons en effet des ressources de création qui peuvent être utilisées pour produire des quantités arbitraires d'autres drogues. Nous nous offrons une demande illimité qui nous permet de passer des gains illimités. Dans une situation réelle, nous ne pouvons pas faire une somme d'argent illimitée. En résumé, si la mention «SET values do not convergent» s'affiche, cela signifie que votre modèle comporte une erreur.

  1. Imaginons que notre société pharmaceutique puisse acheter jusqu'à 500 heures de travail à $1 de plus en heures par rapport aux frais de main-d'œuvre en vigueur. Comment optimiser votre profit?

  2. Pour les usines de fabrication de circuits, quatre techniciens (A, B, C et D) produisent trois produits (produits 1, 2 et 3). Ce mois-ci, le fabricant de puces peut vendre 80 unités de produit 1, 50 unités de produit 2 et au maximum 50 unités de produit 3. Le technicien A ne peut faire que les produits 1 et 3. Le technicien B peut uniquement effectuer 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 contribuent au bénéfice suivant: produit 1, $6; Produit 2, $7; et produit 3, $10. Le temps (en heures) pour chaque technicien doit fabriquer un produit en procédant comme suit:

    Produit

    Technicien A

    Technicien B

    Technicien C

    Technicien D

    1

    2

    2,5

    Ne peut pas faire

    Ne peut pas faire

    2

    Ne peut pas faire

    3

    Ne peut pas faire

    3,5

    3

    3

    Ne peut pas faire

    4

    Ne peut pas faire

  3. Chaque technicien peut travailler sur 120 heures par mois. Comment le fabricant de votre processeur peut-il optimiser son profit mensuel? Supposez que le nombre d'unités peut être obtenu.

  4. Une usine de fabrication d'ordinateurs génère des souris, des claviers et des manettes de jeu vidéo. Le taux de profit unitaire, le taux d'utilisation de la main d'œuvre par unité, la demande mensuelle et l'utilisation de l'ordinateur par unité, sont fournis dans le tableau suivant:

    Puisse

    Possèdent

    Joysticks

    Profit/unité

    $8

    $11

    $9

    Utilisation/unité de main-d'œuvre

    2 heures

    2 heures

    .24 heures

    Temps/unité de poste

    .04 heures

    .055 heures

    .04 heures

    Demande mensuelle

    15 000

    27 000

    11 000

  5. Chaque mois, un total de 13 000 heures de travail et 3000 heures d'ouverture de poste sont disponibles. Comment le fabricant peut-il optimiser son concours mensuel de l'entreprise?

  6. Résolvez notre exemple de drogues, en supposant qu'une demande minimum de 200 unités pour chaque médicament doit être satisfaite.

  7. Jason effectue des bracelets en treillis, des colliers et des boucles. Il souhaite travailler sur un maximum de 160 heures par mois. Il a 800 onces de diamants. Les bénéfices, heures de main-d'œuvre et onces de diamants nécessaires pour produire chaque produit sont les suivants. S'il s'agit d'une demande de produits illimités, comment Jason peut-il optimiser son profit?

    Produit

    Profit unitaire

    Heures de travail par unité

    Onces de diamants par unité

    Bracelet

    300 €

    .35

    1,2

    Collier

    200 $

    .15

    .75

    Oreille

    100 €

    0,05

    0.5

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×