Tutorial de Análisis de Sensibilidad en Excel: Visión general, tipos y buenas prácticas

Al diseñar un modelo en una hoja de cálculo, puede interesarnos saber cómo podría cambiar un resultado al cambiar el valor de una de las variables implicadas. Este proceso puede realizarse en Excel ejecutando un análisis de sensibilidad

Saber cómo realizar un análisis de sensibilidad es crucial si eres un profesional de los datos que trabaja en modelización financiera o en procesos de decisión. 

En este artículo profundizaremos en el análisis de sensibilidad. Cubriremos los fundamentos de esta útil técnica dentro de la familia de análisis "Y si..." y cómo realizar e interpretar los resultados de los análisis de sensibilidad básicos y avanzados en Excel. Por último, debatiremos el papel de la IA y el machine learning en el avance de un análisis de sensibilidad más potente, así como las mejores prácticas y los escollos más comunes. 

Si quieres adquirir experiencia práctica, consulta nuestro curso Análisis financiero en Excel, que trata en detalle el análisis hipotético. 

¿Qué es el Análisis de Sensibilidad en Excel?

El enfoque más habitual en el análisis de sensibilidad consiste en examinar cómo afectan al resultado final los cambios de una variable cada vez, manteniendo constantes todos los demás factores. Esto se repite para todas las variables consideradas. Ayuda a identificar los impulsores más influyentes de un modelo, permitiendo a las empresas centrarse en los factores clave que afectan a su rendimiento. 

El análisis de sensibilidad ayuda a las empresas a priorizar los riesgos y oportunidades clave, identificando qué factores tienen mayor impacto en el rendimiento. Dada su naturaleza, el análisis de sensibilidad es habitual en finanzas, empresa y gestión de proyectos, por ejemplo, para calcular cómo pueden cambiar los tipos de interés a lo largo del tiempo o para comprender distintas tendencias de crecimiento. 

Además, la sensibilidad suele combinarse con el análisis de escenarios, que suele analizar la combinación de variables al mismo tiempo. Estos dos análisis hipotéticos estudian cómo reaccionará una variable dependiente ante determinadas entradas. Sin embargo, el análisis de escenarios es específico de un determinado "escenario", mientras que el análisis de sensibilidad es más abierto porque da una gama de entradas y valores. 

El objetivo del análisis de sensibilidad es comprender cómo reacciona la variable dependiente ante una serie de valores de entrada, conocidos como variables independientes.

Al incorporar esta técnica a la toma de decisiones, las organizaciones pueden ajustar proactivamente las estrategias y mejorar la resistencia frente a la incertidumbre. 

Para una explicación más detallada de la teoría que subyace al análisis de sensibilidad, te recomendamos encarecidamente nuestro Probabilidad Avanzada: Incertidumbre en el curso de Datos.

A menudo, el análisis de sensibilidad se presenta en una tabla de sensibilidad con formato condicional que resalta los valores de mayor a menor. 

A continuación se muestra un ejemplo de la sensibilidad al precio de la oferta y la demanda. La oferta y la demanda son las variables independientes marcadas en los ejes, y el precio es la variable dependiente, que contiene los valores de la tabla.

Trabajaremos con tablas de sensibilidad de Excel similares en las próximas secciones.

Tabla de sensibilidad. Fuente: DataCamp

Introducción al análisis de sensibilidad en Excel

Veamos cómo realizar un análisis de sensibilidad en Excel, siguiendo cada paso del proceso. 

Herramientas y funciones de Excel

Excel es una herramienta muy útil para crear modelos matemáticos, incluidos los análisis hipotéticos. El análisis hipotético consiste esencialmente en crear fórmulas que permitan explorar cuestiones, como un análisis de sensibilidad.

Las versiones más recientes de Excel vienen con una pestañaDatos, que proporciona un sinfín de herramientas y posibilidades para trabajar con datos. Incluye un botón Análisis Y si... en la cinta de opciones. Incluye tres herramientas. Elbotón Tabla de datos está diseñado específicamente para crear tablas de sensibilidad.

Para realizar el análisis de escenarios, basta con hacer clic en el Gestor de Escenarios del escenario.

También utilizaremos Solverun complemento que puedes activar desde la página Programador Desarrollador.

El solucionador se utiliza en los análisis de suposición Y si... para encontrar un valor óptimo (máximo o mínimo) para una fórmula en una celda -llamada celda objetivo- sujeta a restricciones, o límites, en los valores de otras celdas de fórmula de una hoja de cálculo.

Antes de activar Solver, debes acceder a la pestaña Programador, que no se muestra por defecto. Para mostrar esta pestaña:

