Média móvel significa que calculamos a média das médias do conjunto de dados que temos, no Excel temos um recurso embutido para o cálculo da média móvel que está disponível na guia de análise de dados na seção de análise, leva um intervalo de entrada e saída intervalo com intervalos como saída, cálculos baseados em meras fórmulas no Excel para calcular a média móvel é difícil, mas temos uma função embutida no Excel para fazer isso.
O que é média móvel no Excel
A média móvel é uma técnica amplamente usada na análise de séries temporais para prever o futuro. As médias móveis em uma série temporal são basicamente construídas tomando médias de vários valores sequenciais de dados de outra série temporal.
Existem três tipos de médias móveis: média móvel simples, média móvel ponderada e média móvel exponencial no Excel.
# 1 - Média móvel simples no Excel
Uma média móvel simples ajuda a calcular a média do último número de períodos de uma série de dados. Suponha que sejam dados preços de n períodos. Então, a média móvel simples é dada como
Média móvel simples = [P1 + P2 + …………. + Pn] / n
# 2 - Média móvel ponderada no Excel
A média móvel ponderada fornece a média ponderada dos últimos n períodos. A ponderação diminui com cada ponto de dados do período anterior.
Média móvel ponderada = (Preço * fator de ponderação) + (Preço do período anterior * fator de ponderação-1)
# 3 - Média móvel exponencial no Excel
É semelhante à média móvel simples que mede as tendências ao longo de um período de tempo. Enquanto a média móvel simples calcula uma média de dados fornecidos, a média móvel exponencial atribui mais peso aos dados atuais.
Média móvel exponencial = (K x (C - P)) + P
Onde,
- K = constante de suavização exponencial
- C = preço atual
- P = média móvel exponencial dos períodos anteriores (média móvel simples usada para o cálculo dos primeiros períodos)
Como calcular a média móvel no Excel?
Abaixo estão os exemplos de média móvel no Excel.
Você pode baixar este modelo Excel de média móvel aqui - Modelo Excel de média móvelExemplo # 1 - Média móvel simples no Excel
Para calcular a média móvel simples, pegamos os dados de vendas de uma empresa de janeiro a dezembro para o ano de 2018. Nossa meta é suavizar os dados e saber o número de vendas em janeiro de 2019. Usaremos a média móvel de três meses aqui.
Etapa 1: a média móvel de janeiro, fevereiro e março é calculada tomando a soma do valor das vendas dos meses e dividindo-a por 3.
Etapa 2: Selecionar no canto da célula D5 e, em seguida, apenas arrastar e soltar dará a média móvel para os períodos restantes. Esta é a função de ferramenta de preenchimento do Excel.
A previsão de vendas para janeiro de 2019 é 10456,666667.
Etapa 3: agora, plotamos o valor das vendas e a média móvel no gráfico de linhas para entender a diferença de tendência. Isso pode ser feito na guia inserir. Em primeiro lugar, selecionamos a série de dados e, em seguida, na seção Gráficos em inserir, usamos o gráfico de linha.
Após a criação dos gráficos, pode-se observar que o gráfico com a média móvel está muito mais suavizado do que a série de dados original.
Exemplo # 2 - Média móvel simples por meio da guia de análise de dados no Excel
- Na guia Dados no grupo Análise, temos que clicar em Análise de Dados. A seguir está a imagem.
- A partir da análise de dados, a média móvel pode ser acessada.
- Depois de clicar na média móvel, selecionamos o valor das vendas como o intervalo de entrada.
- Os rótulos na primeira linha são clicados para fazer o excel entender que a primeira linha tem o nome do rótulo.
- O intervalo 3 é selecionado porque queremos uma média móvel de três anos.
- Selecionamos a faixa de produção adjacente ao valor de vendas.
- Também queremos ver a saída do gráfico em que seremos capazes de ver a diferença entre o real e o previsto.
Este gráfico mostra a diferença entre a média móvel real e prevista.
Exemplo # 3 - Média móvel ponderada no Excel
Usamos a média móvel ponderada de três anos e a fórmula é fornecida na imagem.
Depois de usar a fórmula, obtivemos a média móvel de um período.
Obtivemos a média móvel de todos os outros períodos arrastando e soltando os valores nas células a seguir.
A previsão para janeiro de 2019 é 10718,33
Agora pegamos o gráfico de linha para ver a suavização dos dados. Para isso, selecionamos nosso mês os dados previstos e, em seguida, inserimos um gráfico de linha.
Agora vamos comparar nossos dados previstos com nossos dados reais. Nas imagens abaixo, podemos ver facilmente a diferença entre os dados reais e os dados previstos. O gráfico na parte superior são os dados reais e o gráfico abaixo é a média móvel e os dados previstos. Podemos ver que o gráfico de média móvel suavizou significativamente em comparação com o gráfico que contém os dados reais.
Exemplo 4 - Média móvel exponencial no Excel
A fórmula para a média móvel exponencial é S t = α.Y t-1 + (1- α) S t-1 ... (1)
Onde,
- Y t-1 = observação real no t-1º período
- S t-1 = média móvel simples no período t-1
- α = fator de suavização e varia entre 0,1 e 0,3. Quanto maior o valor de α, mais próximo está o gráfico dos valores reais e quanto menor o valor de α, mais suave será o gráfico.
Primeiro, calculamos a média móvel simples, conforme mostrado anteriormente. Depois disso, aplicamos a fórmula dada na equação (1). Para fixar o valor α para todos os seguintes valores, pressionamos F4.
Obtemos os valores arrastando e soltando nas células a seguir.
Agora, queremos ver a comparação entre os valores reais, média móvel simples e média móvel exponencial no Excel. Fizemos isso fazendo um gráfico de linha.
Na captura de tela acima, podemos ver a diferença entre o número real de vendas, média móvel simples e média móvel exponencial no Excel.
Coisas para lembrar sobre a média móvel no Excel
- A média móvel simples pode ser calculada usando uma função AVERAGE no Excel
- A média móvel ajuda a suavizar os dados
- As médias sazonais são frequentemente denominadas como um índice sazonal
- A média móvel exponencial no Excel dá mais peso aos dados recentes do que a média móvel simples. Portanto, a suavização no caso da média móvel exponencial no Excel é mais do que a média móvel simples.
- Em negócios como o mercado de ações, a média móvel ajuda o trader a identificar a tendência com mais facilidade.
