Anàlisi de sensibilitat a Excel (full de dades de mostra)

Els processos en l'àmbit financer sempre estan interconnectats: un factor depèn d'un altre i canvia amb ell. Fes un seguiment d'aquests canvis i entén què esperar en el futur, potser utilitzant funcions d'Excel i mètodes de full de càlcul.

Obtenció de múltiples resultats amb una taula de dades

Les capacitats del full de dades són elements de l'anàlisi de si, que sovint es fa mitjançant Microsoft Excel. Aquest és el segon nom de l'anàlisi de sensibilitat.

Descripció

Una taula de dades és un tipus de rang de cel·les que es pot utilitzar per resoldre problemes canviant els valors d'algunes cel·les. Es crea quan cal fer un seguiment dels canvis en els components de la fórmula i rebre actualitzacions dels resultats, segons aquests canvis. Descobrim com utilitzar les taules de dades en la investigació i quins tipus són.

Conceptes bàsics sobre taules de dades

Hi ha dos tipus de taules de dades, es diferencien pel nombre de components. Heu de compilar una taula centrada en el nombre de valors uXNUMXbuXNUMXbque heu de comprovar amb ella.

Els estadístics utilitzen una única taula de variables quan només hi ha una variable en una o més expressions que poden canviar el seu resultat. Per exemple, s'utilitza sovint juntament amb la funció PMT. La fórmula està dissenyada per calcular l'import del pagament regular i té en compte el tipus d'interès especificat en el conveni. En aquests càlculs, les variables s'escriuen en una columna i els resultats dels càlculs en una altra. Un exemple de placa de dades amb 1 variable:

A continuació, considereu les plaques amb 2 variables. S'utilitzen en els casos en què dos factors influeixen en el canvi de qualsevol indicador. Les dues variables poden acabar en una altra taula associada al préstec, que serveix per determinar el període òptim d'amortització i l'import de la mensualitat. En aquest càlcul, també cal utilitzar la funció PMT. Un exemple de taula amb 2 variables:

Creació d'una taula de dades amb una variable

Considereu el mètode d'anàlisi utilitzant l'exemple d'una llibreria petita amb només 100 llibres en estoc. Alguns d'ells es poden vendre més cars (50 $), la resta costarà als compradors menys (20 $). Es calculen els ingressos totals de la venda de tots els béns: el propietari va decidir que vendria el 60% dels llibres a un preu elevat. Heu d'esbrinar com augmentaran els ingressos si augmenteu el preu d'un volum més gran de béns: un 70%, etc.

Presta atenció! Els ingressos totals s'han de calcular mitjançant una fórmula, en cas contrari no serà possible elaborar una taula de dades.

1. Seleccioneu una cel·la lliure lluny de la vora del full i escriviu-hi la fórmula: = Cel·la dels ingressos totals. Per exemple, si els ingressos s'escriuen a la cel·la C14 (s'indica una designació aleatòria), heu d'escriure això: =S14.
2. Escrivim el percentatge del volum de mercaderies a la columna de l'esquerra d'aquesta cel·la; no a sota, això és molt important.
3. Seleccionem el rang de cel·les on es troba la columna del percentatge i l'enllaç als ingressos totals.

4. Trobem a la pestanya "Dades" l'element "Què passaria si anàlisi" i fem clic sobre ell; al menú que s'obre, seleccioneu l'opció "Taula de dades".

5. S'obrirà una petita finestra on haureu d'especificar una cel·la amb el percentatge de llibres venuts inicialment a un preu elevat a la columna "Substituir valors per files a...". Aquest pas es fa per tal de recalcular els ingressos totals tenint en compte el percentatge creixent.

Després de fer clic al botó "D'acord" a la finestra on es van introduir les dades per compilar la taula, els resultats dels càlculs apareixeran a les línies.

Afegir una fórmula a una taula de dades de variable única

A partir d'una taula que va ajudar a calcular una acció amb només una variable, podeu crear una eina d'anàlisi sofisticada afegint una fórmula addicional. S'ha d'introduir al costat d'una fórmula ja existent; per exemple, si la taula està orientada a files, introduïm l'expressió a la cel·la que hi ha a la dreta de l'existent. Quan s'estableix l'orientació de la columna, escrivim la fórmula nova sota l'antiga. A continuació, seguiu l'algorisme:

1. Torneu a seleccionar l'interval de cel·les, però ara hauria d'incloure la fórmula nova.
2. Obriu el menú d'anàlisi "I si" i seleccioneu "Full de dades".
3. Afegim una nova fórmula al camp corresponent en files o columnes, en funció de l'orientació de la placa.

Creeu una taula de dades amb dues variables

L'inici d'aquesta taula és lleugerament diferent: heu de posar un enllaç als ingressos totals per sobre dels valors percentuals. A continuació, realitzem aquests passos:

