El pronóstico lineal es la técnica de forecasting más usada en Excel. Con ella puedes proyectar ventas, demanda, inventario, costos o cualquier variable que muestre tendencia en el tiempo. En esta guía construimos un modelo completo: desde la regresión simple hasta PRONOSTICO.LINEAL, TENDENCIA, PENDIENTE, INTERSECCION.EJE y ESTIMACION.LINEAL, con gráfico visual y análisis de errores (MAE, RMSE, MAPE).
Al terminar sabrás responder tres preguntas clave: ¿cuál es la tendencia de mis datos? ¿cuánto voy a vender los próximos meses? ¿qué tan fiable es mi pronóstico?
Caso 1 — Fundamentos del pronóstico lineal
Paso 1.1 — La idea en una frase
El pronóstico lineal ajusta una recta a tus datos históricos y la prolonga hacia el futuro. Esa recta tiene la forma clásica:
Donde m es la pendiente (cuánto sube o baja y por cada unidad de x), y b es la intersección con el eje Y (el valor de y cuando x = 0).
Paso 1.2 — Cuándo SÍ y cuándo NO usar pronóstico lineal
| Situación | ¿Usar? | Por qué |
|---|---|---|
| Ventas que crecen de forma sostenida | Sí | Tendencia clara y estable. |
| Demanda estacional (juguetes en Navidad) | No solo | Necesitas además estacionalidad. |
| Serie con tendencia exponencial (virus, redes sociales) | No | Usa regresión exponencial o logarítmica. |
| Datos sin tendencia, solo ruido | No | El R² será bajo y el pronóstico inútil. |
| Costos que suben con el volumen de producción | Sí | Relación lineal directa. |
Paso 1.3 — Funciones de Excel para pronóstico lineal
| Función | Devuelve | Cuándo usar |
|---|---|---|
| PRONOSTICO.LINEAL | Un valor Y para un X dado | Pronóstico puntual (mes 13, mes 14…). |
| TENDENCIA | Varios valores Y a la vez | Pronóstico de varios períodos. |
| PENDIENTE | m (pendiente) | Quieres construir la recta tú mismo. |
| INTERSECCION.EJE | b (ordenada al origen) | Junto con PENDIENTE, control total. |
| ESTIMACION.LINEAL | m, b y estadísticos | Análisis completo: R², error típico, F. |
| COEFICIENTE.R2 | R² (bondad del ajuste) | Saber qué % de la variación explicas. |
Caso 2 — Arquitectura del modelo y datos
Paso 2.1 — Planteamiento
Somos una tienda de bicicletas. Tenemos 12 meses de ventas (enero a diciembre) y queremos pronosticar los meses 13, 14 y 15 (enero, febrero, marzo del año siguiente) para planificar compras de inventario.
Paso 2.2 — Tabla de datos (Hoja 1_Datos)
| Fila | A | B | C |
|---|---|---|---|
| 1 | Mes | Período (x) | Ventas (y) |
| 2 | Enero | 1 | 120 |
| 3 | Febrero | 2 | 135 |
| 4 | Marzo | 3 | 128 |
| 5 | Abril | 4 | 150 |
| 6 | Mayo | 5 | 158 |
| 7 | Junio | 6 | 165 |
| 8 | Julio | 7 | 172 |
| 9 | Agosto | 8 | 168 |
| 10 | Septiembre | 9 | 185 |
| 11 | Octubre | 10 | 192 |
| 12 | Noviembre | 11 | 200 |
| 13 | Diciembre | 12 | 210 |
Las celdas azules son datos de entrada. X = período (1 al 12). Y = ventas en unidades.
Caso 3 — PRONOSTICO.LINEAL: un valor puntual
Paso 3.1 — Planteamiento
Queremos saber cuánto venderemos en el mes 13 (enero del próximo año). PRONOSTICO.LINEAL es la función más directa: le decimos el X futuro, los Y conocidos y los X conocidos, y nos devuelve el Y estimado.
Paso 3.2 — Fórmula
Sintaxis: =PRONOSTICO.LINEAL(x_futuro; conocido_y; conocido_x)
- x_futuro: el período que quieres predecir (13).
- conocido_y: las ventas históricas (C2:C13).
- conocido_x: los períodos históricos (B2:B13).
Paso 3.3 — Pronósticos de los meses 13, 14 y 15
Truco: si en A2:A4 escribes 13, 14 y 15, puedes hacer =PRONOSTICO.LINEAL(A2;$C$2:$C$13;$B$2:$B$13) y arrastrar. Así cambias el horizonte sin tocar las fórmulas.
Paso 3.4 — Resultados
| Fila | A (Mes futuro) | E (Pronóstico) |
|---|---|---|
| 2 | 13 (ene año 2) | 216.05 |
| 3 | 14 (feb año 2) | 223.86 |
| 4 | 15 (mar año 2) | 231.68 |
La recta es y = 7.8147·x + 114.4545
y(13) = 7.8147 × 13 + 114.4545 = 101.591 + 114.455 = 216.05 ✔
Caso 4 — TENDENCIA: varios pronósticos de golpe
Paso 4.1 — Planteamiento
Si necesitas pronosticar varios meses seguidos, TENDENCIA es más eficiente: recibe un rango de X futuros y devuelve un rango de Y pronosticados. Además, es una función matricial: hay que confirmarla con Ctrl+Shift+Enter en versiones antiguas, o simplemente Enter en Excel 365.
Paso 4.2 — Fórmula
Sintaxis: =TENDENCIA(conocido_y; conocido_x; nueva_x; [constante])
- conocido_y: ventas históricas.
- conocido_x: períodos históricos.
- nueva_x: los períodos a pronosticar (13, 14, 15).
- constante: opcional. VERDADERO (u omitido) = calcular b normalmente. FALSO = forzar b = 0 (raro, solo si sabes que la recta pasa por el origen).
Paso 4.3 — Cómo introducirla
- Selecciona primero el rango de destino: E2:E4.
- Escribe la fórmula.
- En Excel 2016 y anteriores: Ctrl+Shift+Enter (verás llaves { } alrededor de la fórmula).
- En Excel 365 y 2021: solo Enter (derrame dinámico, un solo resultado llena las 3 celdas).
TENDENCIA y PRONOSTICO.LINEAL dan exactamente el mismo resultado para cada valor individual. La diferencia es que TENDENCIA procesa rangos. Si solo necesitas un valor, usa PRONOSTICO.LINEAL; si necesitas varios de golpe, TENDENCIA.Paso 4.4 — Resultados
| Fila | A | E (TENDENCIA) |
|---|---|---|
| 2 | 13 | 216.05 |
| 3 | 14 | 223.86 |
| 4 | 15 | 231.68 |
Caso 5 — PENDIENTE + INTERSECCION.EJE: control total de la recta
Paso 5.1 — Planteamiento
Si quieres tener la recta «a la vista» — la pendiente y la ordenada al origen — para reutilizarla en varias fórmulas o para explicar el modelo, calcula m y b por separado.
Paso 5.2 — Fórmulas
Ventaja: ahora puedes escribir la ecuación «a mano» en cualquier celda: =$G$2*A2+$G$3. Es la fórmula más transparente y la que más control te da si necesitas explicar el modelo a otra persona.
Paso 5.3 — Resultados
| Fila | F | G |
|---|---|---|
| 1 | Concepto | Valor |
| 2 | Pendiente (m) | 7.8147 |
| 3 | Intersección (b) | 114.4545 |
Recta del modelo: y = 7.8147·x + 114.4545
Interpretación de b: si extrapoláramos al mes 0, el modelo predeciría 114 unidades. En la práctica, b rara vez tiene significado físico: solo sirve para completar la recta.
Caso 6 — ESTIMACION.LINEAL y R²: la calidad del modelo
Paso 6.1 — Planteamiento
Ya tenemos la recta. Ahora queremos saber qué tan bien explica nuestros datos. Para eso usamos el coeficiente de determinación R²: un número entre 0 y 1 que indica la proporción de la variación de Y explicada por X.
Paso 6.2 — Fórmula de R²
Sintaxis: =COEFICIENTE.R2(conocido_y; conocido_x). Ojo con el orden: primero Y, después X.
Paso 6.3 — ESTIMACION.LINEAL para ir más allá
ESTIMACION.LINEAL es la función «completa»: devuelve pendiente, intersección y varios estadísticos de una sola vez. Es una función matricial que ocupa 5 filas × 2 columnas (o 1 fila × 2 columnas si solo pides m y b).
Selecciona el rango G7:H11, escribe la fórmula y confirma con Ctrl+Shift+Enter (o Enter en 365). Obtendrás:
| Fila | G | H | Significado |
|---|---|---|---|
| 7 | 7.8147 | 114.4545 | Pendiente m / Intersección b |
| 8 | 1.3866 | 10.5892 | Error típico de m / de b |
| 9 | 0.9699 | 4.9203 | R² / Error típico de Y |
| 10 | 322.6 | 10 | Estadístico F / Grados de libertad |
| 11 | 8733.1 | 271.4 | Suma de cuadrados regresión / residual |
- R² < 0.5 → modelo pobre, no confiar.
- R² 0.5 – 0.75 → aceptable, con reservas.
- R² 0.75 – 0.9 → bueno.
- R² > 0.9 → excelente.
Caso 7 — Gráfico visual del pronóstico
Paso 7.1 — Cómo insertar el gráfico en Excel
- Selecciona B1:C13 (períodos y ventas).
- Ve a Insertar → Gráficos → Dispersión → Dispersión con solo marcadores.
- Haz clic derecho sobre cualquier punto → Agregar línea de tendencia.
- Marca Lineal, Presentar ecuación en el gráfico y Presentar el valor R².
- En Proyección → Hacia delante pon 3 períodos para que la línea se extienda hasta el mes 15.
- Opcional: cambia el color de los marcadores y de la línea de tendencia para distinguirlos.
Paso 7.2 — Así se ve el gráfico terminado
Este gráfico muestra las 12 observaciones históricas (puntos azules), la recta de regresión ajustada (línea azul continua) y la proyección hacia los meses 13, 14 y 15 (línea roja discontinua con marcadores rojos).
Caso 8 — Análisis de errores: MAE, RMSE y MAPE
Paso 8.1 — Planteamiento
Un pronóstico sin análisis de error es un acto de fe. Necesitamos medir qué tan lejos están las predicciones de los valores reales. Para eso usamos tres métricas clásicas:
- MAE (Error Absoluto Medio): promedio de los errores en valor absoluto. Se interpreta en las mismas unidades que Y.
- RMSE (Raíz del Error Cuadrático Medio): penaliza más los errores grandes. Siempre ≥ MAE.
- MAPE (Error Porcentual Absoluto Medio): promedio del error en %. Sirve para comparar modelos entre series distintas.
Paso 8.2 — Calcular las predicciones del modelo sobre el histórico
Primero generamos la columna de predicciones del modelo para los 12 meses históricos:
Se arrastra D2 hasta D13. Luego calculamos el error absoluto y el porcentual:
Paso 8.3 — Las tres métricas
=(C2-D2)^2 y luego =RAIZ(PROMEDIO(H2:H13)).Paso 8.4 — Resultados
| Métrica | Valor | Interpretación |
|---|---|---|
| MAE | 4.09 | En promedio, el modelo se desvía 4 unidades. |
| RMSE | 4.92 | Errores grandes penalizan más. Es el error «típico». |
| MAPE | 2.58% | Error porcentual medio muy bajo. Pronóstico fiable. |
Resumen, buenas prácticas y ejercicios
Tabla resumen: cada función y cuándo usarla
| Función | Sintaxis | Devuelve |
|---|---|---|
| PRONOSTICO.LINEAL | (x; y_conocido; x_conocido) | Un valor Y pronosticado |
| TENDENCIA | (y_conocido; x_conocido; x_nuevo) | Varios valores Y a la vez |
| PENDIENTE | (y_conocido; x_conocido) | m (pendiente) |
| INTERSECCION.EJE | (y_conocido; x_conocido) | b (ordenada al origen) |
| ESTIMACION.LINEAL | (y; x; const; estadística) | m, b, R², error típico, F |
| COEFICIENTE.R2 | (y_conocido; x_conocido) | R² (calidad del ajuste) |
Buenas prácticas
- Mínimo 8–10 observaciones. Con menos, la recta es inestable. Mejor 12 o más.
- X debe ser numérico y ordenado. No uses fechas directamente; conviértelas a número de período (1, 2, 3…).
- No extrapoles demasiado. Pronosticar 1–3 períodos es razonable; 12 períodos hacia adelante puede ser fantasía.
- Analiza siempre el R². Si es < 0.7, sospecha: puede que no haya relación lineal o que haya estacionalidad.
- Reporta el error. Un pronóstico sin RMSE o MAPE es un número huérfano.
- Revisa los residuos. Si los errores muestran patrón (curva, estacionalidad), el modelo lineal no es el adecuado.
- Usa tablas estructuradas. Convierte los datos con Ctrl+T para que las referencias se expandan solas.
Errores comunes
| # | Error | Solución |
|---|---|---|
| 1 | Intercambiar Y y X en las funciones | Y siempre primero en PENDIENTE, INTERSECCION.EJE y COEFICIENTE.R2 |
| 2 | Usar fechas directamente como X | Convertir a período numérico 1, 2, 3… |
| 3 | No confirmar TENDENCIA y ESTIMACION.LINEAL como matriciales | Ctrl+Shift+Enter en Excel 2016 y anteriores |
| 4 | Extrapolar 12+ períodos | Limitar el horizonte a 1–3 períodos o validar contra la realidad |
| 5 | Ignorar el R² | Si < 0.7, buscar otro modelo o más variables |
| 6 | No analizar residuos | Graficar (real − predicho) vs tiempo; debe ser aleatorio |
Ejercicios propuestos
Ejercicio 1 — Pronóstico de visitas web
Tienes 15 semanas de visitas a un sitio web: 850, 920, 890, 980, 1010, 1075, 1040, 1120, 1180, 1150, 1240, 1290, 1260, 1350, 1400. Construye un modelo lineal con PRONOSTICO.LINEAL, calcula R², y pronostica las semanas 16, 17 y 18. Calcula MAE, RMSE y MAPE sobre el histórico. ¿Confiarías en este pronóstico para decidir el ancho de banda del servidor?
Ejercicio 2 — Comparar dos modelos
Repite el modelo del ejercicio 1 pero ahora usando solo los últimos 8 datos. Compara el R², el MAPE y las predicciones para la semana 16. ¿Cuál modelo es mejor? ¿Por qué usar menos datos puede dar un modelo más útil en algunos casos?
Ejercicio 3 — Detectar cuándo falla el modelo lineal
Toma los datos del artículo y multiplica por 1.3 las ventas de los meses 6, 7, 8, 9, 10, 11 y 12 (simulando una aceleración). Recalcula el modelo lineal y el R². Observa cómo cambia. Ahora construye un gráfico de residuos y responde: ¿el modelo lineal sigue siendo apropiado? ¿Qué tipo de modelo alternativo propondrías?
Conclusión
El pronóstico lineal en Excel es una herramienta accesible pero poderosa. Con PRONOSTICO.LINEAL obtienes predicciones puntuales; con TENDENCIA proyectas varios períodos; con PENDIENTE + INTERSECCION.EJE controlas completamente la recta; y con ESTIMACION.LINEAL + COEFICIENTE.R2 evalúas la calidad del modelo.
Recuerda: un pronóstico solo es útil si (1) el R² es razonable, (2) el horizonte es corto, y (3) reportas el error junto con la predicción. Si sigues estos tres principios, tus proyecciones en Excel serán confiables y defendibles ante cualquier audiencia.
Si este artículo te resultó útil, compártelo con alguien que necesite mejorar sus pronósticos en Excel.
Deja una respuesta