Projeto aplicado com finanças: fluxo básico, inadimplência e conciliações simples

Neste capítulo, você vai montar um mini-projeto aplicado de finanças com três entregas muito comuns no dia a dia: (1) fluxo básico de contas a receber (o que foi faturado, o que venceu, o que foi pago), (2) inadimplência (atrasos, aging e taxa de atraso) e (3) conciliações simples (bater pagamentos com faturas e identificar divergências). A ideia é trabalhar com um conjunto pequeno de tabelas e produzir consultas que poderiam alimentar um relatório operacional e um acompanhamento semanal.

Modelo de dados do projeto (visão prática)

Para manter o foco no que importa, vamos assumir um modelo típico de contas a receber com quatro entidades:

• customers: cadastro do cliente (id, nome, segmento).
• invoices: faturas emitidas (id, customer_id, invoice_date, due_date, amount, status).
• payments: pagamentos recebidos (id, customer_id, payment_date, amount, method, reference).
• payment_allocations: alocações de pagamento em faturas (payment_id, invoice_id, allocated_amount).

Por que existe payment_allocations? Porque um pagamento pode quitar mais de uma fatura, e uma fatura pode ser quitada em partes (parcelas, abatimentos, pagamentos parciais). Essa tabela é o “elo” que permite conciliação e cálculo de saldo por fatura.

Também vamos assumir que invoices.status pode ter valores como OPEN, PAID, CANCELED. Mesmo que exista status, você não deve depender apenas dele para métricas financeiras: o mais confiável é calcular com base em valores alocados e datas.

Entrega 1: fluxo básico de contas a receber

1) Definir o “as of date” (data de referência)

Relatórios financeiros quase sempre são “na data”: o que estava em aberto até um dia específico. Você pode parametrizar com uma data fixa (ex.: fechamento do mês) ou usar a data atual. Para exemplos, vamos usar um parâmetro lógico chamado :as_of_date.

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

2) Calcular quanto foi pago por fatura até a data de referência

O primeiro passo é obter, por fatura, o total alocado em pagamentos com payment_date até :as_of_date. Isso evita contar pagamentos futuros.

SELECT  pa.invoice_id,        SUM(pa.allocated_amount) AS paid_amount_asof FROM payment_allocations pa JOIN payments p   ON p.id = pa.payment_id WHERE p.payment_date <= :as_of_date GROUP BY pa.invoice_id;

Esse resultado é uma “tabela de apoio” que você vai juntar com invoices para calcular saldo e situação real.

3) Montar a visão de faturas com saldo

Agora, para cada fatura emitida até a data de referência (e não cancelada), calcule:

• paid_amount_asof: quanto foi pago até a data
• open_amount_asof: quanto ainda está em aberto
• is_overdue_asof: se está vencida e ainda em aberto

SELECT  i.id AS invoice_id,        i.customer_id,        i.invoice_date,        i.due_date,        i.amount AS invoice_amount,        COALESCE(paid.paid_amount_asof, 0) AS paid_amount_asof,        (i.amount - COALESCE(paid.paid_amount_asof, 0)) AS open_amount_asof,        CASE          WHEN i.due_date < :as_of_date               AND (i.amount - COALESCE(paid.paid_amount_asof, 0)) > 0            THEN 1          ELSE 0        END AS is_overdue_asof FROM invoices i LEFT JOIN (   SELECT pa.invoice_id,          SUM(pa.allocated_amount) AS paid_amount_asof   FROM payment_allocations pa   JOIN payments p     ON p.id = pa.payment_id   WHERE p.payment_date <= :as_of_date   GROUP BY pa.invoice_id ) paid   ON paid.invoice_id = i.id WHERE i.invoice_date <= :as_of_date   AND i.status <> 'CANCELED';

Com essa visão, você já consegue responder perguntas como: “qual o total em aberto hoje?”, “quantas faturas estão vencidas?”, “qual o saldo por cliente?”.

4) Fluxo agregado: emitido, recebido, em aberto e vencido

Um fluxo básico costuma ter pelo menos quatro números:

• Faturado (emitido) no período
• Recebido no período
• Em aberto na data
• Vencido na data

Para faturado e recebido, você pode usar um intervalo de datas (ex.: mês). Para aberto/vencido, use :as_of_date.

