Solveur Excel: Comment minimiser les coûts tout en satisfaisant la demande?

Récemment, j’ai publié sur ce blogue deux billets qui traitaient de l’utilisation du solveur dans Excel. Un premier billet portait sur un problème de maximisation (comment maximiser les profits) et un deuxième billet portait sur un problème d’optimisation (combien investir annuellement dès aujourd’hui pour obtenir 100,000$ par année à la retraite). Aujourd’hui, je vous propose un billet sur un problème de minimisation à résoudre avec le solveur (comment minimiser les coûts tout en satisfaisant la demande?).

Problème de minimisation

Supposons le contexte suivant:

• Votre entreprise possède 3 usines, qui livrent leurs produits dans 4 régions distinctes.
• Vous connaissez les coûts de production et les coûts de livraison par livre pour chacune des combinaisons usines/régions.
• Vous connaissez également la capacité de production (en livres), de chacune des usines.
• Finalement, vous connaissez également la demande (en livres) de vos produits, par région.
• Vous pouvez donc construire un "business case" comme celui démontré sur l’image ci-bas.

Utilisation du solveur d’Excel

Vous devez ensuite fixer les paramètres du solveur d’Excel.

D’abord, vous devez indiquer à Excel que la cellule E28 (qui représente le calcul des coûts totaux) doit être au minimum.

Pour y parvenir, Excel peut changer la valeur des cellules E17 à H19.

Excel doit toutefois tenir compte des contraintes suivantes:

• Les quantités à livrer doivent être plus grandes ou égales à la demande
• Les quantités à livrer par usine doivent être inférieures à la capacité de production de chaque usine

Résolution du cas

Lorsque vous demandez au solveur d’Excel de résoudre cette équation, vous apprenez que certaines usines ne devraient tout simplement pas livrer dans certaines régions (voir dans l’image ci-bas, les cellules E17 à H19). Vous apprenez également quelles sont les quantités à produire pour minimiser les coûts, tout en satisfaisant la demande.

Conclusion

Excel dispose d’une suite d’outils fort intéressants pour faire de l’analyse financière. Le solveur est assurément un des outils intéressants, lorsque l’on doit résoudre des problèmes d’optimisation. Cela dit, vous constaterez que le tout repose néanmoins sur une bonne modélisation préliminaire des données. En effet, si l’on souhaite obtenir de bons résultats, nos données doivent être modélisées adéquatement.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation d’affaires