El análisis de sensibilidad en Excel nos ayuda a estudiar la incertidumbre en la salida del modelo con los cambios en las variables de entrada. Principalmente realiza pruebas de estrés de nuestros supuestos modelados y conduce a conocimientos de valor agregado.

En el contexto de la valoración DCF, el análisis de sensibilidad en Excel es especialmente útil en finanzas para modelar el precio de las acciones o la sensibilidad de la valoración a supuestos como tasas de crecimiento o costo de capital.

En este artículo, analizamos el siguiente análisis de sensibilidad en Excel para el modelado DCF de forma profesional.

Lo más importante: descargue el análisis de sensibilidad en la plantilla de Excel

Aprenda una variable y dos tipos de TABLA de DATOS variables en Excel

Análisis de sensibilidad en Excel

# 1 - Análisis de sensibilidad de tabla de datos de una variable en Excel

Tomemos el ejemplo de Finanzas (modelo de descuento de dividendos) a continuación para comprender este en detalle.

El DDM de crecimiento constante nos da el valor razonable de una acción como valor presente de un flujo infinito de dividendos que están creciendo a una tasa constante.

La fórmula de Gordon Growth es la siguiente:

Dónde:

D1 = Valor del dividendo que se recibirá el próximo año
D0 = Valor del dividendo recibido este año
g = Tasa de crecimiento del dividendo
Ke = Tasa de descuento

Ahora, supongamos que queremos comprender qué tan sensible es el precio de las acciones con respecto al rendimiento esperado (ke). Hay dos formas de hacer esto:

Camino del burro :-)
Y si el análisis

# 1 - Camino del burro

El análisis de sensibilidad en Excel usando el método de Donkey es muy sencillo, pero difícil de implementar cuando hay muchas variables involucradas.

¿Quiere continuar haciendo esto dadas 1000 suposiciones? ¡Obviamente no!

Aprenda el siguiente análisis de sensibilidad en la técnica de Excel para evitar el problema.

# 2 - Usando una tabla de datos variables

La mejor forma de realizar análisis de sensibilidad en Excel es utilizar tablas de datos. Las tablas de datos proporcionan un atajo para calcular múltiples versiones en una operación y una forma de ver y comparar los resultados de todas las diferentes variaciones juntas en su hoja de trabajo. A continuación se muestran los pasos que puede seguir para implementar un análisis de sensibilidad unidimensional en Excel.

Paso 1: crea la tabla en un formato estándar

En la primera columna, tiene las suposiciones de entrada. En nuestro ejemplo, las entradas son la tasa de rendimiento esperada (ke). Además, tenga en cuenta que hay una fila en blanco (de color azul en este ejercicio) debajo del encabezado de la tabla. Esta fila en blanco tiene un propósito importante para esta tabla de datos unidimensional que verá en el Paso 2.

Paso 2: vincule la entrada y la salida de referencia como se muestra en la instantánea a continuación.

El espacio proporcionado por la fila en blanco ahora se usa para proporcionar entrada (retorno esperado Ke) y la fórmula de salida. ¿Por qué se hace así?

Vamos a utilizar "¿Qué pasaría si análisis?", Esta es una forma de indicarle a Excel que para la entrada (ke), la fórmula correspondiente proporcionada en el lado derecho debe usarse para volver a calcular todas las demás entradas.

Paso 3: seleccione la herramienta de análisis hipotético para realizar un análisis de sensibilidad en Excel

Es importante tener en cuenta que esto se subdivide en dos pasos

Seleccione el rango de la tabla comenzando desde el lado izquierdo desde el 10% hasta la esquina inferior derecha de la tabla.
Haga clic en Datos -> Análisis Y si -> Tablas de datos

Paso 4: se abre el cuadro de diálogo de la tabla de datos.

El cuadro de diálogo busca dos entradas: Entrada de fila y Entrada de columna. Dado que solo hay una entrada Ke en consideración, proporcionaremos una entrada de una sola columna.

Paso 5: vincular la entrada de la columna

En nuestro caso, toda la entrada se proporciona en una columna y, por lo tanto, enlazaremos a la entrada de la columna. La entrada de la columna está vinculada al rendimiento esperado (Ke). Tenga en cuenta que la entrada debe estar vinculada desde la fuente original y no desde la que está dentro de la tabla

Paso 6: disfrute de la salida

# 2 - Análisis de sensibilidad de la tabla de datos de dos variables en Excel

Las tablas de datos son muy útiles para el análisis de sensibilidad en Excel, especialmente en el caso de DCF. Una vez que se establece un caso base, el análisis DCF siempre debe probarse en varios escenarios de sensibilidad. Las pruebas implican examinar el efecto incremental de varios cambios en los supuestos (costo de capital, tasas de crecimiento terminal, menor crecimiento de ingresos, mayores requisitos de capital, etc.) sobre el valor razonable de las acciones.

Tomemos el análisis de sensibilidad en Excel con un ejemplo financiero del análisis de flujo de efectivo descontado de Alibaba.

Con los supuestos básicos del costo de capital como 9% y una tasa de crecimiento constante del 3% , llegamos a la valoración justa de $ 191,45 mil millones.

Supongamos ahora que no está completamente de acuerdo con los supuestos del costo de capital o los supuestos de la tasa de crecimiento que he tomado en la valoración de la oferta pública inicial de Alibaba. Es posible que desee cambiar las suposiciones y acceder al impacto en las valoraciones.

Una forma es cambiar las suposiciones manualmente y verificar los resultados de cada cambio. (palabra clave: método burro)

Sin embargo, estamos aquí para discutir una forma mucho mejor y eficiente de calcular la valoración utilizando el análisis de sensibilidad en Excel que no solo ahorra tiempo sino que también nos brinda una forma de visualizar todos los detalles de salida en un formato efectivo.

