Optimisation avec Excel Solver

Solver est un programme complémentaire Microsoft Excel que vous pouvez utiliser pour l'optimisation dans l'analyse hypothétique.

Selon O'Brien et Marakas, optimization analysisest une extension plus complexe de l'analyse de la recherche d'objectifs. Au lieu de définir une valeur cible spécifique pour une variable, le but est de trouver la valeur optimale pour une ou plusieurs variables cibles, sous certaines contraintes. Ensuite, une ou plusieurs autres variables sont modifiées à plusieurs reprises, sous réserve des contraintes spécifiées, jusqu'à ce que vous découvriez les meilleures valeurs pour les variables cibles.

Dans Excel, vous pouvez utiliser Solver pour trouver un optimal value (maximum ou minimum, ou une certaine valeur) pour une formule dans une cellule appelée cellule objectif, sous réserve de certaines contraintes ou limites, sur les valeurs d'autres cellules de formule sur la feuille de calcul.

Cela signifie que le solveur fonctionne avec un groupe de cellules appelées variables de décision qui sont utilisées pour calculer les formules dans les cellules d'objectif et de contrainte. Le solveur ajuste les valeurs dans les cellules de variable de décision pour satisfaire les limites des cellules de contrainte et produire le résultat souhaité pour la cellule objectif.

Vous pouvez utiliser Solver pour trouver des solutions optimales à divers problèmes tels que -

Déterminer le mix de produits mensuel pour une unité de fabrication de médicaments qui maximise la rentabilité.
Planification du personnel dans une organisation.
Résoudre les problèmes de transport.
Planification financière et budgétisation.

Activation du complément Solver

Avant de rechercher une solution à un problème avec Solver, assurez-vous que le Solver Add-in est activé dans Excel comme suit -

Cliquez sur l'onglet DONNÉES sur le ruban. leSolver La commande doit apparaître dans le groupe Analyse comme indiqué ci-dessous.

Si vous ne trouvez pas la commande Solver, activez-la comme suit -

Cliquez sur l'onglet FICHIER.
Cliquez sur Options dans le volet gauche. La boîte de dialogue Options Excel s'affiche.
Cliquez sur Compléments dans le volet gauche.
Sélectionnez Compléments Excel dans la zone Gérer et cliquez sur OK.

La boîte de dialogue Compléments s'affiche. VérifierSolver Add-inet cliquez sur Ok. Vous devriez maintenant pouvoir trouver la commande Solveur sur le ruban sous l'onglet DONNÉES.

Méthodes de résolution utilisées par le solveur

Vous pouvez choisir l'une des trois méthodes de résolution suivantes prises en charge par Excel Solver, en fonction du type de problème -

LP Simplex

Utilisé pour les problèmes linéaires. UNESolver le modèle est linéaire dans les conditions suivantes -

La cellule cible est calculée en additionnant les termes de la forme (cellule changeante) * (constante).
Chaque contrainte satisfait l'exigence du modèle linéaire. Cela signifie que chaque contrainte est évaluée en additionnant les termes de la forme (cellule changeante) * (constante) et en comparant les sommes à une constante.

Gradient réduit généralisé (GRG) Non linéaire

Utilisé pour les problèmes non linéaires lisses. Si votre cellule cible, l'une de vos contraintes ou les deux contiennent des références à des cellules changeantes qui ne sont pas de la forme (cellule changeante) * (constante), vous avez un modèle non linéaire.

Évolutionniste

Comprendre l'évaluation du solveur

Le solveur requiert les paramètres suivants -

Cellules de variable de décision
Cellules de contrainte
Cellules d'objectifs
Méthode de résolution

L'évaluation du solveur est basée sur les éléments suivants:

Les valeurs des cellules de variable de décision sont limitées par les valeurs des cellules de contrainte.
Le calcul de la valeur dans la cellule objectif inclut les valeurs dans les cellules de variable de décision.
Le solveur utilise la méthode de résolution choisie pour obtenir la valeur optimale dans la cellule objectif.

Définition d'un problème

Supposons que vous analysiez les bénéfices réalisés par une entreprise qui fabrique et vend un certain produit. On vous demande de trouver le montant qui peut être dépensé en publicité au cours des deux prochains trimestres sous réserve d'un maximum de 20 000. Le niveau de publicité de chaque trimestre affecte les éléments suivants:

Le nombre d'unités vendues, déterminant indirectement le montant du chiffre d'affaires.
Les dépenses associées, et
Le profit.

Vous pouvez continuer à définir le problème comme -

Trouvez le coût unitaire.
Trouvez le coût publicitaire par unité.
Trouvez le prix unitaire.

Ensuite, définissez les cellules pour les calculs requis comme indiqué ci-dessous.

Comme vous pouvez le constater, les calculs sont effectués pour le trimestre 1 et le trimestre 2 qui sont pris en compte:

Le nombre d'unités disponibles à la vente au trimestre1 est de 400 et au trimestre2 est de 600 (cellules - C7 et D7).
Les valeurs initiales du budget publicitaire sont définies sur 10 000 par trimestre (cellules - C8 et D8).
Le nombre d'unités vendues dépend du coût publicitaire par unité et donc du budget pour le trimestre / Adv. Coût par unité. Notez que nous avons utilisé la fonction Min pour veiller à ce que le no. d'unités vendues en <= no. d'unités disponibles. (Cellules - C9 et D9).
Les revenus sont calculés en tant que prix unitaire * nombre d'unités vendues (cellules - C10 et D10).
Les dépenses sont calculées comme le coût unitaire * Nombre d'unités disponibles + Adv. Coût pour ce trimestre (Cellules - C11 et D12).
Le profit est un revenu - Dépenses (cellules C12 et D12).
Le profit total correspond au profit du premier trimestre + le profit du deuxième trimestre (cellule - D3).