• Ir a Archivo > Opciones
• Selecciona Personalizar cinta en el panel izquierdo
• En la columna de la derecha, en "Pestañas principalesmarca la casilla Programador

Realizar un análisis de sensibilidad con una sola variable

Ahora que tenemos listas nuestras herramientas, empecemos por crear un sencillo análisis de sensibilidad de una sola variable en Excel.

Configurar tu modelo

El paso inicial para realizar un análisis de sensibilidad en Excel es identificar las entradas y salidas en las que se basará tu modelo. 

En este tutorial, utilizaremos el análisis de sensibilidad para estudiar cómo variará el beneficio neto de una empresa de venta de guitarras si cambiamos el valor de determinadas variables de entrada, como el número de guitarras vendidas, el precio de la guitarra o los costes de producción.

A continuación, encontrarás las tablas con las variables de entrada y salida. 

Cuando utilices tablas de datos para el análisis de sensibilidad, es importante vincular las celdas de salida a las variables de entrada para que Excel pueda entender la relación entre las variables del modelo. Este proceso se realiza mediante fórmulas. En nuestro ejemplo:

• Ingresos se calcula multiplicando el precio unitario por las unidades vendidas (=B2*B4)
• Coste de venta se calcula multiplicando el coste de producción por unidad por las unidades vendidas (=B3*B4)
• Beneficio se calcula restando los ingresos del coste de las ventas (=B7-B8).

Crear una tabla de datos unidireccional

Quizá quieras saber cómo cambia el beneficio si aumentamos o disminuimos el precio por unidad, manteniendo invariables el coste de producción y el número de unidades vendidas. Cuando analizamos cómo cambia una variable de salida cuando modificamos el valor de una variable de entrada, necesitamos una tabla de datos llamada unidireccional.

El siguiente GIF muestra cómo crear una tabla de datos unidireccional en Excel. Como puedes ver, la tabla recalcula automáticamente el beneficio por cada unidad vendida. Naturalmente, el beneficio cuando vendemos 250 unidades es el mismo que en los parámetros originales (es decir, 50.000 euros).

También puedes estudiar cómo cambian las distintas variables de salida cuando modificamos las ventas unitarias. Esto también es bastante fácil, como se muestra a continuación. En el siguiente GIF, calculamos cómo afectan las ventas unitarias tanto a los beneficios como al coste de las ventas.

Realización de un análisis de sensibilidad de dos variables

En lugar de analizar cómo se ve afectada una salida por una entrada, también podrías crear una tabla de dos variables para analizar el impacto de dos variables de entrada. Digamos que quieres saber cómo cambia el beneficio si modificamos las ventas de guitarras y el precio por guitarra. 

En primer lugar, tienes que crear una tabla bidimensional con los rangos de precio unitario en las filas y las ventas unitarias en las columnas. Después, en la esquina superior izquierda de la tabla, elige la variable que quieras estudiar. Por último, elige la referencia de celda de las variables independientes en la tabla de variables de entrada (es decir, precio por unidad y unidades vendidas).

Como puedes ver, una vez creada la tabla, puedes cambiar fácilmente la variable de salida de la esquina superior izquierda por otra variable, y Excel recalculará todos los valores

Interpretar los resultados del análisis de sensibilidad de Excel

Ahora que tienes tus tablas de datos, el último paso es interpretar los resultados para generar ideas sobre cómo los cambios en las entradas afectan a las salidas.

Analizar las salidas de las tablas de datos

Teniendo en cuenta este análisis de sensibilidad, podemos tomar una decisión informada sobre el beneficio. He aquí algunas ideas:

• Si aumentamos los precios de las guitarras a 350 euros, podríamos igualar el beneficio actual de 50.000 euros vendiendo sólo 200 guitarras.
• Si reducimos el precio de las guitarras a 200 €, necesitaríamos vender 500 unidades para alcanzar los beneficios actuales.
• Si reducimos el número de guitarras vendidas a 150, no igualaremos el beneficio actual a menos que cambiemos el precio de las guitarras a más de 400€.

La interpretación del análisis dependerá de las necesidades particulares de tu empresa. Plantearte preguntas como cuántas guitarras puedes producir, cuál es el nivel mínimo de beneficios que necesitas para mantener la solidez de tu empresa y qué está en tu mano para reducir el coste de producción de la guitarra será crucial para extraer ideas significativas del análisis de sensibilidad.

Utilizar Solver para el análisis de sensibilidad

