Aujourd’hui, je vais vous montrer comment utiliser le gestionnaire de scénarios afin d’élaborer et vérifier des hypothèses émises.
L’exemple…
Pour bien comprendre, et comme d’habitude, nous allons prendre un exemple dont voici les données:
Dans la première partie, nous trouvons les données par unité et dans la seconde l’analyse des totaux. Les cases en jaune correspondent aux entrées attendues tandis que les cellules non remplies de la colonne ‘Constante’ sont les résultats qui découlent des autres cellules.
En résumé, une entreprise envisage de fabriquer de nouveaux circuits imprimés. Le coût de fabrication unitaire est fixé à 45,10 € l’unité. Ces circuits sont conditionnées pas boîte de 50. Le coût du conditionnement est de 3,52 € par boîte de 50.
Nous pouvons en déduire le coût de revient qui sera donc de 2 258,52 € par boîte. Mais l’entreprise doit faire face à des taux de rebut identifié en fin de fabrication compris entre 3% et 10%. Pour amortir l’ensemble de ses frais, l’entreprise applique une marge de 25%.
Nous pouvons en déduire le coût de revient qui sera donc de 2 258,52 € par boîte. Mais l’entreprise doit faire face à des taux de rebut identifié en fin de fabrication compris entre 3% et 10%. Pour amortir l’ensemble de ses frais, l’entreprise applique une marge de 25%.
Questions…
La question que nous pouvons nous poser est la suivante: Que se passe t-il si la marge brute unitaire varie et si nous pouvons améliorer le taux de rebut ? Nous pourrions très bien utiliser une table de données pour représenter cela:
Nous pouvons voir sur la gauche différentes valeurs du taux de rebut et en colonnes, les différentes marge possibles. Chaque cellule du tableau représentant le bénéfice en fonction des différents taux.
Pour notre cas, nous souhaitons voir différents scénarios que nous connaissons déjà:
- Un scénario pessimiste: Marge de 20% et taux de rebut à 10%
- Un scénario normal (celui de nos calculs précédents): Marge à 25% et taux rebut à 5%
- Un scénario optimiste: Marge à 25% et taux de rebut à 3%.
Ces données sont issues de nos hypothèses décrites plus haut. Voici dans Excel une représentation de ces 3 scénarios:
Solutions…
Notons qu’avec la table de données, nous aurions trouvé directement les valeurs de nos scénarios:
A présent que nous avons nos trois scénarios, nous allons pouvoir utiliser l’utilitaire d’Excel de gestionnaire de scénarios. Nous pouvons également noter que nous aurions pu utiliser une autre méthode qui consiste à utiliser la fonction CHOISIR d’Excel.
Voici un exemple:
Nous ne le verrons pas aujourd’hui.
Pour utiliser le gestionnaire de scénario, il suffit de créer nos 3 scénarios comme explicité ci-dessous:
Dans le ruban, partie Données, sélectionner dans Outils de données Analyse de Scénarios puis Gestionnaire de scénarios:
Dans Excel 2013:
Dans Excel 2010:
Ensuite le principe est le même quelque soit la version d’Excel. Vous devez donner un nom au scénario (pour le premier, nous choisirons Pessimiste par exemple) puis vous définissez vos cellules variables (F18;F25 dans notre cas, correspondant respectivement à la marge et au taux de rebut). Vous pouvez décrire le scénario et ensuite il suffit de cliquer sur OK. Enfin, vous devez entrer vos valeurs pour chaque variable. Vous devez ensuite entrer les valeurs de chaque variable (il faut saisir les valeurs car il est impossible de les récupérer depuis la feuille).
Refaire ensuite la manipulation pour les deux autres scénarios, à savoir « normal » et « optimiste ».
Ainsi vous allez avoir l’écran suivant dans le gestionnaire de scénario:
A présent, nous allons cliquer sur le bouton synthèse afin de pouvoir comparer les différents scénarios. Sélectionner ensuite Synthèse de scénario, choisir la cellule résultantes (F28 dans notre cas, correspondant au bénéfice) et cliquer sur OK.
Une nouvelle feuille va être créée avec la comparaison des différentes scénarios:
Nous noterons que si nous faisions évoluer notre modèle, il faudrait à nouveau faire la synthèse des scénarios pour voir l’évolution sur ces derniers.
Un autre outil d’Excel qui nous permettrait de définir les données d’entrée en fonction de certaines contraintes, telle que le bénéfices minimum à atteindre pour décider de lancer le nouveau produit pourrait être utilisé: le Solveur.
En résumé, pour la gestion des hypothèses et des scénarios, Excel propose de nombreux outils, que ce soit au niveau des fonctionnalités (comme le gestionnaire de scénarios, les tables de données ou le solveur) ou au niveau des fonctions (comme CHOISIR par exemple).
En modélisation, l’idée étant de simuler des hypothèses dans le Business model de l’organisation afin de pouvoir prendre les meilleures décisions, ces outils peuvent avoir une très grande importance. Bien entendu, il faudra au préalable réaliser le modèle financier qui répondra aux critères et aux besoins définis par l’organisation elle-même.