Analyse de sensibilité Excel (échantillon de la table de données)

Les processus financiers sont toujours interdépendants - un facteur dépend de l'autre et change avec elle. Suivi de ces changements et comprendre ce à quoi s'attendre à l'avenir est possible à l'aide de fonctions Excel et de méthodes tabulaires.

Obtention de multiples résultats à l'aide d'une table de données

Les capacités des tables de données sont des éléments de la "réponse si" est souvent effectuée via Microsoft Excel. Ceci est le deuxième nom de l'analyse de sensibilité.

Général

La table de données est le type de plage de cellules, avec laquelle vous pouvez résoudre les problèmes résultant en modifiant les valeurs dans certaines cellules. Il est fait lorsqu'il est nécessaire de surveiller les modifications des composants de la formule et de recevoir des mises à jour des résultats, en fonction de ces modifications. Découvrez comment appliquer des comprimés de données dans des études et quelles espèces elles sont.

Informations de base sur les tables de données

Il existe deux types de tables de données, ils diffèrent dans le nombre de composants. Faire une table est nécessaire avec une orientation par le nombre de valeurs qui doivent être vérifiées.

Les spécialistes de statistiques appliquent une table avec une variable lorsqu'il n'y a qu'une variable dans une ou plusieurs expressions, ce qui peut affecter le changement de leur résultat. Par exemple, il est souvent utilisé dans un paquet avec la fonction PL. La formule est conçue pour calculer le montant de la rémunération régulière et prend en compte le taux d'intérêt défini dans le contrat. Avec de tels calculs, les variables sont enregistrées dans une colonne et les résultats des calculs à un autre. Un exemple de plaque de données avec 1 variable:

une

Ensuite, envisagez des panneaux avec 2 variables. Ils s'appliquent dans des cas où deux facteurs affectent le changement de tout indicateur. Deux variables peuvent être dans une autre table associée à un prêt - avec son aide, vous pouvez identifier la période de paiement optimale et le montant du paiement mensuel. Ce calcul doit également utiliser la fonction PPT. Un exemple de plaque avec 2 variables:

2 Création d'une table de données avec une variable

Considérez la méthode d'analyse sur l'exemple d'une petite librairie, où seuls 100 livres sont disponibles. Certains d'entre eux peuvent être vendus plus chers (50 $), le reste coûtera moins cher aux acheteurs moins cher (20 $). Le revenu total de la vente de tous les biens est conçu - le propriétaire a décidé qu'à un prix élevé de 60% des livres. Il est nécessaire de déterminer comment les revenus augmenteront si vous augmentez le prix d'un volume de marchandises plus important - 70% et ainsi de suite.

1. Choisissez une distance de cellule libre à partir du bord de la feuille et écrivez la formule dedans: = la cellule du chiffre d'affaires total. Par exemple, si le revenu est enregistré dans la cellule C14 (une désignation aléatoire est indiquée), il est nécessaire d'écrire comme ceci: = C14.
2. Nous écrivons la quantité de marchandises dans la colonne à gauche de cette cellule - non sous elle, c'est très important.
3. Nous allouons la gamme de cellules où la colonne d'intérêt est située et un lien vers le revenu total.

3.

1. Nous trouvons sur l'onglet "Data" de "Analyse" Et si "" et cliquez dessus - Dans le menu qui s'ouvre, vous devez sélectionner l'option "Tableau de données".

quatre

1. Une petite fenêtre s'ouvrira, où vous devez spécifier une cellule avec un pourcentage des livres vendus à l'origine à un prix élevé dans la colonne "Pour substituer des valeurs sur les lignes de ...". Cette étape est effectuée pour effectuer la recalcul du chiffre d'affaires général, en tenant compte du pourcentage croissant.

cinq

Après avoir appuyé 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 avec une variable

Dans la table qui a aidé à calculer l'action avec une seule variable, vous pouvez créer un outil d'analyse complexe en ajoutant une formule supplémentaire. Il doit être entré près de la formule déjà existante - par exemple, si la table est concentrée sur des lignes, entrez l'expression dans la cellule à droite du déjà existant. Lorsque l'orientation est installée sur des colonnes, écrivez une nouvelle formule sous l'ancien. Vous devriez ensuite agir selon l'algorithme:

1. Nous soulignons à nouveau la gamme de cellules, mais il devrait maintenant inclure une nouvelle formule.
2. Ouvrez le menu "Et si" analyse et sélectionnez "Tableau de données".
3. Ajoutez une nouvelle formule au champ correspondant en ligne ou par colonnes, en fonction de l'orientation de la plaque.

Création d'une table de données avec deux variables

