Analyse des hypothèses dans Excel

Qu'est-ce qu'une analyse What-If dans Excel?

What-If Analysis dans Excel est un outil qui nous aide à créer différents modèles, scénarios, tableaux de données. Dans cet article, nous examinerons les façons d'utiliser l'analyse hypothétique.

Nous avons 3 parties de l'analyse hypothétique dans Excel. Ils sont les suivants:

  1. Gestionnaire de scénario
  2. Recherche d'objectifs dans Excel
  3. Tableau de données dans Excel
Vous pouvez télécharger ce modèle Excel d'analyse hypothétique ici - Modèle Excel d'analyse hypothétique

Gestionnaire de scénarios n ° 1 dans l'analyse hypothétique

En tant que chef d'entreprise, il est important de connaître les différents scénarios de votre futur projet. Sur la base des scénarios, le chef d'entreprise prendra des décisions. Par exemple, vous allez entreprendre l'un des projets importants. Vous avez fait vos devoirs et énuméré toutes les dépenses possibles de votre côté et ci-dessous se trouve la liste de toutes vos dépenses.

Le flux de trésorerie attendu de ce projet est de 75 millions, qui se trouve dans la cellule C2. Les dépenses totales comprennent toutes vos dépenses fixes et variables et le coût total est de 57,45 millions dans la cellule C12. Le bénéfice total est de 17,55 millions dans la cellule C14 et le pourcentage de profit est de 23,40% de vos entrées de trésorerie.

C'est le scénario de base de votre projet. Vous devez maintenant connaître le scénario de profit si certaines de vos dépenses augmentent ou diminuent.

Scénario 1

  • Dans un scénario de cas général, vous avez estimé le coût de la licence de projet à 10 millions, mais vous prévoyez certainement qu'il sera à 15 millions.
  • Augmentation du coût des matières premières de 2,5 millions
  • Autres dépenses à diminuer de 50 mille.

Scénario 2

  • Le coût du projet doit être de 20 millions.
  • Le salaire journalier du travail doit être de 5 millions
  • Coût d'exploitation à 3,5 millions.

Vous avez maintenant répertorié tous les scénarios dans le formulaire. Sur la base de ces scénarios, vous devez créer un tableau de scénarios sur l'impact de votre profit et% de vos bénéfices. Afin de créer des scénarios d'analyse hypothétique, suivez les étapes ci-dessous.

  • Étape 1: accédez à DATA> What-If Analysis> Scenario Manager.

  • Étape 2: Une fois que vous avez cliqué sur Scenario Manager, il vous montrera sous la boîte de dialogue.

  • Étape 3: Cliquez sur AJOUTER. Donnez un nom au scénario.

  • Étape 4: Dans les cellules changeantes, sélectionnez les premiers changements de scénario que vous avez listés. Les changements sont le coût du projet (cellule C10) à 15 millions, le coût des matières premières (cellule C7) à 11 millions et les autres dépenses (cellule C11) à 4,5 millions. Mentionnez ces 3 cellules ici.

  • Étape 5: Cliquez sur OK. Il vous demandera de mentionner les nouvelles valeurs, de mentionner les nouvelles valeurs comme indiqué dans le scénario 1.

  • Étape 6: Ne cliquez pas sur OK mais cliquez sur OK Ajouter. Cela enregistrera ce scénario pour vous.
  • Étape 7: Maintenant, il vous demandera de créer un autre scénario. Comme nous l'avons indiqué dans le scénario 2, apportez les modifications. Cette fois, nous devons modifier le coût du projet (C10), le coût de la main-d'œuvre (C8) et le coût de fonctionnement (C9)

  • Étape 8: Ajoutez maintenant de nouvelles valeurs ici.

  • Étape 9: Cliquez maintenant sur Ok. Il montrera tous les scénarios que nous avons créés.

  • Étape 10: Cliquez sur RÉSUMÉ, il vous demandera quelles cellules de résultat vous souhaitez modifier. Ici, nous devons changer la cellule de dépense totale (C12), la cellule de profit total (C14) et la cellule de% de profit (C16).

  • Étape 11: Cliquez sur OK. Il créera un rapport de synthèse pour vous dans la nouvelle feuille de calcul.

Total Excel a créé 3 scénarios même si nous n'avons fourni que 2 changements de scénario. Parce qu'Excel affichera les rapports existants comme un seul scénario.