SELECT  SUM(CASE WHEN i.invoice_date BETWEEN :start_date AND :end_date            AND i.status <> 'CANCELED'           THEN i.amount ELSE 0 END) AS billed_in_period,  (SELECT SUM(p.amount)   FROM payments p   WHERE p.payment_date BETWEEN :start_date AND :end_date) AS received_in_period,  SUM(CASE WHEN i.invoice_date <= :as_of_date            AND i.status <> 'CANCELED'           THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END) AS open_asof,  SUM(CASE WHEN i.invoice_date <= :as_of_date            AND i.status <> 'CANCELED'            AND i.due_date < :as_of_date           THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END) AS overdue_asof FROM invoices i LEFT JOIN (   SELECT pa.invoice_id,          SUM(pa.allocated_amount) AS paid_amount_asof   FROM payment_allocations pa   JOIN payments p     ON p.id = pa.payment_id   WHERE p.payment_date <= :as_of_date   GROUP BY pa.invoice_id ) paid   ON paid.invoice_id = i.id;

Note que “recebido no período” vem de payments (entrada de caixa), enquanto “faturado no período” vem de invoices (competência). Em muitas empresas, esses dois números não batem no curto prazo por causa de prazos de pagamento.

Entrega 2: inadimplência (atrasos, aging e taxas)

1) Conceito: inadimplência operacional vs. contábil

No dia a dia, inadimplência costuma significar “valor vencido e não pago”. Para análise operacional, você quer medir:

• Saldo vencido (quanto está atrasado)
• Distribuição por faixas de atraso (aging)
• Taxa de inadimplência (vencido / total em aberto, ou vencido / faturado, dependendo da política)

O ponto crítico é sempre usar a data de referência e considerar pagamentos parciais: uma fatura pode estar parcialmente vencida (saldo em aberto) mesmo com algum pagamento.

2) Calcular dias em atraso por fatura

Para cada fatura, calcule o saldo em aberto e, se houver saldo e a fatura estiver vencida, calcule os dias de atraso. A função de diferença de datas varia por banco; aqui vamos usar uma forma genérica com DATEDIFF (ajuste conforme seu SGBD).

SELECT  i.id AS invoice_id,        i.customer_id,        i.due_date,        i.amount AS invoice_amount,        (i.amount - COALESCE(paid.paid_amount_asof, 0)) AS open_amount_asof,        CASE          WHEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) > 0               AND i.due_date < :as_of_date            THEN DATEDIFF(day, i.due_date, :as_of_date)          ELSE 0        END AS days_past_due FROM invoices i LEFT JOIN (   SELECT pa.invoice_id,          SUM(pa.allocated_amount) AS paid_amount_asof   FROM payment_allocations pa   JOIN payments p     ON p.id = pa.payment_id   WHERE p.payment_date <= :as_of_date   GROUP BY pa.invoice_id ) paid   ON paid.invoice_id = i.id WHERE i.invoice_date <= :as_of_date   AND i.status <> 'CANCELED';

Se o seu banco não tiver DATEDIFF, substitua pela função equivalente. O importante é o raciocínio: atraso só existe se há saldo e passou do vencimento.

3) Aging: faixas de atraso

Agora, transforme days_past_due em faixas. Um padrão comum: 1–30, 31–60, 61–90, 90+ (e “não vencido”).

SELECT  CASE    WHEN open_amount_asof <= 0 THEN 'Quitado'    WHEN days_past_due = 0 THEN 'A vencer'    WHEN days_past_due BETWEEN 1 AND 30 THEN '1-30'    WHEN days_past_due BETWEEN 31 AND 60 THEN '31-60'    WHEN days_past_due BETWEEN 61 AND 90 THEN '61-90'    ELSE '90+'  END AS aging_bucket,  SUM(open_amount_asof) AS open_amount FROM (   SELECT    i.id AS invoice_id,    (i.amount - COALESCE(paid.paid_amount_asof, 0)) AS open_amount_asof,    CASE      WHEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) > 0           AND i.due_date < :as_of_date        THEN DATEDIFF(day, i.due_date, :as_of_date)      ELSE 0    END AS days_past_due   FROM invoices i   LEFT JOIN (     SELECT pa.invoice_id,            SUM(pa.allocated_amount) AS paid_amount_asof     FROM payment_allocations pa     JOIN payments p       ON p.id = pa.payment_id     WHERE p.payment_date <= :as_of_date     GROUP BY pa.invoice_id   ) paid     ON paid.invoice_id = i.id   WHERE i.invoice_date <= :as_of_date     AND i.status <> 'CANCELED' ) base GROUP BY aging_bucket;

Esse resultado já é um relatório de aging simples. Em operações financeiras, ele costuma ser segmentado por cliente, segmento, carteira, região ou responsável.

4) Taxa de inadimplência (definição e cálculo)

Existem várias definições. Duas comuns:

• Inadimplência sobre aberto: vencido / (vencido + a vencer). Mostra a “qualidade” do contas a receber atual.
• Inadimplência sobre faturado em um período: vencido de faturas do período / faturado do período. Útil para comparar meses, mas exige cuidado com prazos (faturas recentes ainda não tiveram tempo de vencer).