Le début de la préparation d'une telle table est légèrement différent - vous devez placer un lien vers les revenus globaux au-dessus des valeurs de pourcentage. Ensuite, nous effectuons ces étapes:

1. Options d'enregistrement pour le prix d'une ligne en référence au revenu - chaque prix est une cellule.
2. Sélectionnez la gamme de cellules.

6

1. Ouvrez la fenêtre Tableau de données, comme lors de l'établissement d'une seule variable - via l'onglet Données de la barre d'outils.
2. Substituez-le dans le comte "de substituer des valeurs sur des colonnes dans ..." Cellule avec un prix élevé initial.
3. Ajouter à la colonne "Pour substituer des valeurs sur les chaînes dans ..." Cell avec un intérêt initial dans les ventes de livres coûteux et cliquez sur "OK".

En conséquence, toute la plaque est remplie de quantités de revenus éventuels avec des termes de vente différents de marchandises.

7 Accélération des calculs des feuilles contenant des tables de données

Si des calculs rapides sont requis dans la plaque signalétique qui n'exécute pas l'ensemble de la recalcul du livre, vous pouvez effectuer plusieurs actions pour accélérer le processus.

1. Ouvrez la fenêtre Paramètres, sélectionnez la clause "Formula" dans le menu de droite.
2. Sélectionnez l'élément "automatiquement, à l'exception des tables de données" dans la section "Calculs dans la section".

huit

1. Effectuez la recalculition des résultats dans la plaque manuellement. Pour cela, vous devez mettre en évidence les formules et appuyer sur la touche F.

Autres outils pour effectuer une analyse de sensibilité

Le programme dispose d'autres outils pour aider à effectuer une analyse de sensibilité. Ils automatisent certaines actions qui seraient autrement à faire manuellement.

1. La fonction «Sélection de la paramètre» convient si le résultat souhaité est connu et il est nécessaire de déterminer la valeur d'entrée de la variable pour obtenir un tel résultat.
2. "La recherche de solution" est un complément pour résoudre des problèmes. Il est nécessaire d'établir des limitations et d'indiquer, après quoi le système trouvera la réponse. La solution est déterminée en modifiant les valeurs.
3. L'analyse de sensibilité peut être effectuée à l'aide du gestionnaire de script. Cet outil est dans le menu d'analyse "Et si" sur l'onglet Données. Il substitue les valeurs dans plusieurs cellules - la quantité peut atteindre 32. Le répartiteur compare ces valeurs et l'utilisateur n'a pas à les changer manuellement. Un exemple d'application de gestionnaire de script:

neuf

Analyse de la sensibilité du projet d'investissement dans Excel

Méthode d'analyse de la sensibilité dans le domaine des investissements

Lors de l'analyse "Et si" utilise le buste - manuel ou automatique. Plage de valeurs connues et elles sont à son tour substituées dans la formule. En conséquence, un ensemble de valeurs est obtenu. De ceux-ci, choisissez une figure appropriée. Considérons quatre indicateurs pour lesquels l'analyse de la sensibilité dans le domaine des finances:

1. La valeur actuelle pure - est calculée en soustrayant la taille de l'investissement du montant du revenu.
2. Le taux interne de rentabilité / profits - indique quel bénéfice est requis de l'investissement pour l'année.
3. Le ratio de récupération est le ratio de tous les bénéfices à l'investissement initial.
4. Indice de bénéfice à prix réduit - indique l'efficacité de l'investissement.

Formule

La sensibilité de la pièce jointe peut être calculée à l'aide de cette formule: modifier le paramètre de sortie en% / modification du paramètre d'entrée en%.

Le paramètre de sortie et d'entrée peut être les valeurs décrites précédemment.

1. Il est nécessaire de connaître le résultat dans des conditions standard.
2. Nous remplaçons l'une des variables et suivons les résultats du résultat.
3. Calculez le pourcentage de modification des deux paramètres par rapport aux conditions établies.
4. Nous insérons les pourcentages obtenus dans la formule et nous déterminons la sensibilité.

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

Pour une meilleure compréhension des techniques d'analyse, un exemple est requis. Analysez-nous le projet avec de telles données bien connues:

dix

1. Remplissez la table pour analyser le projet dessus.

Onze

1. Calculez les flux de trésorerie à l'aide de la fonction de déplacement. Au stade initial, le flux est égal aux investissements. Ensuite, nous utilisons la formule: = si (déplacement (nombre (numéro; 1;) = 2; SUMES (entrée 1: sortie 1); somme (entrée 1: sortie 1) + $ 5) Les désignations de cellules dans la formule peuvent Soyez différent, cela dépend de la table de placement. À la fin, la valeur des données initiales est ajoutée - la valeur de liquidation.

