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. 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 – Notions avancées, 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).

Contraintes

Qui dit cas d’optimisation, dit contraintes. Dans notre exemple, 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, 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.

Envie d’en apprendre davantage sur Solveur?

Je vous invite à assister à ma formation Excel – Notions avancées, où je présenterai notamment, plusieurs cas d’optimisation avec Solveur.

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