Solução profissional em Excel: painel de contas a receber e inadimplência (passo a passo, com todas as fórmulas)

• NumeroTitulo — identificador do documento (nota, duplicata, boleto)
• CodigoCliente — código do cliente no ERP
• DataEmissao — data de emissão
• DataVencimento — data de vencimento
• ValorOriginal — valor do título
• ValorRecebido — valor já baixado
• Vendedor — responsável comercial

Uma segunda base, clientes.xlsx, traz CodigoCliente, RazaoSocial, Regiao e LimiteCredito. Ela é o cadastro, não muda todo dia.

1. Dados → Obter Dados → De Arquivo → De Texto/CSV → selecione titulos.csv → botão Transformar Dados (nunca "Carregar" direto).
2. No Editor do Power Query, clique com o botão direito na coluna DataVencimento → Alterar Tipo → Usando Localidade → Data → Português (Brasil). Isso elimina o clássico erro de dia/mês invertido.
3. Selecione ValorOriginal e ValorRecebido → Alterar Tipo → Número Decimal.
4. Página Inicial → Remover Linhas → Remover Linhas em Branco e depois Remover Duplicatas com a coluna NumeroTitulo selecionada.
5. Adicionar Coluna → Coluna Personalizada, nome Saldo, fórmula:
   = [ValorOriginal] - [ValorRecebido]
6. Página Inicial → Manter Linhas → Filtros de Número → Maior que → 0 na coluna Saldo (títulos quitados saem da análise).
7. Repita a importação para clientes.xlsx e depois use Página Inicial → Mesclar Consultas: títulos à esquerda, clientes à direita, chave CodigoCliente, tipo de junção Externa Esquerda. Expanda apenas RazaoSocial, Regiao e LimiteCredito.
8. Página Inicial → Fechar e Carregar Em… → Somente Criar Conexão + marque Adicionar ao Modelo de Dados.

Benefício: a partir de agora, trocar o arquivo titulos.csv e clicar em Dados → Atualizar Tudo refaz limpeza, junção e cálculo em segundos, sempre da mesma forma. Zero PROCV manual, zero risco de arrastar fórmula errado.

Coluna "DiasAtraso"
=SE([@DataVencimento]>=HOJE(); 0; DIAS(HOJE(); [@DataVencimento]))

Coluna "Situacao"
=SE([@DiasAtraso]=0; "Em dia"; "Vencido")

Coluna "Faixa" (aging)
=SES([@DiasAtraso]=0;  "A vencer";
     [@DiasAtraso]<=30; "1 a 30 dias";
     [@DiasAtraso]<=60; "31 a 60 dias";
     [@DiasAtraso]<=90; "61 a 90 dias";
     VERDADEIRO;        "Acima de 90 dias")

Coluna "Cliente" (quando o cadastro está em outra tabela)
=PROCX([@CodigoCliente]; tblClientes[CodigoCliente]; tblClientes[RazaoSocial]; "Cadastro ausente"; 0)

Coluna "ProvisaoPerda" (PCLD simplificada por faixa)
=[@Saldo] * SES([@DiasAtraso]<=60; 0;
                 [@DiasAtraso]<=90; 0,25;
                 [@DiasAtraso]<=180; 0,5;
                 VERDADEIRO; 1)

Três detalhes profissionais: PROCX substitui o PROCV porque aceita valor padrão quando não encontra e não depende da posição da coluna; SES elimina o aninhamento ilegível de vários SE; e HOJE() faz o aging se recalcular sozinho todo dia que a planilha abre.

Carteira total em aberto
=SOMA(tblTitulos[Saldo])

Total vencido
=SOMASES(tblTitulos[Saldo]; tblTitulos[Situacao]; "Vencido")

Percentual de inadimplência
=SEERRO(SOMASES(tblTitulos[Saldo]; tblTitulos[Situacao]; "Vencido")
        / SOMA(tblTitulos[Saldo]); 0)

Quantidade de títulos vencidos
=CONT.SES(tblTitulos[Situacao]; "Vencido")

Vencido acima de 90 dias
=SOMASES(tblTitulos[Saldo]; tblTitulos[Faixa]; "Acima de 90 dias")

Prazo médio de atraso ponderado pelo valor
=SEERRO(SOMARPRODUTO(tblTitulos[DiasAtraso]; tblTitulos[Saldo])
        / SOMA(tblTitulos[Saldo]); 0)

Provisão total para perdas
=SOMA(tblTitulos[ProvisaoPerda])

Maior devedor da carteira
=ÍNDICE(tblTitulos[Cliente];
        CORRESP(MÁXIMO(SOMASES(tblTitulos[Saldo]; tblTitulos[Cliente]; tblTitulos[Cliente]));
                SOMASES(tblTitulos[Saldo]; tblTitulos[Cliente]; tblTitulos[Cliente]); 0))

