Modelagem em Excel

O modelo foi construído em Excel de forma a simular a quantidade de estoque prevista semana a semana.

A partir da análise da demanda pode-se visualizar perfeitamente que o fornecedor não tem acesso à demanda efetiva para fazer o planejamento do estoque de segurança de acordo. Verifica-se ainda a forte confiabilidade da previsão fornecida pelo cliente, portanto, é razoável assumir as seguintes premissas para o modelo:

• A cobertura em estoque é calculada com base na previsão de consumo; • com exceção de 2011 o faturamento real corresponde ao consumo real do

Para a visualização mais clara da cobertura de 3 semanas, a demanda mensal foi dividida igualmente pelo número de semanas do mês sendo que cada segunda- feira marca o início de uma semana. Este método permite uma maior confiabilidade do nível de serviço, pois indica que o reabastecimento se dá no primeiro dia da semana. Foi criada uma planilha com a previsão de consumo e a entrega, classificada por itens, separada em semanas. A Figura 13 indica a família de cada item, seu lead time de entrega e a demanda prevista para cada semana, representada pela data de sua respectiva segunda-feira.

Figura 13 – Demanda dividida em semanas

Após esta etapa a demanda foi agrupada em famílias (case, roda-guia, suporte e travessa), conforme linha produtiva de acabamento, para facilitar a visualização. O simulador foi dividido em 3 partes: a primeira para simulação das diferentes políticas de estoque, a segunda para o cálculo do estoque de segurança recomendado pela teoria, a qual foi subdividida em abas por família e a terceira foi construída juntamente com a primeira para o fornecimento de gráficos de atendimento e nível de serviço.

Figura 14 – Simulador de atendimento Legenda:

Células azul claro – demanda semanal para cada família.

Células em amarelo – o estoque inicial a cada semana segundo a política de cobertura adotada (3, 2 e 1 semana, respectivamente).

Células em vermelho – o estoque inicial a cada semana, de acordo com o cálculo teórico.

Células em verde – a entrega efetiva da semana.

Células em branco (linhas 135 a 137 da planilha) – o estoque previsto ao final de cada semana, sendo igual ao estoque inicial menos a entrega real.

As células em amarelo são calculadas automaticamente segundo a política de cobertura. A cobertura deve levar em consideração a previsão futura da demanda. Assim, o estoque previsto pode ser calculado pela multiplicação da quantidade de semanas adotadas pela política, pela média das 4 próximas semanas. O trabalho

sempre é feito com 4 semanas, pois as análises são sempre mensais. Como por exemplo, a célula K141 pode ser calculada pela expressão =ARRED(MÉDIA(L$111:O$111);0)*$I114. Aplicando a fórmula para as demais células obtêm-se as linhas amarelas da planilha.

Para se determinar o estoque previsto segundo a teoria é preciso determinar os valores de k e DL. Inicialmente é explicada a determinação de DL, visto que k é função deste valor. O valor de DL é conhecido como desvio padrão da demanda no lead time e está associado à demanda média e ao lead time médio, bem como seus respectivos desvios padrão conforme a equação 14.

Conforme já informado anteriormente, a programação é feita mensalmente, portanto os valores de d e d devem ser analisados mês a mês. O problema é que, devido ao fato da demanda ter sido igualmente distribuída entre as semanas do mês o valor de d será zero, contudo este valor é errôneo visto que a demanda é probabilística. A alternativa que será adotada é supor o comportamento da demanda indicada com base no comportamento da demanda real.

Inicialmente assume-se que a demanda comporta-se segundo uma distribuição normal. Como há apenas quatro amostras por mês, os testes de aderência podem não ter sua eficiência comprovada e, de maneira geral, Oliveira (1997) indica que, historicamente, há pouco ou nenhum prejuízo nos resultado quando da suposição preliminar da aderência dos dados. Caso a distribuição não seja aderente, a conclusão será perceptível no momento da análise dos resultados.

Para o cálculo do desvio padrão foram estudadas duas propostas de estimativa. Na primeira calculou-se o desvio padrão percentual da demanda de cada mês para cada família. Este valor é dado pela razão d por d da demanda real. Foi feito então cálculo da média das razões ao longo do período estudado para uma estimativa.

Seguem abaixo os valores encontrados para cada família: Tabela 1 – Primeira proposta de desvios percentuais