Cuando realizas un análisis de sensibilidad, estás devolviendo un rango de posibles resultados basados en cambios en las variables de entrada de un modelo. Sin embargo, puede que quieras conocer más detalles sobre el funcionamiento interno de tu modelo.

Afortunadamente, Excel dispone de una potente herramienta llamada Solver, que puede ayudarte en esta tarea. Como ya hemos dicho, el Solver se utiliza en los análisis de suposición Y si... para encontrar un valor óptimo (máximo o mínimo) para una fórmula sujeta a restricciones, o límites, sobre los valores de otras celdas de fórmula de una hoja de cálculo. 

Esto puede sonar como el objetivo de la Búsqueda de Objetivos, otro tipo de análisis Y si... que se centra en ajustar una única variable para alcanzar un resultado deseado. Sin embargo, Solver es mucho más que Búsqueda de objetivos. 

En primer lugar, Solver te permite calcular un resultado óptimo basado en más de una variable y te permite incluir restricciones en los modelos. 

Es más, Solver también viene con una función opcional de sensibilidad que te da una idea de cómo cambia la solución óptima cuando modificas los coeficientes del modelo. 

Para ilustrar la potencia de Solver, volvamos a nuestra empresa de guitarras. Imagina que la empresa fabrica dos modelos de guitarra (modelo A y B), cada uno de los cuales requiere una determinada combinación de caoba y cedro. Cada modelo tiene un precio diferente. 

La empresa quiere saber cuántas unidades de cada modelo debe producir para maximizar sus beneficios, dada su disponibilidad actual de caoba y cedro. 

Hay tres cosas que queremos que calcule Solver: Beneficio, unidades del Modelo A y unidades del Modelo B. La imagen de arriba muestra las entradas, fórmulas y restricciones en las que se basará el modelo. 

En el siguiente GIF, mostramos cómo utilizar Solver para encontrar los valores óptimos de unidades para maximizar los beneficios, utilizando restricciones de disponibilidad de madera y asegurándonos de que las unidades son números completos. Hay varias estrategias que Solver puede utilizar para optimizar el modelo, tanto para problemas lineales como no lineales. En nuestro caso, utilizaremos LP Simple. 

Una vez sumados todos los valores de los parámetros, Solver estima que las 48 unidades del modelo A y las 9 del modelo B son la mejor combinación para maximizar el beneficio (17.591 euros). Podemos hacer clic en Sensibilidad antes de cerrar el cuadro Solver, y Excel creará una nueva hoja con un informe de Sensibilidad.

La sensibilidad nos proporciona información valiosa para estimar cómo variarían los beneficios si cambiamos las variables de entrada. El Precio Sombra indica cuánto aumentaría el beneficio si tuvieras una unidad adicional de un recurso restringido. En este ejemplo, cada metro cuadrado adicional de caoba aumenta el beneficio en 98 euros, mientras que el cedro lo hace en 103 euros, lo que pone de manifiesto el mayor rendimiento potencial de la inversión en cedro

Otro elemento importante del informe de sensibilidad son los intervalos dentro de los cuales podrías mantener los precios de los modelos para que los resultados del modelo no varíen. 

El beneficio se calcula multiplicando las unidades de cada modelo por su precio, que se da en la casilla Valor final y Coeficiente Objetivo columnas. Los intervalos se indican en las columnas Incremento permitido y Disminución permitida.

Por ejemplo, Solver encontraría el mismo número de unidades a fabricar si aumentas el valor del Modelo A en 1140€ o lo disminuyes en 180€.

Visualizar datos con formato condicional

Si creas tablas de sensibilidad unidireccionales y bidireccionales, formatear los resultados puede ser un cambio de juego para detectar rápidamente la información relevante y mejorar la toma de decisiones. El formato condicional es clave para crear umbrales importantes y mapas de calor convincentes. 

Volvamos a nuestra tabla de dos direcciones. En lugar de mostrar la tabla en celdas blancas, podemos utilizar el formato condicional para crear un mapa de calor multicolor de una escala de dos colores, estableciendo un umbral personalizado, como se muestra en el GIF.

Integrar la IA y el machine learning

Veamos brevemente cómo la IA y el machine learning pueden ayudarte a mejorar tu análisis de sensibilidad.

Análisis de sensibilidad mejorado con IA

Hasta ahora, hemos visto cómo realizar manualmente un análisis de sensibilidad. Sin embargo, a medida que aumenta la complejidad de tus modelos, realizar e interpretar los resultados del análisis de sensibilidad puede llevar mucho tiempo. 

Aquí es donde entra en escena la IA. Con la IA y el machine learning, puedes dejar que las máquinas hagan los cálculos cada vez que se cumplan determinadas condiciones e insertar los resultados en cuadros de mando convincentes. 

