Início » Blog Guia do Excel » Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel

Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel

Neste artigo é demonstrado como clcular o estoque mínimo, ponto de ressuprimento, estoque de segurança e estoque máximo em uma planilha Excel.

Em uma empresa, um de seus maiores ativos é o Estoque, e neste caso significa dinheiro parado, sem render, sendo assim temos que reduzir o estoque até o seu menor nível possível.

Mas como ter um estoque mínimo sem deixar descoberta a produção da indústria ou os clientes do varejo?

Neste artigo é demonstrado como realizar o cálculo do ponto de ressuprimento, estoque de segurança e estoque máximo. (Conheça também a Planilha de Controle de Estoque)

Em um primeiro momento é necessário definir a quantidade prevista para venda ou produção em cada período, como feito na tabela:

Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel 3

O período pode ser hora, dia, semana, mês, conforme definido pela empresa. No nosso exemplo é por dia.

No exemplo temos uma série histórica de saídas diárias, sendo o Lead Time, tempo entre o pedido e o recebimento da compra é de 5 dias.

Os cálculos são amplamente detalhados no vídeo acima neste artigo e abaixo são descritas as fórmulas.

Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel 4

MÉDIA

Média -> Média simples dos valores que fizemos utilizando a seguinte fórmula:

=MÉDIA(DESLOC(L5;0;0;CONT.VALORES($L$5:$L$1048576)))

A função média é utilizada para o cálculo dos indicadores Estoque de Segurança, Ponto de ressuprimento e Estoque Máximo.

Para o cálculo da média utilizamos a função MÉDIA, que tem como parâmetro apenas a lista de valores de saída.

Esta lista de valores foi criada utilizando a função DESLOC e a função CONT.VALORES para fazer com que a lista seja dinamicamente incrementada conforme a quantidade de valores aumentar.

DESVIO PADRÃO

DESVPAD.A-> Esta função realiza o cálculo de desvio padrão no Excel. Esta é uma medida de dispersão, ela demonstra o quanto os valores dispersam entre os valores acima ou abaixo em relação ao valor médio.

No Excel foi utilizada a função =DESVPAD.A(DESLOC(L5;0;0;CONT.VALORES($L$5:$L$1048576))), o parâmetro que esta função precisa é somente a lista de valores, no caso a lista de valores de saída.

De forma semelhante á função MÉDIA foram utilizadas as funções DESLOC e CONT.VALORES para deslocar dinamicamente a lista de valores.

LEAD TIME

O Lead Time para o ressuprimento de Estoque é o tempo entre o ponto de pedido (PP) e o ponto de ressuprimento (PR). De forma geral o termo indica o tempo entre o início e final de um ciclo ou atividade.

No nosso exemplo utilizamos o valor de 5 para indicar que o ciclo entre o ponto de pedido e o ponto de ressuprimento é 5 dias.

NÍVEL DE SEGURANÇA

O nível de segurança é o nível percentual de segurança que você terá na cobertura do seu estoque. Ou seja, a garantia que o estoque não ficará descoberto.

No caso utilizamos o percentual de 95% é um índice bastante seguro, por isso é amplamente utilizado.

Conforme a tabela acima você pode perceber que o índice Fator de Serviço aumenta gradativamente ao escolher níveis de serviço maiores para o nível de segurança do estoque e que quanto maior este índice, maior será a quantidade de produtos que você terá no seu estoque, investindo assim muito mais em estoque, local de armazenamento e processos.

ESTOQUE DE SEGURANÇA

Estoque de segurança é o estoque mínimo para evitar que falte matéria-prima, na produção/venda. É caracterizado pela fórmula seguinte: =INV.NORMP(F4)*RAIZ((D4^2)*E4+(C4^2)*(1^2)).

INV.NORMP(F4) = o F4 é o nível de segurança, ou seja 95%. O retorno que esta função dá é de 1,645, não sendo assim necessária a tabela demonstrada no nível de segurança.
D4^2 = D4 é o valor do desvio padrão, ele é elevado ao quadrado.
E4 = O E4 é o valor do Lead time, no caso 5.
C4^2 = O C4 é o valor da média dos valores de saída.
1^2 = O 1 é o período a ser considerado na sua base, no caso, usamos 1 pois um período é considerado um dia, caso um perído fosse 2 dias seria 2^2, uma semana 7^2, depende da sua necessidade. Por padrão 1

O resultado que temos desta fórmula é o nível mínimo de estoque, o estoque de segurança, no caso temos uma saída diária média de 124 produtos e o estoque de segurança é de 211,95 para não faltar entre o pedido e a entrega que é de 5 dias no Lead Time.

PONTO DE RESSUPRIMENTO

É o estoque mínimo do período, aonde temos que obrigatoriamente fazer um novo pedido, para que não ocorra a falta do material/produto. O cálculo do ressuprimento é definido pela função: =MÉDIA*LEAD TIME*ESTOQUE DE SEGURANÇA, 124,06*5*211,95, gerando assim o total de 832,26.

O valor deste cálculo é o utilizado para definir o momento em que deve ser realizada a compra do do produto, ou seja, ao estoque alcançar este nível é necessário que seja imediatamente realizada a compra do produto, para que em até 5 dias o produto chegue e ressupra o seu estoque.

Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel 6

No desenho acima é caracterizado pela flecha apontando o Pedido, perceba também o ponto Recebimento, aonde temos o valor mais baixo do nosso estoque, mas que nunca pode faltar.

ESTOQUE MÁXIMO

Estoque máximo ->define o estoque máximo que teremos ao realizarmos o ressuprimento em um primeiro momento. O cálculo é a soma do Estoque de segurança e do valor da média das saídas mutilicado pelo Lead time.

Desta forma conseguimos identificar que o estoque pode alcançar no máximo este nível após o ressuprimento, variando portanto entre o estoque de segurança 211,95 e o valor de 832,26.

TESTE DO RESSUPRIMENTO

Para identificarmos e o teste está realmente adequado, aplicamos os valores de entrada nos pontos de ressuprimento e analisamos como se comporta a série.

Estoque mínimo, ponto de ressuprimento, estoque de segurança Excel 7

Para isto no cálculo da previsão usamos a fórmula: =SE(N5<=$H$4;SE(CONT.NÚM(DESLOC(O4;;;-4)) >0;””; K5+$E$4);””), ela identifica se o valor do estoque alcançou o nível do ponto de ressuprimento. Ela retorna a data prevista para o fim do lead time.

Para o recebimento utilizamos a função =SE(ÉNÚM(O5);$I$4-(N5-SOMA(L5:L9));””) que identifica se foi atingida a data do término do lead time, previamente calculada, e retorna o valor previsto de compra com base no estoque máximo.

Na entrada utilizamos a fórmula =SEERRO(PROCV(K22;$O$1:$P21;2;0);0) aonde temos a busca do valor da entrada conforme a data prevista do recebimento da mercadoria.