Forecast simples

Segue um exercício / desafio.

Dadas séries históricas, como a do print abaixo, como fazer o forecast para os próximos 12 meses?

Forecast é um assunto extremamente difícil, pelo simples motivo de que não conseguimos prever o futuro.

Há vários tipos de técnicas, média móvel, ARMA, ARIMA, cada uma adequada a uma situação específica. Em particular, o R tem muitos métodos.

Como fazer direto no Excel?

Ou utilizando um misto de Excel / R / python?

Uma solução possível. Uma macro que faz decomposição clássica (tendência + sazonalidade).

A sazonalidade considerada é de 12 meses.

Exemplo. Uma das séries era sazonal. O forecast vai seguir isto.

Link para download: https://1drv.ms/x/s!Aumr1P3FaK7jkWqkK0Xx3RgNLZ__?e=JNoCDG


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Anúncios

A little integer problem

A little problem to be solved using integer variables (in excel or aimms);

Suppose we have 5 forest stands, with the volumes given as below:

i Vol
1 13.576,00
2 11.635,00
3 12.514,00
4 17.755,00
5 19.947,00

The distances to our basis in A or B are given below (these are random numbers).

j
i A B
1 64 73
2 100 79
3 85 81
4 66 99
5 98 81

A and B both need at least 30.000 tons.

30000 30000

Suppose also when you harvest a stand, it’s completely to one of the destinies, A or B – it can’t be fractioned.

I want to minimize distances, subject to the constraints.

What’s the solution of this case?

The data is given below:

The decision variable is reserved in columns K and L.

The sumproduct per destiny must be greater than 30.000.

The distance is calculated multiplying vol*distance*decision variable

The objective function is the sum of distance*decision variable.

In solver, we include the constraints. And we want to minimize distances.

Because this test is very basic, there’s not much possibilities. In general, integer programming is difficult.

Other little detail: if we need necessary to harvest the plantation, the sum of the decision variable must be one. If not, it can be less or equal 1.

This is a simple example of integer optimization using solver.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Quebrar vínculos no Excel

Um problema comum que surge é o de quebrar vínculos.

Existe o método corriqueiro,
Dados -> conexões -> Editar links, onde podemos fazer a quebra.

Porém, nem sempre dá certo.

Quando não der certo, outra tentativa a fazer é editar nomes.

Em Fórmulas -> Gerenciar nomes,

Procurar os nomes que têm vínculo ou estão como #N/D, e excluir.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Função para detectar campos numéricos

Em relatórios, digamos do BI ou SAP, muitas vezes a informação numérica vem com um # ou alguma outra informação não numérica. Isto pode dar um erro de “Tipos incompatíveis” no código subquente.

Para tratar, podemos utilizar a função “IsNumeric”. Ela retorna verdadeiro se o campo for numérico, e falso se não for numérico (string, data).

If Information.IsNumeric( variável ) Then          

                ‘se for numérico, vai em frente

Else

                ‘se não for, faz algum tratamento

endif

Vide exemplo aqui.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Procv em dois campos

Dúvida de um colega, que pode ser útil.

Tenho dois campos de critérios, como na figura abaixo. Como fazer um procv com dois campos?

Uma resposta simples: concatenar os valores dos dois campos, com o operador &:

Daí, fazer o procv sobre o campo concatenado:

Vide arquivo aqui como exemplo.



Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Otimizar Match

Sobre o puzzle de match de um site de relacionamento descrito abaixo, vamos fazer a formulação e resolver no excel – solver.

Fonte: PuzzleOR (http://puzzlor.com/)

A primeira coisa a fazer é transcrever os dados da tabela acima para o Excel.


Via fórmulas, nas colunas de K a Q, contamos o número de coincidências.

Esta fórmula verifica se o conteúdo das células é igual. Deve-se fazer fórmula semelhante para cada critério, e somar o número de pontos de cada match possível.

=SE(DESLOC($C$3;$K4;0) = DESLOC($C$3;$L4;0);1;0)

Transcrevo a tabela de pontos na aba “Formulação”, copiando e colando.

Agora, a formulação para resolver com o solver do Excel.

De C18 a L28, o espaço é reservado às variáveis de decisão.
Na célula C1, a função objetivo, um somarproduto da variável de decisão com os pontos.
=SOMARPRODUTO(C5:L14;C19:L28)

Quero maximizar tal função objetivo.

Quem não tiver o solver instalado, ir em Arquivo – Opções – suplementos do excel – suplementos

Acionar o solver (só é necessário fazer a primeira vez).

Em dados – solver, especificar:

  • A função objetivo como a célula C1
  • Células variáveis

Restrições:

  • variáveis binárias (ou é zero ou 1)
  • soma das linhas deve ser <= 1 (só posso fazer o match de uma mulher com um homem)
  • soma das colunas deve ser <= 1 (só posso fazer o match de um homem com uma mulher)

O método deve ser LP Simplex (ou seja, é um Linear program que utiliza o método Simplex), e resolver.

A função objetivo vai assumir o valor 33, e os matches serão os valores 1 na tabela.

Com, isso, esperamos casar as pessoas com o maior número de características possíveis.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Exercício de estatística

Temos uma série histórica de dados (digamos, uma medição por mês do consumo).

Responda:

  • Qual a distribuição Normal que melhor fita os dados da Coluna A?
  • Qual a distribuição Uniforme que melhor fita os dados da Coluna A?
  • Desenhe um histograma desta distribuição
  • Qual das duas distribuições (Normal ou Uniforme) fita melhor os dados?

Respostas:

Primeiro, para que serve isto?

Uma aplicação clássica é para forecast, por exemplo. Tenho uma série de dados históricos, e quero prever o comportamento futuro, e tomar ações, digamos, considerar um estoque mínimo para garantir um nível de serviço.

A Normal é a curva em forma de sino. Ela tem dois parâmetros, média e desvio padrão.
=MÉDIA(A2:A94)
=DESVPAD.A(A2:A94)

No fundo, fazer forecast é como se eu tivesse que dar um chute.

Se eu não souber muito bem o comportamento da curva, posso dizer: fica entre 270 e 723.


A uniforme é exatamente isto, ela tem a mesma probabilidade de dar qualquer valor entre mínimo e máximo.
=MÍNIMO($A$2:$A$94)
=MÁXIMO($A$2:$A$94)

Para ilustrar o resultado, fiz mais duas listas. A primeira faz um sorteio baseado numa normal, e a segunda, numa uniforme.



A uniforme é mais fácil de explicar.
É como usar a função aleatório entre (mínimo, máximo).
Porém, ela é discreta, e a versão contínua é a fórmula seguinte.
=ALEATÓRIO()*($F$17-$F$16)+$F$16

Para sortear uma normal, o truque é usar a fórmula inv.normal (inverso da normal acumulada), com um parâmetro aleatório.
=INV.NORM(ALEATÓRIO();$F$11;$F$12)

Fiz o histograma com fórmulas cont.ses. A ideia é contar quantos itens das listas estão entre 250 e 300, 300 e 350, etc…

Nota-se que a normal parece representar melhor os dados que a uniforme, para este caso específico.

A distribuição acumulada é outra forma de ver.

Clicar em F9 para sortear outros valores, e ver a diferença.

Segue arquivo no onedrive.

Estatística não é a área que mais domino, então sugestões e complementações são bem vindas.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/