Blog

Postado em em 10 de junho de 2022

Controle de estoque no Excel: Modelo Simples e Avançado

Para fazer um sistema de controle de estoque no Excel, você só precisa de algumas funções, como PROCV e SOMASES, e formatações. Não é nada muito complexo ou fora do comum. 

Assim, você garante uma administração mais cuidadosa de um negócio para evitar decisões ruins e perda de dinheiro.

Por isso, se quiser aprofundar sobre esse assunto e descobrir como usar uma planilha de controle de estoque, acompanhe.

O que é um controle de estoque?

Como o próprio nome já diz é uma forma de fazer um controle para que os usuários saibam sempre como está o andamento do estoque para que não falte produto ou para que não haja um excesso, ou seja, é útil para manter um equilíbrio de produtos no estoque.

Qual a importância do controle de estoque?

O controle de estoque é essencial em qualquer empresa que venda produtos. Ele permite evitar perda de produtos, com desorganização do que está sendo estocado. 

Da mesma forma, possibilita enfrentar duas situações que são muito prejudiciais ao comércio: falta e excesso de estoque

Ou seja, é possível evitar que não haja produtos para serem vendidos, mesmo com anúncio de venda. Por outro lado, evita-se a situação de ter produtos em excesso no estoque, causando uma perda de dinheiro por conta do acúmulo.

Pensando no fluxo de produtos na cadeia de suprimentos, faz-se essencial ter essa visão otimizada com ferramentas como uma planilha de controle de estoque no Excel. 

Assim, é possível combater atrasos na hora de enviar os produtos, bem como receber e cadastrar devidamente o que chega dos fornecedores. Isso agiliza a cadeia, melhora a relação com os parceiros, fortalece a reputação e satisfaz os clientes

A seguir, apresentaremos um modelo simples para otimizar o controle de estoque no Excel.

Criando um controle de estoque simples no Excel

Inicialmente vamos analisar a tabela inicial que devemos ter para iniciar a construção desse controle de estoque.

Tabela de movimentações final:

Tabela de movimentações final

Nesta tabela é possível observar que temos 4 informações: data da ocorrência, o tipo (se foi entrada ou saída de produto), o produto e a quantidade dessa atividade. 

Passo 1 – Movimentações (entradas e saídas)

Para construirmos essa tabela, vamos até a guia Mov. do Zero. Aqui temos uma planilha vazia apenas com os cabeçalhos das informações que queremos inserir.

Tabela para começar do zero:

Tabela para começar do zero

Na parte de tipo e produto vamos querer algo mais automatizado para evitar com que o usuário escreva as informações de forma errada e atrapalhe nas análises futuras. Para isso vamos fazer uma validação de dados.

Então vamos selecionar a célula B2 que é onde teremos as informações de tipo, em seguida, vamos até a guia Dados e depois vamos selecionar a opção Validação de Dados.

Ferramenta validação de dados:

Ferramenta Validação de Dados

Feito isso, será aberta uma janela para que possamos informar ao Excel como queremos validar esses dados. Como temos mais de 1 opção e elas não serão alteradas vamos escolher a opção de Lista.

Inserindo as opções para a validação de dados do tipo lista:

Inserindo as opções para a validação de dados do tipo lista

Como temos apenas duas opções e são opções mais básicas, podemos escrever manualmente as duas (separadas por ponto e vírgula).

Resultado da validação de dados:

Resultado da validação de dados

Feito isso, a nossa célula, ao ser selecionada, ficará com uma seta e ao pressioná-la teremos as duas opções que acabamos de inserir. 

Como se trata de uma validação, o Excel não vai permitir que o usuário escreva qualquer outra coisa além das duas opções inseridas na validação.

Desta forma é possível evitar com que o usuário escreva a informação errada ou que escreva qualquer outra informação que não esteja relacionada ao tipo.

Para as informações de produto, vamos seguir o mesmo procedimento, no entanto, como se tratam de produtos é mais fácil selecionar o intervalo em que temos esses produtos.

Até porque se a quantidade for maior e o usuário quiser modificá-los fica mais fácil do que escrever manualmente dentro da validação.

Neste caso, ao invés de escrevermos manualmente cada um dos produtos, vamos clicar na seta ao lado do espaço em branco, vamos até a aba Estoque do Zero (que é onde o usuário terá resumo do estoque, vamos completar logo adiante), em seguida vamos selecionar as células que contém os nossos produtos.

Validação de dados do tipo lista para selecionar os dados:

Selecionando os dados para a validação

Neste caso, teremos a referência para os produtos da aba estoque do zero, desta forma o Excel irá criar uma lista igual fizemos para o tipo, só que para produtos com todos os itens selecionados.