12

1. Nous définissons la date limite pour laquelle le projet va payer. Pour la période initiale, nous utilisons cette formule: = silencieux (G7: G17; "0; premier d.potok; 0). Le projet est au point de pause pendant 4 ans.

13

1. Créez une colonne pour des numéros de ces périodes lorsque le projet est remboursé.

Quatorze

1. Calculer la rentabilité des investissements. Il est nécessaire de créer une expression, où le bénéfice dans une période donnée est divisé en investissements initiaux.

quinze

1. Déterminez le coefficient d'actualisation pour cette formule: = 1 / (1 + disque.%) ^ Numéro.

seize

1. Calculez la valeur actuelle par multiplication - le flux de trésorerie est multiplié par le taux d'actualisation.

17

1. Calculez PI (index de rentabilité). La valeur donnée dans le segment du temps est divisée en pièces jointes au début du développement du projet.

dix-huit

1. Nous définissons le taux interne des profits à l'aide de la fonction de l'EMD: = FMR (plage de trésorerie).

Analyse de la sensibilité des investissements en utilisant la table de données

Pour l'analyse de projets dans le domaine des investissements, d'autres méthodes sont mieux adaptées à la table de données. De nombreux utilisateurs ont une confusion lors de l'élaboration de la formule. Pour connaître la dépendance d'un facteur des changements d'autrui, vous devez sélectionner les cellules de calcul correctes et lire les données.

Analyse des facteurs et des dispersions dans Excel avec l'automatisation des calculs

Analyse de la dispersion dans Excel

L'objectif d'une telle analyse est de diviser la variabilité de la magnitude de trois composantes:

1. La variabilité résultant de l'influence d'autres valeurs.
2. Changements en raison de la relation des valeurs qui l'affectent.
3. Changements aléatoires.

Effectuez une analyse de dispersion via une «analyse des données» de l'Excel. S'il n'est pas activé, il peut être connecté aux paramètres.

La table de départ doit être conforme aux deux règles: chaque valeur comple pour une colonne et les données de celui-ci sont disposées en ascendant ou en descendant. Il est nécessaire de tester l'impact du niveau d'éducation sur le comportement en conflit.

dix-neuf

1. Nous trouvons l'onglet "Data" "Data" et ouvre sa fenêtre. La liste doit sélectionner une analyse de dispersion à un facteur.

vingt

1. Remplissez les lignes de la boîte de dialogue. L'intervalle d'entrée est toutes des cellules sans prendre en compte les capuchons et les chiffres. Nous nous regroupons sur des colonnes. Dire des résultats sur une nouvelle feuille.

21

Étant donné que la valeur dans la cellule jaune est supérieure à celle de l'unité, nous pouvons assumer l'hypothèse d'incorrecte - il n'y a pas de relation entre l'éducation et le comportement dans le conflit.

Analyse factorielle dans Excel: exemple

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

22.

1. Il est nécessaire de déterminer quelles biens la demande pour le deuxième mois a augmenté au cours du deuxième mois. Nous faisons un nouveau tableau pour déterminer la croissance et la réduction de la demande. La croissance est calculée selon cette formule: = si ((((demande 2-demande 1)> 0; demande 2 - demande 1; 0). La formule du déclin: = si (croissance = 0; la demande est de 1- demande 2; 0).

23.

1. Calculez la croissance de la demande de biens en pourcentage: = Si (Croissance / Total 2 = 0; réduction / totale 2; hauteur / totale 2).

24

1. Nous allons faire un graphique de clarté - allouer la gamme de cellules et créer un histogramme dans l'onglet "INSERT". Dans les paramètres, vous devez supprimer le remplissage, il peut être effectué via l'outil "Format de données".

25 Analyse de dispersion à deux facteurs dans Excel

L'analyse de la dispersion est effectuée avec plusieurs variables. Considérez ceci sur l'exemple: vous devez savoir la rapidité avec laquelle la réaction au son de différents volumes chez les hommes et les femmes est manifestée.

26.

1. Ouvrez une "analyse des données", vous devez trouver une analyse de dispersion à deux facteurs sans répétition.
2. Intervalle d'entrée - cellules où les données sont contenues (sans chapeau). Nous apportons des résultats à la nouvelle feuille et cliquez sur "OK".

27

L'indicateur F est supérieur au F-critique - cela signifie que le plancher affecte la vitesse de réaction au son.

28

Conclusion

Cet article décrit en détail l'analyse de sensibilité dans le processeur de table Excel, de sorte que chaque utilisateur puisse comprendre les méthodes de son utilisation.

Message L'analyse de sensibilité dans Excel (exemple de tableau de données) est apparue en premier à la technologie de l'information.