Análisis de sensibilidad en Excel (hoja de datos de muestra)

Los procesos en el campo de las finanzas siempre están interconectados: un factor depende de otro y cambia con él. Realice un seguimiento de estos cambios y comprenda qué esperar en el futuro, quizás utilizando funciones de Excel y métodos de hoja de cálculo.

Obtener múltiples resultados con una tabla de datos

Las capacidades de la hoja de datos son elementos del análisis hipotético, que a menudo se realiza a través de Microsoft Excel. Este es el segundo nombre para el análisis de sensibilidad.

General

Una tabla de datos es un tipo de rango de celdas que se puede utilizar para resolver problemas cambiando los valores en algunas celdas. Se crea cuando es necesario realizar un seguimiento de los cambios en los componentes de la fórmula y recibir actualizaciones de los resultados, de acuerdo con estos cambios. Averigüemos cómo usar las tablas de datos en la investigación y qué tipos son.

Conceptos básicos sobre las tablas de datos

Hay dos tipos de tablas de datos, se diferencian en el número de componentes. Debe compilar una tabla con un enfoque en la cantidad de valores uXNUMXbuXNUMXbque necesita verificar con ella.

Los estadísticos usan una tabla de una sola variable cuando solo hay una variable en una o más expresiones que pueden cambiar su resultado. Por ejemplo, a menudo se usa junto con la función PAGO. La fórmula está diseñada para calcular el monto del pago regular y tiene en cuenta la tasa de interés especificada en el acuerdo. En tales cálculos, las variables se escriben en una columna y los resultados de los cálculos en otra. Un ejemplo de placa de datos con 1 variable:

A continuación, considere las placas con 2 variables. Se utilizan en los casos en que dos factores influyen en el cambio de cualquier indicador. Las dos variables pueden terminar en otra tabla asociada con el préstamo, que puede usarse para determinar el período de pago óptimo y el monto del pago mensual. En este cálculo, también necesita usar la función PAGO. Un ejemplo de una tabla con 2 variables:

Crear una tabla de datos con una variable

Considere el método de análisis utilizando el ejemplo de una pequeña librería con solo 100 libros en stock. Algunos de ellos se pueden vender más caros ($ 50), el resto le costará menos ($ 20) a los compradores. Se calcula el ingreso total de la venta de todos los bienes: el propietario decidió que vendería el 60% de los libros a un precio alto. Debe averiguar cómo aumentarán los ingresos si aumenta el precio de un mayor volumen de bienes: 70%, y así sucesivamente.

¡Presta atención! Los ingresos totales deben calcularse mediante una fórmula, de lo contrario no será posible compilar una tabla de datos.

1. Seleccione una celda libre alejada del borde de la hoja y escriba la fórmula en ella: = Celda de ingresos totales. Por ejemplo, si los ingresos están escritos en la celda C14 (se indica una designación aleatoria), debe escribir esto: =S14.
2. Escribimos el porcentaje del volumen de bienes en la columna a la izquierda de esta celda, no debajo, esto es muy importante.
3. Seleccionamos el rango de celdas donde se encuentra la columna de porcentaje y el enlace a los ingresos totales.

4. Encontramos en la pestaña "Datos" el elemento "Qué pasa si el análisis" y hacemos clic en él; en el menú que se abre, seleccione la opción "Tabla de datos".

5. Se abrirá una pequeña ventana donde debe especificar una celda con el porcentaje de libros vendidos inicialmente a un precio alto en la columna "Sustituir valores por filas en ...". Este paso se realiza para recalcular los ingresos totales teniendo en cuenta el porcentaje creciente.

Después de hacer clic en el botón "Aceptar" en la ventana donde se ingresaron los datos para compilar la tabla, los resultados de los cálculos aparecerán en las líneas.

Adición de una fórmula a una tabla de datos de una sola variable

A partir de una tabla que ayudó a calcular una acción con una sola variable, puede crear una herramienta de análisis sofisticada agregando una fórmula adicional. Debe ingresarse junto a una fórmula ya existente; por ejemplo, si la tabla está orientada a filas, ingresamos la expresión en la celda a la derecha de la existente. Cuando se establece la orientación de la columna, escribimos la nueva fórmula debajo de la anterior. A continuación, sigue el algoritmo:

1. Seleccione el rango de celdas nuevamente, pero ahora debería incluir la nueva fórmula.
2. Abra el menú de análisis "qué pasaría si" y seleccione "Hoja de datos".
3. Añadimos una nueva fórmula al campo correspondiente en filas o columnas, dependiendo de la orientación de la placa.

Crear una tabla de datos con dos variables.

El comienzo de dicha tabla es ligeramente diferente: debe colocar un enlace a los ingresos totales por encima de los valores porcentuales. A continuación, realizamos estos pasos:

1. Escriba las opciones de precio en una línea con un enlace a los ingresos: una celda para cada precio.
2. Seleccione un rango de celdas.

3. Abra la ventana de la tabla de datos, como cuando compila una tabla con una variable, a través de la pestaña "Datos" en la barra de herramientas.
4. Sustituir en la columna “Sustituir valores por columnas en…” una celda con un precio alto inicial.
5. Agregue una celda con el porcentaje inicial de ventas de libros caros a la columna "Sustituir valores por filas en ..." y haga clic en "Aceptar".

Como resultado, toda la tabla se llena con montos de posibles ingresos con diferentes condiciones para la venta de bienes.

Acelere los cálculos de las hojas de trabajo que contienen tablas de datos

Si necesita cálculos rápidos en una tabla de datos que no activen un nuevo cálculo de todo el libro de trabajo, hay algunas cosas que puede hacer para acelerar el proceso.

1. Abra la ventana de opciones, seleccione el elemento "Fórmulas" en el menú de la derecha.
2. Seleccione el elemento "Automático, excepto para tablas de datos" en la sección "Cálculos en el libro de trabajo".

3. Recalculemos los resultados en la tabla manualmente. Para hacer esto, seleccione las fórmulas y presione la tecla F.

Otras herramientas para realizar análisis de sensibilidad

Hay otras herramientas en el programa para ayudarlo a realizar análisis de sensibilidad. Automatizan algunas acciones que de otro modo tendrían que hacerse manualmente.

1. La función "Selección de parámetros" es adecuada si se conoce el resultado deseado y necesita conocer el valor de entrada de la variable para obtener dicho resultado..
2. “Buscar una solución” es un complemento para resolver problemas. Es necesario establecer límites y señalarlos, después de lo cual el sistema encontrará la respuesta. La solución se determina cambiando los valores.
3. El análisis de sensibilidad se puede realizar utilizando Scenario Manager. Esta herramienta se encuentra en el menú de análisis hipotético en la pestaña Datos. Sustituye valores en varias celdas, el número puede llegar a 32. El despachador compara estos valores para que el usuario no tenga que cambiarlos manualmente. Un ejemplo del uso del administrador de secuencias de comandos:

Análisis de sensibilidad de un proyecto de inversión en Excel

El análisis hipotético es especialmente útil en situaciones en las que se requiere previsión, como la inversión. Los analistas utilizan este método para averiguar cómo cambiará el valor de las acciones de una empresa como resultado de cambios en algunos factores.

Método de análisis de sensibilidad de inversión

Al analizar "qué pasaría si" use la enumeración: manual o automática. El rango de valores es conocido y se sustituyen en la fórmula uno por uno. El resultado es un conjunto de valores. Elija el número apropiado de ellos. Consideremos cuatro indicadores para los cuales se lleva a cabo un análisis de sensibilidad en el campo de las finanzas:

1. Valor actual neto: se calcula restando el monto de la inversión del monto de los ingresos.
2. Tasa interna de rendimiento/ganancia: indica cuánta ganancia se requiere recibir de una inversión en un año.
3. El índice de recuperación de la inversión es la relación entre todas las ganancias y la inversión inicial.
4. Índice de beneficio descontado: indica la efectividad de la inversión.

Fórmula

La sensibilidad de incrustación se puede calcular usando esta fórmula: Cambio en el parámetro de salida en % / Cambio en el parámetro de entrada en %.

Los parámetros de salida y entrada pueden ser los valores descritos anteriormente.

1. Necesita saber el resultado en condiciones estándar.
2. Reemplazamos una de las variables y monitoreamos los cambios en el resultado.
3. Calculamos el cambio porcentual de ambos parámetros respecto a las condiciones establecidas.
4. Insertamos los porcentajes obtenidos en la fórmula y determinamos la sensibilidad.

Un ejemplo de análisis de sensibilidad de un proyecto de inversión en Excel

Para una mejor comprensión de la metodología de análisis, se necesita un ejemplo. Analicemos el proyecto con los siguientes datos conocidos:

1. Rellena la tabla para analizar el proyecto en ella.

2. Calculamos el flujo de caja usando la función OFFSET. En la etapa inicial, el flujo es igual a las inversiones. A continuación, aplicamos la fórmula: =SI(DESPLAZAMIENTO(Número,1;)=2;SUMA(Flujo de entrada 1:Flujo de salida 1); SUMA(Flujo de entrada 1:Flujo de salida 1)+$B$ 5)
   Las designaciones de las celdas en la fórmula pueden ser diferentes, según el diseño de la tabla. Al final, se agrega el valor de los datos iniciales: el valor de salvamento.