Resultado da validação de dados:

Resultado da validação de dados

Assim fica mais fácil inserir os dados, pois o usuário já terá essas informações para fazer o controle desse estoque, portanto facilita e evita com que ele insira produtos que não tenha em seu estoque.

Para evitar com que o usuário tenha que repetir esse procedimento de validação de dados sempre que for inserir uma nova informação, precisamos automatizar esse processo.

É bem simples, basta selecionar as informações que temos, da célula A1 até D2, ir até a guia Página Inicial e em seguida na opção Formatar como Tabela. Ao selecionar essa opção, o usuário terá diversos estilos de tabela, basta escolher o que achar melhor e selecionar.

Opção para formatar os dados como tabela:

Opção para formatar os dados como tabela

 Ao selecionar o estilo desejado, o Excel mostra uma caixa para perguntar se a tabela selecionada tem cabeçalhos.

Verificando o intervalo e a existência dos cabeçalhos:

Verificando o intervalo e a existência dos cabeçalhos

Os cabeçalhos são os nomes que identificam as informações de cada coluna, como já temos isso vamos deixar essa opção marcada e pressionar ok.

Resultado da formatação como tabela:

Resultado da formatação como tabela

Agora temos os nossos dados formatados como tabela, desta forma se o usuário for inserir alguma informação na linha logo abaixo, como a data da próxima atividade, o Excel automaticamente vai inserir e puxar as formatações das células que estão logo acima.

Inserindo novos dados para verificar a validação de dados:

Inserindo novos dados para verificar a validação de dados

É possível observar que o Excel traz a validação de dados também, ou seja, o usuário não terá a necessidade de criar uma validação sempre que for incluir uma nova atividade na planilha.

OBS: Caso o usuário queira abrir as opções da validação de dados sem utilizar o mouse basta ir até a célula desejada com as teclas direcionais do teclado e pressionar ALT + SETA PARA BAIXO.

Passo 2 – Tabela com resumo do estoque

O próximo passo é preencher a tabela que temos na aba Estoque do Zero, pois é nela que teremos o resumo do estoque, que iria completar o controle de estoque.

Tabela de estoque:

Tabela de estoque

Aqui temos uma tabela com os produtos e as quantidades de estoque inicial, ou seja, o estoque que temos no início para que possamos partir dele para obter o estoque final e saber se a quantia final está boa ou se será necessário fazer compras.

Podemos iniciar com a fórmula para o estoque final que é bem simples, é o estoque inicial + a quantidade de produto que entrou – a quantidade de produto que saiu.

Fórmula para o estoque final:

Fórmula para o estoque final

Feito isso, basta arrastar a fórmula para as demais células. Para isso basta clicar no quadrado verde no canto inferior direito da célula e arrastar até a última célula.

OBS: Se o usuário der um duplo clique nesse quadrado também funciona e é mais rápido, principalmente se tiver uma grande quantidade de produtos.

Resultado do estoque final para todos os produtos:

Resultado do estoque final para todos os produtos

Como não preenchemos as informações de entrada e saída o nosso estoque final será igual ao estoque inicial.

Para preencher a célula C2, vamos precisar saber quantos produtos referentes a Coxinha estão com o tipo entrada, ou seja, nessa célula vamos querer apenas os valores dos produtos que estão na célula A2 que são referentes a entrada.

Isso quer dizer que não podemos ter dentro de coxinha o valor de entrada de Mini Pizza por exemplo e para fazer esse tipo de cálculo temos que utilizar a fórmula SOMASES que permite ao usuário somar valores de acordo com alguns critérios (mais de 1 critério pode ser utilizado nessa fórmula).

Então neste caso vamos somar as quantidades da nossa tabela, baseado no tipo e no produto.

Fórmula SOMASES:

Fórmula SOMASES

Essa é a fórmula, inicialmente ela pede o intervalo de soma, o intervalo de critérios 1 e o critério 1, em seguida segue a mesma sequência para os intervalos seguintes.

=SOMASES(Movimentações!D:D ; Movimentações!C:C ; Estoque!A2 ; Movimentações!B:B ; “Entrada”)

Inicialmente, temos o intervalo de soma que é toda a coluna D da aba Mov. do Zero, que representa as quantidades dos produtos.

Em seguida, temos o intervalo do primeiro critério que é a coluna C, que representa a coluna dos produtos e por fim o primeiro critério que é o item que temos na célula A2.

Para o segundo intervalo, temos a coluna B que representa a coluna dos tipos (entradas ou saídas) e por fim o critério que é Entrada, como estamos analisando só as entradas nessa coluna esse critério será fixo. Feito isso basta arrastar a fórmula para as demais células.

