Guía Práctica de Análisis de Sensibilidad en Excel: De la Evolución Manual a la Inteligente

Al diseñar modelos en hojas de cálculo, a menudo nos preguntamos cómo cambiar el valor de una variable afectaría el resultado final. Este proceso se puede realizar ejecutando un análisis de sensibilidad en Excel.

Si eres un profesional de datos que trabaja en modelización financiera o en procesos de toma de decisiones, entonces es crucial saber cómo realizar un análisis de sensibilidad.

En este artículo, profundizaremos en el análisis de sensibilidad. Cubriremos los conceptos básicos de esta útil técnica de "qué pasaría si", cómo realizar e interpretar los resultados del análisis de sensibilidad básico y avanzado en Excel, y finalmente, discutiremos el papel de la IA y el aprendizaje automático para permitir un análisis de sensibilidad más potente, junto con las mejores prácticas y errores comunes.

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

El enfoque más común para el análisis de sensibilidad es examinar cómo un cambio en una variable afecta el resultado final mientras se mantienen constantes todos los demás factores. Este proceso se repite para todas las variables consideradas. Ayuda a identificar los impulsores más impactantes en un modelo, permitiendo a las empresas centrarse en los factores clave que afectan su desempeño.

El análisis de sensibilidad ayuda a las empresas a priorizar riesgos y oportunidades críticos identificando qué factores tienen el mayor impacto en el desempeño. Dada su naturaleza, el análisis de sensibilidad es común en finanzas, negocios y gestión de proyectos, por ejemplo, para calcular cómo las tasas de interés podrían cambiar con el tiempo o para comprender diferentes tendencias de crecimiento.

Además, el análisis de sensibilidad se utiliza a menudo junto con el análisis de escenarios, que típicamente analiza combinaciones de variables simultáneamente. Ambos análisis de "qué pasaría si" estudian cómo reaccionará la variable dependiente a ciertos insumos. Sin embargo, el análisis de escenarios es para un "escenario" determinado, mientras que el análisis de sensibilidad es más abierto, ya que ofrece un rango de entradas y valores.

El objetivo del análisis de sensibilidad es comprender cómo reacciona la variable dependiente a un rango de valores de entrada (llamadas variables independientes).

Al incorporar esta técnica en el proceso de toma de decisiones, las organizaciones pueden ajustar proactivamente sus estrategias y mejorar su resiliencia ante la incertidumbre.

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

Aquí hay un ejemplo de sensibilidad al precio para la oferta y la demanda. La oferta y la demanda son las variables independientes en los ejes, y el precio es la variable dependiente, que ocupa los valores dentro de la tabla.

En las siguientes secciones, utilizaremos tablas de análisis de sensibilidad de Excel similares.

Comenzando con el Análisis de Sensibilidad en Excel

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

Herramientas y Características Tradicionales en Excel

Excel es una herramienta conveniente para crear modelos matemáticos, incluido el análisis de "qué pasaría si". El análisis de "qué pasaría si" consiste esencialmente en crear fórmulas para explorar preguntas como el análisis de sensibilidad.

La mayoría de las versiones recientes de Excel vienen con una pestaña Datos que ofrece numerosas herramientas y posibilidades para trabajar con datos. Esto incluye el botón Análisis de "Qué pasaría si" en la cinta. Contiene tres herramientas. El botón Tabla de datos está diseñado específicamente para crear tablas de análisis de sensibilidad.

Para realizar un análisis de escenarios, simplemente haz clic en el botón Administrador de escenarios.

También usaremos Solver, un complemento que se puede activar desde la pestaña Programador.

Solver se utiliza en el análisis de "qué pasaría 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 en la hoja de trabajo.

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

• Ve a Archivo > Opciones
• Selecciona Personalizar la cinta de opciones en el panel izquierdo
• En "Pestañas principales" en la columna derecha, marca la casilla para Programador

Realizando un Análisis de Sensibilidad de Una Variable

Ahora que nuestras herramientas están listas, comencemos creando un simple análisis de sensibilidad de una variable en Excel.

Método Tradicional: Configurando Tu Modelo

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

En este tutorial, usaremos el análisis de sensibilidad para estudiar cómo cambiará la ganancia neta de una empresa de venta de guitarras si alteramos los valores de algunas variables de entrada, como la cantidad de guitarras vendidas, el precio por guitarra o el costo de producción.

A continuación, puedes ver la tabla que contiene las variables de entrada y salida.

Al usar Tablas de Datos para el análisis de sensibilidad, es importante vincular la celda de salida a la variable de entrada para que Excel comprenda la relación entre las variables del modelo. Este proceso se realiza a través de fórmulas. En nuestro caso:

• Los ingresos se calculan multiplicando el precio unitario por la cantidad de unidades vendidas (=B2*B4)
• El costo de ventas se calcula multiplicando el costo de producción unitario por la cantidad de unidades vendidas (=B3*B4)
• La ganancia se calcula restando el costo de ventas de los ingresos (=B7-B8)

Método Tradicional: Creando una Tabla de Datos de Una Vía

Podrías preguntarte cómo cambiaría la ganancia si aumentamos o disminuimos el precio unitario mientras mantenemos constante el costo de producción y la cantidad de unidades vendidas. Cuando analizamos cómo cambia una variable de salida a medida que cambiamos los valores de una variable de entrada, necesitamos una llamada tabla de datos de una vía.

El GIF a continuación demuestra cómo crear una tabla de datos de una vía en Excel. Como puedes ver, la tabla recalcula automáticamente la ganancia por cada unidad vendida. Naturalmente, la ganancia al vender 250 unidades es la misma que en los parámetros originales (es decir, €50,000).

Método Más Inteligente: Análisis de Una Variable con Excelmatic

Aunque la función Tabla de datos es potente, el proceso de configuración requiere referencias de celda precisas y múltiples pasos. Para modelos más complejos o dinámicos, esto puede volverse tedioso y propenso a errores.

Excelmatic ofrece una alternativa más intuitiva. Como un Agente de IA para Excel, te permite realizar análisis utilizando lenguaje natural.

Para lograr el mismo objetivo, simplemente:

1. Sube tu archivo de Excel que contiene tu modelo a Excelmatic.
2. Indica tu requisito en lenguaje simple: "Crea una tabla de análisis de sensibilidad para mostrar cómo cambia la Ganancia (celda B9) cuando las Unidades Vendidas (celda B4) varían de 150 a 500 en incrementos de 50."

Excelmatic entiende instantáneamente la estructura de tu modelo y genera automáticamente exactamente los mismos resultados que una tabla de datos de una vía creada manualmente, sin requerir que configures manualmente la tabla, vincules celdas o navegues por ningún menú. La ventaja de este enfoque radica en su velocidad y simplicidad, permitiéndote centrarte en analizar los resultados en lugar de operar la herramienta.

También podrías querer examinar cómo cambian diferentes variables de salida con las ventas unitarias. En el método tradicional, necesitarías ajustar la configuración de la Tabla de datos.

Con Excelmatic, simplemente modificas ligeramente tu solicitud: "Muestra cómo cambian tanto la Ganancia (B9) como el Costo de Ventas (B8) a medida que las Unidades Vendidas (B4) varían de 150 a 500." La IA maneja todos los detalles por ti.

Realizando un Análisis de Sensibilidad de Dos Variables

Método Tradicional: Creando una Tabla de Datos de Dos Vías

En lugar de analizar cómo una salida se ve afectada por una entrada, también puedes crear una tabla de dos variables para analizar el impacto de dos variables de entrada. Digamos que quieres saber cómo cambiaría la ganancia si modificáramos tanto la cantidad de guitarras vendidas como el precio por guitarra.

Primero, necesitas crear una tabla bidimensional donde las filas sean el rango para los precios unitarios y las columnas sean el rango para las ventas unitarias. Luego, en la esquina superior izquierda de la tabla, selecciona la variable que quieres estudiar. Finalmente, en la tabla de variables de entrada, selecciona las referencias de celda para las variables independientes (es decir, el precio unitario y la cantidad de unidades vendidas).

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

Método Más Inteligente: Análisis de Dos Variables con Excelmatic

Configurar una tabla de datos de dos vías es más complejo que una tabla de una vía, ya que requiere una referencia adecuada de entradas y salidas en filas, columnas y la celda de la esquina.

Con Excelmatic, este proceso también se simplifica a una sola instrucción simple. Después de subir tu archivo, puedes preguntar directamente:

"Genera una tabla de sensibilidad de dos vías que muestre cómo cambia la Ganancia según las Unidades Vendidas (de 150 a 500) y el Precio por Unidad (de €150 a €400)."

Excelmatic construye toda la tabla bidimensional por ti, llenando todos los valores calculados, eliminando así posibles errores de referencia de la configuración manual. Si quieres analizar otra salida, como "Ingresos", simplemente haz una nueva pregunta: "Ahora, muestra la misma tabla pero para los Ingresos." Este flujo de trabajo conversacional hace que el análisis complejo sea sencillo.