Vamos calcular a primeira (sobre aberto) na data de referência:

SELECT  SUM(CASE WHEN i.due_date < :as_of_date            THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END) AS overdue_open_amount,  SUM((i.amount - COALESCE(paid.paid_amount_asof, 0))) AS total_open_amount,  CASE    WHEN SUM((i.amount - COALESCE(paid.paid_amount_asof, 0))) = 0 THEN 0    ELSE      SUM(CASE WHEN i.due_date < :as_of_date                THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END)      / SUM((i.amount - COALESCE(paid.paid_amount_asof, 0)))  END AS delinquency_rate_on_open FROM invoices i LEFT JOIN (   SELECT pa.invoice_id,          SUM(pa.allocated_amount) AS paid_amount_asof   FROM payment_allocations pa   JOIN payments p     ON p.id = pa.payment_id   WHERE p.payment_date <= :as_of_date   GROUP BY pa.invoice_id ) paid   ON paid.invoice_id = i.id WHERE i.invoice_date <= :as_of_date   AND i.status <> 'CANCELED'   AND (i.amount - COALESCE(paid.paid_amount_asof, 0)) > 0;

Repare que filtramos apenas faturas com saldo em aberto no final, para evitar que quitadas distorçam o denominador.

Entrega 3: conciliações simples (pagamentos x faturas)

1) Conceito: o que significa “conciliar” aqui

Conciliação, no contexto deste projeto, é garantir que:

• Todo pagamento recebido está associado a uma ou mais faturas (ou identificado como adiantamento/crédito).
• O total alocado de um pagamento não excede o valor do pagamento.
• O total pago de uma fatura não excede o valor da fatura (a menos que você permita crédito/juros e tenha campos para isso).
• Pagamentos e faturas do mesmo cliente fazem sentido (evitar alocar pagamento de um cliente em fatura de outro).

Você vai construir consultas de auditoria que listam exceções. Em finanças, essas listas são tão importantes quanto os indicadores.

2) Checagem A: pagamentos sem alocação (dinheiro “solto”)

Identifique pagamentos que não possuem nenhuma linha em payment_allocations. Isso pode ser: (a) falha de integração, (b) pagamento ainda não conciliado, (c) crédito/adiantamento.

SELECT  p.id AS payment_id,        p.customer_id,        p.payment_date,        p.amount AS payment_amount,        p.method,        p.reference FROM payments p LEFT JOIN payment_allocations pa   ON pa.payment_id = p.id WHERE pa.payment_id IS NULL   AND p.payment_date BETWEEN :start_date AND :end_date;

Passo a passo de uso: rode para a última semana, exporte a lista e valide com o time se são adiantamentos ou se faltou conciliar.

3) Checagem B: pagamento com alocação maior que o valor pago

Agora, some as alocações por pagamento e compare com o valor do pagamento. Se alocou mais do que recebeu, existe erro de lançamento (ou duplicidade de alocação).

SELECT  p.id AS payment_id,        p.customer_id,        p.payment_date,        p.amount AS payment_amount,        SUM(pa.allocated_amount) AS allocated_total,        (SUM(pa.allocated_amount) - p.amount) AS diff_overallocated FROM payments p JOIN payment_allocations pa   ON pa.payment_id = p.id GROUP BY p.id, p.customer_id, p.payment_date, p.amount HAVING SUM(pa.allocated_amount) > p.amount;

Em operações reais, você pode permitir pequena diferença por arredondamento. Se for o caso, troque o > por > p.amount + 0.01 (ou a tolerância definida).

4) Checagem C: fatura com pagamento acima do valor da fatura

O mesmo raciocínio vale para faturas. Se a soma alocada excede o valor da fatura, pode haver duplicidade, alocação errada ou necessidade de modelar juros/multa/desconto separadamente.

SELECT  i.id AS invoice_id,        i.customer_id,        i.invoice_date,        i.due_date,        i.amount AS invoice_amount,        SUM(pa.allocated_amount) AS allocated_total,        (SUM(pa.allocated_amount) - i.amount) AS diff_overpaid FROM invoices i JOIN payment_allocations pa   ON pa.invoice_id = i.id GROUP BY i.id, i.customer_id, i.invoice_date, i.due_date, i.amount HAVING SUM(pa.allocated_amount) > i.amount;

5) Checagem D: alocação cruzada entre clientes

Uma regra básica: o customer_id do pagamento deve ser o mesmo da fatura. Se não for, ou existe erro, ou você precisa de uma regra explícita para pagamentos em nome de terceiros.

