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
O que é estoque?
Estoque são bens materiais guardados por uma empresa com o objetivo de suprir uma eventual demanda futura.
O que é controle de estoque?
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.
Quais funções do Excel você aprenderá ao fazer uma planilha de controle de estoque
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:
- Somases;
- Médiases;
- Procv;
- Índice;
- Corresp;
- Se;
- Concatenar.
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.
Iniciando a sua planilha de gestão de estoque no Excel
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:
- Guia Página Inicial → Células → Formatar → Renomear Planilha.
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.
Gestão de Estoque – Entrada 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.
Mas, o que as funções SEERRO, ÍNDICE e CORRESP fazem, afinal de contas?
- SEERRO(valor;valor_se_erro) ⇒ caso não encontre a informação desejada, “oculta” os valores de erros retornados (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!), por uma outra informação personalizada.
- valor ⇒ fórmula ou função a ser verificada;
- valor_se_erro ⇒ caso haja erro no valor retornado da fórmula ou função, exibir a informação personalizada.
- ÍNDICE(matriz;núm_linha;núm_coluna) ⇒ retorna uma informação em uma determinada célula, com base na combinação do número de linhas e colunas da pesquisa desejada.
- matriz ⇒ é o intervalo de células, onde está localizada a informação (CNPJ) a ser retornada (Fornecedores!B:B);
- núm_linha ⇒ é o número da linha, onde está localizada a informação (Fornecedor) a ser pesquisada (CORRESP(C4;Fornecedores!C:C;0);
- núm_coluna ⇒ é o número da coluna, onde está localizada a informação (Fornecedores) a ser pesquisada (1).
- CORRESP(valor_procurado;matriz_procurada;tipo_correspondência) ⇒ retorna o número da linha da informação pesquisada em um intervalo de células.
- valor_procurado ⇒ é a célula (C4) que queremos associar, pesquisar na matriz_procurada e saber em qual linha ela está localizada;
- matriz_procurada ⇒ é o intervalo de células (Fornecedores!C:C), onde está localizada a informação (Fornecedor) a ser pesquisada;
- tipo_correspondência ⇒ estamos informando que queremos retornar com exatidão (0), a informação pesquisada.
Complementando o Gerenciamento de Estoque – Entrada de Produtos
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:
- PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo) ⇒ retorna uma determinada informação pesquisada em um intervalo de células.
- valor_procurado ⇒ é a célula (D4) que queremos associar (Código), pesquisar na matriz_tabela e exibir a informação relacionada a ela;
- matriz_tabela ⇒ é o intervalo de células (Estoque!$A$7:$B$16), onde está localizada a informação (Produto) a ser pesquisada;
- núm_índice_coluna ⇒ é o número da coluna (2) onde está localizada a informação a ser exibida.
- procurar_intervalo ⇒ estamos informando que queremos retornar com exatidão (0), a informação pesquisada.
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:
Gestão de Estoque – Saída de Produtos
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.
Modelo Gestão de Estoque – Controle de Estoque no Excel
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:
- Código ⇒ é o código do produto;
- Produto ⇒ é o nome do produto;
- Estoque Mínimo ⇒ é o estoque mínimo ideal;
- Estoque Inicial ⇒ é o estoque de início das operações da empresa;
- Estoque ⇒ é o estoque final, com base nas entradas, saídas e o estoque inicial;
- CMV ⇒ é o Custo de Mercadorias Vendidas (CMV). O cálculo do CMV segue a seguinte lógica:
- CMV = EI (Estoque Inicial) + CM (Compra de Mercadorias ou Entrada de Produtos) – EF (Estoque Final ou Estoque). Lembre-se, esse não é o único cálculo do CMV.
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.
Calculando o estoque
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.
Calculando o CMV
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:
- CMV = EI (Estoque Inicial) + CM (Compra de Mercadorias ou Entrada de Produtos) – EF (Estoque Final ou Estoque)
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)).
Entendendo as funções CONCATENAR, SE, SOMASES e MEDIÁSES no cálculo do CMV
- SOMASES(intervalo_soma;intervalo_critérios1;critérios1;…) ⇒ soma um intervalo de células, mediante um ou mais critérios.
- intervalo_soma ⇒ soma o intervalo dos valores desejado;
- intervalo_critérios1 ⇒ selecione o intervalo onde está o critério a ser somado.
- critérios1 ⇒ indicamos qual informação (critério) deve ser somada.
- MÉDIASES(intervalo_média;intervalo_critérios1;critérios1;…) ⇒ retorna a média de um intervalo de células, mediante um ou mais critérios.
- intervalo_média ⇒ calcula a média do intervalo dos valores desejado;
- intervalo_critérios1 ⇒ selecione o intervalo onde está o critério a ser calculada a média.
- critérios1 ⇒ indicamos qual informação (critério) deve efetuada a média.
- CONCATENAR(texto1;texto2;…) ⇒ agrupa dois os mais valores.
- texto1 ⇒ texto ou célula a ser agrupada;
- texto2 ⇒ idem ao anterior;
- SE(teste_lógico;valor_se_verdadeiro;valor_se_falso) ⇒ faz um teste lógico em uma célula e retorna uma informação, quando a condição é satisfeita.
- teste_lógico ⇒ compara a informação de uma célula;
- valor_se_verdadeiro ⇒ exibe uma informação caso a comparação seja satisfeita;
- valor_se_falso ⇒ exibe uma informação caso a comparação não seja satisfeita.
Bônus: Formatação Condição – Coluna Estoque
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:
- Se o Estoque for menor do que o Estoque Mínimo, faça as configurações da Formatação Condicional mencionada anteriormente.
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:
- =E7<=C7 ⇒ uma vez inserida a fórmula, clique no botão Formatar e formate da maneira desejada. Finalize clicando nos botões Ok;
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:
Conclusão – Como fazer uma planilha de controle de estoque no Excel
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.