3. Determinamos el período durante el cual el proyecto dará sus frutos. Para el período inicial, usamos esta fórmula: =SUMESTAMENTE(G7: G17;»<0″). El rango de celdas es la columna de flujo de caja. Para períodos posteriores, aplicamos esta fórmula: =Período inicial+SI(Primera corriente electrónica>0; Primera corriente electrónica;0). El proyecto está en el punto de equilibrio en 4 años.

4. Creamos una columna para los números de esos períodos cuando el proyecto vale la pena.

5. Calculamos el retorno de la inversión. Es necesario hacer una expresión donde la ganancia en un determinado periodo de tiempo se divida por la inversión inicial.

6. Determinamos el factor de descuento utilizando esta fórmula: =1/(1+disco.%) ^Número.

7. Calculamos el valor actual mediante la multiplicación: el flujo de efectivo se multiplica por el factor de descuento.

8. Calculemos el PI (índice de rentabilidad). El valor presente a lo largo del tiempo se divide por la inversión al inicio del proyecto.

9. Definamos la tasa interna de retorno usando la función TIR: =TIR(Rango de flujo de efectivo).

Análisis de sensibilidad de inversión utilizando la hoja de datos

Para el análisis de proyectos en el campo de la inversión, otros métodos son más adecuados que una tabla de datos. Muchos usuarios experimentan confusión al compilar una fórmula. Para averiguar la dependencia de un factor de los cambios en otros, debe seleccionar las celdas correctas para ingresar cálculos y leer datos.

Análisis factorial y de dispersión en Excel con automatización de cálculo

Otra tipología del análisis de sensibilidad es el análisis factorial y el análisis de varianza. El primer tipo define la relación entre números, el segundo revela la dependencia de una variable con respecto a otras.

ANOVA en Excel

El propósito de dicho análisis es dividir la variabilidad del valor en tres componentes:

1. Variabilidad como resultado de la influencia de otros valores.
2. Cambios debido a la relación de los valores que la afectan.
3. Cambios aleatorios.

Realicemos un análisis de varianza a través del complemento de Excel "Análisis de datos". Si no está habilitado, se puede habilitar en la configuración.

La tabla inicial debe seguir dos reglas: hay una columna para cada valor y los datos en ella se organizan en orden ascendente o descendente. Es necesario comprobar la influencia del nivel educativo en el comportamiento en conflicto.

1. Busque la herramienta Análisis de datos en la pestaña Datos y abra su ventana. En la lista, debe seleccionar el análisis de varianza unidireccional.

2. Rellene las líneas del cuadro de diálogo. El intervalo de entrada es todas las celdas, excluyendo encabezados y números. Agrupar por columnas. Mostramos los resultados en una nueva hoja.

Dado que el valor en la celda amarilla es mayor que uno, la suposición puede considerarse incorrecta: no existe una relación entre la educación y el comportamiento en conflicto.

Análisis factorial en Excel: un ejemplo

Analicemos la relación de datos en el campo de las ventas: es necesario identificar productos populares e impopulares. Información inicial:

1. Necesitamos averiguar para qué bienes la demanda aumentó más durante el segundo mes. Estamos compilando una nueva tabla para determinar el crecimiento y la disminución de la demanda. El crecimiento se calcula usando esta fórmula: =SI((Demanda 2-Demanda 1)>0; Demanda 2- Demanda 1;0). Fórmula de disminución: =SI(Crecimiento=0; Demanda 1- Demanda 2;0).

2. Calcule el crecimiento de la demanda de bienes como porcentaje: =SI(Crecimiento/Resultado 2 =0; Disminución/Resultado 2; Crecimiento/Resultado 2).

3. Hagamos un gráfico para mayor claridad: seleccione un rango de celdas y cree un histograma a través de la pestaña "Insertar". En la configuración, debe eliminar el relleno, esto se puede hacer a través de la herramienta Formato de serie de datos.

Análisis de varianza bidireccional en Excel

El análisis de varianza se realiza con varias variables. Considere esto con un ejemplo: debe averiguar qué tan rápido se manifiesta la reacción a un sonido de diferente volumen en hombres y mujeres.

1. Abrimos "Análisis de datos", en la lista necesita encontrar un análisis de varianza bidireccional sin repeticiones.
2. Intervalo de entrada: celdas que contienen datos (sin encabezado). Mostramos los resultados en una nueva hoja y hacemos clic en "Aceptar".

El valor F es mayor que el F-crítico, lo que significa que el piso afecta la velocidad de reacción al sonido.

Conclusión

En este artículo, se discutió en detalle el análisis de sensibilidad en una hoja de cálculo de Excel, para que cada usuario pueda comprender los métodos de su aplicación.