1. Escriviu les opcions de preu en una línia amb un enllaç als ingressos: una cel·la per cada preu.
2. Seleccioneu un rang de cel·les.

3. Obriu la finestra de la taula de dades, com quan es compila una taula amb una variable, a través de la pestanya "Dades" de la barra d'eines.
4. Substituïu a la columna "Substitueix valors per columnes a..." una cel·la amb un preu inicial alt.
5. Afegiu una cel·la amb el percentatge inicial de vendes de llibres cars a la columna "Substitueix valors per files a..." i feu clic a "D'acord".

Com a resultat, tota la taula s'omple amb quantitats d'ingressos possibles amb diferents condicions per a la venda de béns.

Accelera els càlculs per a fulls de treball que contenen taules de dades

Si necessiteu càlculs ràpids en una taula de dades que no desencadenin un recàlcul de tot el llibre de treball, hi ha algunes coses que podeu fer per accelerar el procés.

1. Obriu la finestra d'opcions, seleccioneu l'element "Fórmules" al menú de la dreta.
2. Seleccioneu l'element "Automàtic, excepte les taules de dades" a la secció "Càlculs al llibre de treball".

3. Recalculem els resultats de la taula manualment. Per fer-ho, seleccioneu les fórmules i premeu la tecla F.

Altres eines per realitzar anàlisis de sensibilitat

Hi ha altres eines al programa per ajudar-vos a realitzar anàlisis de sensibilitat. Automatitzen algunes accions que, d'altra manera, s'haurien de fer manualment.

1. La funció "Selecció de paràmetres" és adequada si es coneix el resultat desitjat i cal conèixer el valor d'entrada de la variable per obtenir aquest resultat..
2. "Cerca una solució" és un complement per resoldre problemes. Cal establir límits i assenyalar-los, després del qual el sistema trobarà la resposta. La solució es determina canviant els valors.
3. L'anàlisi de sensibilitat es pot fer mitjançant el Gestor d'escenaris. Aquesta eina es troba al menú d'anàlisi de què passa a la pestanya Dades. Substitueix valors en diverses cel·les: el nombre pot arribar a 32. El despatxador compara aquests valors perquè l'usuari no hagi de canviar-los manualment. Un exemple d'ús del gestor d'scripts:

Anàlisi de sensibilitat d'un projecte d'inversió en Excel

L'anàlisi de si és especialment útil en situacions en què es requereix previsió, com ara la inversió. Els analistes utilitzen aquest mètode per esbrinar com canviarà el valor de les accions d'una empresa com a conseqüència dels canvis en alguns factors.

Mètode d'anàlisi de la sensibilitat de la inversió

Quan analitzeu "Què passa si" feu servir l'enumeració: manual o automàtica. Es coneix el rang de valors i es substitueixen a la fórmula un per un. El resultat és un conjunt de valors. Trieu entre ells el nombre adequat. Considerem quatre indicadors per als quals es realitza l'anàlisi de sensibilitat en l'àmbit financer:

1. Valor actual net: es calcula restant l'import de la inversió de l'import dels ingressos.
2. Taxa interna de rendibilitat / benefici: indica quants beneficis cal rebre d'una inversió en un any.
3. La relació d'amortització és la relació entre tots els beneficis i la inversió inicial.
4. Índex de benefici descomptat: indica l'eficàcia de la inversió.

Fórmula

La sensibilitat d'incrustació es pot calcular mitjançant aquesta fórmula: Canvi del paràmetre de sortida en % / Canvi del paràmetre d'entrada en %.

Els paràmetres de sortida i entrada poden ser els valors descrits anteriorment.

1. Cal conèixer el resultat en condicions estàndard.
2. Substituïm una de les variables i controlem els canvis en el resultat.
3. Calculem la variació percentual d'ambdós paràmetres respecte a les condicions establertes.
4. Inseriu els percentatges obtinguts a la fórmula i determinem la sensibilitat.

Un exemple d'anàlisi de sensibilitat d'un projecte d'inversió en Excel

Per a una millor comprensió de la metodologia d'anàlisi, cal un exemple. Analitzem el projecte amb les següents dades conegudes:

1. Omple la taula per analitzar-hi el projecte.

2. Calculem el flux de caixa mitjançant la funció OFFSET. En l'etapa inicial, el cabal és igual a les inversions. A continuació, apliquem la fórmula: =SI(OFFSET(Nombre,1;)=2;SUMA(Entrada 1: Sortida 1); SUMA(Entrada 1: Sortida 1)+$B$ 5)
   Les designacions de cel·les a la fórmula poden ser diferents, depenent de la disposició de la taula. Al final, s'afegeix el valor de les dades inicials: el valor de recuperació.