Resultado da fórmula de entrada para todos os produtos:

Resultado da fórmula de entrada para todos os produtos

É possível observar que neste caso temos todas as entradas (foram adicionadas novas informações para que seja possível analisar melhor o funcionamento da fórmula, imagem abaixo) e temos também o estoque final que neste caso vai estar apenas somando as entradas, pois não temos nenhuma saída.

Tabela de movimentações com mais informações:

Tabela de movimentações com mais informações - Controle de estoque

Para preencher a coluna de saída vamos utilizar exatamente a mesma fórmula, o único detalhe que irá mudar é o segundo critério que vai passar de Entrada para Saída.

Resultado da fórmula de saída para todos os produtos:

Resultado da fórmula de saída para todos os produtos

Desta forma, temos o total de entradas e saídas de cada um dos produtos e o estoque final de todos eles.

Para finalizar e deixar o nosso controle de estoque no Excel completo vamos inserir mais uma validação de dados na nossa tabela para impedir que o usuário insira um valor que faça com que o estoque final fique menor do que zero.

Isso quer dizer que vamos criar uma validação de dados para alertar o usuário quando a saída de algum produto zerar o estoque, ou seja, não será possível vender além, pois não teremos produto para vender.

O primeiro passo é voltar para a aba de movimentações, selecionar toda a coluna D, que é a coluna de quantidade (basta clicar na letra D para que toda a coluna seja selecionada) e então vamos selecionar a Validação de Dados.

Validação de dados para a quantidade das movimentações:

Validação de dados para a quantidade das movimentações - Controle de estoque

Neste caso, como precisamos de uma validação um pouco mais elaborada vamos selecionar o tipo Personalizado. Para a fórmula vamos fazer uma contagem dos valores da coluna E da aba Estoque do Zero que são menores do que zero.

Em seguida, ao fazer essa contabilidade de quantos valores são menores do que zero, vamos igualar esse valor a zero, pois se tivermos algum valor, um único que seja menor do que zero essa fórmula será falsa, pois não será igual a zero.

Se essa fórmula for falsa, o Excel não vai “autorizar” essa validação e irá retornar um erro ao usuário (vamos modificá-lo a fim de mostrar uma mensagem para melhor explicar o motivo do erro).

  •  =CONT.SE(‘Estoque do Zero’!E:E;”<0″)=0

 Feito isso, a nossa validação está completa. Caso o usuário insira algum valor em quantidades que venha a zerar o estoque, o Excel irá retornar um erro para avisar que tem um problema.

Erro ao inserir um valor em que o estoque fique negativo:

Erro ao inserir um valor em que o estoque fique negativo - Controle de estoque

Neste caso, temos somente mais 6 unidades de Joelho, então ao inserir 7 unidades como saída o Excel retorna um erro informando que não corresponde com a validação de dados.

Como essa mensagem para outro usuário pode não ser muito intuitiva é possível alterar essa mensagem de erro para explicar melhor o ocorrido. Para isso basta selecionar novamente a coluna D e ir até a Validação de Dados.

Alterando a mensagem de erro:

Alterando a mensagem de erro

Agora vamos até a aba Alerta de erro para alterar o título e a mensagem de erro que irá aparecer quando o usuário inserir algum valor que deixe o nosso estoque com o valor menor do que zero, ou seja, para que não possibilite o usuário de vender um produto que não tem no estoque.

Dessa forma, o alerta que aparecerá ao usuário quando o estoque final for menor do que zero estará com a nova mensagem de erro:

Erro com a nova mensagem inserida pelo usuário - Controle de estoque

Planilha avançada para controle de estoque

Aqui, diferentemente do que fizemos no exemplos anterior, não vamos te mostrar como montar uma planilha de controle de estoque do zero, mas sim te disponibilizar uma planilha gratuita de controle de estoque em Excel.

Com ela, você já vai ter tudo pronto para uso assim que você fizer o download da mesma.

Nos próximos tópicos, vamos ver como utilizar cada uma das abas, além de explicar também as funcionalidades da planilha.

Guia inicial da planilha

Nossa planilha é composta de diversas fichas de controle de estoque, nas quais podemos cadastrar ou alterar informações previamente preenchidas.

Essa planilha também vai funcionar como um dashboard, isto é, clicando nos botões dessa planilha você vai ser direcionado para a aba em questão, não precisando ficar selecionando manualmente as abas.

Nesta primeira aba podemos escolher para qual parte do nosso dashboard queremos ir.

Aba de Capa

Abas de cadastro de informações

Nas três abas de cadastro (Produtos, Lojas e Fornecedores) podemos preencher as informações de todos os produtos utilizados na empresa, de todas as lojas que representam a empresa e de todos os fornecedores que possuem algum relacionamento com a empresa em questão.

