1. Introducción y Contexto
En este ejercicio práctico, aprenderás a utilizar la función PRONOSTICO.LINEAL de Excel para predecir la demanda futura de productos y calcular el MAPE (Error Porcentual Absoluto Medio), una métrica clave para evaluar la precisión de los pronósticos en ingeniería industrial.
2. Funciones de Excel que utilizaremos
PRONOSTICO.LINEAL
Sintaxis: =PRONOSTICO.LINEAL(x, conocido_y, conocido_x)
- x: El valor para el cual deseas predecir (mes 13)
- conocido_y: Rango de valores conocidos (demanda histórica)
- conocido_x: Rango de valores x conocidos (meses 1-12)
Propósito: Calcula o predice un valor futuro utilizando valores existentes mediante regresión lineal.
MAPE (Mean Absolute Percentage Error)
Fórmula: MAPE = (1/n) × Σ|((Real - Pronosticado)/Real)| × 100
En Excel: =PROMEDIO(ABS((Real-Pronosticado)/Real))*100
Interpretación: Valores menores al 10% indican excelente precisión; 10-20% buena; 20-50% aceptable; >50% pobre.
3. Datos de Ejemplo – Demanda Histórica
Los siguientes datos representan la demanda mensual de un producto durante 12 meses:
| Mes | Demanda Real | Descripción | Tendencia |
|---|---|---|---|
| 1 | 120 | Enero | ↑ |
| 2 | 135 | Febrero | ↑ |
| 3 | 148 | Marzo | ↑ |
| 4 | 162 | Abril | ↑ |
| 5 | 175 | Mayo | ↑ |
| 6 | 188 | Junio | ↑ |
| 7 | 195 | Julio | ↑ |
| 8 | 210 | Agosto | ↑ |
| 9 | 225 | Septiembre | ↑ |
| 10 | 238 | Octubre | ↑ |
| 11 | 250 | Noviembre | ↑ |
| 12 | 265 | Diciembre | ↑ |
4. Desarrollo Paso a Paso
1 Configuración de Datos en Excel
Coloca los datos en el rango A1:D13 de tu hoja de Excel:
2 Calcular el Pronóstico para el Mes 13
En la celda E14, introduce la fórmula:
Resultado esperado: 278.5
3 Calcular el MAPE
Primero, calcula el error para cada mes en la columna F:
Luego, calcula el MAPE promedio:
5. Variantes del Ejercicio
Variante 1: Umbral Dinámico de Precisión
Modifica el ejercicio para incluir un umbral dinámico que determine si el pronóstico es aceptable:
Condiciones:
- MAPE < 10%: Excelente precisión
- MAPE 10-20%: Buena precisión
- MAPE > 20%: Requiere revisión
Variante 2: Manejo de Errores con SI.ERROR
Implementa manejo de errores para evitar #DIV/0! cuando la demanda real es cero:
Ventaja: Evita errores de cálculo cuando hay valores cero o nulos en los datos.
6. Visualización de Resultados
Pronóstico vs Real
| Mes | Real | Pronosticado | Error % |
|---|---|---|---|
| 13 | – | 278.5 | – |
Métricas de Precisión
MAPE: 3.2%
Precisión: 96.8%
Calificación: Excelente
7. Ejercicio Propuesto
Situación: Eres el responsable de planificación de producción en una fábrica de muebles. Tienes los siguientes datos de demanda trimestral (en unidades):
| Trimestre | Demanda | Estación |
|---|---|---|
| Q1 | 450 | Primavera |
| Q2 | 520 | Verano |
| Q3 | 480 | Otoño |
| Q4 | 550 | Invierno |
Tareas a realizar:
- Crear una tabla con los datos en Excel (rango A1:B5)
- Calcular el pronóstico para el Q5 usando PRONOSTICO.LINEAL
- Calcular el MAPE si el valor real del Q5 fue 580 unidades
- Crear un gráfico de tendencia
- Evaluar si el modelo es aceptable (MAPE < 15%)
Solución esperada:
Pronóstico Q5: 505 unidades
MAPE: 12.9%
Evaluación: Aceptable
8. Consejos y Mejores Prácticas
✅ Hacer
- Verificar que los datos estén completos
- Usar SI.ERROR para evitar errores
- Validar el modelo con datos de prueba
- Documentar las fórmulas utilizadas
❌ Evitar
- Ignorar valores atípicos
- Usar pronósticos sin validación
- Olvidar actualizar rangos
- No verificar la precisión del modelo
Deja una respuesta