Como conciliar o extrato bancário na planilha: modelo, colunas e fórmulas

Imagine uma consultoria pequena, com uma conta no banco e uns 40 lançamentos por mês. Até agora, a "conciliação" era olhar o aplicativo do banco e a planilha lado a lado, com o dedo na tela. Funciona até o dia em que o saldo não bate por R$ 230 e você passa uma hora procurando de onde veio a diferença.

Dá para fazer isso de um jeito mais rápido, ainda na planilha. Este guia mostra um modelo com três abas, as colunas de cada uma, as fórmulas que fazem a busca por você, como tratar o que a planilha marcar e em que momento a planilha deixa de ser a melhor ferramenta.

Como conciliar o extrato bancário na planilha?

Coloque o extrato numa aba e o seu controle em outra, use uma fórmula para procurar cada lançamento do banco no controle e trate tudo o que ficar sem par. No fim, o saldo calculado precisa ser igual ao saldo do banco.

O roteiro completo:

1. Importe o extrato do período para a aba Extrato.
2. Garanta que o saldo inicial é o mesmo do último fechamento conferido.
3. Deixe as fórmulas marcarem o que tem par no controle e o que não tem.
4. Lance no controle o que faltava, investigue o que sobrou e confira o saldo final.

Se você ainda não tem claro o que é conciliar e por que fazer, comece pelo guia de conciliação bancária. Aqui o foco é a mecânica da planilha.

Que abas e colunas a planilha de conciliação precisa ter?

Três abas resolvem: Extrato, com o que o banco registrou; Controle, com o que você lançou; e Resumo, com saldos, diferença e totais por categoria.

O modelo abaixo usa as colunas de A a F nas duas primeiras abas:

Na aba Resumo, reserve a célula B1 para o saldo inicial do período, B2 para o saldo final que aparece no banco e B3 para a diferença. Abaixo, uma lista das suas categorias, cada uma com o total do período.

Duas regras deixam o modelo funcionando:

• Uma única coluna de valor, com sinal. Se o banco separa débito e crédito, crie a coluna Valor com crédito menos débito.
• Categorias sempre com o mesmo nome. O Sebrae recomenda padronizar os lançamentos e evitar nomes diferentes para o mesmo tipo de gasto. Para a planilha, "Software" e "software " (com espaço) são categorias diferentes.

Como levar o extrato do banco para a planilha?

Exporte o extrato em CSV e use a importação da planilha, escolhendo o separador e o formato das colunas. Evite copiar do PDF e evite digitar.

• No Google Planilhas, a central de ajuda indica o caminho Arquivo > Importar. Na importação, dá para escolher o caractere separador e anexar as linhas à página atual, o que serve para ir somando os meses na mesma aba.
• No Excel, a Microsoft explica que abrir o CSV direto faz o programa aplicar o formato padrão a cada coluna. Pela importação de texto ou CSV, na aba Dados, você escolhe o separador e o tipo de cada coluna, o que evita, por exemplo, datas trocadas.

Depois de importar, apague as linhas que não são lançamentos, como cabeçalho do banco, saldo anterior e totais. Se o seu banco oferece outros formatos, o guia OFX ou CSV ajuda a escolher.

Quais fórmulas usar na conciliação bancária?

Com quatro fórmulas você faz quase tudo: soma simples para o saldo acumulado, CONT.SES para achar o par de cada lançamento, SE para marcar a situação e SOMASE para somar por categoria. Nas planilhas em português, os argumentos são separados por ponto e vírgula.

Saldo calculado (aba Extrato, coluna D). Na primeira linha de lançamento, some o saldo inicial ao valor; nas seguintes, some o saldo da linha de cima:

D2: =Resumo!$B$1+C2
D3: =D2+C3

Arraste a fórmula de D3 até o fim. O último saldo calculado precisa ser igual ao saldo do banco.

