Implementação da planilha de ajuste sazonal e suavização exponencial É direto realizar ajustes de sazonal e ajustar 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 o alisamento exponencial linear nos seguintes dados trimestrais de vendas da Outboard Marine: Para obter uma cópia do próprio arquivo de planilha, clique aqui. A versão do alisamento exponencial linear que será usada aqui para fins de demonstração é a versão Brown8217s, meramente porque pode ser implementada com uma única coluna de fórmulas e há apenas uma constante de suavização para otimizar. Normalmente, é melhor usar a versão Holt8217s que possui constantes de suavização separadas para nível e tendência. O processo de previsão prossegue da seguinte forma: (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 alisamento exponencial linear e (iii) finalmente, as previsões sazonalmente ajustadas são quantitativas para obter previsões para a série original . O processo de ajuste sazonal é realizado nas colunas D a G. O primeiro passo no ajuste sazonal é calcular uma média móvel centrada (realizada aqui na coluna D). Isso pode ser feito tomando a média de duas médias de um ano que são compensadas por um período relativo um 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 é igual.) O próximo passo é calcular a proporção para a média móvel - i. e. Os dados originais divididos pela média móvel em cada período - o que é realizado aqui na coluna E. (Isso também é chamado de quottrend-cyclequot componente do padrão, na medida em que os efeitos da tendência e do ciclo comercial podem ser considerados como sendo tudo isso Permanece após uma média de um ano inteiro de dados. Claro, as mudanças de mês a mês que não são devidas à sazonalidade podem 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 pela primeira média de todos os índices para essa estação específica, o que é feito nas células G3-G6 usando uma fórmula AVERAGEIF. Os rácios médios são então redimensionados de modo que somam exatamente 100 vezes o número de períodos em uma estação, ou 400 neste caso, o que é feito 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 quarto do ano que representa. A média móvel centrada e os dados sazonalmente ajustados ficam assim: note que a média móvel normalmente se parece com uma versão mais suave 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 linear de suavização exponencial aos dados dessazonalizados, começando na coluna G. Um valor para a constante de alisamento (alfa) é inserido acima da coluna de previsão (aqui, na célula H9) e Por conveniência, é atribuído o nome do intervalo quotAlpha. quot (O nome é atribuído usando o comando quotInsert Name Createquot.) O modelo LES é inicializado definindo as duas primeiras previsões iguais ao primeiro valor real da série sazonalmente ajustada. A fórmula usada aqui para a previsão LES é a forma recursiva de equação única do modelo Brown8217s: Esta fórmula é inserida na célula correspondente ao terceiro período (aqui, célula H15) e copiada a partir daí. Observe que a previsão LES para o período atual refere-se às duas observações precedentes e aos dois erros de previsão precedentes, 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 desejássemos usar um alisamento exponencial linear em vez de linear, poderíamos substituir a fórmula SES aqui em vez disso. Também poderíamos usar Holt8217s em vez do modelo LES Brown8217s, o que exigiria mais duas colunas de fórmulas para calcular o nível e a tendência Que são usados na previsão). Os erros são computados na próxima coluna (aqui, coluna J) subtraindo as previsões dos valores reais. O erro quadrático médio é calculado como a raiz quadrada da variância dos erros mais o quadrado da média. (Isso se segue à identidade matemática: VARIÂNCIA MSE (erros) (MÉDIA (erros)) 2. No cálculo da média e variância dos erros nesta fórmula, os dois primeiros períodos são excluídos porque o modelo na verdade não inicia a previsão até O terceiro período (linha 15 na planilha). O valor ideal de alfa pode ser encontrado alterando o alfa manualmente até que o RMSE mínimo seja encontrado, ou então você pode usar o quotSolverquot para executar uma minimização exata. O valor de alfa que o Solver encontrou é mostrado aqui (alfa0.471). Geralmente é uma boa idéia traçar os erros do modelo (em unidades transformadas) e também calcular e traçar suas autocorrelações em atrasos de até uma estação. Aqui está uma série de séries temporais dos erros (ajustados sazonalmente): as autocorrelações de erro são computadas usando a função CORREL () para calcular as correlações dos erros com elas mesmas atrasadas por um ou mais períodos - os detalhes são mostrados no modelo de planilha . Aqui está um enredo das autocorrelações dos erros nos primeiros cinco atrasos: as autocorrelações nos intervalos 1 a 3 são muito próximas de zero, mas o pico no intervalo 4 (cujo valor é 0.35) é um pouco incômodo - sugere que o O processo de ajuste sazonal não foi completamente bem sucedido. No entanto, na verdade, é apenas marginalmente significativo. 95 bandas de significância para testar se as autocorrelações são significativamente diferentes de zero são mais ou menos 2 SQRT (n-k), onde n é o tamanho da amostra e k é o atraso. Aqui n é 38 e k varia de 1 a 5, então a raiz quadrada de n-menos-k é em torno de 6 para todos eles 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 do alfa à mão neste modelo do Excel, você pode observar o efeito na série de tempo e nos gráficos de autocorrelação dos erros, bem como no erro da raiz-médio-quadrado, que será ilustrado abaixo. Na parte inferior da planilha, a fórmula de previsão é citada no futuro, simplesmente substituindo as previsões por valores reais no ponto em que os dados reais se esgotaram - ou seja. Onde quotthe futurequot começa. (Em outras palavras, em cada célula onde um futuro valor de dados ocorreria, uma referência de célula é inserida que aponta para a previsão feita para esse período.) Todas as outras fórmulas são simplesmente copiadas de cima para cima: Observe que os erros para as previsões de O futuro é calculado para ser zero. Isso não significa que os erros reais serão zero, mas sim reflete apenas o fato de que, para fins de predição, estamos assumindo que os dados futuros serão iguais às previsões em média. As previsões resultantes de LES para os dados dessazonalizados são assim: com este valor particular de alfa, otimizado para previsões de um período de antecedência, a tendência projetada é ligeiramente ascendente, refletindo a tendência local observada nos últimos 2 anos ou então. 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 for ajustado manualmente para 0.25: A tendência de longo prazo projetada agora é negativa em vez de positiva. Com um menor valor de alfa, o modelo está colocando mais peso em dados mais antigos em A estimativa do nível e da tendência atual e suas previsões de longo prazo refletem a tendência de queda observada nos últimos 5 anos em vez da tendência ascendente mais recente. Este gráfico também ilustra claramente como o modelo com um menor valor de alfa é mais lento para responder aos pontos de referência nos dados e, portanto, tende a fazer um erro do mesmo sinal por vários períodos seguidos. Seus erros de previsão de 1 passo a frente são maiores em média do que os obtidos antes (RMSE de 34,4 em vez de 27,4) e fortemente auto-correlacionados positivamente. A autocorrelação de lag-1 de 0,56 excede muito o valor de 0,33 calculado acima para um desvio estatisticamente significativo de zero. Como alternativa para diminuir o valor do alfa, a fim de introduzir mais conservadorismo em previsões de longo prazo, um fator de amortecimento de quotstend às vezes é adicionado ao modelo, a fim de tornar a tendência projetada abrandar depois de alguns períodos. O passo final na construção do modelo de previsão é para quantificar as previsões do LES, multiplicando-os pelos índices sazonais apropriados. Assim, as previsões reestruturadas na coluna I são simplesmente o produto dos índices sazonais na coluna F e as previsões de LES temporariamente ajustadas na coluna H. É relativamente fácil calcular intervalos de confiança para previsões de um passo a frente feitas por este modelo: primeiro Computa o RMSE (erro da raiz-meio-quadrado, que é apenas a raiz quadrada do MSE) e depois calcula 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 95 para uma previsão de um período anterior é aproximadamente igual ao ponto de previsão mais-ou-menos-duas vezes o desvio padrão estimado dos erros de previsão, assumindo que a distribuição do erro é aproximadamente normal e o 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 do desvio padrão dos futuros erros de previsão porque leva também o viés, bem como variações aleatórias.) Os limites de confiança Para a previsão ajustada sazonalmente são então resgatados. Juntamente com a previsão, multiplicando-os pelos índices sazonais apropriados. Nesse caso, o RMSE é igual a 27,4 e a previsão ajustada sazonalmente para o primeiro período futuro (dezembro-93) é 273,2. Então o intervalo de confiança 95 ajustado sazonalmente é de 273,2-227,4 218,4 a 273,2227,4 328,0. Multiplicando esses limites pelo índice sazonal Decembers de 68,61. Obtemos limites de confiança inferiores e superiores de 149,8 e 225,0 em torno da previsão do ponto 93 de 187,4. Os limites de confiança para as previsões mais de um período adiante geralmente se ampliarão à medida que o horizonte de previsão aumentar, devido à incerteza sobre o nível e a tendência, bem como os fatores sazonais, mas é difícil computá-los em geral por métodos analíticos. (A maneira apropriada de calcular os limites de confiança para a previsão LES é usando a teoria ARIMA, mas a incerteza nos índices sazonais é outra questão.) Se você quer um intervalo de confiança realista para uma previsão de mais de um período adiante, tomando todas as fontes de Erro na sua conta, a sua melhor opção é usar métodos empíricos: por exemplo, para obter um intervalo de confiança para uma previsão anterior de 2 passos, você poderia criar outra coluna na planilha para calcular uma previsão de duas etapas para cada período ( Por bootstrapping a previsão one-step-ahead). Em seguida, calcule o RMSE dos erros de previsão de duas etapas e use isso como base para um intervalo de confiança de 2 passos. O que é um índice sazonal - O quarto trimestre do ano é os meses de outubro a dezembro. Como você provavelmente sabe, e nós assinalamos nos vídeos do capítulo um, a Amazon vende muito mais mercadorias durante o quarto trimestre do que em qualquer outro trimestre, principalmente por causa da temporada de férias. Este é um exemplo de sazonalidade, e o problema com a sazonalidade é que é realmente difícil prever valores futuros de uma série temporal. Se você notou, todos os exemplos que fizemos até agora na previsão não tiveram sazonalidade. Eles foram dados anuais, mas agora estamos prontos para abordar a questão da sazonalidade nos dois capítulos restantes deste vídeo. Então, um conceito realmente importante que realmente irá refinar a sua compreensão, neste vídeo, é o conceito de um índice sazonal e, em seguida, no resto do capítulo, você ensinará o índice ao método de média móvel, que é simples, mas poderoso Método para incorporar sazonalidade em suas previsões, usadas por muitas corporações. Ok, então, suponha que você tenha para Q1 até Q4 esses quatro números, que chamaremos de índices sazonais. Então, o que isso significa? O índice sazonal do Q4 de 1,3 significa que no quarto trimestre essa empresa tende a vender 30 mais do que um trimestre médio. Isso é o que o 1.3 significa. E no primeiro trimestre, esta empresa vende 20 menos que um trimestre médio. Isso é o que significa 0.8. Assim, os índices sazonais devem ter uma certa propriedade. Eles devem ser médios para um. Em outras palavras, os trimestres que estão acima da média devem ser cancelados pelos trimestres abaixo da média. Mas você realmente não pode fazer muita previsão em dados trimestrais ou dados mensais, se você não entender a sazonalidade, e esse será o tema principal desse capítulo inteiro, mas neste vídeo, queremos apenas dar uma compreensão simples dos índices sazonais. Então, temos um pouco de provocação de cérebro para você que eu uso muitas vezes quando treino em empresas e muito poucas pessoas adquirem o avanço do cérebro. Então, nós trabalharemos você através dele. Ok, então vamos ver se entendemos a sazonalidade. Então, suponha que você trabalhe para uma empresa cujo quarto trimestre é ótimo. O índice sazonal é de dois. Então, o que isso significa? Durante o quarto trimestre, suas vendas tendem a ser o dobro de um trimestre médio, e eles foram muito ruins no primeiro trimestre. Seu índice sazonal é de 0,5, o que significa que, no primeiro trimestre, suas vendas tendem a ser metade de um trimestre médio. Vamos analisar alguns dados de vendas para esta empresa fictícia. Suponhamos que, no quarto trimestre de 2014, vendessem 400 milhões de dólares em mercadorias. Q1 de 2015, venderam 200 milhões de dólares em mercadorias, e você foi convidado a avaliar o desempenho da empresa como consultor externo. Eles estão melhorando ou estão fazendo pior A análise ingênua é a seguinte. As vendas caíram 50. Duzentos é 50 de quatrocentos. Esta empresa tem problemas reais. Bem, você não é um consultor muito bom se você acha isso, porque está negligenciando a sazonalidade. O que você precisa fazer é realmente desestabilizar as vendas. Eu costumo dizer dessalinização, mas dessazonalizar. Então, o que você quer fazer é dizer, oi, o que realmente aconteceu em cada trimestre, em termos de um trimestre médio, basicamente, o quarto trimestre de 2014, mas o índice sazonal era dois. Então, isso realmente gosta de vender isso em um trimestre médio. Você divide pelo índice sazonal. Essa é uma estimativa muito boa do que o nível foi durante o Q4. Em outras palavras, 400 no quarto trimestre são basicamente dizendo que o nível da série temporal, com base nessa observação, foi de 200 no quarto trimestre. Agora, quando você dessazona o Q1 de 2015, você divide pelo índice sazonal desse trimestre de 0,5, e você obtém 400 em um trimestre médio. Então, se você olhar para o caminho certo, mesmo que as vendas caiam 50, os dados indicam que o nível de vendas dobrou do quarto trimestre de 2014 para o primeiro trimestre de 2015. Então, você pode ver deste exemplo muito simples, se você não entendesse a sazonalidade, Você faria uma conclusão incorreta de que esta empresa está piorando, quando eles realmente estão ficando fantásticos. Então, no próximo vídeo apresentamos o índice ao método da média móvel, que pode ser usado para incorporar sazonalidade nas previsões e estimar índices sazonais. Curriculum vitae do currículo Auto-Scroll Professor Wayne Winston ensinou técnicas avançadas de previsão para empresas da Fortune 500 por mais de vinte anos. Neste curso, ele mostra como usar as ferramentas de análise de dados da Excels, incluindo gráficos, fórmulas e funções para criar previsões precisas e perspicazes. Saiba como exibir os dados das séries temporais visualmente, certifique-se de que suas previsões são precisas, ao computar erros e usar o uso das tendências para identificar as tendências e o crescimento do modelo de dados extravagantes para a sazonalidade e identificar variáveis desconhecidas, com análise de regressão múltipla. Uma série de desafios de prática ao longo do caminho ajuda você a testar suas habilidades e comparar seu trabalho com as soluções Waynes. Lynda é um provedor de educação registrada do PMI. Este curso se qualifica para unidades de desenvolvimento profissional (PDUs). Para ver a atividade e os detalhes da PDU para este curso, clique aqui. O logotipo do PMI Registered Education Provider é uma marca registrada do Project Management Institute, Inc. Os tópicos incluem: Traçar e exibir dados da série temporal Criando um gráfico médio móvel Contabilização de erros e viés Usando e interpretando linhas de tendência Modelagem de crescimento exponencial Cálculo da taxa de crescimento anual composto (CAGR) Analisando o impacto da sazonalidade Apresentando o método da relação com a média móvel Previsão com regressão múltipla Nível de habilidade Intermediário
No comments:
Post a Comment