Moving Average. This exemplo ensina como calcular a média móvel de uma série temporal em Excel Uma média móvel é usado para suavizar irregularidades picos e vales para reconhecer facilmente trends.1 Primeiro, vamos dar uma olhada em nossa série de tempo. Na guia Dados, clique em Análise de dados. Nota não pode encontrar o botão Análise de dados Clique aqui para carregar o complemento Analysis ToolPak.3 Selecione Média móvel e clique em OK.4 Clique na caixa Intervalo de entrada e selecione o intervalo B2 M2. 5 Clique na caixa Intervalo e digite 6.6 Clique na caixa Output Range e selecione a célula B3.8 Trace um gráfico desses valores. Explicação porque definimos o intervalo como 6, a média móvel é a média dos 5 pontos de dados anteriores e O ponto de dados atual Como resultado, os picos e os vales são suavizados O gráfico mostra uma tendência crescente O Excel não pode calcular a média móvel para os primeiros 5 pontos de dados porque não há pontos de dados anteriores suficientes.9 Repita os passos 2 a 8 para o intervalo 2 E intervalo 4.Conclusão O la Quanto mais pequeno o intervalo, mais próximas as médias móveis são para os pontos de dados reais. Quando se calcula uma média móvel em execução, colocar a média no período de tempo médio faz sentido. No exemplo anterior, calculamos a média dos três primeiros períodos de tempo e colocamos-o próximo ao período 3 Poderíamos ter colocado a média no meio do intervalo de tempo de três períodos, ou seja, próximo ao período 2 Isso funciona bem com períodos de tempo ímpares , Mas não tão bom para períodos de tempo mesmo Então, onde seria colocar a primeira média móvel quando M 4.Technically, a média móvel iria cair em t 2 5, 3 5.Para evitar esse problema, suavizar o MA s usando M 2 Assim Nós suavizar os valores alisados. Se nós médio um número par de termos, precisamos de suavizar os valores alisados. A tabela a seguir mostra os resultados usando M implementação 4.Separsheet de ajuste sazonal e suavização exponencial. É fácil de executar ajuste sazonal E montar modelos de suavização exponencial usando o Excel As imagens de tela e os gráficos abaixo são retirados de uma planilha que foi configurada para ilustrar o ajuste sazonal multiplicativo e suavização exponencial linear nos seguintes dados de vendas trimestrais do Bordo Marítimo. Para obter uma cópia da planilha Clique aqui A versão de suavização exponencial linear que será usada aqui para fins de demonstração é a versão de Brown, simplesmente porque ela pode ser implementada com uma única coluna de fórmulas e há apenas uma constante de suavização para otimizar Normalmente é melhor Para usar a versão de Holt que tem constantes de suavização separadas para nível e tendência. O processo de previsão segue como segue i primeiro os dados são ajustados sazonalmente ii então as previsões são geradas para os dados sazonalmente ajustados através de suavização exponencial linear e iii finalmente as previsões ajustadas sazonalmente Reseasonalized para obter previsões para a série original O ajuste sazonal É levado a cabo nas colunas D a G. O primeiro passo no ajuste sazonal é computar uma média móvel centrada realizada aqui na coluna D Isto pode ser feito tomando a média de duas médias anuais que são compensadas por um período Em relação ao outro Uma combinação de duas médias de compensação em vez de uma única média é necessária para fins de centralização quando o número de estações é mesmo O próximo passo é calcular a razão para a média móvel --e os dados originais divididos pela média móvel em Cada período - o que é realizado aqui na coluna E. Isto também é chamado de componente de tendência-ciclo do padrão, na medida em que os efeitos da tendência e do ciclo de negócios podem ser considerados como tudo o que resta após a média de um ano inteiro de dados Naturalmente, as mudanças mês a mês que não são devido à sazonalidade poderiam ser determinadas por muitos outros fatores, mas a média de 12 meses suaviza sobre eles em grande medida. O índice sazonal estimado para cada estação é calculado por f Com a média de todas as razões para essa estação particular, que é feita nas células G3-G6 usando uma fórmula AVERAGEIF. As razões médias são então reescaladas de modo que somam exatamente 100 vezes o número de períodos em uma estação, ou 400 neste caso, Que é feita nas células H3-H6 Abaixo na coluna F, as fórmulas VLOOKUP são usadas para inserir o valor do índice sazonal apropriado em cada linha da tabela de dados, de acordo com o trimestre do ano que representa A média móvel centrada e os dados dessazonalizados Acabam parecendo isto. Observe que a média móvel normalmente se parece com uma versão mais lisa da série ajustada sazonalmente e é mais curta em ambas as extremidades. Outra planilha no mesmo arquivo do Excel mostra a aplicação do modelo de suavização exponencial linear à sazonalidade Dados ajustados, começando no valor da coluna GA para a constante alisante alfa é inserido acima da coluna de previsão aqui, na célula H9 e por conveniência é atribuído o nome do intervalo Alfa O nome é atribuído Usando o comando Inserir Nome Criar O modelo LES é inicializado estabelecendo as duas primeiras previsões iguais ao primeiro valor real da série ajustada sazonalmente A fórmula usada aqui para a previsão LES é a forma recursiva de equação única do modelo de Brown. É inserida na célula correspondente ao terceiro período aqui, célula H15 e copiada para baixo a partir de lá Observe que a previsão LES para o período atual refere-se às duas observações precedentes e os dois erros de previsão anteriores, bem como ao valor de alfa Assim , A fórmula de previsão na linha 15 refere-se apenas a dados que estavam disponíveis na linha 14 e anteriores. É claro que, se quiséssemos usar o alisamento exponencial linear simples, em vez disso, poderíamos substituir a fórmula SES aqui. Brown s modelo LES, o que exigiria mais duas colunas de fórmulas para calcular o nível ea tendência que são utilizados na previsão. Os erros são calculados na próxima coluna aqui, coluna J Subtraindo as previsões dos valores reais A raiz do erro quadrático médio é calculada como a raiz quadrada da variância dos erros mais o quadrado da média Isso decorre da identidade matemática MSE VARIANCE erros MÉDIO erros 2 No cálculo da média e variância de Os erros nesta fórmula, os dois primeiros períodos são excluídos porque o modelo não começa realmente a previsão até o terceiro período fila 15 na planilha O valor ótimo de alfa pode ser encontrado por mudança manual alfa até que o mínimo RMSE é encontrado, ou Senão você pode usar o Solver para executar uma minimização exata O valor de alfa que o Solver encontrado é mostrado aqui alpha 471.It é geralmente uma boa idéia para traçar os erros do modelo em unidades transformadas e também para calcular e traçar suas autocorrelações Em defasagens de até uma temporada Aqui está um gráfico de séries temporais dos erros corrigidos de sazonalidade. As autocorrelações de erro são calculadas usando a função CORREL para calcular a co Rrelações dos erros com eles mesmos atrasados por um ou mais períodos - detalhes são mostrados no modelo de planilha Aqui está um gráfico das autocorrelações dos erros nos primeiros cinco lags. As autocorrelações nos retornos 1 a 3 são muito próximas de zero, Mas o pico no intervalo 4, cujo valor é 0 35, é ligeiramente problemático - sugere que o processo de ajuste sazonal não foi completamente bem sucedido No entanto, é apenas marginalmente significativo 95 bandas de significância para testar se autocorrelações são significativamente diferentes de zero são aproximadamente N + 2, onde n é o tamanho da amostra ek é o retardo Aqui n é 38 e k varia de 1 a 5, então a raiz quadrada de - n-menos-k é de cerca de 6 para todos E, portanto, os limites para testar a significância estatística de desvios de zero são aproximadamente mais ou menos 2 6, ou 0 33 Se você variar o valor de alfa à mão neste modelo do Excel, você pode observar o efeito sobre o Séries temporais e gráficos de autocorrelação do e , Bem como sobre o erro raiz-médio quadrado, que será ilustrado abaixo. Na parte inferior da planilha, a fórmula de previsão é bootstrapped no futuro, simplesmente substituindo as previsões de valores reais no ponto onde os dados reais é executado Ou seja, onde o futuro começa Em outras palavras, em cada célula onde um futuro valor de dados ocorreria, uma referência de célula é inserido que aponta para a previsão feita para esse período Todas as outras fórmulas são simplesmente copiadas para baixo de cima. Os erros para as previsões do futuro são todos calculados como sendo zero. Isto não significa que os erros reais serão zero, mas sim meramente reflete o fato de que para fins de previsão estamos assumindo que os dados futuros serão iguais às previsões em média. As previsões de LES resultantes para os dados ajustados sazonalmente se parecem com isso. Com este valor específico de alfa, que é ótimo para previsões de um período de tempo, a tendência projetada é ligeiramente para cima, refletindo o t local Que foi observado nos últimos 2 anos ou mais Para outros valores de alfa, uma projeção de tendência muito diferente pode ser obtida É geralmente uma boa idéia ver o que acontece com a projeção de tendência de longo prazo quando o alfa é variado, porque o valor Que é melhor para a previsão de curto prazo não será necessariamente o melhor valor para prever o futuro mais distante Por exemplo, aqui está o resultado que é obtido se o valor de alfa é manualmente definido como 0 25.A tendência de longo prazo projetada é Agora negativo em vez de positivo Com um valor menor de alfa, o modelo está a colocar mais peso em dados mais antigos na sua estimativa do nível e tendência actuais e as suas previsões a longo prazo reflectem a tendência descendente observada nos últimos 5 anos, Tendência ascendente mais recente Este gráfico também ilustra claramente como o modelo com um valor menor de alfa é mais lento para responder a pontos de viragem nos dados e, portanto, tende a fazer um erro do mesmo sinal para muitos períodos em uma linha Sua Os erros de previsão de 1 passo são maiores em média do que aqueles obtidos antes do RMSE de 34 4 em vez de 27 4 e fortemente autocorrelacionados positivamente A autocorrelação de lag-1 de 0 56 excede largamente o valor de 0 33 calculado acima para um desvio estatisticamente significativo de Zero Como uma alternativa ao cranking para baixo o valor de alfa, a fim de introduzir mais conservadorismo em previsões de longo prazo, um fator de amortecimento de tendência às vezes é adicionado ao modelo, a fim de fazer a tendência projetada aplanar após alguns periods. The etapa final Assim, as projeções reseasonalized na coluna I são simplesmente o produto dos índices sazonais na coluna F e as previsões de LES estacionalmente ajustadas na coluna H. It é Relativamente fácil para calcular os intervalos de confiança para as previsões de um passo à frente feitas por este modelo primeiro calcular o RMSE root-mean-squared erro, que é apenas o s Quare raiz do MSE e, em seguida, calcular um intervalo de confiança para a previsão ajustada sazonalmente, adicionando e subtraindo duas vezes o RMSE Em geral, um intervalo de confiança de 95 para um período de previsão à frente é aproximadamente igual à previsão de ponto mais ou menos - duas vezes o desvio padrão estimado dos erros de previsão, assumindo que a distribuição de erro é aproximadamente normal eo tamanho da amostra é grande o suficiente, digamos, 20 ou mais Aqui, o RMSE em vez do desvio padrão da amostra dos erros é a melhor estimativa de O desvio padrão dos futuros erros de previsão, pois leva em consideração o viés e as variações aleatórias. Os limites de confiança para a previsão ajustada sazonalmente são então reseasonalized junto com a previsão, multiplicando-os pelos índices sazonais apropriados. Neste caso, o RMSE é igual a 27 4 e a previsão ajustada sazonalmente para o primeiro período futuro Dec-93 é 273 2 então o intervalo de confiança 95 ajustado sazonalmente é de 273 2-2 27 4 218 4 a 273 2 2 27 4 328 0 Multiplicando esses limites pelo índice sazonal de dezembro de 68 61 obtemos limites de confiança inferior e superior de 149 8 e 225 0 em torno da previsão de ponto Dec-93 de 187 4. Limites de confiabilidade para previsões mais Do que um período de tempo em diante irá alargar-se geralmente à medida que o horizonte de previsão aumenta, devido à incerteza sobre o nível ea tendência, bem como os factores sazonais, mas é difícil de calcular em geral por métodos analíticos A maneira adequada para calcular limites de confiança para o LES Se você quer um intervalo de confiança realista para uma previsão mais de um período à frente, tendo todas as fontes de erro em conta, a sua melhor aposta é a utilização de métodos empíricos para a previsão é usando a teoria ARIMA, mas a incerteza nos índices sazonais é outra questão. Por exemplo, para obter um intervalo de confiança para uma previsão de duas etapas antecipadas, você pode criar outra coluna na planilha para calcular uma previsão de duas etapas para cada período, iniciando o passo um Reformulação Em seguida, calcule o RMSE dos erros de previsão de 2 etapas e use isso como a base para um intervalo de confiança de 2 passos à frente.
Comments
Post a Comment