Proyecciones Temporales en Excel

Proyecciones Temporales en Excel

Cómo utilizar las funciones TENDENCIA y PRONOSTICO.ETS para generar proyecciones

Hacer proyecciones es una de las tareas más habituales cuando trabajamos con datos en Excel: proyecciones de ventas, de costes de producción o de cualquier serie temporal que evolucione a lo largo del tiempo. Para resolverlas, Excel pone a nuestra disposición dos funciones muy potentes, TENDENCIA y PRONOSTICO, cada una pensada para un tipo de serie distinto. En este tutorial vemos cómo usarlas y, sobre todo, cómo mejorar el resultado descartando datos que distorsionan el histórico.

La función TENDENCIA para series sin estacionalidad

TENDENCIA genera una proyección lineal de la serie que tengamos. Es la opción ideal cuando los datos crecen de forma sostenida y no presentan estacionalidad: la serie sube y baja, pero sin un ciclo repetitivo marcado por el día de la semana, el mes o cualquier otro patrón que se repita en el tiempo.

Su uso es sencillo. La función pide:

  • Conocido_y: el resultado que ya tenemos, en este caso la tabla de ventas.
  • Conocido_x: el valor asociado, que aquí son las fechas. TENDENCIA también acepta valores que no sean fechas; simplemente mostrará la relación entre dos valores.
  • Nueva_matriz_x: las fechas para las que queremos obtener la proyección.
  • Constante: indica si la estimación lineal incluye una constante. Excel calcula la recta de la forma y = a + bx, la misma que estudiamos en el colegio.

La recomendación es poner siempre la constante como VERDADERO, ya que corresponde al cálculo normal: partimos de una base de ventas que tomamos como referencia para que arranque toda la progresión.

Una vez generada la tendencia, podemos combinar las matrices con APILARV (apilar verticalmente) para unir el histórico real con la proyección y construir una gráfica de líneas. El resultado de TENDENCIA es siempre una línea recta: a medida que acumulamos fechas, se va añadiendo la parte proporcional que marca la pendiente. Para una serie sin ciclos, esta aproximación funciona muy bien y nos da una idea clara de hacia dónde nos dirigimos.

Cuando hay estacionalidad: la función PRONOSTICO.ETS

El problema aparece con series que sí tienen un ciclo marcado: suben y bajan por meses, con picos y valles que se repiten, e incluso con tramos anómalos (por ejemplo, el bajón de la pandemia) que alteran el resultado final. En estos casos, una simple recta no refleja la realidad mes a mes, donde los valores se disparan o se hunden según la estacionalidad.

Para estos escenarios, Excel ofrece PRONOSTICO.ETS, una función diseñada específicamente para trabajar con series temporales estacionales. Sus argumentos principales son:

  • Fecha_objetivo: la fecha (o fechas) a la que queremos dar respuesta.
  • Valores: el histórico de datos, en este ejemplo las ventas.
  • Escala_de_tiempo: las fechas del histórico. Es imprescindible, porque PRONOSTICO.ETS está enfocado en discriminar y trabajar con la estacionalidad de la serie.

Además, la función incluye varios argumentos opcionales para afinar el cálculo. Al graficar el pronóstico vemos que ya intenta reproducir los picos y bajadas de la serie, en lugar de una simple recta ascendente.

Excluir datos que distorsionan el histórico

Aun usando PRONOSTICO.ETS, un tramo anómalo como el de la pandemia puede desvirtuar el resultado. Una opción sería recortar la serie, pero a veces no queremos perder esos datos. La alternativa es excluir solo algunos valores de la estimación.

PRONOSTICO.ETS permite no incluir ciertos valores siempre que mantengamos fechas con diferencias similares entre ellas. Ante un hueco, ofrece dos opciones: rellenarlo con un cero o rellenarlo con un valor promedio entre el dato anterior y el posterior. Esta segunda opción equivale a decirle a la función: "no tengas en cuenta este valor, sustitúyelo por uno que no altere la interpretación de los datos".

Para aplicarlo de forma cómoda, podemos apoyarnos en un checkbox por fila más una función que actúe sobre cada valor:

  • Si la casilla está marcada (VERDADERO), el valor se deja vacío, excluyéndolo del cálculo.
  • Si no está marcada, se devuelve el valor original de ventas.

Al alimentar PRONOSTICO.ETS con esta columna de valores filtrados en lugar de la columna de ventas original, podemos marcar los meses de la pandemia y verlos desaparecer del cálculo. La función los sustituye por un valor aproximado y la estimación resultante se ajusta mucho mejor a la tendencia reciente que realmente nos interesa.

Comparativa de resultados

Al enfrentar las tres estimaciones con los datos reales:

  • TENDENCIA: marca una evolución genérica, pero no aporta valor mes a mes en series muy estacionales.
  • PRONOSTICO.ETS sin limpiar: se acerca algo más, pero los meses anómalos del histórico generan un error notable.
  • PRONOSTICO.ETS con valores excluidos: ofrece una gráfica representativa y útil para planificar volúmenes, producción o cualquier aspecto que dependa de la estimación.

Conclusión

Las series temporales son un terreno apasionante que mezcla estadística, pendientes, desviaciones y la comprensión de por qué ciertos patrones se comportan como lo hacen. TENDENCIA y PRONOSTICO.ETS son el punto de partida, pero ambas tienen funciones relacionadas que permiten saber si una serie tiene estacionalidad o realizar ajustes adicionales. Vale la pena investigarlas para ir perfeccionando cada vez más nuestras proyecciones.

Más contenido de Excel en InflueXcel