A hipótese II de estimativa foi com base no módulo da média dos desvios. Analisou-se as divergências semana a semana em % através dos desvios percentuais dados em 2.21:

(2.21)

Onde:

D = desvio em porcentagem R = entrega efetiva da semana P = previsão de entrega indicada

Obs.: Para P = 0 se R = 0 então D = 0, senão, se P = 0 e R <> 0 então D = 100%

Montou-se então uma tabela com os desvios para as 209 semanas do estudo. Segue abaixo as primeiras 44 linhas da tabela:

Tabela 2 – Exemplos dos desvios percentuais obtidos

! ! " ! ! " ! ! " " # ! ! # # ! # " " ! !

! " ! ! " " ! ! ! ! ! ! $ $ $ $ $ ! ! ! ! ! ! ! ! !

Calculando-se as médias por famílias tem-se que:

Tabela 3 – Segunda proposta de desvios percentuais

! !

Os valores de µl e l serão estimados visto que não se dispõe de um sistema que controle o tempo de chegada. Sabe-se que o transit time médio fornecido pelo operador logístico é de 3 semanas. Quando há carregamentos semanais esta é uma

boa aproximação para o lead time de entrega, porém no caso de feriados existe um acréscimo de uma semana (equivalente à remoção de um navio programado) e de duas semanas no caso de férias coletivas (equivalente à remoção de dois carregamentos de containers).

Considerando que existem em média 8 feriados por ano que causam a redução de um navio na programação e que a fundição historicamente apresenta férias coletivas de quinze dias a cada dois anos, obtêm-se os resultados indicados na tabela abaixo:

Tabela 4 – Análise estatística do transit time

%& ! ' ( % % ) ( * + , " - " . $ %/ " %01 " " " % 2 3 % 2 3 4/ $ , 5 2 ! 3 !

Portanto adota-se µl = 3,2 semanas e l = 0,5 semanas.

O valor de k também é dependente do valor de Q. Conforme indicado na teoria, o valor de Q será calculado pelo EOQ (2.5) considerando-se uma taxa de juros de 2% ao mês e inflação de 1,5% ao mês. O custo de cada item será representado pelo custo médio de fabricação dos itens de uma mesma família. Devido ao referencial de fornecedor deste trabalho, não existe custo associado à colocação de pedido, mas sim de processos de embarque. Portanto, neste trabalho,

o valor de Q representa a quantidade de peças a serem embarcadas para cada embarque a ser realizado no mês, considerando-se um máximo de 4 embarques por mês devido à freqüência dos navios. Supõe-se que para qualquer tamanho de Q não há restrição de número de containers e que há aproveitamento de pelo menos 95% no embarque, conforme política interna da fundição para aprovação dos embarques. É estimado um custo F de R$800,00 por embarque, referentes aos custos de desembaraço no porto. Custos variáveis não serão considerados, visto que são os mesmos independentemente da política de estoque adotada e o foco do presente trabalho é a comparação entre as políticas.

O último parâmetro para determinação de k é o P2* que no caso, é fornecido como a meta do cliente e é igual a 2%.

A partir dessas informações, a planilha foi preenchida com as informações segundo recomendação da teoria e traçados diversos gráficos de atendimento para análise do sistema. As Tabelas 5 e 6 indicam respectivamente os parâmetros adotados para o cálculo do EOQ e do custo de manutenção h na planilha indicada pela Figura 15.

Tabela 5 – Parâmetros para cálculo do EOQ

5 "& 6 !

Tabela 6 – Custo médio por família

6 7

6 7

6 7 !

Figura 15 – Planilha para cálculo do valor teórico do estoque de segurança

Destaca-se no sistema indicado pela Figura 15 a existência de uma variável Q efetiva. Este valor é igual à demanda D total do mês caso o EOQ seja maior que D.

Com os valores disponíveis neste ponto do trabalho, é possível calcular o valor da função Gu(k). Para determinar k, foi montada um planilha com os valores da função Gu(k) por meio da aplicação da equação 2.18 e o uso da função NORMP.N (distribuição normal) do Excel. O valor de k é obtido através da função PROCV. Abaixo, os vinte e três primeiros valores encontrados na tabela. A procura é feita com precisão de três casas decimais.

Tabela 7 – Primeiras 23 linhas da função normal de perdas ! ! ! ! ! ! ! ! ! ! !! ! ! ! ! ! !