Interpretando los Resultados del Análisis de Sensibilidad en Excel

Ahora que tienes la tabla de datos, el paso final es interpretar los resultados para obtener información sobre cómo los cambios en las entradas afectan la salida.

Analizando la Salida de la Tabla de Datos

Basándonos en este análisis de sensibilidad, podemos tomar decisiones informadas sobre la ganancia. Aquí hay algunas ideas:

• Si aumentamos el precio de la guitarra a €350, solo necesitamos vender 200 guitarras para lograr la ganancia actual de €50,000.
• Si disminuimos el precio de la guitarra a €200, necesitamos vender 500 unidades para alcanzar la ganancia actual.
• Si reducimos la cantidad de guitarras vendidas a 150, no podemos lograr la ganancia actual a menos que aumentemos el precio de la guitarra por encima de €400.

La interpretación del análisis dependerá de las necesidades específicas de tu empresa. Hacer preguntas como cuántas guitarras puedes producir, cuál es el nivel mínimo de ganancia que necesitas mantener para una empresa saludable y qué puedes hacer para reducir el costo de producción de la guitarra es esencial para extraer información significativa del análisis de sensibilidad.

Método Tradicional: Optimización y Análisis de Sensibilidad con Solver

Cuando realizas un análisis de sensibilidad, obtienes un rango de salidas potenciales basadas en cambios en las variables de entrada de tu modelo. Sin embargo, podrías querer aprender más sobre el funcionamiento interno de tu modelo.

Afortunadamente, Excel viene con una herramienta poderosa llamada Solver que puede ayudarte con esta tarea. Como se mencionó antes, Solver se utiliza en el análisis de "qué pasaría si" para encontrar un valor óptimo (máximo o mínimo) para una fórmula sujeta a restricciones, o límites, en los valores de otras celdas de fórmula en la hoja de trabajo.

Solver es más que una versión mejorada de Buscar objetivo. En primer lugar, Solver te permite calcular resultados óptimos basados en múltiples variables y te permite incluir restricciones en tu modelo. Más importante aún, Solver también viene con una función de sensibilidad opcional que te permite ver cómo cambia la solución óptima cuando alteras los coeficientes de tu modelo.

Para ilustrar el poder de Solver, volvamos a nuestra empresa de guitarras. Imagina que la empresa produce dos modelos de guitarra (Modelo A y Modelo B), cada uno requiriendo una cierta cantidad de madera de caoba y cedro. Cada modelo tiene un precio diferente.

La empresa quiere saber cuántas unidades de cada modelo producir para maximizar su ganancia, dada la disponibilidad actual de madera de caoba y cedro.

En el GIF a continuación, mostramos cómo usar Solver para encontrar los valores óptimos de unidades para maximizar los ingresos mientras se utilizan restricciones de disponibilidad de madera y se asegura que las unidades sean números enteros.

Una vez que agregamos todos los valores de los parámetros, Solver estima que 48 unidades del Modelo A y 9 unidades del Modelo B son la mejor combinación para maximizar la ganancia (€17,591). Podemos hacer clic en Sensibilidad antes de cerrar el cuadro de diálogo de Solver, y Excel creará una nueva hoja con un informe de sensibilidad.

El informe de sensibilidad nos da información valiosa para estimar cómo cambiaría la ganancia si alteráramos las variables de entrada. El Precio Sombra indica cuánto aumentaría la ganancia si agregaras una unidad más del recurso restringido. En este ejemplo, cada metro cuadrado adicional de caoba aumenta la ganancia en €98, mientras que el cedro aumenta la ganancia en €103, destacando que el cedro tiene un mayor retorno potencial de la inversión.

Método Más Inteligente: Optimización Avanzada con Excelmatic

Las capacidades de Solver son potentes, pero su interfaz y configuración pueden ser complejas y poco intuitivas para muchos usuarios. Necesitas definir con precisión la celda objetivo, las celdas variables y las restricciones.

Excelmatic transforma este complejo problema de optimización en una simple conversación también. Solo describes tu objetivo:

"Maximiza la Ganancia (celda G10) cambiando las Unidades del Modelo A (G4) y del Modelo B (G5), sujeto a las restricciones de que la caoba total utilizada (D14) no puede exceder 2000, el cedro total utilizado (E14) no puede exceder 3000, y las unidades deben ser números enteros. Luego, genera un informe de sensibilidad."

Excelmatic analiza tu solicitud, ejecuta el algoritmo de optimización, proporciona la combinación de producción óptima y genera un informe de sensibilidad fácil de entender. Esto no solo ahorra tiempo