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.
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.
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 uma planilha de controle de estoque simples para otimizar o controle de estoque 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:
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.
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
É 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.
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:
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:
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:
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:
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:
É 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:
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:
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:
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).
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:
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:
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:
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 de controle de estoque gratuita no 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.
Nossa planilha de Controle de Estoque é 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.
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.
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:
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:
A parte de quantidade de venda, estoque acumulado, preço unitário e valor total de venda são cálculos automatizados.
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.
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.
Esse é mais um dos nossos Modelos de Planilha no Excel e mais uma opção de planilha de controle de estoque gratuita para você fazer o download e começar a utilizar. Tenha total controle do seu estoque e visualize as informações em um dashboard interativo!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:
Com esta planilha de controle de estoque no Excel, você conseguirá ter total controle do seu estoque, cadastrando produtos, fornecedores, entradas e saídas de mercadorias, valores gastos e recebidos e ainda visualizar tudo isso em um dashboard dinâmico e completo.
Nos tópicos a seguir, explicarei detalhadamente o funcionamento dessa planilha e como você pode utilizá-la e personalizá-la para controlar e organizar o estoque da sua empresa no Excel.
O objetivo é que você tenha uma planilha de controle de estoque completa para preencher com as suas informações e começar a utilizar. Portanto, faça o download dela no material disponível abaixo do vídeo e acompanhe o passo a passo de como utilizá-la.
Além disso, ao final, vou listar para você as ferramentas que utilizei para a criação dessa planilha e quais aulas temos aqui na Hashtag para que você possa acompanhar e desenvolver sua própria planilha ou apenas se aprofundar mais nessas ferramentas como estudo.
O grande diferencial dessa planilha de controle de estoque é o seu dashboard visual e interativo, que permite uma análise completa e detalhada do seu estoque.
Dentro deste dashboard, é possível visualizar as informações sobre as despesas e receitas por mês, os maiores estoques por período e os estados com mais produtos vendidos.
Além disso, você pode filtrar a exibição dos dados por produtos e períodos específicos, possibilitando uma análise mais específica e um controle maior das movimentações de estoque.
Para termos essas informações exibidas no dashboard, primeiro precisamos preencher as tabelas de Cadastros e Estoque.
Para exemplificar o uso e a aplicação dessa planilha, estou considerando uma loja de autopeças. No entanto, você pode adaptá-la de acordo com a realidade e necessidades do seu negócio ou da empresa em que trabalha.
Na primeira aba, na planilha de Cadastros, temos duas tabelas. Na primeira, você pode registrar o código do produto, o nome dele, a quantidade mínima que precisa ter daquele produto em estoque, a data em que ele foi cadastrado e a unidade de medida correspondente.
Na segunda tabela disponível, temos as informações sobre os fornecedores, como o nome, os dados de contato e em quais estados esses fornecedores atuam.
Dessa forma, além de ter um registro completo dos produtos da sua empresa, você terá um fácil acesso aos dados de contato dos fornecedores para quando precisar realizar uma nova compra ou verificar uma compra já feita.
Na segunda guia, temos a planilha Estoque, que será responsável pelo controle de entrada e saída de mercadorias do seu estoque. Registrando a data da movimentação, a quantidade, os valores gastos ou recebidos, e o fornecedor ou destino desse produto.
Essa planilha é muito interessante, pois em uma única tabela, temos todos os produtos que foram comprados e vendidos, com as informações de forma clara e intuitiva. Ela permite apenas o registro de produtos que já estejam cadastrados no seu estoque.
Além disso, dependendo do valor registrado em Quantidade Movimentada, a planilha identificará se trata-se de uma entrada (valor positivo) ou saída (valor negativo) de estoque e liberará apenas as colunas correspondentes para serem preenchidas.
Depois de preencher as duas planilhas de Cadastros e Estoque, você terá todas as informações necessárias para que seu dashboard possa exibi-las.
Observação: Após atualizar suas planilhas com as informações desejadas, não se esqueça de selecionar seu dashboard, ir na guia Dados e clicar em Atualizar Tudo (Ctrl + Alt + F5) para que ele atualize todos os gráficos.
Caso queira desenvolver sua própria planilha ou apenas utilizar essa planilha como material de estudo para aprofundar seus conhecimentos em Excel, vou deixar listado abaixo os recursos mais importantes que utilizei para a construção dela, com as respectivas aulas para que você possa conferir:
Essas foram as principais ferramentas utilizadas para a construção dessa planilha de controle de estoque com dashboard interativo. Agora, além de ter à sua disposição a planilha pronta para usar, você ainda pode compreender melhor o processo de construção dela.
Hoje, você aprendeu tudo o que precisa saber sobre controle de estoque, como construir uma Planilha de Controle de Estoque e também pôde fazer o download de três modelos prontos de planilha de controle de estoque.
Você pode baixar essas planilhas agora mesmo e personalizá-las de acordo com o seu tipo de negócio, adaptando-as à sua realidade e às suas necessidades.
Dessa forma, você terá um registro completo do seu estoque e terá à sua disposição dashboards totalmente interativos e informativos que facilitarão a visualização dos dados.
Aproveite essas planilhas para auxiliar no registro e no acompanhamento do estoque do seu negócio. E se você gostou desses modelos de planilha de controle de estoque, não deixe de conferir os outros modelos de planilhas prontas para download gratuito que oferecemos aqui no site da Hashtag.
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!
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
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!
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador