Antes de tudo, o estoque tem uma relação direta com todos os setores de uma empresa. Caso não haja um controle adequado, muitos problemas financeiros poderão surgir e, posteriormente, dificilmente serão sanados. Atrasando o pagamento de fornecedores, funcionários, falta de um capital de giro e, o principal, a falta de estoque para manter as atividades da empresa.
Por isso, o ideal é ter ferramentas que auxiliem na gestão e controle do estoque ou seja, das entradas e saídas de mercadorias de uma empresa. Dessa forma, haverá um gerenciamento adequado, tendo a certeza que as informações lançadas no sistema estão, de fato corretas, evitando eventuais, problemas futuros.
Uma boa planilha de gerenciamento de estoque, feita de forma adequada, auxiliará a sua empresa com informações primordiais para um planejamento ou uma importante tomada de decisão, possibilitando controlar a entrada e a saída de materiais diversos, por exemplo.
Por isso, desenvolvemos o passo-a-passo para você aprender a criar um planilha simples de controle de estoque e possa adaptá-la as suas necessidades. Veja abaixo, o que você irá ver ao fazer sua planilha para gerenciar o estoque das suas mercadorias:
Faça o download do exemplo da Planilha de Controle de Estoque e personalize da sua maneira
Estoque são bens materiais guardados por uma empresa com o objetivo de suprir uma eventual demanda futura.
Controle de estoque ou gestão de estoque é método que tem o objetivo de controlar a quantidade correta de mercadorias disponíveis em uma empresa.
Agora, você aprenderá passo a passo a fazer uma planilha de controle de estoque, utilizando operadores matemáticos “tradicionais” e funções do Excel como:
Você aprenderá não só a fazer a melhor planilha para controle de vendas e compras e gestão de estoque, como também, aprenderá a utilizar as funções mencionadas acima.
O nosso modelo de gerenciamento de estoque, apesar de simples, será completo. Nele, teremos suas respectivas entradas e saídas, onde não poderiam faltar, os clientes e fornecedores. Serão por esses dois últimos gerenciamentos que iremos dar início a nossa planilha.
Crie as planilhas abaixo e renomeia-as para “Fornecedores”, “Clientes”, “Entrada”, “Saida” e “Estoque”, respectivamente, sem as aspas. Para renomear, acesse:
Agora, vamos concluir as planilhas de “Entrada”, “Saída” e “Estoque”, nessa ordem. Inserindo as devidas fórmulas e funções para que elas possam trabalhar de forma adequada. As duas primeiras, “Fornecedores” e “Clientes”, elas não são dinâmicas e servirão de base para controlarmos a entrada e a saída de produtos.
Selecione a planilha “Entrada”, nela, podemos perceber que temos as seguintes colunas: Data, CNPJ, Fornecedor, Código, Produto, Qtde., Preço e Total. Algumas colunas, já estão preenchidas, outras, não. E, serão nessas colunas que iremos inserir as devidas fórmulas para que a nossa planilha para controle de estoque tome “corpo” e você possa gerenciar o estoque da sua empresa.
Coluna B (CNPJ)
O objetivo dessa coluna é carregar o CNPJ ao digitarmos o nome do Fornecedor. Para isso, iremos utilizar a combinação de funções Índice e Corresp, para que possamos alcançar esse objetivo. Vamos, inclusive, utilizar a função SEERRO, que explicaremos mais adiante.
Clique na célula B4 e digite a seguinte fórmula abaixo:
=SEERRO(ÍNDICE(Fornecedores!B:B;CORRESP(C4;Fornecedores!C:C;0);1);””)
O que queremos dizer é:
Se houver erros (SEERRO), no uso da função ÍNDICE(Fornecedores!B:B;CORRESP(C4;Fornecedores!C:C;0);1), retorna o valor Branco (“”), na célula, onde a informação não foi localizada.
Coluna E (Produto)
O objetivo dessa coluna é carregar o nome do Produto, associado ao código do produto, na coluna (D). Para resolvermos esse “problema” utilizaremos a função PROCV, combinada com a SEERRO, que vimos anteriormente, dispensando maiores explicações. Quer aprender como utilizar a função PROCV? Assiste essa vídeo aula sobre PROCV com duas planilhas.
Clique na célula E4 e digite a fórmula abaixo:
=SEERRO(PROCV(D4;Estoque!$A$7:$B$16;2;0);””)
Onde:
Coluna H (Total)
Na coluna Total (célula H4), devemos calcular o Valor Total de compra do produto com base na quantidade (Qtde.) e o seu respectivo Preço. A fórmula a ser digitada é a seguinte:
=F4*G4
Ao final, sua planilha ficará assim:
A planilha “Saída”, é similar a Entrada com as mesmas colunas, exceto Fornecedor que foi alterada para Cliente. E, da mesma forma, algumas colunas, já estão preenchidas, outras não, aguardando as fórmulas e funções a serem inseridas nelas. Como já vimos a explicação delas anteriormente, vamos apenas indicar onde inseri-las.
Coluna B (CNPJ)
=SEERRO(ÍNDICE(Clientes!B:B;CORRESP(C4;Clientes!C:C;0);1);””)
Coluna E (Produto)
=SEERRO(PROCV(D4;Estoque!$A$7:$B$16;2;0);””)
Coluna H (Total)
=F4*G4
Sua planilha de Saída, para controle das suas vendas está pronta. Ficando como o modelo abaixo:
Nosso Gerenciamento de Estoque já está quase completo, faltando apenas a cereja do bolo, que é a nossa de planilha para controle de estoque.
Agora, vamos completar o nosso desenvolvimento, inserindo as informações necessárias na planilha estoque para que ela trabalhe corretamente. Nela, temos as seguintes colunas:
Também, temos uma célula isolada para inserirmos a data. Onde, o objetivo é inserirmos manualmente a data para que o Estoque e o CMV sejam calculados, automaticamente, até aquela data. Caso a data esteja em branco, será calculado o Estoque e o CMV, sem uma data base.
Coluna E (Estoque)
Como o próprio nome diz, iremos calcular o estoque de produtos, com ou sem uma data base. Para esse cálculo, iremos utilizar a combinação de funções SE, SOMASES e CONCATENAR.
Clique na célula E7 e digite a seguinte fórmula abaixo, não se assuste com ela, é bem simples:
=SE($C$3=””;SOMASES(Entrada!$F$4:$F$36;Entrada!$D$4:$D$36;A7)-SOMASES(Saida!$F$4:$F$33;Saida!$D$4:$D$33;A7);SOMASES(Entrada!$F$4:$F$36;Entrada!$D$4:$D$36;A7;Entrada!$A$4:$A$36;CONCATENAR(“<=”;$C$3))-SOMASES(Saida!$F$4:$F$33;Saida!$D$4:$D$33;A7;Saida!$A$4:$A$33;CONCATENAR(“<=”;$C$3)))
Entendendo a função digitada:
Se a Data ($C$3) for igual a Branco (“”). Somaremos a quantidade de Entrada (Entrada!$F$4:$F$36) de um determinado Produto (Entrada!$D$4:$D$36;A7) subtraindo pela quantidade de Saida (Saida!$F$4:$F$33), referente ao mesmo Produto (Saida!$D$4:$D$33;A7). Caso contrário, será feito o mesmo procedimento onde a Data (Entrada!$A$4:$A$36;CONCATENAR(“<=”;$C$3)) seja menor ou igual a Data digitada.
Coluna F (CMV)
Agora, iremos calcular o Custo de Mercadorias Vendidas com o uso das funções SE, SOMASES, MÉDIASES e CONCATENAR. Lembrando que o cálculo do CMV segue a seguinte lógica:
Para saber mais o que é CMV, acesse o site Eccosys e entenda passo a passo a sua importância.
Clique na célula F7 e digite a seguinte fórmula abaixo, “calma”, não se assuste com ela, é bem simples, também:
=SE($C$3=””;(D7+SOMASES(Entrada!$F$4:$F$36;Entrada!$D$4:$D$36;A7)-E7)*MÉDIASES(Entrada!$G$4:$G$36;Entrada!$D$4:$D$36;A7);SEERRO((D7+SOMASES(Entrada!$F$4:$F$36;Entrada!$D$4:$D$36;A7;Entrada!$A$4:$A$36;CONCATENAR(“<=”;$C$3))-E7)*MÉDIASES(Entrada!$G$4:$G$36;Entrada!$D$4:$D$36;A7;Entrada!$A$4:$A$36;CONCATENAR(“<=”;$C$3));0))
Entendendo a função digitada:
Se a Data ($C$3) for igual a Branco (“”). Somaremos a quantidade de Entrada (Entrada!$F$4:$F$36) de um determinado Produto (Entrada!$D$4:$D$36;A7) subtraindo pela quantidade do Estoque Inicial (E7) e multiplicando pela média de Preço (MÉDIASES(Entrada!$G$4:$G$36;Entrada!$D$4:$D$36;A7)) do mesmo Produto. Caso contrário, faremos o mesmo procedimento, com a data sendo menor ou igual a Data digitada (Entrada!$A$4:$A$36;CONCATENAR(“<=”;$C$3)).
Na coluna Estoque, foi utilizada a Formatação Condicional para exibir uma ou mais células, com preenchimento vermelho, texto branco e negrito, com a seguinte lógica:
Para fazê-la, selecione o intervalo de células de E7 até E16. Clique na aba Página Inicial, grupo de ferramentas Estilos e botão Formatação Condicional. No menu, selecione Nova Regra e, depois, Usar uma fórmula para determinar quais células devem ser formatadas. Na caixa de texto, digite a seguinte fórmula:
Uma vez inserida e compreendida as devidas funções, temos a planilha Modelo de Controle e Gestão de Estoque, Vendas e Compras, totalmente concluída, conforme o modelo abaixo:
Para fazer uma planilha de controle de estoque é simples. Para isso, basta criar a estrutura adequada para efetuar os devidos lançamentos. Obrigatoriamente, para que você tenha um estoque, você deverá ter uma planilha de vendas e outra de compras. Bem como, o uso adequado das fórmulas que foram comentadas acima.
Não esqueça de fazer o download do exemplo da Planilha de Controle de Estoque e personalize da sua maneira. Gostou? Dúvidas, complementos, melhorias na planilha? Comente e compartilhe em suas redes sociais.
Atualmente, o Power Query desempenha um papel fundamental no processo de preparação e transformação de…
O Excel é um software de planilha desenvolvido pela Microsoft. Inclusive, ele faz parte do…
Em algumas situações ao usarmos o Excel, podemos nos deparar com os caracteres "####" em…
Uma das maiores dúvidas de quem utiliza o Excel é saber qual a versão que…
Você sabia que é possível contar a quantidade de caracteres ou de palavras em uma…
A Tabela Dinâmica, também conhecida como Pivot Table, é um recurso muito poderoso presente no…
Copyright © 2016-2022 Cursos de Excel Online. Todos os direitos reservados. CNPJ: 14.197.994/0001-92 - Mapa do Site | Termos e condições de uso - Desenvolvido por: RHB Informática