Para inserir uma nova informação nessas abas, basta preencher na primeira linha vazia que todas as fórmulas e formatações serão aproveitadas automaticamente.

Para um novo produto, você deve inserir o nome do item, o código, a unidade de medida, o estoque mínimo, o custo unitário e o preço unitário.

É importante observar que não é possível inserir um produto que já existe, pois a planilha já faz uma validação automática.

Para o cadastro de um novo fornecedor, deve-se inserir o nome da empresa, o telefone, o e-mail e o endereço. Perceba que a parte do total comprado não é alterável pelo usuário, pois é um cálculo automatizado da própria planilha.

A planilha vai olhar para as entradas e levantar o valor total de todas as compras daquele fornecedor.

Sempre que você tiver uma aba com informações em azul (a não ser o e-mail), você tem um dado que é calculado pela própria planilha.

Para cadastro de lojas, segue-se a mesma lógica, com inserção de nome, telefone, e-mail e endereço. O total vendido é levantado pelo sistema.

Abas de Cadastro

Abas de entradas e saídas

Essas são as duas abas nas quais vamos preencher as movimentações realizadas na empresa, isto é, toda movimentação de entrada de produto (compra de produtos/materiais) deverá ser cadastrada na aba de entradas.

Por outro lado, as movimentações de saídas (vendas, doações…) deverão ser cadastradas na aba de saídas.

Para inserir uma nova informação, basta seguir o mesmo passo a passo das abas de cadastro, ou seja, você só precisa preencher as informações na primeira linha vazia.

A diferença entre a aba de entrada da aba de saídas é que, na primeira, temos os fornecedores dos quais compramos o produto e, na aba de saída, temos a loja na qual foi efetuada a venda.

Além disso, na aba de saídas também temos a opção de preencher um campo para vendas que foram integralmente ou parcialmente devolvidas.

A cada entrada, você deve se atentar ao que será preenchido por você e ao que surgirá automaticamente. O usuário precisa inserir: 

  • a data de compra;
  • o produto; 
  • o fornecedor;
  • e a quantidade. 

O custo unitário e o valor serão trazidos automaticamente.

É importante destacar que o nome do fornecedor deve estar na lista de fornecedores cadastrados na aba de cadastro. Caso haja inconsistência, a planilha acusa o erro com sua validação.

Na aba de saída, o usuário deve registrar: 

  • data;
  • produto; 
  • loja; 
  • quantidade; 
  • quantidade devolvida;
  • e data de devolução. 

A parte de quantidade de venda, estoque acumulado, preço unitário e valor total de venda são cálculos automatizados.

Entradas e Saídas da Planilha de Controle de Estoque

Aba de controle de estoque

Essa é a nossa aba de resumo da planilha de controle de estoque, com entrada e saída de produtos.

Isto é, após termos preenchido todas as entradas e saídas, aqui teremos um resumo consolidando todas essas movimentações.

Além disso, aqui podemos ver qual o nível de segurança do estoque de cada um dos itens que temos dentro da nossa empresa.

Aba de Controle de Estoque

Aba Dashboard

Nessa aba do nosso dashboard de controle de estoque do Excel podemos ver um outro tipo de resumo das informações preenchidas nas abas de entradas e saídas.

Podemos encontrar o número total de produtos, lojas e fornecedores que foram cadastrados, assim como um resumo de receita, custos e lucro total para cada um dos itens preenchidos.

Além disso, também podemos analisar o saldo de estoque em uma determinada data para certo produto e também realizar uma comparação entre o resultado de compras x vendas para esse produto.

Aba Dashboard

Aprenda mais sobre planilhas e Excel

O Excel tem sempre recursos, funções e vantagens para te surpreender. Agora, para conhecer esse mundo de possibilidades, é muito importante investir em um curso.

A prática te mostra tudo o que é possível fazer no programa e, ainda, como surpreender colegas de trabalho.

Para tudo isso, nós da Hashtag Treinamentos temos uma solução: o curso 100% on-line de Excel. Como o conteúdo é todo digital, dá para assistir às aulas e fazer os exercícios onde e quando você quiser.

Também vale ressaltar que o curso foi pensado para pessoas de quaisquer níveis chegarem ao patamar de impressionador no Excel (nível acima do avançado).

Muito legal, não é mesmo? Então, não perca essa grande oportunidade!

Hashtag Treinamentos

Para mais conteúdo de Excel, VBA e Power BI, acesse nosso Canal do YouTube!

Para acessar os outros modelos de planilhas elaborados pela Hashtag, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?

Quer receber um minicurso 100% gratuito de Excel? Preencha seu e-mail abaixo!