Una vez que conozcas los cálculos que quieres realizar, tu siguiente paso es definir claramente una canalización de IA para agilizar y automatizar el proceso. 

¿Tienes curiosidad por saber cómo hacerlo? Consulta nuestro tutorial sobre machine learning, pipelines, despliegue y MLOps.

Simulaciones Monte Carlo

La Simulación de Montecarlo es una técnica matemática utilizada para modelizar la probabilidad de distintos resultados en un proceso que no puede predecirse fácilmente debido a la intervención de variables aleatorias.

El problema se modela primero mediante una distribución de probabilidad para cada variable que tiene incertidumbre inherente. A continuación, se extraen grandes cantidades de muestras aleatorias de estas distribuciones de probabilidad, y estas muestras se utilizan para calcular los resultados. 

Este proceso se repite muchas veces para crear una distribución de posibles resultados, que puede analizarse estadísticamente para proporcionar predicciones sobre cómo se comportará un sistema. Monte Carlo ayuda a analizar la incertidumbre de forma más exhaustiva que las tablas de sensibilidad tradicionales.

Puedes aprenderlo todo sobre esta potente técnica en nuestra Guía de Simulación Monte Carlo en Excel.

Dada la naturaleza de la simulación de Montecarlo, podrías utilizarla para sobrecargar tu análisis de sensibilidad y encontrar qué variables tienen el impacto más significativo en el resultado. 

En nuestro ejemplo de la guitarra, podemos mantener constantes el precio de coste y las unidades vendidas y cambiar el precio unitario para comprender los cambios en las estimaciones. A continuación, repite el mismo proceso para las dos variables restantes, una a una. Finalmente, esta técnica ayuda a comprender en qué variable hay que centrar los esfuerzos para mejorar la precisión.

Buenas prácticas y errores comunes

Aunque Excel hace que el análisis de sensibilidad sea extremadamente fácil y accesible, todavía hay algunos escollos comunes que hay que tener en cuenta. Aquí tienes algunas cosas que debes tener en cuenta.

Diseñar modelos robustos

Como has visto en este tutorial, para que funcione el análisis de sensibilidad, Excel debe conocer las relaciones entre las variables de entrada y de salida. Esto requiere que utilices las fórmulas con prudencia, proporcionando referencias de celdas y rangos claramente definidos. 

Si quieres añadir nuevos valores a las tablas de datos, una buena práctica para evitar posibles problemas durante el recálculo es utilizar tablas de Excel, ya que son intrínsecamente dinámicas, lo que significa que se autoexpanden cuando se añaden nuevas filas o columnas. 

Esto te ayudará a realizar los recálculos de fórmulas adecuados para mantener la información en el formato correcto. 

Mitigar los errores y garantizar la precisión

Aunque tus análisis de sensibilidad estén claramente definidos, debes verificar tus resultados cuidadosamente. Las pruebas manuales y los gráficos son excelentes opciones para visualizar tus resultados y comprobar si hay valores atípicos o anomalías en los resultados.  

Sin embargo, es importante recordar que el análisis de sensibilidad puede simplemente no funcionar para tu caso de uso. Este análisis se basa en una serie de supuestos simples, como la independencia de las variables de entrada, la existencia de relaciones lineales y la naturaleza estática de las variables de entrada. 

Sin embargo, tu modelo puede ser más complejo y verse afectado por factores externos que el análisis de sensibilidad pasa por alto por naturaleza. Por eso, a pesar de sus ventajas, es importante tener un conocimiento exhaustivo de tu modelo, más allá de la sensibilidad, para garantizar un análisis correcto.

Conclusión

Enhorabuena por haber llegado hasta el final de este tutorial. El análisis de sensibilidad es un potente análisis hipotético que puede cambiar las reglas del juego en tu proceso de toma de decisiones. Sólo existen análisis hipotéticos para sustituir a tus modelos financieros, como Goal Seek o Scenario Manager. Afortunadamente, DataCamp está aquí para ayudarte. Consulta nuestros cursos y materiales de Excel:

• Análisis de datos en Excel
• Modelización financiera en Excel 
• Fundamentos de Excel 
• Hoja de trucos de manipulación de datos en Excel 
• Análisis de datos en Google Sheets 
• Funciones avanzadas de Excel 
• Cómo calcular intervalos de confianza en Excel 
• Hoja de trucos de fórmulas de Excel 
• Cómo calcular el Coeficiente de Variación en Excel 
• Simulación Monte Carlo en Excel: Guía completa