Simulación de impacto de cambios en precios con tabla de datos de dos variables
Este instructivo técnico está diseñado para profesionales de la ingeniería industrial, analistas de negocio y gestores de operaciones que necesitan modelar escenarios de sensibilidad de precios utilizando las capacidades de simulación de Excel.
Definición del problema
En el sector de la distribución de componentes electrónicos, una empresa de manufactura (TechParts S.A.) enfrenta la necesidad de optimizar su estrategia de precios para un conjunto de tres componentes clave (Resistor R1, Capacitor C2, LED L3). La demanda de estos componentes no es lineal; está influenciada por dos variables principales: el Precio Unitario del componente y el Nivel de Inventario actual en almacén. Si se incrementa el precio, la demanda puede caer, pero si el inventario es muy bajo, la empresa podría perder ventas potenciales. El reto operativo es determinar el nuevo punto de precio óptimo que maximice el margen de beneficio total, considerando cómo el cambio de precio afecta la demanda proyectada en función del stock disponible.
Explicación técnica
Para abordar este problema, utilizaremos una combinación de modelado lineal y herramientas de simulación en Excel. La lógica matemática se basa en una función de demanda simplificada:
$$Demanda = K – (m_1 times Precio) – (m_2 times Inventario)$$
Donde $K$, $m_1$ y $m_2$ son constantes determinadas por el análisis histórico.
En Excel, implementaremos esto mediante una tabla de datos (Data Table) o, para escenarios más complejos, utilizando la función BUSCARX (o VLOOKUP) junto con una tabla de escenarios. Dado que estamos simulando el impacto de un cambio en una variable (Precio) manteniendo la otra (Inventario) constante, la Tabla de Datos es la herramienta más eficiente para generar rápidamente múltiples resultados de sensibilidad.
Guía paso a paso
Paso 1: Configuración de los Datos Base (Datos Históricos y Parámetros)
Primero, debemos establecer los datos iniciales y las constantes del modelo. Crearemos una hoja de cálculo con los parámetros fijos y la tabla de escenarios inicial.
Acción: Crea una nueva hoja llamada «DatosBase». Introduce los siguientes datos en las celdas especificadas.
Por qué: Esto establece el punto de partida del análisis y define las relaciones matemáticas del negocio.
Resultado: Una tabla estructurada con los parámetros de entrada.
| A | B | C | |
|---|---|---|---|
| 1 | Componente | Precio Base (€) | Inventario Actual (Unidades) |
| 2 | R1 | 1.50 | 5000 |
| 3 | C2 | 0.80 | 12000 |
| 4 | L3 | 0.30 | 25000 |
| 5 | Constante K (Demanda Máx) | 10000 | |
| 6 | Coef. Precio ($m_1$) | 150 | |
| 7 | Coef. Inventario ($m_2$) | 0.001 |
Paso 2: Definición del Escenario de Simulación (Tabla de Datos)
Vamos a simular el impacto de cambiar el precio del componente R1 (fila 2) en un rango de precios, manteniendo el inventario fijo en 5000 unidades.
Acción: En una nueva hoja llamada «Simulacion», configura la siguiente estructura. En la celda B2, introduce el precio base de R1 (1.50). En la columna A, lista los precios que deseas probar (ej. 1.00, 1.25, 1.50, 1.75, 2.00).
Por qué: Esta columna de entrada (variable independiente) es lo que Excel usará para iterar en la simulación.
Resultado: Una lista de precios de prueba lista para ser evaluada.
| A | B | C | |
|---|---|---|---|
| 1 | Precios a Probar | Precio Base R1 | Demanda Proyectada |
| 2 | 1.00 | 1.50 | |
| 3 | 1.25 | 1.50 | |
| 4 | 1.50 | 1.50 | |
| 5 | 1.75 | 1.50 | |
| 6 | 2.00 | 1.50 |
Paso 3: Creación de la Fórmula de Cálculo (La Función de Impacto)
Antes de usar la Tabla de Datos, necesitamos una celda de referencia que calcule la demanda para un precio dado. Usaremos la fórmula: $Demanda = K – (m_1 times Precio) – (m_2 times Inventario)$.
Acción: En una celda auxiliar (ej. D1), escribe la fórmula que calcula la demanda usando los valores de la hoja «DatosBase» y un precio de prueba (ej. 1.50 en B2 de la hoja «Simulacion»). La fórmula debe ser: =DatosBase!$E$5 - (DatosBase!$E$6 * B2) - (DatosBase!$E$7 * DatosBase!$E$3).
Por qué: Esta fórmula encapsula la lógica de negocio. Al fijar las referencias absolutas ($), nos aseguramos de que al arrastrar o usar la Tabla de Datos, Excel siempre apunte a los parámetros correctos.
Resultado: Una celda que muestra la demanda esperada para el precio introducido.
Paso 4: Ejecución de la Tabla de Datos (Simulación)
Este es el paso clave donde Excel realiza la simulación iterativa.
Acción: Selecciona el rango completo que incluye los precios a probar y la celda de resultado (ej. A2:B6 y la celda D1). Ve a la pestaña Datos y selecciona Tabla de Datos (Data Table). En el cuadro de diálogo, en la celda de «Columna de entrada», selecciona la celda que contiene el precio que deseas variar (ej. B2 de la hoja «Simulacion»). Haz clic en Aceptar.
Por qué: Excel toma cada valor de la columna de entrada (A2:A6), lo sustituye en la fórmula de referencia (D1), recalcula el resultado y lo coloca en la columna adyacente (C2:C6).
Resultado: La columna C se llenará automáticamente con la demanda proyectada para cada precio probado, completando la simulación.
| A | B | C (Resultado Simulado) | |
|---|---|---|---|
| 1 | Precios a Probar | Precio Base R1 | Demanda Proyectada |
| 2 | 1.00 | 1.50 | 9850 |
| 3 | 1.25 | 1.50 | 9500 |
| 4 | 1.50 | 1.50 | 9150 |
| 5 | 1.75 | 1.50 | 8800 |
| 6 | 2.00 | 1.50 | 8450 |
Ejercicio propuesto
Extiende el análisis anterior para el componente C2. En lugar de simular el impacto del precio, simula el impacto del Nivel de Inventario (manteniendo el precio base de 0.80 €). Crea una nueva tabla de datos donde la columna de entrada sean niveles de inventario (ej. 5000, 10000, 15000, 20000) y la celda de resultado sea la demanda proyectada. Luego, calcula el margen de beneficio total para cada escenario de inventario y determina qué nivel de inventario maximiza el beneficio.
Errores habituales
- Referencia de Celdas Incorrecta en la Fórmula Base: El error más común es no usar referencias absolutas ($) en la fórmula de cálculo (Paso 3). Si no se usan, al ejecutar la Tabla de Datos, Excel intentará arrastrar la fórmula incorrectamente, apuntando a celdas aleatorias en lugar de a los parámetros fijos de la hoja «DatosBase». Solución: Siempre utiliza
$A$1para fijar celdas de parámetros constantes. - Confusión en la Columna de Entrada de la Tabla de Datos: Al configurar la Tabla de Datos (Paso 4), es vital saber si estás variando una fila o una columna. Si la variable que quieres probar está en una columna (como los precios en A2:A6), debes seleccionar la celda de la columna de entrada en el campo «Columna de entrada» del asistente. Solución: Verifica que la variable que deseas iterar esté en la columna que seleccionaste como entrada.
- Ignorar la Restricción de Negocio: El modelo matemático puede arrojar demandas negativas. En un contexto real, la demanda nunca puede ser menor a cero. Solución: Después de la simulación, aplica una función
MAX(0, [Resultado])a la columna de demanda simulada para asegurar que los resultados sean físicamente posibles.
Deja una respuesta