Solveur Excel : quest-ce que cest et quand lutiliser

Les tableurs Excel tels que Goal Seek qui modifient une seule variable sont utiles, mais malheureusement, la plupart des problèmes dans les affaires ne sont pas si faciles. Vous serez généralement confronté à des formules avec au moins deux et parfois des dizaines de variables. Souvent, un problème aura plus d'une solution, et votre défi consistera à utiliser Excel pour trouver la solution optimale (c'est-à-dire celle qui maximise les profits, minimise les coûts ou correspond à d'autres critères). Pour ces plus grands défis, vous avez besoin d'un outil plus musclé. Excel a juste la réponse : Solver. Excel Solver est un programme d'optimisation sophistiqué qui vous permet de trouver les solutions à des problèmes complexes qui nécessiteraient autrement une analyse mathématique de haut niveau.

Comprendre le solveur Excel

Solver, comme Goal Seek , utilise une méthode itérative pour effectuer ses calculs. L'utilisation de l'itération signifie qu'Excel Solver essaie une solution, analyse les résultats, essaie une autre solution, etc. Cependant, cette itération cyclique n'est pas seulement une conjecture de la part de Solver. Ce serait idiot. Non, Excel Solver examine comment les résultats changent à chaque nouvelle itération et, grâce à certains processus mathématiques sophistiqués (qui, heureusement, se produisent en arrière-plan et peuvent être ignorés), peut généralement dire dans quelle direction il doit se diriger vers la solution.

Les avantages du Solveur Excel

Oui, Goal Seek et Solver sont tous les deux itératifs, mais cela ne les rend pas égaux. En fait, Excel Solver apporte un certain nombre d'avantages à la table :

• Excel Solver vous permet de spécifier plusieurs cellules ajustables. Vous pouvez utiliser jusqu'à 200 cellules réglables en tout.
• Excel Solver vous permet de définir des contraintes sur les cellules ajustables. Par exemple, vous pouvez demander à Solver de trouver une solution qui non seulement maximise les profits, mais satisfasse également à certaines conditions, telles que l'obtention d'une marge brute comprise entre 20 et 30 %, ou le maintien des dépenses inférieures à 100 000 $. Ces conditions sont dites contraintes sur la solution.
• Excel Solver recherche non seulement un résultat souhaité (le « objectif » dans Goal Seek) mais également le résultat optimal. Par exemple, la recherche d'un résultat optimal peut signifier que vous pouvez trouver une solution qui est le maximum ou le minimum possible.
• Pour les problèmes complexes, Solver peut générer plusieurs solutions . Vous pouvez ensuite enregistrer ces différentes solutions sous différents scénarios.

Quand utiliser Excel Solver ?

D'accord, tirons directement ici : Excel Solver est un outil puissant dont la plupart des utilisateurs d'Excel n'ont pas besoin. Il serait exagéré, par exemple, d'utiliser Solver pour calculer le bénéfice net compte tenu des chiffres de revenus et de coûts fixes. De nombreux problèmes, cependant, ne nécessitent rien de moins que l'approche Solver. Ces problèmes recouvrent de nombreux domaines et situations différents, mais ils ont tous en commun les caractéristiques suivantes :

• Ils ont une seule cellule d'objectif (également appelée cellule cible ) qui contient une formule que vous souhaitez maximiser, minimiser ou définir sur une valeur spécifique. Cette formule pourrait être un calcul tel que les dépenses totales de transport ou le bénéfice net.
• La formule de cellule objective contient des références à une ou plusieurs cellules variables (également appelées inconnues ou cellules changeantes ). Le solveur ajuste ces cellules pour trouver la solution optimale pour la formule de cellule objective. Ces cellules variables peuvent inclure des éléments tels que les unités vendues, les frais d'expédition ou les dépenses publicitaires.
• Facultativement, il existe une ou plusieurs cellules de contrainte qui doivent satisfaire certains critères. Par exemple, vous pouvez exiger que la publicité représente moins de 10 % des dépenses totales ou que la remise aux clients soit comprise entre 40 et 60 %.

Par exemple, l'image ci-dessous montre un modèle de données de feuille de calcul configuré pour Excel Solver. Le modèle montre les revenus (prix multiplié par les unités vendues) et les coûts pour deux produits, le profit généré par chaque produit et le profit total. La question à laquelle il faut répondre est la suivante : combien d'unités de chaque produit doivent être vendues pour obtenir un profit total de 0 $ ? C'est ce qu'on appelle dans les affaires une analyse du seuil de rentabilité.

L'objectif de ce modèle de données est de trouver le seuil de rentabilité (où le profit total est de 0 $).

Cela ressemble à une tâche de recherche d'objectif simple, mais ce modèle a un aspect délicat : les coûts variables. Normalement, les coûts variables d'un produit sont son coût unitaire multiplié par le nombre d'unités vendues. S'il en coûte 10 $ pour produire le produit A et que vous vendez 10 000 unités, les coûts variables pour ce produit sont de 100 000 $. Cependant, dans le monde réel, ces coûts sont souvent mélangés entre plusieurs produits. Par exemple, si vous lancez une campagne publicitaire conjointe pour deux produits, ces coûts sont supportés par les deux produits. Par conséquent, ce modèle suppose que les coûts d'un produit sont liés aux unités vendues de l'autre. Voici, par exemple, la formule utilisée pour calculer les coûts du jeu de fléchettes gonflable (cellule B8) :

=B7 * B4 – C4

En d'autres termes, les coûts variables de la cible gonflable sont réduits d'un dollar pour chaque unité vendue de la polisseuse pour chien. Les coûts variables de ce dernier utilisent une formule similaire (dans la cellule C8) :

=C7 * C4 – B4

Les coûts variables liés à plusieurs produits placent ce modèle de données en dehors de ce que Goal Seek peut faire, mais Solver est à la hauteur du défi. Voici les cellules spéciales du modèle que Solver utilisera :

• La cellule objectif est C14; le bénéfice total et la solution cible pour cette formule sont 0 (c'est-à-dire le seuil de rentabilité).
• Les cellules à langer sont B4 et C4, qui contiennent le nombre d'unités vendues pour chaque produit.
• Pour les contraintes, vous pouvez ajouter que les deux cellules de profit du produit (B12 et C12) doivent également être égales à 0.

Chargement du complément Excel Solver

Un complément est un logiciel qui ajoute une ou plusieurs fonctionnalités à Excel. L'installation de compléments vous offre des fonctionnalités Excel supplémentaires qui ne sont pas disponibles dans le ruban par défaut. Le logiciel complémentaire fourni est inclus avec Excel mais n'est pas automatiquement installé lorsque vous installez Excel . Plusieurs compléments sont fournis en standard avec Excel, notamment Solver, qui vous permet de résoudre les problèmes d'optimisation.

Vous installez les compléments fournis à l'aide de la boîte de dialogue Options Excel ; vous pouvez les trouver dans la section Add-Ins. Une fois installés, les compléments sont disponibles immédiatement. Ils apparaissent généralement sur un onglet lié à leur fonction. Par exemple, Solveur apparaît dans l'onglet Données.

Voici les étapes à suivre pour charger le complément Excel Solver :

Choisissez Fichier → Options.
La boîte de dialogue Options Excel s'affiche.

Choisissez Compléments.

Dans la liste Gérer, sélectionnez Compléments Excel, puis sélectionnez Aller.
Excel affiche la boîte de dialogue Compléments.

Cochez la case Complément Solveur

Cliquez sur OK.
Excel ajoute un bouton Solveur au groupe Analyser de l'onglet Données.

Jetez un œil pour savoir comment utiliser le solveur Excel .