Top 5 clientes por valor em aberto (Microsoft 365)
=CLASSIFICAR(ÚNICO(tblTitulos[Cliente]); 1; 1)   ' lista de clientes
=SOMASES(tblTitulos[Saldo]; tblTitulos[Cliente]; H10#)  ' valor de cada um

Formate o percentual em Página Inicial → Porcentagem com uma casa decimal, e os valores em Contábil sem casas decimais. Legibilidade também é parte do resultado.

1. Selecione todas as linhas de dados da tabela (clique no canto superior esquerdo da tabela, duas vezes).
2. Página Inicial → Formatação Condicional → Nova Regra → Usar uma fórmula que determine as células a serem formatadas.
3. Fórmula para atraso crítico:
   =$K2>90 (onde K é a coluna DiasAtraso) → Formatar → Preenchimento vermelho claro.
4. Nova regra para atenção:
   =E($K2>0;$K2<=90) → preenchimento âmbar.
5. Nova regra para limite de crédito estourado:
   =SOMASES(tblTitulos[Saldo];tblTitulos[Cliente];$D2)>$N2 → borda vermelha e negrito.
6. Em Gerenciar Regras, marque Parar se Verdadeiro na regra crítica para evitar sobreposição.

Benefício: o analista deixa de perder tempo procurando o que está errado. Ele abre a planilha e o problema já está apontando para ele.

1. Inserir → Tabela Dinâmica → De Tabela/Intervalo → tblTitulos → destino: planilha Painel, célula A20.
2. Arraste Regiao para Linhas, Faixa para Colunas e Saldo para Valores (resumo por Soma).
3. Clique com o botão direito no campo de valores → Mostrar Valores Como → % do Total da Linha para ler a qualidade da carteira de cada região.
4. Análise de Tabela Dinâmica → Inserir Segmentação de Dados → marque Vendedor e Regiao.
5. Análise de Tabela Dinâmica → Inserir Linha do Tempo → DataVencimento, agrupada por mês.
6. Inserir → Gráfico de Colunas Empilhadas a partir da dinâmica; nas Conexões de Relatório da segmentação, marque todas as dinâmicas para que um clique filtre o painel inteiro.

Coluna "AcaoCobranca"
=SES([@DiasAtraso]=0;  "Nenhuma";
     [@DiasAtraso]<=7;  "Lembrete cordial";
     [@DiasAtraso]<=30; "Contato telefônico";
     [@DiasAtraso]<=60; "Notificação formal";
     VERDADEIRO;        "Negativação / jurídico")

Coluna "Mensagem"
=CONCAT("Olá, "; [@Cliente]; ". Consta em aberto o título ";
        [@NumeroTitulo]; " no valor de ";
        TEXTO([@Saldo]; "R$ #.##0,00"); ", vencido em ";
        TEXTO([@DataVencimento]; "dd/mm/aaaa"); " (";
        [@DiasAtraso]; " dias). Podemos regularizar hoje?")

Use Dados → Validação de Dados → Lista na coluna de status de negociação (Aguardando, Prometido, Renegociado, Quitado) para padronizar o retorno do time — texto livre destrói qualquer relatório.

Sub AtualizarPainel()
    Dim pt As PivotTable
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 1) Atualiza as consultas do Power Query
    ThisWorkbook.RefreshAll
    DoEvents

    ' 2) Recalcula tudo e atualiza as tabelas dinâmicas
    Application.CalculateFullRebuild
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' 3) Carimba data e hora da atualização
    ThisWorkbook.Sheets("Painel").Range("B2").Value = _
        "Atualizado em " & Format(Now, "dd/mm/yyyy hh:mm")

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox "Painel atualizado com sucesso.", vbInformation
End Sub

Proteja as planilhas de cálculo em Revisão → Proteger Planilha, deixando desbloqueadas apenas as células de entrada. Isso evita a causa número um de erro em planilhas corporativas: alguém digitar um número por cima de uma fórmula.

• Tempo: a rotina mensal de montar o relatório (de 6 a 8 horas na média dos nossos clientes) cai para menos de 5 minutos, porque virou um botão.
• Confiabilidade: a limpeza é sempre a mesma. O número da segunda-feira é o mesmo da sexta-feira, e é auditável passo a passo no Power Query.
• Caixa: a régua de cobrança por faixa de atraso antecipa o contato. Em carteiras de PMEs, agir na faixa de 1 a 30 dias costuma recuperar a maior parte do que viraria perda em 90 dias.
• Risco: a provisão por faixa mostra a perda provável antes dela acontecer, e o cruzamento com limite de crédito bloqueia venda nova para quem já deve.
• Gestão comercial: a dinâmica por vendedor e região revela quem vende bem e recebe mal — informação que o relatório de faturamento nunca mostra.
• Escalabilidade: quando a base passar de algumas centenas de milhares de linhas, a mesma lógica migra para SQL sem refazer nada: as regras já estão escritas e documentadas.