Utilisation du solveur de Microsoft Excel pour résoudre des problèmes de transport ou de distribution
■■ Comment une compagnie pharmaceutique peut-elle déterminer à quel endroit elle doit produire des médicaments et à partir de quel endroit elle doit expédier les médicaments aux clients?
| De nombreuses entreprises fabriquent des produits à différents endroits (souvent appelés points d’approvisionnement) et expédient leurs produits aux clients (souvent appelés points de demande). Une question naturelle est: «Quel est le moyen le moins cher de produire et d’expédier des produits aux clients tout en répondant à la demande?» Ce type de problème est appelé problème de transport. Un problème de transport peut être configuré comme un modèle de solveur linéaire avec les spécifications suivantes: ■■ Cellule cible Minimisez les coûts de production et d’expédition totaux. ■■ Cellules changeantes Il s’agit de la quantité produite à chaque point d’approvisionnement qui est expédiée à chaque point de demande. ■■ Contraintes La quantité expédiée de chaque point d’approvisionnement ne peut pas dépasser la capacité de l’usine. Chaque point de demande doit recevoir sa demande requise. De plus, chaque cellule changeante doit être non négative. |
|
Comment une société pharmaceutique peut-elle déterminer à quel endroit elle doit produire des médicaments et à partir de quel endroit elle doit expédier les médicaments aux clients?
Vous pouvez suivre ce problème en consultant le fichier Transport.xlsx. Supposons qu’une entreprise produise un certain médicament dans ses installations de Los Angeles, Atlanta et New York. Chaque mois, l’usine de Los Angeles peut produire jusqu’à 10 000 livres de médicament. Atlanta peut produire jusqu’à 12 000 livres, et New York peut produire jusqu’à 14 000 livres. L’entreprise doit expédier chaque mois le nombre de livres indiqué dans les cellules B2: E2 aux quatre régions des États-Unis – Est, Midwest, Sud et Ouest – comme illustré à la figure 1. Par exemple, la région de l’Ouest doit recevoir au moins 13 000 livres de médicament chaque mois. Le coût par livre de la production d’un médicament dans chaque usine et de son expédition dans chaque région du pays est indiqué dans les cellules B4: E6. Par exemple, il en coûte 3,50 $ pour produire une livre du médicament à Los Angeles et l’expédier dans la région du CENTRE-OUEST. Quelle est la façon la moins chère de délivrer la quantité de médicament dont chaque région a besoin?
FIGURE 1: Il s’agit de données pour un problème de transport.
Pour exprimer la cellule cible, vous devez suivre le coût total d’expédition. Après avoir saisi les valeurs d’essai dans la plage de cellules B10: E12 pour les expéditions de chaque point d’approvisionnement vers chaque région, vous pouvez calculer le coût total d’expédition comme suit:
(Montant envoyé de LA vers l’Est) * (Coût par livre d’envoi de médicament de LA vers l’Est)
+ (Montant envoyé de LA vers le Midwest) * (Coût par livre d’envoi de médicament de LA vers le Midwest)
+ (Montant envoyé de LA vers le Sud) * (Coût par livre d’envoi de médicament de LA vers le Sud)
+ (Montant envoyé de LA vers l’Ouest) * (Coût par livre d’envoi de médicament de LA vers l’Ouest)
+ … (Montant envoyé de New York à l’Ouest)
* (Coût par livre d’envoi de drogue de New York à l’Ouest)
La fonction SOMMEPROD peut multiplier les éléments correspondants dans deux rectangles (tant que les rectangles sont de la même taille) et ajouter les produits ensemble. La plage de cellules B4: E6 a été nommée Coûts et la plage de cellules changeantes (B10: E12) a été expédiée. Par conséquent, le coût total d’expédition et de production est calculé dans la cellule B18 avec la formule SOMMAIRE (coûts, expédiés).
Pour exprimer les contraintes du problème, calculez d’abord le total expédié de chaque point d’approvisionnement. En entrant la formule SOMME (B10: E10) dans la cellule F10, vous pouvez calculer le nombre total de livres expédiées de Los Angeles comme (LA expédiée vers l’Est) + (LA expédiée vers le CENTRE-OUSET) + (LA expédiée vers le Sud) + (LA expédiés à l’Ouest). Copie de cette formule dans F11: F12 calcule le total expédié d’Atlanta et La ville de New York. Plus tard, vous ajouterez une contrainte (appelée contrainte d’approvisionnement) qui garantit que la quantité expédiée de chaque emplacement ne dépasse pas la capacité de l’usine. Ensuite, calculez le total reçu par chaque point de demande. Commencez par saisir la formule SOMME (B10: B12) dans la cellule B13. Cette formule calcule le nombre total de livres reçues dans l’Est comme (Livres expédiées de LA vers l’Est) + (Livres expédiées d’Atlanta vers l’Est) + (Livres expédiées de New York vers l’Est). En copiant cette formule de B13 à C13: E13, vous calculez les kilos de médicament reçus par les régions du Midwest, du Sud et de l’Ouest. Plus tard, vous ajouterez une contrainte (appelée contrainte de demande) qui garantit que chaque région reçoit la quantité de médicament dont elle a besoin.
Ouvrez la boîte de dialogue Paramètres du solveur (cliquez sur Solveur dans le groupe Analyse de l’onglet Données) et remplissez-le comme illustré à la figure 2 ci-dessous.
FIGURE 2 : Le solveur est configuré pour résoudre votre problème de transport.
Vous souhaitez minimiser le coût total d’expédition (calculé dans la cellule B18). Les cellules changeantes sont le nombre de livres expédiées de chaque usine vers chaque région du pays. (Ces quantités sont répertoriées dans la plage nommée Expédié, composée des cellules B10: E12.) Le F10: F12 < = H10: la contrainte H12 (la contrainte d’approvisionnement) garantit que la quantité envoyée depuis chaque usine ne dépasse pas sa capacité. Le B13: E13> = B15: La contrainte E15 (la contrainte de demande) garantit que chaque région reçoit au moins la quantité de médicament dont elle a besoin.
Ce modèle est un modèle de solveur linéaire car la cellule cible est créée en additionnant les termes de la forme (cellule changeante) * (constante), et vos contraintes d’offre et de demande sont créées en comparant la somme des cellules changeantes à une constante. Parce que le modèle est linéaire, choisissez le Simplex Moteur LP. De toute évidence, les expéditions doivent être non négatives, alors sélectionnez les variables Rendre sans contrainte Case à cocher non négatif.
Après avoir cliqué sur Résoudre dans la boîte de dialogue Paramètres du solveur, la solution optimale présentée ci-dessus s’affiche, dans la figure 1. Le coût minimum pour répondre à la demande des clients est de 86 800 $. Ce coût minimum peut être atteint si l’entreprise utilise le calendrier de production et d’expédition suivant:
■■ Expédiez 10 000 livres de Los Angeles à la région Ouest.
■■ Expédier 3 000 livres d’Atlanta à la région Ouest et le même montant d’Atlanta à la
Région du CENTRE-OUEST. Expédiez 6 000 livres d’Atlanta dans la région sud.
■■ Expédier 9 000 livres de New York vers la région Est et 3 000 livres de New York
Ville dans la région du CENTRE OUEST.