Como fazer uma planilha de controle de estoque no Excel

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.