Analyse de sensibilité dans Excel (exemple de fiche technique)

Les processus dans le domaine de la finance sont toujours interconnectés - un facteur dépend d'un autre et change avec lui. Suivez ces changements et comprenez à quoi vous attendre à l'avenir, peut-être en utilisant les fonctions Excel et les méthodes de feuille de calcul.

Obtenir plusieurs résultats avec un tableau de données

Les fonctionnalités de feuille de données sont des éléments de l'analyse de simulation, souvent effectuée via Microsoft Excel. C'est le deuxième nom de l'analyse de sensibilité.

Vue d’ensemble

Un tableau de données est un type de plage de cellules qui peut être utilisé pour résoudre des problèmes en modifiant les valeurs de certaines cellules. Il est créé lorsqu'il est nécessaire de suivre les modifications des composants de la formule et de recevoir des mises à jour des résultats, en fonction de ces modifications. Découvrons comment utiliser les tableaux de données dans la recherche et de quels types il s'agit.

Notions de base sur les tableaux de données

Il existe deux types de tableaux de données, ils diffèrent par le nombre de composants. Vous devez compiler un tableau en mettant l'accent sur le nombre de valeurs uXNUMXbuXNUMXbque vous devez vérifier avec lui.

Les statisticiens utilisent une seule table de variables lorsqu'il n'y a qu'une seule variable dans une ou plusieurs expressions qui peut modifier leur résultat. Par exemple, il est souvent utilisé en conjonction avec la fonction PMT. La formule est conçue pour calculer le montant du paiement régulier et tient compte du taux d'intérêt spécifié dans l'entente. Dans de tels calculs, les variables sont écrites dans une colonne et les résultats des calculs dans une autre. Un exemple de plaque signalétique avec 1 variable :

Considérons ensuite les plaques à 2 variables. Ils sont utilisés dans les cas où deux facteurs influencent le changement d'un indicateur. Les deux variables peuvent se retrouver dans un autre tableau associé au prêt, qui permet de déterminer la durée optimale de remboursement et le montant de la mensualité. Dans ce calcul, vous devez également utiliser la fonction PMT. Un exemple de tableau à 2 variables :

Créer une table de données avec une variable

Considérons la méthode d'analyse en utilisant l'exemple d'une petite librairie avec seulement 100 livres en stock. Certains d'entre eux peuvent être vendus plus cher (50 $), le reste coûtera moins cher aux acheteurs (20 $). Le revenu total de la vente de tous les biens est calculé - le propriétaire a décidé qu'il vendrait 60% des livres à un prix élevé. Vous devez savoir comment les revenus augmenteront si vous augmentez le prix d'un plus grand volume de marchandises - 70%, etc.

Faites attention! Le revenu total doit être calculé à l'aide d'une formule, sinon il ne sera pas possible de compiler un tableau de données.

1. Sélectionnez une cellule libre loin du bord de la feuille et écrivez-y la formule : =Cellule de revenu total. Par exemple, si le revenu est écrit dans la cellule C14 (une désignation aléatoire est indiquée), vous devez écrire ceci : =S14.
2. Nous écrivons le pourcentage du volume des marchandises dans la colonne à gauche de cette cellule - pas en dessous, c'est très important.
3. Nous sélectionnons la plage de cellules où se trouvent la colonne de pourcentage et le lien vers le revenu total.

4. Nous trouvons sur l'onglet "Données" l'élément "Et si l'analyse" et cliquez dessus - dans le menu qui s'ouvre, sélectionnez l'option "Tableau de données".

5. Une petite fenêtre s'ouvrira dans laquelle vous devrez spécifier une cellule avec le pourcentage de livres initialement vendus à un prix élevé dans la colonne "Valeurs de remplacement par lignes dans ...". Cette étape est effectuée afin de recalculer le revenu total en tenant compte du pourcentage croissant.

Après avoir cliqué sur le bouton "OK" dans la fenêtre où les données ont été saisies pour compiler le tableau, les résultats des calculs apparaîtront dans les lignes.

Ajout d'une formule à une table de données à variable unique

A partir d'un tableau qui a permis de calculer une action avec une seule variable, vous pouvez faire un outil d'analyse sophistiqué en ajoutant une formule supplémentaire. Il doit être entré à côté d'une formule déjà existante - par exemple, si le tableau est orienté ligne, nous entrons l'expression dans la cellule à droite de celle existante. Lorsque l'orientation de la colonne est définie, nous écrivons la nouvelle formule sous l'ancienne. Ensuite, suivez l'algorithme :

1. Sélectionnez à nouveau la plage de cellules, mais elle devrait maintenant inclure la nouvelle formule.
2. Ouvrez le menu d'analyse "et si" et sélectionnez "Fiche technique".
3. Nous ajoutons une nouvelle formule au champ correspondant en lignes ou en colonnes, selon l'orientation de la plaque.

Créer une table de données avec deux variables

