Planilha para Gestão de Estoque 2 comentários


Neste post vamos demonstrar como utilizar uma planilha para controle e gestão de estoque utilizando o Microsoft Excel.


Controle de estoque é uma área muito importante de uma empresa, grande ou pequena, pois é através dele que ela será capaz de prever o quanto que será necessário comprar no próximo pedido ao fornecedor, além de fornecer informações úteis sobre as vendas, já que muitas vezes os relatórios do setor de vendas não são muito claros e não condizem com a realidade, afinal, o setor de vendas quer comissões. O principal objetivo do controle de estoque é otimizar o investimento em estoques, aumentando o uso eficiente dos meios internos de uma empresa, e minimizar as necessidades de capital investido em estoque“. (Marco Aurélio Dias, Administração de Materiais, 1995)

Então, o que necessitamos é de uma ferramenta que irá permitir contabilizar as vendas da empresa, compras, além de avaliar o estoque, realizar previsão das saídas e classificar os materiais. Uma planilha útil para o profissional de logística ou qualquer outra profissão que necessite controlar materiais de alto, médio ou baixo valor.

Criando um cadastro do inventário

Antes de tudo, vamos criar uma aba para a qual iremos cadastrar cada um dos itens que fazem parte do nosso inventário. Juntamente com este cadastro vamos incluir também um painel contendo os principais indicadores que são importantes para a gestão do estoque. Vamos chamar esta aba de “Item”, e inseriremos as colunas abaixo:

  • Código do Item: Insira nesta coluna a fórmula =SE(C6=””;””;”0000″&1), na primeira linha, e na segunda insira =SE(C7=””;””;”0000″&1+B6). Isso irá gerar um código incremental para o item cada vez que digitarmos um novo item na coluna “Item”.
  • Item: Será a descrição do item cadastrado. Note que ao inserir um novo nome de item, um código será automaticamente criado na coluna “Código do Item”.
  • Estoque Inicial: Um campo em aberto para incluir as unidades inciais em estoque. É necessário para iniciar o controle de estoque quando já possuímos volume estocado. Informe-o nesta coluna.
  • Vl Unitário: Um campo aberto para inserirmos o valor unitário de cada unidade. Útil para realizarmos as análises de grandeza monetária e não apenas em volume unitário.

GestaoEstoq1

Registrando as movimentações

Havendo um cadastro íntegro de todos os materiais, itens ou produtos podemos iniciar o registros de cada entrada ou saída de item. Estas informações servirão de base para realizarmos todos os cálculos dos indicadores necessários para gestão adequada do estoque.

Criaremos uma aba nomeada “Movimen” contendo uma listagem com os campos a saber:

  • Código de Item: O mesmo código de item que criamos no formulário anterior. Será utilizado aqui para relacionar as informações de movimentação de item com os detalhes do item e vice-versa. Neste campo iremos utilizar um recurso de validação de dados, que irá restringir a entrada de dados na célula que deverá ser exatamente igual à um determinado critério, neste caso, aos dados específicos de uma lista. Selecione as células desejadas, na guia superior “Dados”, clique em “Validação de Dados”. Na caixa “Validação de Dados”, selecione a guia “Configurações”, em “Critério de validação”, escolha “Permitir:” > “Lista” e no campo “Fonte” aponte para a guia “Item” (onde estão cadastrados todos os materiais) =Item!$B$6:$B$1003.

GestaoEstoq2

  • Item: Este campo não é editável, deve ser preenchido com a fórmula =SE(B4=””;””;PROCV(B4;Item!$B$6:$C$1003;2;0)). Esta fórmula significa que, caso haja informado um “Código de item”, localize a descrição do item. Note que ele irá se alterar automaticamente, conforme inserir o código do item.
  • Data: Um campo para indicar a data em que ocorreu a movimentação.
  • Qtd. (Und): Um campo para indicar a quantidade em unidades que foi movimentada (entrada ou saída).
  • Valor: Este campo não é editável e deve ser preenchido com a fórmula: =SE(B3=””;””;PROCV(B3;Item!$B$6:$E$1003;4;0)*E3) que localiza o valor unitário do cadastro do item e multiplica pelas unidades em saída ou entrada.
  • Tipo: Um campo para identificar se a movimentação é uma entrada ou uma saída. Utilizaremos a mesma validação de dados aqui, mas neste caso vamos informar manualmente quais dados serão permitidos na lista.

