Promedio móvil significa que calculamos el promedio de los promedios del conjunto de datos que tenemos, en Excel tenemos una función incorporada para el cálculo del promedio móvil que está disponible en la pestaña de análisis de datos en la sección de análisis, toma un rango de entrada y salida rango con intervalos como salida, los cálculos basados en meras fórmulas en Excel para calcular el promedio móvil es difícil, pero tenemos una función incorporada en Excel para hacerlo.
¿Qué es la media móvil en Excel?
La media móvil es una técnica ampliamente utilizada en el análisis de series de tiempo que se utiliza para predecir el futuro. Los promedios móviles en una serie de tiempo se construyen básicamente tomando promedios de varios valores secuenciales de otros datos de series de tiempo.
Hay tres tipos de medias móviles: media móvil simple, media móvil ponderada y media móvil exponencial en Excel.
# 1 - Media móvil simple en Excel
Un promedio móvil simple ayuda a calcular el promedio del último número de períodos de una serie de datos. Suponga que se dan los precios de n períodos. Entonces la media móvil simple se da como
Media móvil simple = [P1 + P2 + …………. + Pn] / n
# 2 - Media móvil ponderada en Excel
La media móvil ponderada proporciona la media ponderada de los últimos n períodos. La ponderación disminuye con cada punto de datos del período de tiempo anterior.
Media móvil ponderada = (Precio * factor de ponderación) + (Precio del período anterior * factor de ponderación-1)
# 3 - Media móvil exponencial en Excel
Es similar al promedio móvil simple que mide las tendencias durante un período de tiempo. Mientras que el promedio móvil simple calcula un promedio de datos dados, el promedio móvil exponencial atribuye más peso a los datos actuales.
Media móvil exponencial = (K x (C - P)) + P
Dónde,
- K = constante de suavizado exponencial
- C = precio actual
- P = promedio móvil exponencial de períodos anteriores (promedio móvil simple utilizado para el cálculo de los primeros períodos)
¿Cómo calcular la media móvil en Excel?
A continuación se muestra el ejemplo de media móvil en Excel.
Puede descargar esta plantilla de Excel de media móvil aquí - Plantilla de Excel de media móvilEjemplo # 1 - Promedio móvil simple en Excel
Para calcular el promedio móvil simple, hemos tomado los datos de ventas de una empresa de enero a diciembre para el año 2018. Nuestro objetivo es suavizar los datos y conocer la cifra de ventas en enero de 2019. Aquí usaremos el promedio móvil de tres meses.
Paso 1: El promedio móvil de enero, febrero y marzo se calcula tomando la suma de la cifra de ventas de los meses y luego dividiéndola por 3.
Paso 2: Al seleccionar en la esquina de la celda D5 y luego simplemente arrastrar y soltar, se obtendrá el promedio móvil de los períodos restantes. Esta es la función de la herramienta de relleno de Excel.
La predicción de ventas para enero de 2019 es 10456,66667.
Paso 3: Ahora trazamos la cifra de ventas y el promedio móvil en el gráfico lineal para comprender la diferencia en la tendencia. Esto se puede hacer desde la pestaña Insertar. En primer lugar, hemos seleccionado la serie de datos y luego de la sección de Gráficos debajo de insertar, hemos utilizado el gráfico de líneas.
Después de crear los gráficos, se puede ver que el gráfico con la media móvil está mucho más suavizado que la serie de datos original.
Ejemplo # 2 - Promedio móvil simple a través de la pestaña Análisis de datos en Excel
- En la pestaña Datos en el grupo Análisis, tenemos que hacer clic en Análisis de datos. A continuación se muestra la captura de pantalla.
- Desde el análisis de datos se puede acceder a la media móvil.
- Después de hacer clic en la media móvil, hemos seleccionado la cifra de ventas como rango de entrada.
- Se hace clic en las etiquetas de la primera fila para que Excel comprenda que la primera fila tiene el nombre de la etiqueta.
- El intervalo 3 se selecciona porque queremos una media móvil de tres años.
- Hemos seleccionado el rango de salida adyacente a la cifra de ventas.
- También queremos ver el resultado del gráfico en el que podremos ver la diferencia entre lo real y lo pronosticado.
Este gráfico muestra la diferencia entre la media móvil real y la prevista.
Ejemplo n. ° 3: media móvil ponderada en Excel
Usamos la media móvil ponderada de tres años y la fórmula se muestra en la captura de pantalla.
Después de usar la fórmula, obtuvimos la media móvil de un período.
Obtuvimos el promedio móvil para todos los demás períodos arrastrando y soltando valores en las siguientes celdas.
El pronóstico para enero de 2019, es decir, 10718.33
Ahora tomamos el gráfico de líneas para ver el suavizado de los datos. Para esto, hemos seleccionado nuestro mes los datos pronosticados y luego insertamos un gráfico de líneas.
Ahora compararemos nuestros datos pronosticados con nuestros datos reales. En las capturas de pantalla a continuación, podemos ver fácilmente la diferencia entre los datos reales y los datos previstos. El gráfico de la parte superior son los datos reales y el gráfico de abajo es el promedio móvil y los datos pronosticados. Podemos ver que el gráfico de promedio móvil se ha suavizado significativamente en comparación con el gráfico que contiene los datos reales.
Ejemplo # 4 - Promedio móvil exponencial en Excel
La fórmula para la media móvil exponencial es S t = α.Y t-1 + (1- α) S t-1 …… (1)
Dónde,
- Y t-1 = observación real en el período t-1.
- S t-1 = promedio móvil simple en el período t-1th
- α = factor de suavizado y varía entre .1 y .3. Cuanto mayor sea el valor de α más cercano está el gráfico a los valores reales y menor el valor de α, más suave será el gráfico.
Primero, calculamos la media móvil simple como se mostró anteriormente. Después de eso, aplicamos la fórmula dada en la ecuación (1). Para fijar el valor α para todos los siguientes valores, hemos presionado F4.
Obtenemos los valores arrastrando y soltando en las siguientes celdas.
Ahora, queremos ver la comparación entre los valores reales, la media móvil simple y la media móvil exponencial en Excel. Lo hemos hecho haciendo un gráfico de líneas.
En la captura de pantalla anterior, podemos ver la diferencia entre la cifra de ventas real, la media móvil simple y la media móvil exponencial en Excel.
Cosas para recordar sobre la media móvil en Excel
- El promedio móvil simple se puede calcular usando una función PROMEDIO en Excel
- La media móvil ayuda a suavizar los datos
- Los promedios estacionales a menudo se denominan índice estacional.
- La media móvil exponencial en Excel le da más peso a los datos recientes que la media móvil simple. Por lo tanto, el suavizado en el caso de la media móvil exponencial en Excel es mayor que el de la media móvil simple.
- En negocios como el mercado de valores, la media móvil ayuda al comerciante a identificar más fácilmente la tendencia.
