Cómo pronosticar la estacionalidad en Excel (de forma fácil)

En esta publicación, veremos cómo pronosticar la estacionalidad en Excel . Anteriormente escribí sobre el pronóstico de la estacionalidad utilizando la función PRONÓSTICO. Sin embargo, con Excel 2016, Microsoft nos brindó un método más sencillo para pronosticar la estacionalidad. En lugar de usar la función PRONÓSTICO con muchos trucos, podemos simplemente usar la función PRONÓSTICO.ETS por sí sola.

Dado que el soporte para Excel 2013 finaliza en abril de 2023, este método de pronosticar la estacionalidad en Excel debería ser el método predominante. Si está utilizando Excel 2013 o anterior, consulte mi publicación anterior aquí: https://exceloffthegrid.com/using-the-forecast-function-with-seasonality/

Tabla de contenido

• El escenario
• Los argumentos de la función PRONÓSTICO.ETS
• Pronosticar estacionalidad en Excel
• Errores comunes
• ¿Qué tan bueno es Excel para predecir el patrón estacional?
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0057 Pronóstico con estacionalidad FORECAST.ETS.xlsx

Tener acceso

El escenario

La siguiente tabla contiene los datos de Excel utilizados para demostrar las técnicas y funciones.

Hay 24 meses de datos históricos en el ejemplo. El objetivo es crear un pronóstico para los próximos 12 meses que mantenga el mismo patrón de estacionalidad.

Lo que quizás no pueda ver en los datos anteriores es un nivel subyacente de crecimiento. Por lo tanto, no solo hay que lidiar con la estacionalidad, sino también un factor de crecimiento que considerar. La buena noticia es que FORECAST.ETS lo simplifica.

El resultado final que creamos se ve así:

La línea continua son los valores históricos y la línea de puntos es el pronóstico estacional, calculado por FORECAST.ETS.

Los argumentos de la función PRONÓSTICO.ETS

La función FORECAST.ETS en Excel calcula resultados estacionales utilizando un algoritmo de suavizado exponencial. La sintaxis de la función es:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

• target_date: La fecha para la cual queremos que Excel prediga un valor. Puede ser un valor numérico o de fecha/hora.
• valores: Los valores históricos en los que basar el pronóstico futuro.
• línea de tiempo: la matriz o rango de fechas correspondientes a los elementos en el argumento de valores anterior. Todas las fechas en la línea de tiempo deben tener un paso consistente entre ellas y no pueden ser cero.
  Nota: No es necesario ordenar la línea de tiempo por fecha; Excel ordenará automáticamente los datos en la memoria antes de realizar el cálculo.
• [estacionalidad]: un valor opcional que representa la duración del patrón estacional.
  • 1 = Excel detecta automáticamente el patrón estacional (este es el valor predeterminado si no se proporciona)
  • 0 = Aplicar un cálculo lineal en lugar de un pronóstico estacional
  • Cualquier otro número entero menor que 8760 = la longitud del patrón repetido. Por ejemplo, para pronosticar la estacionalidad durante un año con datos mensuales, el número sería 12.
• [data_completion]: un valor opcional de 1 o 0. La función puede funcionar con hasta el 30% de los datos faltantes.
  • 1 = los datos faltantes se calculan como el promedio de los puntos de datos vecinos (el valor predeterminado, si no se proporciona el argumento)
  • 0 = los datos faltantes deben tratarse como ceros
• [agregación]: un argumento opcional que detalla cómo se deben tratar varios valores con la misma marca de tiempo.
  • 1 = PROMEDIO (el valor predeterminado, si no se proporciona el argumento)
  • 2 = CONTAR
  • 3 = CONTAR
  • 4 = MÁXIMO
  • 5 = MEDIANA
  • 6 = MÍN.
  • 7 = SUMA

Obtenga más información sobre la función FORECAST.ETS aquí: https://support.microsoft.com/en-us/office/forecast-ets-function-15389b8b-677e-4fbd-bd95-21d464333f41

Pronosticar estacionalidad en Excel

Bien, ahora sabemos cómo funciona FORECAST.ETS. Pongámoslo a prueba.

Si está trabajando junto con el archivo de ejemplo, ingrese la siguiente fórmula en la celda C26:

=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12)

• A26 – La fecha para la cual queremos calcular el valor.
• B$2:B$25 – El rango de valores anteriores en los que se basa el pronóstico
• A$2:A$25 – Las fechas de los valores anteriores en los que se basa el pronóstico
• 12 – El patrón de estacionalidad es 12 porque son valores mensuales.

En este ejemplo no utilizamos los argumentos opcionales data_completion o agregación.

Ahora copie la fórmula. Siempre que coloque los símbolos $ en el lugar correcto, debería obtener lo siguiente.

Creemos un gráfico de líneas para ver fácilmente los resultados en acción.

He agregado la función =NA() en las celdas en blanco para que no se muestren en el gráfico (esto es visible en el archivo de ejemplo).

¡¡¡AUGE!!! Hecho. Eso fue muy fácil. Excel no sólo ha reconocido la estacionalidad sino también la tasa de crecimiento subyacente.

Errores comunes

FORECAST.ETS realiza algunos cálculos bastante complejos. Por lo tanto, FORECAST.ETS puede ser susceptible a muchos tipos diferentes de errores. Excel no puede calcular un valor adecuado si no utilizamos los argumentos correctos.

Los errores más comunes son:

#¡VALOR!

• Los argumentos fecha_objetivo, estacionalidad, finalización_datos o agregación contienen valores no numéricos.
• La línea de tiempo contiene valores duplicados.

#N / A

• Los valores y la línea de tiempo no tienen la misma duración.

#NÚMERO

• No se puede encontrar un paso consistente dentro del cronograma.
• El valor del argumento de estacionalidad no es un número entero entre 0 y 8764
• El valor del argumento data_completion no es 0 ni 1.
• El valor del argumento de agregación no es un número entero del 1 al 7.

¿Qué tan bueno es Excel para predecir el patrón estacional?

Normalmente sabemos con precisión cuánto dura una temporada: trimestral, mensual, semanal, diaria, etc. Pero si no lo sabemos, ¿deberíamos confiar en Excel para que lo haga por nosotros?

Como ejemplo, actualicé la función para que Excel calcule automáticamente el argumento de estacionalidad.

¡¡¡Dios mío, eso no es realmente lo que queremos!!!

Parece que el algoritmo de búsqueda de estacionalidad de Excel puede no ser tan útil. Para calcular el pronóstico estacional en Excel, parece que es mejor ingresar el valor de estacionalidad nosotros mismos

Conclusión

Antes de Excel 2016, los cálculos de estacionalidad requerían muchos pasos adicionales. Sin embargo, con la introducción de la función FORECAST.ETS, ahora es fácil pronosticar la estacionalidad en Excel.

---