Excel: Résoudre un cas de maximisation grâce au Solveur

Excel dispose d’une fonctionnalité intéressante pour résoudre des cas d’optimisation. Cet article vous présente la résolution d’un cas de maximisation, plus précisément, la maximisation des profits. Si vous souhaitez en apprendre davantage sur l’utilisation du Solveur pour répondre des cas d’optimisation, je vous invite à jeter un coup d’oeil à ma formation Excel – Modélisation financière (niveau 2), où je présente notamment, plusieurs cas de figures semblables.

Installation du Solveur

D’abord, assurez-vous d’activer le Add-in “Solveur” dans vos options Excel.

Cas à résoudre : Maximisation des profits

Dans l’exemple suivant, vous avez différentes variables d’entrée (# unités produits, # heures de MOD, # livres de MP, prix unitaires, coûts variables, demande (en unités)) et vous avez différentes variables de sortie (Marges bénéficiaires et profits). Les marges bénéficiaires sont obtenues en soustrayant les coûts variables des prix unitaires alors que les profits multiplient chacune des marges bénéficiaires par produit par le nombre d’unités produites (voir image cib-as).

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.

Contraintes

Qui dit cas d’optimisation, dit contraintes. Dans notre exemple de maximisation des profits, il y a une contrainte au niveau de la MOD et de la MP utilisées. Nous avons fixé ces contraites à 4,000 heures et à 2,500 livres.

De plus, nous devons également respecter une contraite concernant la demande. Il ne faut jamais produire plus que le niveau demandé.

Solveur

Pour résoudre ce cas de maximisation des profits , rendez-vous d’abord dans le menu Données/Data et cliquer sur Solveur/Solver.

Par la suite, il s’agit de définir le cas, de la façon suivante:

Combien d’unités produire pour maximiser les profits?

D’abord, l’objectif est de maximiser les profits. Nous allons donc inscrire la cellule D16 comme objectif et nous allons cocher la case “Max” pour maximisation.

Ensuite, nous allons indiquer à Excel que c’est en changeant les valeurs des cellules D8 à I8 (# unités produites), que nous voulons maximiser les profits.

Vient ensuite l’étape des contraintes. D’abord, nous allons indiquer à Excel que les cellules D19 et D20 doivent être inférieures aux cellules F19 et F20 (contraintes au niveau de la MOD et des MP). Ensuite, nous allons indiquer à Excel que les valeurs de la ligne 8 doivent être inférieures aux valeurs de la ligne 14 (contrainte de demande).

Résolution du cas

Excel vous indiquera que c’est en produisant 756 unités du produit C et 390 unités du produit F que vous maximiserez vos profits.

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 :

La 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é.