Como Criar um Relatório de Aging de Contas a Receber no Excel (30, 60, 90 Dias)
Um relatório de aging (contas a receber vencidas) classifica faturas não pagas em faixas de acordo com o atraso, geralmente de 0–30, 31–60, 61–90 e mais de 90 dias. Duas decisões determinam se o seu relatório está correto. A primeira é se você calcula o aging a partir da data de vencimento ou da data da fatura. A segunda é se uma fatura parcialmente paga mostra seu valor total ou o saldo restante.
Erre nessas duas decisões e o total de cada faixa estará errado, o que é pior do que não ter relatório nenhum.
Este guia aborda por que a estrutura quebra, as três abordagens que as pessoas usam e onde cada uma delas deixa de funcionar. Trata-se de um fluxo de trabalho de dados, não de uma consultoria contábil, portanto, confirme o tratamento com o responsável pelo seu livro-razão.
Por que um relatório de aging quebra uma planilha
O primeiro problema é a questão da data. O aging a partir da data da fatura mostra quão antigo é o documento. O aging a partir da data de vencimento mostra o atraso do cliente e, para fins de cobrança, esse é o número que você quer.
Ambas as opções são justificáveis e geram relatórios diferentes. O problema clássico é uma planilha onde ninguém registrou qual critério foi utilizado.
O segundo problema são os pagamentos parciais. Uma fatura de $10,000 com $7,000 recebidos representa um saldo a receber de $3,000, e deve aparecer como $3,000 em exatamente uma faixa. Relatórios de aging criados a partir de uma lista de faturas, em vez de uma lista de itens em aberto, silenciosamente superestimam todos os valores.
O terceiro problema é que o relatório é um retrato do momento. As faixas são calculadas em relação ao dia de hoje, portanto, o arquivo de ontem já está desatualizado, e cada nova geração recalcula todas as linhas.
Além disso, existem as linhas complexas. Notas de crédito, adiantamentos, faturas contestadas e saldos em várias moedas precisam, cada um, de uma regra própria. E cada regra precisa sobreviver à próxima pessoa que abrir o arquivo.
Nenhum desses problemas é difícil isoladamente. Eles se tornam difíceis porque surgem todos juntos, uma vez por mês, sob a pressão de um prazo de entrega.
O custo disso para você
Uma lista de cobrança inútil na prática. O objetivo de criar faixas é saber para quem ligar primeiro. Um relatório que superestima os saldos faz com que alguém vá cobrar um dinheiro que já foi recebido.
Refazer o trabalho todo santo mês. Como as faixas são relativas ao dia de hoje, o relatório de aging nunca está concluído. Cada ciclo repete os mesmos cruzamentos de dados, as mesmas fórmulas e as mesmas verificações manuais.
Totais que não batem com o livro-razão. Quando a soma das faixas não bate com o saldo de contas a receber, o relatório perde a credibilidade. Descobrir o motivo geralmente leva mais tempo do que a própria criação do relatório.
Um relatório de aging é confiável porque seu total bate com o livro-razão. Nada mais importa se isso falhar.
As soluções improvisadas que as pessoas tentam
Opção 1: Definir as regras antes de mexer em qualquer fórmula
Escreva quatro coisas no topo da planilha: a partir de qual data você calcula o aging, quais são os limites das faixas, se os valores são brutos ou líquidos de pagamentos e qual é a data de referência (data de corte).
Isso leva dez minutos e evita a discussão mais comum. O Journal of Accountancy detalha o mesmo processo de criação com a mesma ênfase em acertar a configuração logo de início.
Isso também define a sua fonte de dados. Você precisa de um extrato de itens em aberto com os saldos restantes, e não de uma lista de todas as faturas já emitidas na história.
O limite é que definições não calculam nada. Elas apenas evitam que você calcule a coisa errada.
Opção 2: Criar a coluna de faixas e depois consolidar os totais
Calcule os dias de atraso como a data de referência menos a data de vencimento e, em seguida, associe esse número a um rótulo de faixa. A função TODAY fornece uma data de referência dinâmica, e a DATEDIF retorna a contagem de dias entre duas datas.
Para o rótulo em si, a função IFS é mais legível do que várias funções IF aninhadas quando você precisar revisar o arquivo seis meses depois. Em seguida, some por cliente e por faixa usando a função SUMIFS, o que mantém o cálculo auditável linha por linha.
Use uma data de referência estática em vez de TODAY ao distribuir o relatório. Um arquivo que atualiza o aging silenciosamente na semana seguinte vai contradizer a versão que já está na caixa de entrada de alguém.
O limite aqui são o volume de dados e as exceções. As fórmulas funcionam, mas notas de crédito, pagamentos parciais e faturas contestadas ainda precisam ser tratados manualmente.
Opção 3: Manter uma aba de regras ao lado dos números
Centralize as decisões complexas em um só lugar: como as notas de crédito são compensadas, se as faturas contestadas são excluídas ou sinalizadas, como os saldos em moeda estrangeira são convertidos e qual taxa é utilizada.
É isso que torna o relatório utilizável quando outra pessoa precisa gerá-lo. Mas também é a aba que acaba sendo ignorada quando o prazo de fechamento do mês está apertado.
A limitação é que uma aba de regras documenta critérios sem aplicá-los na prática. Alguém ainda precisa implementar cada regra a cada ciclo. Nosso guia sobre como conciliar transações em uma planilha aborda o trabalho de correspondência de dados que alimenta esse processo.
O limite comum. Todas as três opções pressupõem que você comece com um extrato limpo de itens em aberto. Quando a origem é uma exportação bruta de faturas mais um arquivo de pagamentos separado, o verdadeiro trabalho é cruzá-los antes mesmo de começar a criar as faixas.
Como criar um relatório de aging com o Powerdrill Bloom
Passo 1: Faça o upload dos dados de faturas e pagamentos
Faça o upload do extrato de itens em aberto ou dos arquivos de faturas e pagamentos juntos. O Powerdrill Bloom analisa o perfil das colunas logo na importação, de modo que datas de vencimento ausentes, valores em branco e números de faturas duplicados são identificados antes de qualquer cálculo de faixa.
Passo 2: Descreva as regras de faixas em linguagem natural
Defina as regras em vez de programá-las. Diga que você está calculando o aging a partir da data de vencimento com base em uma data de referência específica. Informe os limites das faixas e determine que os valores devem ser líquidos de pagamentos recebidos.
Em seguida, solicite as verificações na mesma etapa. Pergunte quais faturas têm pagamentos que excedem o valor faturado e quais têm datas de vencimento anteriores às datas de emissão. Depois, pergunte se os totais das faixas batem com o saldo de contas a receber.
Passo 3: Exporte o gráfico, relatório ou apresentação
Extraia uma tabela de aging por cliente, um gráfico de distribuição das faixas ou uma lista de cobrança ordenada pelo saldo mais antigo.
Por que isso é muito melhor do que refazer o relatório todo mês
| Caminho manual | Powerdrill Bloom | |
|---|---|---|
| Cruzar faturas e pagamentos | Fórmulas de busca para cada arquivo | Faça o upload de ambos e pergunte |
| Alterar a data de referência | Recalcular e verificar novamente | Indicar a nova data |
| Abater pagamentos parciais | Coluna de saldo manual | Pedir os saldos líquidos de pagamentos |
| Bater os totais com o livro-razão | Verificação manual a cada ciclo | Perguntar se os totais batem |
É nas etapas intermediárias que o seu mês vai embora. Classificar em faixas é apenas aritmética; chegar a uma lista limpa de itens em aberto é o verdadeiro trabalho.
Erros comuns
Calcular o aging pela data da fatura quando você queria usar a data de vencimento. Para cobranças, a data de vencimento quase sempre é a escolha correta. Seja qual for a sua opção, deixe-a registrada no relatório.
Mostrar os valores das faturas em vez dos saldos restantes. Uma fatura parcialmente paga deve constar em uma faixa com o seu saldo devedor. Usar os valores totais infla todos os resultados.
Deixar que a função TODAY atualize o aging de um arquivo já distribuído. Congele a data de referência antes de enviar o relatório, caso contrário, duas pessoas lerão números diferentes no mesmo arquivo.
Ignorar notas de crédito. Um crédito não aplicado fica associado ao cliente e reduz o que ele deve. Deixá-lo de fora faz com que o saldo pareça pior do que realmente é.
Classificar em faixas por cliente em vez de por fatura. As faixas devem ser calculadas por fatura e depois somadas por cliente. Tirar a média do aging de um cliente esconde o item mais antigo, que é justamente o que você precisa identificar.
Nunca fazer a conciliação com o livro-razão. A soma das faixas deve bater com o saldo de controle de contas a receber. Se você pular essa verificação, o relatório servirá apenas como enfeite.
Refazer tudo do zero a cada ciclo. As regras não mudam mensalmente, apenas os dados. Mantenha as regras e apenas substitua a exportação de dados, seguindo a mesma disciplina de um relatório de orçamento versus realizado.
Conclusão
Defina a data de aging, use os saldos restantes, congele a data de referência e faça os totais baterem com o livro-razão. Esses quatro passos fazem a diferença entre um relatório que gera ações práticas e uma tabela que gera discussões.
O que torna esse processo caro é que tudo é relativo ao dia de hoje, ou seja, o trabalho nunca acaba. Os cruzamentos e as verificações retornam a cada ciclo.
Se é aí que você perde o seu fechamento de mês, experimente o Powerdrill Bloom nas suas exportações de faturas e pagamentos. Veja também o nosso guia sobre como transformar demonstrações financeiras em PDF em gráficos e a página de análise de fluxo de caixa com IA.
Perguntas frequentes
Quais são as faixas padrão em um relatório de aging de contas a receber?
A maioria dos relatórios utiliza as faixas de 0–30, 31–60, 61–90 e mais de 90 dias, geralmente com uma coluna para valores em dia ou a vencer. Os limites são uma convenção e não uma regra rígida, portanto, indique quais você utilizou.
Devo calcular o aging das faturas a partir da data de emissão ou da data de vencimento?
Use a data de vencimento se quiser saber o atraso do cliente, que é o objetivo mais comum para cobranças. Use a data da fatura se quiser saber a antiguidade do documento.
Como devo lidar com pagamentos parciais?
Mostre o saldo restante, não o valor original da fatura, e coloque esse saldo em uma única faixa. Trabalhar com um extrato de itens em aberto, em vez de uma lista de faturas, resolve isso automaticamente.
De quais funções do Excel eu preciso?
TODAY ou uma data fixa para a data de referência, e DATEDIF para os dias de atraso. A função IFS atribui o rótulo da faixa, e a SUMIFS soma os totais por cliente e por faixa. Nenhuma delas é complicada; as definições é que são a parte difícil.
Com que frequência o relatório deve ser atualizado?
Pelo menos mensalmente, e semanalmente se houver um processo ativo de cobrança, já que cada faixa é relativa à data de referência. Congele essa data em cada versão que você distribuir.