GestaoEstoq3


 Criando os indicadores para o painel

Considerando que possuímos, agora, um cadastro íntegro e os registros das movimentações no estoque, é momento de fazermos os cálculos na planilha que irão nos alimentar com indicadores fáceis a visualizar na tela do Excel.

Para estes indicadores conhecemos alguns que se baseiam no estado atual das circunstâncias (por exemplo, estoque atual) mas, na grande maioria, nos fundamentaremos em um período selecionado: um dia, uma semana, um mês. Para podermos contabilizar estes intervalos será necessário abrir ao usuário a possibilidade de entrada destes dados e, é por este motivo que vamos criar duas novas linhas na aba “Item”, e denominá-las de “Data Início” e “Data Final”.

GestaoEstoq4

Então, vamos criar as colunas a seguir e a descrição de como calculá-las:

  • Estoque Atual (Und): O cálculo para o estoque atual é [Estoque Inicial]-[Saídas]+[Entradas]. É um indicador que nos apontará a quantidade de unidades em estoque no momento presente. Crie esta colune e insira a fórmula: =D7-SOMASES(Movimen!$E$4:$E$4501;Movimen!$G$4:$G$4501;”Saída”;Movimen!$B$4:$B$4501;Item!$B7)+SOMASES(Movimen!$E$4:$E$4501;Movimen!$G$4:$G$4501;”Entrada”;Movimen!$B$4:$B$4501;Item!$B7) e arraste até o final.
  • Saída Média: Saída média representa a soma do volume que foi retirado do estoque dividido pela quantidade de vezes que isso ocorreu. Para este cálculo vamos considerar, também, o período selecionado em “Data Início” e “Data Final”. Fórmula: =SEERRO(SOMASES(Movimen!$E$4:$E$4501;Movimen!$B$4:$B$4501;Item!$B7;Movimen!$G$4:$G$4501;”Saída”;Movimen!$D$4:$D$4501;”>=”&Item!$C$3;Movimen!$D$4:$D$4501;”<=”&Item!$C$4)/CONT.SES(Movimen!$B$4:$B$4501;Item!$B7;Movimen!$G$4:$G$4501;”Saída”;Movimen!$D$4:$D$4501;”>=”&Item!$C$3;Movimen!$D$4:$D$4501;”<=”&Item!$C$4);0) e arraste até o final.
  • Estoque Período: Este indicador é semelhante ao estoque atual, mas é calculado considerando o período determinado no nosso formulário: =D7-SOMASES(Movimen!$E$4:$E$4501;Movimen!$B$4:$B$4501;Item!$B7;Movimen!$G$4:$G$4501;”Saída”;Movimen!$D$4:$D$4501;”>=”&Item!$C$3;Movimen!$D$4:$D$4501;”<=”&Item!$C$4)+SOMASES(Movimen!$E$4:$E$4501;Movimen!$B$4:$B$4501;Item!$B7;Movimen!$G$4:$G$4501;”Entrada”;Movimen!$D$4:$D$4501;”>=”&Item!$C$3;Movimen!$D$4:$D$4501;”<=”&Item!$C$4) e arraste até o final.
  • Dias em Estoque: Este indicador demonstra quanto tempo o estoque vai durar no ritmo atual das vendas. Nesta planilha, os dias em estoque é calculado dividindo [Estoque Período] / [Saída Média]. A fórmula que utilizamos aqui é =SEERRO(H7/G7;0) e arraste até o final. Note que “=SEERRO()” é utilizada como condicional para que, caso retorne como erro no resultado, mostre-nos o número “0”.
  • Obj Dias Estoque: Este campo deverá ser alimentado manualmente, pois representará o seu objetivo em dias de estoque, ou seja, qual é o indicador ideal esperado, de acordo com as premissas da sua atividade. Ele será utilizado como comparativo com o resultado atual de dias em estoque com o o objetivo.
  • Giro de Estoque: O indicador representa a quantidade de vezes em que seu estoque se renova. Por exemplo, se o giro for 3, isto significa que seu estoque será renovado (comprado ou vendido), 3 vezes dentro do período selecionado. Para o fornecedor demonstra que ele possui 3 oportunidades de vender o mesmo lote para você dentro deste período. O cálculo para “Giro de Estoque” é o inverso de “Dias em Estoque”: =SEERRO(G7/H7;0) e arraste até o final.
  • Lead Time Médio (dias): O lead time ou tempo para entrega deve ser inserido também manualmente considerando uma estimativa de seus fornecedores para aquele item. Poderemos utilizar uma média de tempo entre os 3 fornecedores, por exemplo, que entregam determinado item. Este campo utilizaremos para calcular o “Estoque Mínimo” ou “Estoque de Segurança”.
  • Estoque de Reserva: Um campo com entrada manual e deverá ser preenchido com o estoque (em unidades) que, obrigatoriamente, precisa existir fisicamente dentro do nosso estoque. Este campo utilizaremos para calcular o “Estoque Mínimo” ou “Estoque de Segurança”.
  • Estoque Mínimo: Também chamado de “Estoque de Segurança” representa o estoque que, minimamente, deverá ser reservado dentro deste controle. É uma medida de segurança para evitar falta de atendimento das saídas solicitadas. Fórmula: =N7+M7*G7/($C$4-$C$3) e arraste até o final.

