Basculer entre différents ensembles de valeurs à l’aide de scénarios

Il existe trois types d’outils d’analyse What-If fournis avec Excel : Scénarios, Tables de données et Recherche d’objectifs. Les scénarios et les tables de données prennent des ensembles de valeurs d’entrée et de projet vers l’avant pour déterminer les résultats possibles. Goal Seek diffère des scénarios et des tables de données en ce sens qu’il prend un résultat et effectue des projets à l’envers pour déterminer les valeurs d’entrée possibles qui produisent ce résultat.

Chaque scénario peut prendre en charge jusqu’à 32 valeurs de variables. Si vous souhaitez analyser plus de 32 valeurs et que les valeurs ne représentent qu’une ou deux variables, vous pouvez utiliser des tables de données. Bien qu’elle soit limitée à une ou deux variables (une pour la cellule d’entrée de ligne et une pour la cellule d’entrée de colonne), une table de données peut inclure autant de valeurs de variable différentes que vous le souhaitez. Un scénario peut avoir un maximum de 32 valeurs différentes, mais vous pouvez créer autant de scénarios que vous le souhaitez.

En plus de ces trois outils, vous pouvez installer des compléments qui vous aident à effectuer une analyse What-If, comme le complément Solver. La macro complémentaire Solveur est similaire à l’outil Valeur cible, mais peut prendre en charge davantage de variables. Vous pouvez également créer des prévisions à l’aide de la poignée de recopie et de diverses commandes intégrées dans Excel. Pour des modèles plus avancés, vous pouvez utiliser la macro complémentaire Utilitaire d'analyse.

Supposons que vous souhaitiez créer un budget, mais que vous ne soyez pas certain de vos revenus. À l’aide de scénarios, vous pouvez définir différentes valeurs possibles pour le chiffre d’affaires, puis basculer entre les scénarios pour effectuer des analyses de scénarios.

Par exemple, supposons que votre pire scénario budgétaire est le revenu brut de 50 000 $ et le coût des marchandises vendues de 13 200 $, ce qui laisse un bénéfice brut de 36 800 $. Pour définir cet ensemble de valeurs en tant que scénario, entrez d’abord les valeurs dans une feuille de calcul, comme illustré dans l’illustration suivante :

Les cellules variables ont des valeurs que vous tapez, tandis que la cellule Result contient une formule basée sur les cellules variables (dans cette cellule d’illustration B4 a la formule =B2-B3).

Vous utilisez ensuite la boîte de dialogue Gestionnaire de scénarios pour enregistrer ces valeurs en tant que scénario. Accédez à l’onglet Données > What-If Analysis > Scenario Manager > Ajouter.

Dans la boîte de dialogue Nom du scénario, nommez le scénario Pire cas et spécifiez que les cellules B2 et B3 sont les valeurs qui changent entre les scénarios. Si vous sélectionnez La modification des cellules dans votre feuille de calcul avant d’ajouter un scénario, le Gestionnaire de scénarios insère automatiquement les cellules à votre place. Sinon, vous pouvez les taper manuellement ou utiliser la boîte de dialogue de sélection de cellules à droite de la boîte de dialogue Modifier les cellules.

Protection : vous pouvez également protéger vos scénarios. Par conséquent, dans la section Protection, case activée les options souhaitées, ou décochez-les si vous ne souhaitez aucune protection.

• Sélectionnez Empêcher les modifications pour empêcher la modification du scénario lorsque la feuille de calcul est protégée.

• Sélectionnez Masqué pour empêcher l’affichage du scénario lorsque la feuille de calcul est protégée.

Supposons maintenant que votre scénario de budget Best Case correspond au revenu brut de 150 000 $ et aux coûts des marchandises vendues de 26 000 $, ce qui laisse 124 000 $ en bénéfice brut. Pour définir cet ensemble de valeurs en tant que scénario, vous créez un autre scénario, nommez-le Meilleur cas et fournissez des valeurs différentes pour la cellule B2 (150 000) et la cellule B3 (26 000). Étant donné que le bénéfice brut (cellule B4) est une formule ( la différence entre les revenus (B2) et les coûts (B3) - vous ne modifiez pas la cellule B4 pour le scénario Meilleur cas.

Après avoir enregistré un scénario, il devient disponible dans la liste des scénarios que vous pouvez utiliser dans vos analyses de scénarios. Compte tenu des valeurs de l’illustration précédente, si vous choisissez d’afficher le scénario Best Case, les valeurs de la feuille de calcul changent pour ressembler à l’illustration suivante :

Il peut arriver que vous ayez toutes les informations dans une feuille de calcul ou un classeur nécessaires pour créer tous les scénarios que vous souhaitez prendre en compte. Toutefois, vous souhaiterez peut-être collecter des informations de scénario à partir d’autres sources. Par exemple, supposons que vous essayez de créer un budget d’entreprise. Vous pouvez collecter des scénarios à partir de différents services, tels que Ventes, Paie, Production, Marketing et Juridique, car chacune de ces sources a des informations différentes à utiliser pour créer le budget.

Vous pouvez regrouper ces scénarios dans une feuille de calcul à l’aide de la commande Fusionner . Chaque source peut fournir autant de valeurs de cellule modifiées que vous le souhaitez. Par exemple, vous souhaiterez peut-être que chaque service fournisse des projections de dépenses, mais n’a besoin que de projections de revenus de quelques-uns.

Lorsque vous choisissez de fusionner, le Gestionnaire de scénarios charge un Assistant Scénario de fusion, qui répertorie toutes les feuilles de calcul du classeur actif, ainsi que tous les autres classeurs que vous avez ouverts à ce moment-là. L’Assistant vous indique le nombre de scénarios que vous avez dans chaque feuille de calcul source que vous sélectionnez.

Lorsque vous collectez différents scénarios à partir de différentes sources, vous devez utiliser la même structure de cellules dans chacun des classeurs. Par exemple, les recettes peuvent toujours aller dans la cellule B2 et les dépenses peuvent toujours aller dans la cellule B3. Si vous utilisez différentes structures pour les scénarios provenant de différentes sources, il peut être difficile de fusionner les résultats.

Pour comparer plusieurs scénarios, vous pouvez créer un rapport qui les résume sur la même page. Le rapport peut répertorier les scénarios côte à côte ou les présenter dans un rapport de tableau croisé dynamique.

Un rapport de résumé de scénario basé sur les deux exemples de scénarios précédents ressemblerait à ce qui suit :

Vous remarquerez qu’Excel a automatiquement ajouté des niveaux de regroupement pour vous, ce qui étend et réduit l’affichage lorsque vous cliquez sur les différents sélecteurs.

Une note s’affiche à la fin du rapport de synthèse expliquant que la colonne Valeurs actuelles représente les valeurs des cellules modifiées au moment de la création du rapport de synthèse du scénario et que les cellules modifiées pour chaque scénario sont mises en surbrillance en gris.