3. Nosaltres determinem el període durant el qual el projecte tindrà beneficis. Per al període inicial, utilitzem aquesta fórmula: =SUMMESLY(G7:G17;»<0″). L'interval de cel·les és la columna de flux d'efectiu. Per a períodes posteriors, apliquem aquesta fórmula: =Període inicial+SI(Primer e.stream>0; Primer e.stream;0). El projecte està en el punt d'equilibri en 4 anys.

4. Creem una columna per als números d'aquells períodes en què el projecte val la pena.

5. Calculem el retorn de la inversió. Cal fer una expressió on el benefici en un període de temps determinat es divideix per la inversió inicial.

6. Determinem el factor de descompte mitjançant aquesta fórmula: =1/(1+disc.%) ^Número.

7. Calculem el valor actual mitjançant la multiplicació: el flux d'efectiu es multiplica pel factor de descompte.

8. Calculem PI (índex de rendibilitat). El valor actual al llarg del temps es divideix per la inversió a l'inici del projecte.

9. Definim la taxa interna de rendibilitat mitjançant la funció IRR: = IRR (Interval de flux d'efectiu).

Anàlisi de la sensibilitat de la inversió mitjançant el full de dades

Per a l'anàlisi de projectes en l'àmbit de la inversió, altres mètodes són més adequats que una taula de dades. Molts usuaris experimenten confusió a l'hora de compilar una fórmula. Per esbrinar la dependència d'un factor dels canvis en altres, cal seleccionar les cel·les correctes per introduir càlculs i per llegir dades.

Anàlisi factorial i de dispersió en Excel amb automatització de càlculs

Una altra tipologia d'anàlisi de sensibilitat és l'anàlisi factorial i l'anàlisi de la variància. El primer tipus defineix la relació entre nombres, el segon revela la dependència d'una variable respecte d'altres.

ANOVA en Excel

L'objectiu d'aquesta anàlisi és dividir la variabilitat del valor en tres components:

1. Variabilitat com a conseqüència de la influència d'altres valors.
2. Canvis per la relació dels valors que l'afecten.
3. Canvis aleatoris.

Anem a realitzar l'anàlisi de la variància mitjançant el complement d'Excel "Anàlisi de dades". Si no està habilitat, es pot activar a la configuració.

La taula inicial ha de seguir dues regles: hi ha una columna per a cada valor i les dades que hi consten estan ordenades en ordre ascendent o descendent. Cal comprovar la influència del nivell d'educació en la conducta en conflicte.

1. Cerqueu l'eina d'anàlisi de dades a la pestanya Dades i obriu-ne la finestra. A la llista, heu de seleccionar l'anàlisi unidireccional de la variància.

2. Ompliu les línies del quadre de diàleg. L'interval d'entrada és de totes les cel·les, excepte les capçaleres i els números. Agrupa per columnes. Mostrem els resultats en un nou full.

Com que el valor de la cel·la groga és més gran que un, la suposició es pot considerar incorrecta: no hi ha cap relació entre l'educació i el comportament en conflicte.

Anàlisi factorial en Excel: un exemple

Analitzem la relació de les dades en l'àmbit de les vendes: cal identificar productes populars i impopulars. Informació inicial:

1. Hem d'esbrinar quins béns ha augmentat més la demanda durant el segon mes. Estem elaborant una nova taula per determinar el creixement i el descens de la demanda. El creixement es calcula amb aquesta fórmula: =SI((Demanda 2-Demanda 1)>0; Demanda 2- Demanda 1;0). Fórmula de disminució: =SI(Creixement=0; Demanda 1- Demanda 2;0).

2. Calcula el creixement de la demanda de béns com a percentatge: =SI(Creixement/Resultat 2 = 0; Disminució/Resultat 2; Creixement/Resultat 2).

3. Fem un gràfic per a més claredat: seleccioneu un rang de cel·les i creeu un histograma a través de la pestanya "Insereix". A la configuració, heu d'eliminar el farciment, això es pot fer mitjançant l'eina Format de la sèrie de dades.

Anàlisi bidireccional de la variància en Excel

L'anàlisi de la variància es realitza amb diverses variables. Considereu-ho amb un exemple: heu d'esbrinar amb quina rapidesa es manifesta la reacció a un so de diferent volum en homes i dones.

1. Obrim "Anàlisi de dades", a la llista cal trobar una anàlisi bidireccional de la variància sense repeticions.
2. Interval d'entrada: cel·les que contenen dades (sense capçalera). Mostrem els resultats en un nou full i feu clic a "D'acord".

El valor F és més gran que el F-crític, el que significa que el sòl afecta la velocitat de reacció al so.

Conclusió

En aquest article s'ha parlat detalladament de l'anàlisi de sensibilitat en un full de càlcul Excel, de manera que cada usuari pugui entendre els mètodes de la seva aplicació.