Uso de la función PRONÓSTICO con estacionalidad
Nota: Este método solo debe usarse para Excel 2013 y versiones anteriores. Hay un nuevo método que utiliza la función FORECAST.ETS. Compruébalo aquí .
En mi opinión, una de las mejores funciones de Excel es PRONÓSTICO. Podemos alimentar esta función con valores del pasado y utilizará la regresión lineal para pronosticar un valor para un punto futuro. Hemos visto esta función anteriormente cuando consideramos cómo interpolar un valor para datos existentes. Como vimos en esa publicación, PRONÓSTICO solo funciona donde los valores tienen una relación lineal.
Mire el cuadro a continuación:
El gráfico muestra 2 años de ingresos por ventas. Está claro que existe una variación estacional, y los ingresos alcanzan su punto máximo en noviembre/diciembre de cada año. También está claro que hay un crecimiento subyacente, ya que cada año aumentan los ingresos.
En esta circunstancia, no podemos utilizar la función PRONÓSTICO por sí sola. Si lo hiciéramos, crearíamos el siguiente tipo de pronóstico.
¡Oh querido! Eso nunca será exacto.
Para crear un pronóstico estacional preciso necesitamos obtener la tasa de crecimiento subyacente mediante el uso de un índice de estacionalidad. Luego podemos aplicar la función PRONÓSTICO a los números subyacentes antes de volver a estacionalizar los valores. (Estoy bastante seguro de que reestacionalizar no es una palabra real, pero ya sabes a qué me refiero).
Los datos
Aquí está la tabla de datos que crearemos.
Esta tabla contiene todo lo que necesitamos para crear un pronóstico estacional. Los datos sin procesar se encuentran en las celdas A2-B25. Nuestro objetivo es calcular valores de pronóstico adecuados relacionados con el 17 de abril y el 18 de marzo (celdas A26-A37). Todos los valores de las columnas C a H son cálculos que veremos a continuación.
Calcular un promedio mensual
La primera tarea es calcular un promedio para cada mes calendario (es decir, un promedio de abril para todos los años o el promedio de mayo para todos los años, etc.). La fórmula en la celda C14 es:
=PROMEDIO(B2,B14)
Si hubiera más años de datos históricos, también los incluiríamos en la fórmula. Esta fórmula se copia en las celdas C15-C25.
Calcular un índice de estacionalidad
El índice de estacionalidad se utiliza para estimar el valor promedio de un mes en comparación con el promedio de todos los meses. En nuestro ejemplo, los valores de abril son el 81,5% (celda D14) de un mes promedio y los valores de diciembre son el 114,9% (celda D22) de un mes promedio. La fórmula en la celda D14 es:
=C14/PROMEDIO($C$14:$C$25)
Esta fórmula se copia en las celdas D15-D25.
Calcular los valores subyacentes
Ahora utilizamos el índice de estacionalidad, calculado anteriormente, para calcular la tendencia subyacente. La función en la celda E2 es:
=B2/D14
Esto se copia en las celdas E3-E13. Una vez que llegamos a la celda E13, llegamos al final del índice de estacionalidad (celda D25), por lo que debemos comenzar nuevamente en abril para los datos del año 2. La fórmula en la celda E14 es:
=B14/D14
Esto se copia en las celdas E15-E25.
Aplicación de la función PRONÓSTICO
Ahora que tenemos datos de tendencias subyacentes en las celdas E2-E25, podemos usar la función PRONÓSTICO para calcular valores futuros. La fórmula en la celda F26 es:
=PRONÓSTICO(A26,$E$2:$E$25,$A$2:$A$25)
Esto se copia en las celdas F27-F37.
Para evitar que el gráfico tenga una sección en blanco, configuramos la celda F25 igual a la celda E25.
Si ahora volviéramos a dibujar el gráfico en las columnas E y F, se vería así:
Reaplicando la estacionalidad al PRONÓSTICO
Las celdas G2-G25 están configuradas para ser iguales a B2-B25. Estos son valores históricos reales, por lo que queremos mantenerlos como están.
La celda H26 es el cálculo del PRONÓSTICO lineal multiplicado por el índice de estacionalidad. La fórmula en H26 es:
=F26*D14
Esta fórmula se copia en las celdas H27-H37.
Las celdas H26-H37 son nuestro pronóstico estacional. Sólo con el fin de dibujar los gráficos, la celda H25 se establece igual a la celda G25.
Crear un gráfico de pronóstico estacional
Quieres ver cómo se ve el gráfico ahora, ¿no? Aquí tienes.
Eso se parece más a lo que esperaríamos ver, ¿verdad?
Conclusión
La función PRONÓSTICO es excelente, pero a veces solo necesitamos un poco de truco para obligarla a funcionar en un mundo no lineal. Para ver otros ejemplos del uso de la función PRONÓSTICO, consulte: Interpolar valores en Excel usando la función PRONÓSTICO
Si busca más información sobre técnicas de pronóstico en Excel, consulte Ingeniero Excel ( https://engineerexcel.com/blog ). Si bien es posible que no esté trabajando en un contexto de ingeniería, las técnicas son aplicables en muchas otras circunstancias.
Deja una respuesta