Análise de sensibilidade en Excel (folla de datos de mostra)

Os procesos no ámbito das finanzas están sempre interconectados: un factor depende doutro e cambia con el. Rastrexa estes cambios e comprende o que esperar no futuro, quizais usando funcións de Excel e métodos de folla de cálculo.

Obter varios resultados cunha táboa de datos

As capacidades das follas de datos son elementos da análise que se fai, moitas veces a través de Microsoft Excel. Este é o segundo nome da análise de sensibilidade.

visión global

Unha táboa de datos é un tipo de intervalo de celas que se pode usar para resolver problemas cambiando os valores nalgunhas celas. Créase cando é necesario realizar un seguimento dos cambios nos compoñentes da fórmula e recibir actualizacións dos resultados, segundo estes cambios. Descubramos como utilizar as táboas de datos na investigación e que tipos son.

Conceptos básicos sobre táboas de datos

Hai dous tipos de táboas de datos, difieren no número de compoñentes. Debes compilar unha táboa centrada no número de valores uXNUMXbuXNUMXbque debes comprobar con ela.

Os estatísticos usan unha única táboa de variables cando só hai unha variable nunha ou máis expresións que poden cambiar o seu resultado. Por exemplo, úsase a miúdo xunto coa función PMT. A fórmula está deseñada para calcular o importe do pagamento ordinario e ten en conta o tipo de xuro especificado no convenio. Nestes cálculos, as variables escríbense nunha columna e os resultados dos cálculos noutra. Un exemplo de placa de datos con 1 variable:

A continuación, considere as placas con 2 variables. Utilízanse nos casos en que dous factores inflúen no cambio de calquera indicador. As dúas variables poden acabar noutra táboa asociada ao préstamo, que permite determinar o período óptimo de amortización e o importe da mensualidade. Neste cálculo, tamén cómpre utilizar a función PMT. Un exemplo de táboa con 2 variables:

Creación dunha táboa de datos cunha variable

Considere o método de análise usando o exemplo dunha pequena librería con só 100 libros en stock. Algúns deles pódense vender máis caros (50 dólares), o resto custará aos compradores menos (20 dólares). Calcúlase o ingreso total da venda de todos os bens: o propietario decidiu que vendería o 60% dos libros a un prezo elevado. Debes descubrir como aumentarán os ingresos se aumentas o prezo dun maior volume de bens - 70%, etc.

Presta atención! Os ingresos totais deben calcularse mediante unha fórmula, se non, non será posible elaborar unha táboa de datos.

1. Seleccione unha cela libre lonxe do bordo da folla e escriba a fórmula nela: =Célula de ingresos totais. Por exemplo, se os ingresos están escritos na cela C14 (indícase a designación aleatoria), cómpre escribir isto: = S14.
2. Escribimos a porcentaxe do volume de mercadorías na columna á esquerda desta cela; non debaixo dela, isto é moi importante.
3. Seleccionamos o rango de celas onde se atopa a columna porcentual e a ligazón aos ingresos totais.

4. Atopamos na pestana "Datos" o elemento "Que pasaría se análise" e prememos nel - no menú que se abre, seleccione a opción "Táboa de datos".

5. Abrirase unha pequena ventá na que debes especificar unha cela coa porcentaxe de libros vendidos inicialmente a un prezo elevado na columna "Substituír valores por filas en...". Este paso realízase para recalcular os ingresos totais tendo en conta a porcentaxe crecente.

Despois de facer clic no botón "Aceptar" na xanela onde se introduciron os datos para compilar a táboa, os resultados dos cálculos aparecerán nas liñas.

Engadir unha fórmula a unha táboa de datos de variable única

A partir dunha táboa que axudou a calcular unha acción cunha única variable, pode crear unha ferramenta de análise sofisticada engadindo unha fórmula adicional. Debe introducirse xunto a unha fórmula xa existente; por exemplo, se a táboa está orientada a filas, introducimos a expresión na cela á dereita da existente. Cando se establece a orientación da columna, escribimos a nova fórmula baixo a antiga. A continuación, siga o algoritmo:

1. Seleccione de novo o intervalo de celas, pero agora debería incluír a nova fórmula.
2. Abre o menú de análise "e se" e selecciona "Folla de datos".
3. Engadimos unha nova fórmula ao campo correspondente en filas ou columnas, dependendo da orientación da placa.

Crea unha táboa de datos con dúas variables

O inicio desta táboa é lixeiramente diferente: cómpre poñer unha ligazón aos ingresos totais por riba dos valores porcentuais. A continuación, realizamos estes pasos:

