Regresión lineal simple en Excel para pronosticar demanda: Guía
Esta guía técnica está diseñada para profesionales de la ingeniería industrial, analistas de datos y gestores de operaciones que necesitan transformar datos históricos de ventas en pronósticos de demanda fiables utilizando la herramienta de Regresión Lineal Simple en Microsoft Excel.
Definición del problema
En el ámbito de la gestión de la cadena de suministro y el control de inventarios, la predicción de la demanda es crítica. Un pronóstico inexacto conduce a dos problemas operativos graves: stockouts (pérdida de ventas por falta de producto) o sobreinventario (aumento de costos de almacenamiento y obsolescencia).
Para este caso práctico, trabajaremos en una empresa de distribución de componentes electrónicos. Queremos pronosticar la demanda futura (Cantidad Vendida) basándonos en una variable externa que sabemos que influye significativamente: el Gasto en Publicidad (en miles de euros). La hipótesis es que, a mayor inversión publicitaria, mayor será la demanda de nuestros productos estrella.
Objetivo Operativo: Determinar la relación lineal entre el Gasto en Publicidad (Variable Independiente, $X$) y la Demanda de Unidades (Variable Dependiente, $Y$) para proyectar cuántas unidades venderemos si invertimos un presupuesto específico en publicidad el próximo trimestre.
Explicación técnica
La Regresión Lineal Simple es una técnica de estadística inferencial que busca modelar la relación entre dos variables: una dependiente ($Y$) y una independiente ($X$). El modelo matemático que buscamos estimar es el de una línea recta:
$$Y = beta_0 + beta_1 X + epsilon$$
- $Y$: Variable Dependiente (Demanda).
- $X$: Variable Independiente (Gasto en Publicidad).
- $beta_0$: Intercepto (la demanda esperada cuando el gasto publicitario es cero).
- $beta_1$: Pendiente (el cambio promedio en la demanda por cada unidad de cambio en el gasto publicitario).
- $epsilon$: Término de error (la variación no explicada por el modelo).
En Excel, utilizaremos la herramienta de «Análisis de Datos» (o la función de Gráficos de Dispersión con línea de tendencia) para calcular automáticamente los coeficientes $beta_0$ y $beta_1$ (Intercepto y Pendiente), así como métricas de bondad de ajuste como el $R^2$ (Coeficiente de Determinación).
Guía paso a paso
Paso 1: Preparación y Carga de Datos Históricos
Primero, debemos estructurar nuestros datos históricos en una hoja de cálculo de Excel. Crearemos un conjunto de 10 meses de datos simulados.
Acción: Abre una nueva hoja de Excel. En la celda A1, escribe «Mes», en B1, «Gasto Publicidad (X)», y en C1, «Demanda (Y)». Ingresa los datos simulados en las filas 2 a 11.
Por qué: Organizar los datos en columnas claras es fundamental para que Excel identifique correctamente qué variable es la predictora ($X$) y cuál es la a ser predicha ($Y$).
Resultado: Una tabla lista para el análisis.
| A | B | C | |
|---|---|---|---|
| 1 | Mes | Gasto Publicidad (X) | Demanda (Y) |
| 2 | Ene | 10 | 120 |
| 3 | Feb | 15 | 145 |
| 4 | Mar | 22 | 180 |
| 5 | Abr | 28 | 210 |
| 6 | May | 35 | 255 |
| 7 | Jun | 40 | 280 |
| 8 | Jul | 45 | 305 |
| 9 | Ago | 50 | 330 |
| 10 | Sep | 55 | 360 |
| 11 | Oct | 60 | 390 |
Paso 2: Creación del Gráfico de Dispersión
La visualización es el primer paso para confirmar si existe una relación lineal aparente.
Acción: Selecciona el rango de datos de Gasto Publicidad (B2:B11) y Demanda (C2:C11). Ve a la pestaña Insertar y selecciona el gráfico de Dispersión (Scatter).
Por qué: El gráfico de dispersión muestra cada par de datos $(X, Y)$ como un punto. Si los puntos forman una línea ascendente o descendente clara, la regresión lineal es aplicable.
Resultado: Un gráfico donde los puntos representan la relación entre la inversión y la demanda.
Paso 3: Añadir la Línea de Tendencia y Obtener la Ecuación
Este es el paso clave donde Excel calcula los coeficientes de regresión.
Acción: Haz clic derecho sobre cualquiera de los puntos del gráfico. Selecciona Agregar línea de tendencia…. En el panel lateral que aparece, asegúrate de seleccionar Lineal. Crucialmente, marca las casillas Presentar ecuación en el gráfico y Presentar el valor R cuadrado en el gráfico.
Por qué: Al marcar estas opciones, Excel calcula automáticamente la mejor línea recta que se ajusta a los datos (la línea de regresión) y nos proporciona la fórmula $Y = mX + b$ y qué tan bien se ajusta ($R^2$).
Resultado: El gráfico se actualiza mostrando la ecuación de la recta (ej: $Y = 10.5 + 5.8X$) y el valor $R^2$ (ej: $0.985$).
Paso 4: Pronóstico de Demanda (Aplicación del Modelo)
Ahora que tenemos la ecuación, podemos pronosticar. Supongamos que el departamento de marketing planea invertir 70 (miles de euros) el próximo mes.
Acción:
Por qué: Estamos utilizando el modelo estadístico aprendido de los datos históricos para extrapolar un valor futuro.
Resultado:
| Concepto | Valor Calculado | Fórmula Aplicada | |
|---|---|---|---|
| 1 | Intercepto ($beta_0$) | 10.5 | (Obtenido del gráfico) |
| 2 | Pendiente ($beta_1$) | 5.8 | (Obtenido del gráfico) |
| 3 | Gasto Futuro ($X$) | 70 | (Input del negocio) |
| 4 | Demanda Pronosticada ($Y$) | 416.5 | =10.5 + 5.8*70 |
Ejercicio propuesto
Para consolidar el aprendizaje, realiza el siguiente ejercicio de extensión:
Escenario: La gerencia de operaciones necesita saber qué nivel de inversión publicitaria ($X$) debe alcanzar para garantizar una demanda mínima de 350 unidades ($Y$).
Tarea: Utilizando la ecuación de regresión que obtuviste en el Paso 3 ($Y = 10.5 + 5.8X$), despeja $X$ para $Y=350$.
Desarrollo (Para verificar):
$350 = 10.5 + 5.8X$
$339.5 = 5.8X$
$X = 339.5 / 5.8 approx 58.53$
Conclusión del ejercicio: Se requeriría una inversión publicitaria de aproximadamente 58.53 (miles de euros) para alcanzar la meta de 350 unidades.
Errores habituales
La aplicación de modelos estadísticos puede ser engañosa si no se entienden sus limitaciones. Aquí se detallan tres errores comunes:
- Confundir Correlación con Causalidad: El error más grave. El modelo solo indica que existe una relación estadística fuerte entre $X$ y $Y$ (correlación). No prueba que la publicidad *cause* directamente el aumento de la demanda; podría haber una tercera variable (ej. estacionalidad, crisis económica) que afecte a ambas. Solución: Siempre validar el modelo con conocimiento de negocio y considerar variables de confusión.
- Extrapolación Excesiva: Intentar pronosticar demandas para niveles de gasto publicitario ($X$) que están muy lejos del rango de los datos históricos utilizados (ej. si los datos van de 10 a 60, pronosticar para $X=500$). Solución: Limitar los pronósticos a un rango razonable y conocido de la operación.
- Ignorar el $R^2$ (Bondad de Ajuste): Asumir que un modelo es perfecto solo porque se ajusta bien visualmente. Si el $R^2$ es bajo (ej. 0.40), significa que solo el 40% de la variación en la demanda es explicada por la publicidad; el 60% restante es ruido o factores no modelados. Solución: Exigir un $R^2$ aceptable (generalmente > 0.70) antes de confiar plenamente en el pronóstico.
Deja una respuesta