Pronóstico Avanzado con Tendencias No Lineales: Regresión Cuadrática en Excel

En el análisis de datos, no todas las tendencias siguen una línea recta. A menudo, nos encontramos con relaciones que se aceleran, se desaceleran o alcanzan un punto máximo o mínimo. Para estos escenarios, un pronóstico lineal es insuficiente. La regresión cuadrática es una técnica estadística avanzada que modela la relación entre variables usando una ecuación de segundo grado (una parábola), cuya forma general es y = ax² + bx + c.

Esta fórmula de Excel es una solución elegante y potente que permite realizar un pronóstico basado en una regresión cuadrática directamente en una celda, sin necesidad de crear columnas de ayuda o gráficos. Combina funciones matriciales para calcular los coeficientes de la parábola que mejor se ajusta a sus datos históricos y luego los utiliza para predecir un valor futuro.

Sintaxis

=INDICE(ESTIMACION.LINEAL(rango_Y, ELEGIR({1.2}, rango_X, rango_X^2)), 1) * (punto_X_futuro^2) + INDICE(ESTIMACION.LINEAL(rango_Y, ELEGIR({1.2}, rango_X, rango_X^2)), 2) * punto_X_futuro + INDICE(ESTIMACION.LINEAL(rango_Y, ELEGIR({1.2}, rango_X, rango_X^2)), 3)

Esta fórmula puede parecer compleja, pero en esencia, recrea la ecuación y = ax² + bx + c. A continuación, se desglosan sus componentes principales:

• ESTIMACION.LINEAL(rango_Y, ELEGIR({1.2}, rango_X, rango_X^2)) Es el corazón de la fórmula. La función ESTIMACION.LINEAL normalmente calcula una línea recta. Sin embargo, al combinarla con ELEGIR, la «engañamos» para que realice una regresión múltiple. ELEGIR({1.2}, rango_X, rango_X^2) crea una matriz de dos columnas: una con los valores de X y otra con los valores de X al cuadrado. Al usar esta matriz como la variable X conocida, ESTIMACION.LINEAL calcula los coeficientes a, b y c de la ecuación cuadrática.
• INDICE(matriz_coeficientes, n) La función INDICE se utiliza para extraer cada coeficiente individual de la matriz que devuelve ESTIMACION.LINEAL.
  • INDICE(..., 1) extrae el coeficiente a (el que multiplica a x²).
  • INDICE(..., 2) extrae el coeficiente b (el que multiplica a x).
  • INDICE(..., 3) extrae la constante c (el punto de intercepción en el eje Y).
• punto_X_futuro Es el nuevo valor del eje X para el cual deseamos realizar el pronóstico en el eje Y.

La fórmula ensambla estos componentes para calcular el resultado final: a * (punto_X_futuro)² + b * punto_X_futuro + c.

Ejemplos

Ejemplo 1: Pronóstico de ventas con crecimiento acelerado

Imaginemos que una empresa ha lanzado un nuevo producto y sus ventas mensuales muestran un crecimiento que parece acelerarse con el tiempo. Un pronóstico lineal subestimaría las ventas futuras. Usaremos la regresión cuadrática para predecir las ventas del mes 7.

Para pronosticar las ventas en el mes 7 (celda A8), usamos la siguiente fórmula en la celda B8:

=INDICE(ESTIMACION.LINEAL(B2:B7, ELEGIR({1.2}, A2:A7, A2:A7^2)), 1) * (A8^2) + INDICE(ESTIMACION.LINEAL(B2:B7, ELEGIR({1.2}, A2:A7, A2:A7^2)), 2) * A8 + INDICE(ESTIMACION.LINEAL(B2:B7, ELEGIR({1.2}, A2:A7, A2:A7^2)), 3) -> Resultado esperado: 489,64

El modelo predice que las ventas en el séptimo mes serán aproximadamente 490 unidades, capturando la curva de crecimiento acelerado que un modelo lineal no podría.

Aplicaciones Prácticas

• 1Economía y Finanzas: Modelar el rendimiento de una inversión que puede tener un crecimiento inicial rápido que luego se modera, o para analizar la relación entre el gasto en publicidad y los ingresos, donde existe un punto de rendimiento decreciente.
• 2Ciencias e Ingeniería: Analizar la trayectoria de un proyectil, modelar la resistencia de un material bajo tensión creciente o estudiar reacciones químicas cuya velocidad cambia con el tiempo.
• 3Agricultura: Estimar el rendimiento de un cultivo en función de la cantidad de fertilizante aplicado, donde una cantidad óptima maximiza el rendimiento, pero un exceso puede ser perjudicial.
• 4Marketing: Predecir la tasa de adopción de un nuevo producto, que a menudo sigue una curva en forma de ‘S’, cuya fase inicial de crecimiento puede ser modelada con una parábola.

Observaciones

Versiones de Excel: En versiones antiguas de Excel (anteriores a Excel 365 con matrices dinámicas), esta fórmula debía ser introducida como una fórmula de matriz, presionando Ctrl + Shift + Enter. En versiones modernas, simplemente se presiona Enter.

Calidad del ajuste: La precisión del pronóstico depende de cuán bien se ajusten los datos a un patrón cuadrático. Siempre es recomendable visualizar los datos en un gráfico de dispersión y añadir una línea de tendencia polinómica de orden 2 para confirmar visualmente que el modelo es apropiado.

Cantidad de datos: Para una regresión cuadrática, se necesita un mínimo de 3 puntos de datos. Sin embargo, para obtener un pronóstico fiable, se recomienda utilizar un conjunto de datos significativamente mayor.

Errores comunes

• #¡VALOR! Ocurre si los rangos de datos X e Y contienen celdas vacías, texto o valores no numéricos.
• #N/A Puede ocurrir si los rangos de datos no tienen el mismo número de filas o si ESTIMACION.LINEAL no puede calcular un resultado válido (por ejemplo, si todos los valores X son idénticos).
• #¡REF! Aparece si los rangos proporcionados en la fórmula son inválidos.

Compatibilidad

Alternativas

• Uso de columnas de ayuda y ESTIMACION.LINEAL

  Una alternativa más fácil de depurar es crear una columna auxiliar en la hoja de cálculo para los valores de X². Por ejemplo, en la columna C, se podría poner la fórmula =A2^2 y arrastrarla. Luego, se usaría una fórmula ESTIMACION.LINEAL más sencilla, seleccionando ambas columnas (la de X y la de X²) como el argumento conocido_x. Esto separa el proceso en pasos más manejables.

• Gráfico de dispersión con línea de tendencia

  Para un análisis visual rápido, se puede crear un gráfico de dispersión con los datos, hacer clic derecho en la serie de datos, seleccionar «Agregar línea de tendencia» y elegir «Polinómica» de orden 2. Además, se puede marcar la casilla «Presentar ecuación en el gráfico» para ver los coeficientes a, b y c calculados por Excel.