Ensuite, vous pouvez définir les paramètres pour Solver comme indiqué ci-dessous -

Comme vous pouvez le constater, les paramètres de Solver sont -

La cellule d'objectif est D3 qui contient le profit total, que vous souhaitez maximiser.
Les cellules de la variable de décision sont C8 et D8 qui contiennent les budgets pour les deux trimestres - Trimestre1 et Trimestre2.
Il existe trois cellules de contrainte - C14, C15 et C16.
- La cellule C14 qui contient le budget total doit définir la contrainte de 20000 (cellule D14).
- Cellule C15 qui contient le no. d'unités vendues au trimestre1 consiste à définir la contrainte <= no. d'unités disponibles dans Quarter1 (cellule D15).
- Cellule C16 qui contient le no. d'unités vendues au trimestre2 est de définir la contrainte de <= no. d'unités disponibles dans Quarter2 (cellule D16).

Résoudre le problème

L'étape suivante consiste à utiliser Solver pour trouver la solution comme suit -

Step 1- Allez dans DONNÉES> Analyse> Solveur sur le ruban. La boîte de dialogue Paramètres du solveur s'affiche.

Step 2 - Dans la zone Définir l'objectif, sélectionnez la cellule D3.

Step 3 - Sélectionnez Max.

Step 4 - Sélectionnez la plage C8: D8 dans le By Changing Variable Cells boîte.

Step 5 - Ensuite, cliquez sur le bouton Ajouter pour ajouter les trois contraintes que vous avez identifiées.

Step 6- La boîte de dialogue Ajouter une contrainte apparaît. Définissez la contrainte du budget total comme indiqué ci-dessous et cliquez sur Ajouter.

Step 7- Définissez la contrainte pour le nombre total. d'unités vendues au premier trimestre comme indiqué ci-dessous et cliquez sur Ajouter.

Step 8- Définissez la contrainte pour le nombre total. d'unités vendues au trimestre2 comme indiqué ci-dessous et cliquez sur OK.

La boîte de dialogue Paramètres du solveur apparaît avec les trois contraintes ajoutées dans la zone - Soumis aux contraintes.

Step 9 - Dans le Select a Solving Method , sélectionnez Simplex LP.

Step 10- Cliquez sur le bouton Résoudre. La boîte de dialogue Résultats du solveur s'affiche. SélectionnerKeep Solver Solution et cliquez sur OK.

Les résultats apparaîtront dans votre feuille de calcul.

Comme vous pouvez le constater, la solution optimale qui produit un profit total maximum, sous réserve des contraintes données, se trouve être la suivante:

Bénéfice total - 30000.
Adv. Budget pour le premier trimestre - 8000.
Adv. Budget pour le deuxième trimestre - 12000.

Parcours des solutions d'essai du solveur

Vous pouvez parcourir les solutions d'essai de Solver, en regardant les résultats de l'itération.

Step 1 - Cliquez sur le bouton Options dans la boîte de dialogue Paramètres du solveur.

le Options la boîte de dialogue apparaît.

Step 2 - Cochez la case Afficher les résultats de l'itération et cliquez sur OK.

Step 3 - Le Solver Parametersla boîte de dialogue apparaît. Cliquez surSolve.

Step 4 - Le Show Trial Solution boîte de dialogue apparaît, affichant le message - Solver paused, current solution values displayed on worksheet.

Comme vous pouvez le constater, les valeurs d'itération actuelles sont affichées dans vos cellules de travail. Vous pouvez soit empêcher le solveur d'accepter les résultats actuels ou continuer avec le solveur de trouver une solution dans les étapes suivantes.

Step 5 - Cliquez sur Continuer.

le Show Trial SolutionLa boîte de dialogue apparaît à chaque étape et enfin, une fois la solution optimale trouvée, la boîte de dialogue Résultats du solveur apparaît. Votre feuille de calcul est mise à jour à chaque étape, affichant enfin les valeurs de résultat.

Enregistrement des sélections du solveur

Vous disposez des options d'enregistrement suivantes pour les problèmes que vous résolvez avec Solver -

Vous pouvez enregistrer les dernières sélections dans la boîte de dialogue Paramètres du solveur avec une feuille de calcul en enregistrant le classeur.
Chaque feuille de calcul d'un classeur peut avoir ses propres sélections de solveur et toutes seront enregistrées lorsque vous enregistrez le classeur.
Vous pouvez également définir plusieurs problèmes dans une feuille de calcul, chacun avec ses propres sélections de solveur. Dans ce cas, vous pouvez charger et enregistrer les problèmes individuellement à l'aide de l'option Charger / Enregistrer dans la boîte de dialogue Paramètres du solveur.
- Clique le Load/Savebouton. La boîte de dialogue Charger / Enregistrer apparaît.
- Pour enregistrer un modèle de problème, entrez la référence de la première cellule d'une plage verticale de cellules vides dans laquelle vous souhaitez placer le modèle de problème. Cliquez sur Enregistrer.

Le modèle de problème (le jeu de paramètres du solveur) apparaît à partir de la cellule que vous avez indiquée comme référence.

Pour charger un modèle de problème, entrez la référence pour toute la plage de cellules qui contient le modèle de problème. Ensuite, cliquez sur le bouton Charger.