Si realizamos el análisis Y si ... en Excel de forma profesional sobre los datos anteriores, obtenemos el siguiente resultado.

Aquí, las entradas de fila consisten en cambios en el costo de capital o WACC (7% a 11%)
Las entradas de la columna consisten en cambios en las tasas de crecimiento (1% a 6%)
El punto de intersección es Alibaba Valuation. Por ej. utilizando nuestro caso base de 9% WACC y tasas de crecimiento del 3%, obtenemos la valoración como $ 191,45 mil millones.

Con estos antecedentes, veamos ahora cómo podemos preparar un análisis de sensibilidad de este tipo en Excel utilizando tablas de datos bidimensionales.

Paso 1: cree la estructura de la tabla como se indica a continuación

Dado que tenemos dos conjuntos de suposiciones: el costo de capital (WACC) y las tasas de crecimiento (g), debe preparar una tabla a continuación.
Puede cambiar las entradas de fila y columna. En lugar de WACC, puede tener tasas de crecimiento y viceversa.

Paso 2: vincule el punto de intersección con la celda de salida.

El punto de intersección de las dos entradas debe usarse para vincular la salida deseada. En este caso, queremos ver el efecto de estas dos variables (WACC y tasa de crecimiento) sobre el valor de las acciones. Por lo tanto, hemos vinculado la celda que se cruza con la salida.

Paso 3: Abra la tabla de datos bidimensionales

Seleccione la tabla que ha creado
Luego haga clic en Datos -> Análisis y si análisis -> Tablas de datos

Paso 4: proporcione las entradas de la fila y las entradas de la columna.

La entrada de fila es el costo de capital o Ke.
La entrada de la columna es la tasa de crecimiento.
Recuerde vincular estas entradas desde la fuente de suposición original y no desde cualquier lugar dentro de la tabla

Paso 5: disfruta del resultado.

La mayoría de los valores de producción pesimistas se encuentran en la esquina superior derecha, donde el costo de capital es del 11% y la tasa de crecimiento es solo del 1%.
El valor de salida a bolsa de Alibaba más optimista es cuando Ke es 7% y g es 6%
El caso base que calculamos para tasas de crecimiento del 9% ke y del 3% se encuentra en el medio.
Este análisis de sensibilidad bidimensional en la tabla de Excel proporciona a los clientes un análisis de escenarios sencillo que ahorra mucho tiempo.

# 3 - Búsqueda de objetivos para el análisis de sensibilidad en Excel

El comando Buscar objetivo se utiliza para llevar una fórmula a un valor específico
Lo hace cambiando una de las celdas a las que hace referencia la fórmula
Goal Seek solicita una referencia de celda que contenga una fórmula (la celda Establecer). También solicita un valor, que es la cifra que desea que iguale la celda
Finalmente, Goal Seek solicita que se modifique una celda para llevar la celda Establecer al valor requerido

Echemos un vistazo al DCF de Alibaba IPO Valuation.

Como sabemos por DCF, las tasas de crecimiento y la valoración están directamente relacionadas. El aumento de las tasas de crecimiento aumenta el precio de las acciones.

Supongamos que queremos verificar ¿a qué tasa de crecimiento tocará el precio de las acciones los $ 80?

Como siempre, podemos hacer esto manualmente cambiando las tasas de crecimiento para seguir viendo el impacto en el precio de la acción. Esto volverá a ser un proceso tedioso, es posible que tengamos que ingresar tasas de crecimiento muchas veces para asegurarnos de que el precio de las acciones coincida con $ 80 en nuestro caso.

Sin embargo, podemos usar una función como Buscar objetivo en Excel para resolver esto en sencillos pasos.

Paso 1: haga clic en la celda cuyo valor desea establecer. (La celda Establecer debe contener una fórmula)

Paso 2: elija Herramientas, Búsqueda de objetivos en el menú y aparecerá el siguiente cuadro de diálogo:

El comando Buscar objetivo sugiere automáticamente la celda activa como la celda Establecer.
Esto se puede sobreescribir con una nueva referencia de celda o puede hacer clic en la celda correspondiente en la hoja de cálculo.
Ahora ingrese el valor deseado que debe alcanzar esta fórmula.
Haga clic dentro del cuadro "A valor" y escriba el valor que desea que su fórmula seleccionada sea igual
Finalmente, haga clic dentro del cuadro "Cambiando celda" y escriba o haga clic en la celda cuyo valor se puede cambiar para lograr el resultado deseado
Haga clic en el botón Aceptar y la hoja de cálculo modificará la celda a un valor suficiente para que la fórmula alcance su objetivo.

Paso 3: disfruta del resultado.

Goal Seek también le informa que el objetivo se logró

Conclusión

El análisis de sensibilidad en Excel aumenta su comprensión del comportamiento financiero y operativo de la empresa. Como aprendimos de los tres enfoques: tablas de datos unidimensionales, tablas de datos bidimensionales y Goal Seek, el análisis de sensibilidad es extremadamente útil en el campo de las finanzas, especialmente en el contexto de las valoraciones: DCF o DDM.

Sin embargo, también puede obtener una comprensión a nivel macro de la empresa y la industria en general. Puede desarrollar casos para reflejar la sensibilidad de la valoración a cambios en las tasas de interés, recesión, inflación, PIB, etc. en la valoración. Se debe emplear el pensamiento y el sentido común para desarrollar casos de sensibilidad razonables y útiles.

¿Qué sigue?

Si aprendió algo sobre el análisis de sensibilidad en Excel, deje un comentario a continuación. Déjame saber lo que piensas. Muchas gracias y cuídate. ¡Feliz aprendizaje!

También puede consultar estos artículos a continuación para obtener más información sobre valoraciones y finanzas corporativas: