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:
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.
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.
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.
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.
Planilha de Estoque Profissional
Precisa de uma solução profissional para o estoque da sua empresa? Conheça a nossa planilha de estoque profissional
Clique abaixo para ver detalhes da nossa planilha profissional de controle de estoque e tenha um controle total do seu estoque.
Download Planilha Gratuita
Baixe a planilha de Estoque Mínimo, Ponto de Ressuprimento, Estoque de Segurança no Excel no botão abaixo.