Encontrado no controle (aba Extrato, coluna E). Conta quantos lançamentos do controle têm o mesmo valor e data até 3 dias antes ou depois, porque a data que você anota nem sempre é a data em que o banco registrou:

E2: =CONT.SES(Controle!$E:$E;C2;Controle!$A:$A;">="&A2-3;Controle!$A:$A;"<="&A2+3)

A CONT.SES aceita vários pares de intervalo e critério, e tanto a Microsoft quanto o Google lembram que todos os intervalos precisam ter o mesmo tamanho. Por isso as três referências usam colunas inteiras.

Situação (aba Extrato, coluna F). Transforma o número em uma etiqueta legível:

F2: =SE(E2=0;"Falta no controle";SE(E2=1;"Conciliado";"Conferir"))

Encontrado no extrato (aba Controle, coluna F). A mesma busca no sentido contrário:

F2: =CONT.SES(Extrato!$C:$C;E2;Extrato!$A:$A;">="&A2-3;Extrato!$A:$A;"<="&A2+3)

Totais por categoria (aba Resumo). A SOMASE soma o intervalo de valores apenas nas linhas em que a categoria é a pedida:

=SOMASE(Controle!$D:$D;"Software";Controle!$E:$E)

E a diferença, em B3, compara o saldo calculado com o saldo do banco: =B2-Extrato!D41, trocando 41 pela última linha preenchida. Diferença zero é conta conciliada.

Como tratar o que a planilha marcar?

Cada marcação pede uma ação diferente: o que falta no controle vira lançamento novo, o que está para conferir pede olhar se é duplicidade e o que está no controle sem par no extrato é algo que não aconteceu no banco.

O que for cliente sem pagar sai da planilha e vai para a cobrança. O guia de régua de cobrança mostra como organizar esses contatos.

Quando a planilha deixa de dar conta?

A planilha deixa de dar conta quando conciliar passa a tomar mais tempo do que analisar os números. Estes são os sinais:

• Várias contas e cartões. Cada conta pede uma aba, e as transferências entre elas precisam ser casadas à mão.
• Muitos lançamentos. A busca por valor começa a achar pares errados, e a coluna "Conferir" fica cheia.
• Mais de uma pessoa editando. Uma fórmula apagada ou uma linha colada por cima e o modelo quebra sem avisar.
• Contratos e parcelas. Saber quem pagou exige cruzar o extrato com uma lista de parcelas que muda todo mês.
• Fatura do cartão. As compras parceladas viram parcelas futuras que a planilha não projeta sozinha.
• DRE e indicadores. Montar o resultado do mês e acompanhar inadimplência exige outra camada de fórmulas.

O Sebrae resume bem esse momento: planilhas ajudam a organizar e dar visibilidade, principalmente no início, mas, à medida que o negócio evolui, concentrar as informações num único lugar evita retrabalho e reduz o risco de erro. O guia de software de conciliação bancária mostra o que observar nessa troca.

Como fazer isso no LUME Hub

O LUME Hub é uma plataforma tudo-em-um para pequenos negócios, prestadores de serviço, consultores e agências, com conteúdo, vendas, clientes, organização e financeiro no navegador. No Financeiro do LUME Hub, o trabalho das fórmulas acontece na importação do extrato:

O Financeiro faz parte do plano Completo. Dá para fazer o teste grátis por 7 dias, sem cartão, ou ver os planos.

Resumindo

Para conciliar o extrato bancário na planilha, monte três abas (Extrato, Controle e Resumo), importe o extrato em CSV e deixe a CONT.SES procurar o par de cada lançamento. A SE transforma o resultado em etiquetas, a soma simples calcula o saldo e a SOMASE dá os totais por categoria. O que fica sem par é tratado um a um até a diferença do resumo chegar a zero. Quando a planilha começa a tomar mais tempo do que a análise, é hora de concentrar tudo num sistema. Para começar, copie as colunas da tabela deste artigo numa planilha nova e importe o extrato do último mês.