Le début d'un tel tableau est légèrement différent - vous devez mettre un lien vers le revenu total au-dessus des valeurs en pourcentage. Ensuite, nous effectuons ces étapes :

1. Écrivez les options de prix sur une ligne avec un lien vers le revenu - une cellule pour chaque prix.
2. Sélectionnez une plage de cellules.

3. Ouvrez la fenêtre du tableau de données, comme lors de la compilation d'un tableau avec une variable - via l'onglet "Données" de la barre d'outils.
4. Remplacez dans la colonne "Remplacer les valeurs par des colonnes dans..." une cellule avec un prix initial élevé.
5. Ajoutez une cellule avec le pourcentage initial des ventes de livres chers à la colonne « Remplacer les valeurs par lignes dans… » et cliquez sur « OK ».

En conséquence, le tableau entier est rempli de montants de revenus possibles avec différentes conditions pour la vente de biens.

Accélérez les calculs pour les feuilles de calcul contenant des tableaux de données

Si vous avez besoin de calculs rapides dans une table de données qui ne déclenchent pas un recalcul de l'ensemble du classeur, vous pouvez effectuer certaines opérations pour accélérer le processus.

1. Ouvrez la fenêtre des options, sélectionnez l'élément "Formules" dans le menu de droite.
2. Sélectionnez l'élément « Automatique, sauf pour les tableaux de données » dans la section « Calculs dans le classeur ».

3. Recalculons manuellement les résultats dans le tableau. Pour ce faire, sélectionnez les formules et appuyez sur la touche F.

Autres outils pour effectuer une analyse de sensibilité

Il existe d'autres outils dans le programme pour vous aider à effectuer une analyse de sensibilité. Ils automatisent certaines actions qui, autrement, devraient être effectuées manuellement.

1. La fonction « Sélection de paramètres » convient si le résultat souhaité est connu et que vous devez connaître la valeur d'entrée de la variable pour obtenir un tel résultat..
2. "Rechercher une solution" est un module complémentaire pour résoudre les problèmes. Il est nécessaire de fixer des limites et de les indiquer, après quoi le système trouvera la réponse. La solution est déterminée en changeant les valeurs.
3. L'analyse de sensibilité peut être effectuée à l'aide du gestionnaire de scénarios. Cet outil se trouve dans le menu d'analyse de simulation sous l'onglet Données. Il substitue des valeurs dans plusieurs cellules - le nombre peut atteindre 32. Le répartiteur compare ces valeurs afin que l'utilisateur n'ait pas à les modifier manuellement. Un exemple d'utilisation du gestionnaire de script :

Analyse de sensibilité d'un projet d'investissement sous Excel

L'analyse de simulation est particulièrement utile dans les situations où la prévision est requise, comme l'investissement. Les analystes utilisent cette méthode pour découvrir comment la valeur des actions d'une entreprise changera à la suite de changements dans certains facteurs.

Méthode d'analyse de la sensibilité des investissements

Lors de l'analyse de « et si », utilisez l'énumération - manuelle ou automatique. La plage de valeurs est connue et elles sont remplacées une par une dans la formule. Le résultat est un ensemble de valeurs. Choisissez le nombre approprié parmi eux. Considérons quatre indicateurs pour lesquels une analyse de sensibilité est réalisée dans le domaine de la finance :

1. Valeur actualisée nette – Calculée en soustrayant le montant de l'investissement du montant du revenu.
2. Taux de rendement interne / profit - indique le montant de profit qui doit être reçu d'un investissement au cours d'une année.
3. Le taux de récupération est le rapport de tous les bénéfices à l'investissement initial.
4. Indice de profit actualisé - indique l'efficacité de l'investissement.

Laits en poudre

La sensibilité d'intégration peut être calculée à l'aide de cette formule : Modification du paramètre de sortie en % / Modification du paramètre d'entrée en %.

Les paramètres de sortie et d'entrée peuvent être les valeurs décrites précédemment.

1. Vous devez connaître le résultat dans des conditions standard.
2. Nous remplaçons l'une des variables et surveillons les changements dans le résultat.
3. Nous calculons le pourcentage de variation des deux paramètres par rapport aux conditions établies.
4. Nous insérons les pourcentages obtenus dans la formule et déterminons la sensibilité.

Un exemple d'analyse de sensibilité d'un projet d'investissement dans Excel

Pour une meilleure compréhension de la méthodologie d'analyse, un exemple est nécessaire. Analysons le projet avec les données connues suivantes :

1. Remplissez le tableau pour analyser le projet sur celui-ci.

2. Nous calculons le flux de trésorerie à l'aide de la fonction OFFSET. Au stade initial, le flux est égal aux investissements. Ensuite, nous appliquons la formule : =IF(OFFSET(Number,1;)=2;SUM(Inflow 1:Outflow 1); SOMME(Inflow 1:Outflow 1)+$B$ 5)
   Les désignations de cellule dans la formule peuvent être différentes, selon la disposition du tableau. À la fin, la valeur des données initiales est ajoutée - la valeur de récupération.

3. Nous déterminons la période pendant laquelle le projet sera rentable. Pour la période initiale, nous utilisons cette formule : =SUMÉMENT(G7: G17 ;"<0"). La plage de cellules est la colonne des flux de trésorerie. Pour les périodes ultérieures, nous appliquons cette formule : =Période initiale+IF(First e.stream>0 ; First e.stream;0). Le projet est à l'équilibre en 4 ans.

4. Nous créons une colonne pour les nombres de ces périodes où le projet porte ses fruits.

5. Nous calculons le retour sur investissement. Il est nécessaire de faire une expression où le profit sur une période de temps donnée est divisé par l'investissement initial.

6. Nous déterminons le facteur d'actualisation à l'aide de cette formule : =1/(1+Disc.%) ^Numéro.

7. Nous calculons la valeur actuelle en utilisant la multiplication - le flux de trésorerie est multiplié par le facteur d'actualisation.

8. Calculons le PI (indice de rentabilité). La valeur actualisée dans le temps est divisée par l'investissement au début du projet.

9. Définissons le taux de rendement interne à l'aide de la fonction IRR : =IRR(Plage de flux de trésorerie).

Analyse de la sensibilité des investissements à l'aide de la fiche technique

Pour l'analyse des projets dans le domaine de l'investissement, d'autres méthodes sont mieux adaptées qu'un tableau de données. De nombreux utilisateurs sont confus lors de la compilation d'une formule. Pour connaître la dépendance d'un facteur aux changements d'autres facteurs, vous devez sélectionner les bonnes cellules pour saisir les calculs et lire les données.

Analyse factorielle et dispersion dans Excel avec automatisation des calculs

Une autre typologie d'analyse de sensibilité est l'analyse factorielle et l'analyse de la variance. Le premier type définit la relation entre les nombres, le second révèle la dépendance d'une variable par rapport aux autres.

ANOVA dans Excel

Le but d'une telle analyse est de diviser la variabilité de la valeur en trois composantes :

1. Variabilité due à l'influence d'autres valeurs.
2. Changements dus à la relation des valeurs qui l'affectent.
3. Changements aléatoires.

Effectuons une analyse de la variance via le complément Excel "Analyse des données". S'il n'est pas activé, il peut être activé dans les paramètres.

Le tableau initial doit suivre deux règles : il y a une colonne pour chaque valeur et les données qu'il contient sont organisées par ordre croissant ou décroissant. Il faut vérifier l'influence du niveau d'éducation sur le comportement en situation de conflit.

1. Trouvez l'outil d'analyse de données dans l'onglet Données et ouvrez sa fenêtre. Dans la liste, vous devez sélectionner l'analyse de variance unidirectionnelle.

2. Remplissez les lignes de la boîte de dialogue. L'intervalle d'entrée correspond à toutes les cellules, à l'exception des en-têtes et des nombres. Grouper par colonnes. Nous affichons les résultats sur une nouvelle feuille.

Étant donné que la valeur dans la cellule jaune est supérieure à un, l'hypothèse peut être considérée comme incorrecte - il n'y a pas de relation entre l'éducation et le comportement en situation de conflit.

Analyse factorielle dans Excel : un exemple

Analysons la relation des données dans le domaine des ventes - il est nécessaire d'identifier les produits populaires et impopulaires. Informations initiales :

1. Nous devons savoir pour quels biens la demande a le plus augmenté au cours du deuxième mois. Nous compilons un nouveau tableau pour déterminer la croissance et la baisse de la demande. La croissance est calculée à l'aide de cette formule : =SI((Demande 2-Demande 1)>0 ; Demande 2- Demande 1 ;0). Diminuer la formule : =SI(Croissance=0; Demande 1- Demande 2;0).

2. Calculez la croissance de la demande de biens en pourcentage : =SI(Croissance/Résultat 2 =0 ; Diminution/Résultat 2 ; Croissance/Résultat 2).

3. Faisons un graphique pour plus de clarté - sélectionnez une plage de cellules et créez un histogramme via l'onglet "Insérer". Dans les paramètres, vous devez supprimer le remplissage, cela peut être fait via l'outil Format de la série de données.

Analyse bidirectionnelle de la variance dans Excel

L'analyse de la variance est effectuée avec plusieurs variables. Considérez cela avec un exemple : vous devez savoir à quelle vitesse la réaction à un son de volume différent se manifeste chez les hommes et les femmes.

1. Nous ouvrons "Analyse des données", dans la liste, vous devez trouver une analyse de variance bidirectionnelle sans répétitions.
2. Intervalle d'entrée - cellules contenant des données (sans en-tête). Nous affichons les résultats sur une nouvelle feuille et cliquons sur "OK".

La valeur F est supérieure au F-critique, ce qui signifie que le sol affecte la vitesse de réaction au son.

Conclusion

Dans cet article, l'analyse de sensibilité dans une feuille de calcul Excel a été abordée en détail, afin que chaque utilisateur puisse comprendre les méthodes de son application.