Como estamos utilizando um painel no cadastro dos itens dentro do estoque, vamos criar um indicador visual. Um círculo vermelho para indicar que os dias de estoque estão abaixo ou acima da faixa de 50% da nossa meta, que é ruim – estoques muito baixos podem levar a falta de atendimento dos solicitantes e estoques muito altos podem levar a custos elevados de manutenção – e um círculo verde para indicar que os dias de estoque estão dentro de 50% da nossa meta de estoque.

GestaoEstoq5

Insira uma coluna entre “Dias em Estoque” e “Obj Dias Estoque”. Provavelmente será a coluna “K”. Selecione a coluna inteira e altere a fonte para “Webdings”. Insira a fórmula =SE(B7=””;””;”n”) e arraste até o final. Ou seja, aqui vamos exibir a letra “n” (que na fonte Webdings é um círculo) somente se houver algum item cadastrado na linha.

Vamos colorir estes círculos utilizando a formatação condicional do Excel. Selecione a coluna K inteira, na guia “Página Inicial”, selecione “Formatação Condicional” e clique em “Gerenciar Regras”. Na caixa “Gerenciador de Regras de Formatação Condicional”, clique em “Nova Regra”.

GestaoEstoq6

Em “Selecione um Tipo de Regra:”, escolha “Usar uma fórmula para determinar quais células devem ser formatadas”. Insira a fórmula a seguir no campo “Formatar valores em que esta fórmula é verdadeira:” =OU($I7>$K7+50%*$K7;$I7<$K7-50%*$K7). Clique em “Formatar…”, na guia “Fonte”, selecione a cor vermelha. Este processo irá criar uma formatação condicional para colorir o círculo com a cor vermelha sempre que estiver abaixo de 50% ou acima de 50% da meta de dias em estoque.

GestaoEstoq7

Para quando estivermos dentro do resultado esperado devemos criar um sinalizador próprio para tal. Repita todos os passos acima criando mais uma regra de formatação condicional mas, desta vez, utilize a fórmula: =E($I7<$K7+50%*$K7;$I7>$K7-50%*$K7) e escolha uma cor verde.

Através dos passos descritos anteriormente, possuiremos todas as ferramentas necessárias para gerenciar o estoque:

>Fique atento para os círculos vermelhos, será necessário emitir um novo pedido de compra;Estabeleça as metas de dias em estoque considerando o tempo de entrega (lead time) dos fornecedores

>Não permita que os estoques fiquem abaixo do estoque mínimo;

>Para produtos com alto giro de estoque, assegure-se de possuir outros fornecedores “na manga” para cobrir eventuais faltas inesperadas.

Faça o download abaixo da planilha para Gestão de Estoque! Como bônus temos uma aba contendo um dashboard muito útil, para acompanhar as variações mensais do estoque (entradas e saídas), previsão e limite mínimo de segurança.

GestaoEstoq8


down_icon Download | Planilha de Gestão de Estoque

6,144 total views, 3 views today


Deixe um Comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

2 comentários sobre “Planilha para Gestão de Estoque