1. Escribe as opcións de prezos nunha liña cunha ligazón á renda: unha cela por cada prezo.
2. Seleccione un intervalo de celas.

3. Abre a xanela da táboa de datos, como cando se compila unha táboa cunha variable, a través da pestana "Datos" da barra de ferramentas.
4. Substitúe na columna "Substituír valores por columnas en..." unha cela cun prezo inicial alto.
5. Engade unha cela coa porcentaxe inicial de vendas de libros caros á columna "Substituír valores por filas en..." e fai clic en "Aceptar".

Como resultado, toda a táboa está chea de cantidades de posibles ingresos con diferentes condicións para a venda de bens.

Acelere os cálculos para follas de traballo que conteñan táboas de datos

Se necesitas cálculos rápidos nunha táboa de datos que non desencadeen un recálculo de todo o libro de traballo, hai algunhas cousas que podes facer para acelerar o proceso.

1. Abra a xanela de opcións, seleccione o elemento "Fórmulas" no menú da dereita.
2. Seleccione o elemento "Automático, excepto as táboas de datos" na sección "Cálculos no libro de traballo".

3. Recalculemos os resultados na táboa manualmente. Para iso, seleccione as fórmulas e prema a tecla F.

Outras ferramentas para a realización da análise de sensibilidade

Hai outras ferramentas no programa para axudarche a realizar análises de sensibilidade. Automatizan algunhas accións que doutro xeito terían que facerse manualmente.

1. A función "Selección de parámetros" é axeitada se se coñece o resultado desexado e cómpre coñecer o valor de entrada da variable para obter tal resultado.
2. "Buscar unha solución" é un complemento para resolver problemas. É necesario establecer límites e sinalalos, despois de que o sistema atopará a resposta. A solución determínase cambiando os valores.
3. A análise de sensibilidade pódese facer mediante o Xestor de escenarios. Esta ferramenta atópase no menú de análise que pasaría baixo a pestana Datos. Substitúe os valores en varias celas: o número pode chegar a 32. O despachador compara estes valores para que o usuario non teña que cambialos manualmente. Un exemplo de uso do xestor de scripts:

Análise de sensibilidade dun proxecto de investimento en Excel

A análise de se é especialmente útil en situacións nas que se require unha previsión, como o investimento. Os analistas usan este método para descubrir como cambiará o valor das accións dunha empresa como resultado dos cambios nalgúns factores.

Método de análise de sensibilidade do investimento

Ao analizar "e se" use a enumeración: manual ou automática. Coñécese o rango de valores e substitúense na fórmula un por un. O resultado é un conxunto de valores. Escolle o número axeitado deles. Consideremos catro indicadores para os que se realiza a análise de sensibilidade no ámbito das finanzas:

1. Valor actual neto: calcúlase restando o importe do investimento da cantidade de ingresos.
2. Taxa interna de retorno / beneficio: indica cantos beneficios se deben recibir dun investimento nun ano.
3. O ratio de amortización é a relación entre todos os beneficios e o investimento inicial.
4. Índice de beneficio descontado: indica a eficacia do investimento.

Fórmula

A sensibilidade de incorporación pódese calcular mediante esta fórmula: Cambio do parámetro de saída en % / Cambio do parámetro de entrada en %.

Os parámetros de saída e entrada poden ser os valores descritos anteriormente.

1. Debe coñecer o resultado en condicións estándar.
2. Substituímos unha das variables e supervisamos os cambios no resultado.
3. Calculamos a variación porcentual de ambos parámetros en relación ás condicións establecidas.
4. Inserimos as porcentaxes obtidas na fórmula e determinamos a sensibilidade.

Un exemplo de análise de sensibilidade dun proxecto de investimento en Excel

Para unha mellor comprensión da metodoloxía de análise, é necesario un exemplo. Analizemos o proxecto cos seguintes datos coñecidos:

1. Enche a táboa para analizar o proxecto sobre ela.

2. Calculamos o fluxo de caixa mediante a función OFFSET. Na fase inicial, o fluxo é igual aos investimentos. A continuación, aplicamos a fórmula: =IF(OFFSET(Número,1;)=2;SUMA(Afluencia 1:Fluxo de saída 1); SUMA(Afluencia de entrada 1:Fluxo de saída 1)+$B$ 5)
   As designacións das celas na fórmula poden ser diferentes, dependendo da disposición da táboa. Ao final, engádese o valor dos datos iniciais: o valor de salvamento.