À partir de ce tableau, nous pouvons facilement voir l'impact des changements sur le pourcentage de profit.

# 2 Recherche d'objectifs dans l'analyse hypothétique

Nous connaissons maintenant l'avantage du gestionnaire de scénario. La recherche d'objectifs d'analyse de simulation peut vous dire ce que vous devez faire pour atteindre l'objectif défini.

Andrew est un élève de 10e classe. Son objectif est d'obtenir la note moyenne de 85 à l'examen final et il a déjà terminé 5 examens et en est reparti avec un seul examen. Dans les 5 examens terminés, les notes attendues sont les suivantes.

Afin de calculer la moyenne actuelle, appliquez la formule Moyenne dans la cellule B7.

La moyenne actuelle est de 82,2.

Le BUT d'Andrew est de 85 et sa moyenne actuelle est de 82,2 et court de 3,8 avec un examen à faire.

Maintenant, la question est de savoir combien il doit marquer à l'examen final pour obtenir finalement une moyenne globale de 85. Cela peut trouver par analyse de simulation Outil de recherche d'objectif.

  • Étape 1: Accédez à DONNÉES> Analyse des hypothèses> Recherche d'objectifs

  • Étape 2: Il vous montrera sous la boîte de dialogue.

  • Étape 3: Ici, nous devons d'abord définir la cellule. Set Cell n'est rien d'autre que la cellule dont nous avons besoin du résultat final, c'est-à-dire notre cellule moyenne globale (B7). Vient ensuite To Value, ce n'est que pour quelle valeur nous devons définir la cellule, c'est-à-dire le BUT moyen global d'Andrew (85).

La partie suivante et finale consiste à changer la cellule dont vous souhaitez voir l'impact. Nous devons donc changer la cellule B6 qui est la cellule du score final du sujet.

  • Étape 4: Cliquez sur OK. Excel prendra quelques secondes pour terminer le processus, mais finalement, il affiche le résultat final comme celui ci-dessous.

Maintenant, nous avons nos résultats ici. Afin d'obtenir une moyenne globale de 85, Andrew doit obtenir 99 points à l'examen final.

# 3 Tableau de données dans l'analyse hypothétique

Nous avons déjà vu deux merveilleuses techniques sous What-If Analysis dans Excel. La table de données peut créer différentes tables de scénario en fonction du changement de variable. Nous avons deux types de tableau de données ici «Un tableau de données à variable» et «Tableau de données à deux variables». Dans cet article, je vais vous montrer une table de données variables Excel.

Supposons que vous vendez un produit 1000 à 15 roupies, votre dépense totale prévue est de 12500 et le bénéfice est de 2500.

Vous n'êtes pas satisfait du profit que vous obtenez. Votre bénéfice anticipé est de 7500. Vous êtes décidé d'augmenter votre prix unitaire pour augmenter votre profit, mais vous ne savez pas combien vous devez augmenter.

Les tableaux de données peuvent vous aider. Créez un tableau ci-dessous

Maintenant, dans la cellule F1, donnez un lien vers la cellule de profit total, c'est-à-dire B6

  • Étape 1: Sélectionnez la table nouvellement créée.

  • Étape 2: Accédez à DONNÉES> Analyse de simulation> Tableau de données.

  • Étape 3: Vous verrez maintenant la boîte de dialogue ci-dessous.

  • Étape 4: Puisque nous affichons le résultat verticalement, laissez la cellule d'entrée Row. Dans la cellule d'entrée de la colonne, sélectionnez la cellule B2 qui correspond au prix de vente d'origine.

  • Étape 5: Cliquez sur OK pour obtenir les résultats. Il listera les nombres de bénéfices dans le nouveau tableau.

Nous avons donc notre tableau de données prêt. Si vous voulez faire un profit à partir de 7500, vous devez vendre à 20 l'unité.

Choses à retenir 

  • Le tableau de données d'analyse hypothétique peut être effectué avec deux changements de variables. Reportez-vous à notre article sur l'analyse hypothétique Tableau de données à deux variables.
  • Analyse de simulation La recherche d'objectifs prend quelques secondes pour effectuer les calculs.
  • Le gestionnaire de scénarios d'analyse des hypothèses peut donner un résumé avec les nombres d'entrée et les valeurs actuelles ensemble.