SELECT  pa.payment_id,        p.customer_id AS payment_customer_id,        pa.invoice_id,        i.customer_id AS invoice_customer_id,        pa.allocated_amount FROM payment_allocations pa JOIN payments p   ON p.id = pa.payment_id JOIN invoices i   ON i.id = pa.invoice_id WHERE p.customer_id <> i.customer_id;

Essa consulta é uma das mais valiosas para auditoria, porque pega inconsistências que podem passar despercebidas em métricas agregadas.

6) Conciliação “básica” de saldo por cliente

Um relatório simples e muito usado é o saldo em aberto por cliente na data de referência, com separação entre a vencer e vencido. Isso ajuda a priorizar cobrança e entender concentração de risco.

SELECT  c.id AS customer_id,        c.name AS customer_name,        SUM(CASE WHEN i.due_date < :as_of_date                 THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END) AS overdue_open_amount,        SUM(CASE WHEN i.due_date >= :as_of_date                 THEN (i.amount - COALESCE(paid.paid_amount_asof, 0)) ELSE 0 END) AS not_due_open_amount,        SUM((i.amount - COALESCE(paid.paid_amount_asof, 0))) AS total_open_amount FROM customers c JOIN invoices i   ON i.customer_id = c.id LEFT JOIN (   SELECT pa.invoice_id,          SUM(pa.allocated_amount) AS paid_amount_asof   FROM payment_allocations pa   JOIN payments p     ON p.id = pa.payment_id   WHERE p.payment_date <= :as_of_date   GROUP BY pa.invoice_id ) paid   ON paid.invoice_id = i.id WHERE i.invoice_date <= :as_of_date   AND i.status <> 'CANCELED'   AND (i.amount - COALESCE(paid.paid_amount_asof, 0)) > 0 GROUP BY c.id, c.name;

Passo a passo de uso: (1) rode diariamente com :as_of_date = hoje, (2) ordene por overdue_open_amount para priorizar cobrança, (3) cruze com segmento para definir abordagem (ex.: grandes contas vs. PMEs).

Roteiro prático do projeto (do zero ao relatório)

Passo 1: validar o “caminho do dinheiro”

Antes de qualquer KPI, rode as checagens de conciliação (pagamentos sem alocação, sobrealocação, overpaid e cliente cruzado). Se houver muitas exceções, qualquer indicador de inadimplência ficará distorcido.

Passo 2: construir a visão de faturas com saldo (base analítica)

Crie uma consulta base (ou view) com: invoice_id, customer_id, invoice_date, due_date, invoice_amount, paid_amount_asof, open_amount_asof, days_past_due. Essa base será reutilizada para fluxo, aging e ranking de clientes.

Passo 3: gerar o fluxo básico (competência x caixa)

Com a base pronta, gere o “emitido no período” e “recebido no período” para o mesmo intervalo, e “aberto/vencido” na data. Se o relatório for mensal, garanta que :start_date e :end_date correspondem ao mês e :as_of_date ao último dia do mês (ou ao dia atual, se for acompanhamento).

Passo 4: gerar aging e taxa de inadimplência

Use days_past_due para faixas e some open_amount_asof. Em seguida, calcule a taxa de inadimplência sobre aberto. Se quiser um recorte mais operacional, filtre apenas clientes ativos ou segmentos específicos.

Passo 5: publicar listas de ação

Além de números agregados, publique listas acionáveis:

• Top clientes por overdue_open_amount.
• Faturas com maior atraso (dias_past_due alto) e saldo relevante.
• Pagamentos não conciliados na semana.

Essas listas normalmente viram tarefas para cobrança, financeiro e operações.

Armadilhas comuns neste tipo de análise (e como evitar)

Pagamentos parciais e múltiplas alocações

Evite inferir que uma fatura está paga apenas por status. Sempre calcule open_amount_asof com base em alocações até a data. Isso previne erros quando há pagamento parcial ou quando o status não foi atualizado.

Pagamentos fora da data de referência

Se você não filtrar payment_date <= :as_of_date, um pagamento futuro pode “zerar” indevidamente o saldo em aberto no passado, quebrando relatórios de fechamento.

Cancelamentos e estornos

Se existir estorno (pagamento negativo) ou cancelamento de fatura, defina regras claras: faturas canceladas devem sair do contas a receber; estornos devem entrar como pagamentos negativos e afetar as alocações. Se seu modelo não tem estornos, pelo menos exclua CANCELED para não inflar faturamento e aberto.

Diferença entre valor do pagamento e valor alocado

É comum existir diferença por taxas, juros, descontos ou crédito. Se isso for recorrente, o modelo deve ter campos próprios (ex.: discount_amount, fee_amount) ou uma tabela de ajustes. Enquanto isso não existe, mantenha uma checagem com tolerância e trate os casos manualmente.