Solveur Excel et optimisation : Comment minimiser les coûts

Publié le 08 mai 2014
par Sophie Marchand M.Sc.
Solveur minimisation

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

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

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.

 


 

Formation complémentaire

Pour couvrir plusieurs aspects avancés de la modélisation financière, qui sont indispensables afin de créer des modèles financiers dynamiques et flexibles, suivez notre formation Excel – Modélisation financière niveau 2.

 

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 2 :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Modélisation financière - Niveau 2
CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

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

Retour en haut