3. Determinamos o período durante o que o proxecto vai pagar. Para o período inicial, usamos esta fórmula: =SUMMESLY(G7: G17;»<0″). O intervalo de celas é a columna de fluxo de caixa. Para máis períodos, aplicamos esta fórmula: =Período inicial+IF(Primeiro e.stream>0; Primeiro e.stream;0). O proxecto está no punto de equilibrio en 4 anos.

4. Creamos unha columna para os números deses períodos nos que o proxecto dá froitos.

5. Calculamos o retorno do investimento. É necesario facer unha expresión onde o beneficio nun determinado período de tempo se divide polo investimento inicial.

6. Determinamos o factor de desconto mediante esta fórmula: =1/(1+disco%) ^Número.

7. Calculamos o valor actual mediante a multiplicación: o fluxo de caixa multiplícase polo factor de desconto.

8. Calculamos o PI (índice de rendibilidade). O valor actual ao longo do tempo divídese polo investimento ao inicio do proxecto.

9. Definamos a taxa interna de retorno usando a función IRR: = IRR (rango de fluxo de caixa).

Análise de sensibilidade do investimento mediante a folla de datos

Para a análise de proxectos no ámbito do investimento, outros métodos son máis adecuados que unha táboa de datos. Moitos usuarios experimentan confusión ao compilar unha fórmula. Para descubrir a dependencia dun factor dos cambios noutros, cómpre seleccionar as celas correctas para introducir cálculos e para ler datos.

Análise factorial e de dispersión en Excel con automatización do cálculo

Outra tipoloxía da análise de sensibilidade é a análise factorial e a análise da varianza. O primeiro tipo define a relación entre os números, o segundo revela a dependencia dunha variable con outras.

ANOVA en Excel

O obxectivo desta análise é dividir a variabilidade do valor en tres compoñentes:

1. Variabilidade como consecuencia da influencia doutros valores.
2. Cambios debido á relación dos valores que o afectan.
3. Cambios aleatorios.

Imos realizar a análise da varianza a través do complemento de Excel "Análise de datos". Se non está activado, pódese activar na configuración.

A táboa inicial debe seguir dúas regras: hai unha columna para cada valor e os datos nela están dispostos en orde ascendente ou descendente. Cómpre comprobar a influencia do nivel de educación na conduta en conflito.

1. Busque a ferramenta de análise de datos na pestana Datos e abra a súa xanela. Na lista, cómpre seleccionar a análise unidireccional da varianza.

2. Encha as liñas do cadro de diálogo. O intervalo de entrada é todas as celas, excluíndo as cabeceiras e os números. Agrupar por columnas. Mostramos os resultados nunha nova folla.

Dado que o valor da cela amarela é maior que un, a suposición pode considerarse incorrecta: non hai relación entre a educación e o comportamento en conflito.

Análise factorial en Excel: un exemplo

Analizemos a relación dos datos no campo das vendas: é necesario identificar produtos populares e impopulares. Información inicial:

1. Necesitamos descubrir cales aumentou máis a demanda de bens durante o segundo mes. Estamos elaborando unha nova táboa para determinar o crecemento e o descenso da demanda. O crecemento calcúlase mediante esta fórmula: =IF((Demanda 2-Demanda 1)>0; Demanda 2- Demanda 1;0). Fórmula de diminución: =IF(Crecemento=0; Demanda 1- Demanda 2;0).

2. Calcula o crecemento da demanda de bens como porcentaxe: =IF(Crecemento/Resultado 2 = 0; Diminución/Resultado 2; Crecemento/Resultado 2).

3. Fagamos un gráfico para obter claridade: seleccione un rango de celas e cree un histograma a través da pestana "Inserir". Na configuración, cómpre eliminar o recheo, isto pódese facer mediante a ferramenta Formatar serie de datos.

Análise bidireccional da varianza en Excel

A análise da varianza realízase con varias variables. Considere isto cun exemplo: cómpre descubrir a rapidez con que se manifesta a reacción a un son de diferente volume en homes e mulleres.

1. Abrimos "Análise de datos", na lista necesitas atopar unha análise bidireccional da varianza sen repeticións.
2. Intervalo de entrada: celas que conteñen datos (sen cabeceira). Amosamos os resultados nunha nova folla e prememos en "Aceptar".

O valor F é maior que o F-crítico, o que significa que o chan afecta a velocidade de reacción ao son.

Conclusión

Neste artigo comentouse en detalle a análise de sensibilidade nunha folla de cálculo de Excel, para que cada usuario poida comprender os métodos da súa aplicación.