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

Publié le 08 mai 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Solveur minimisation

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.

Solveur minimisation

 

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

Solveur minimisation

 

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.

Solveur minimisation

 

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.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top