Cash Flow Analysis and Projections

Cash flow is the lifeblood of any business — revenue on paper means nothing if cash isn't actually arriving. Cash flow analysis tracks when money comes in (collections from invoices) and goes out, identifies patterns, and projects future receipts based on historical payment behaviour.

This lesson focuses on cash receipts analysis: understanding when payments arrive, forecasting future collections, and identifying gaps between expected and actual cash.

Cash Received vs Invoiced: The Core Gap

The most fundamental cash flow question is: "Of everything we invoiced, how much have we actually collected, and when did it arrive?" This requires joining invoices (when we billed) with payments (when cash arrived).

The gap between issue_date and payment_date represents the collection period. Long collection periods mean cash is tied up in receivables — good for the client, bad for your cash position.

WITH payment_timing AS (
    SELECT
        i.invoice_id AS invoice_id,
        i.client_id,
        i.issue_date,
        i.due_date,
        (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id),
        p.payment_date,
        p.amount,
        (p.payment_date - i.issue_date) AS days_to_collect,
        (p.payment_date - i.due_date) AS days_relative_to_due
    FROM invoices i
    JOIN payments p ON p.invoice_id = i.invoice_id
)
SELECT
    DATE_TRUNC('month', payment_date) AS collection_month,
    COUNT(DISTINCT invoice_id) AS invoices_collected,
    ROUND(SUM(amount), 2) AS cash_collected,
    ROUND((AVG(days_to_collect))::NUMERIC, 1) AS avg_days_from_invoice,
    ROUND((AVG(days_relative_to_due))::NUMERIC, 1) AS avg_days_relative_to_due,
    SUM(CASE WHEN days_relative_to_due < 0 THEN amount ELSE 0 END) AS early_payments,
    SUM(CASE WHEN days_relative_to_due = 0 THEN amount ELSE 0 END) AS on_time_payments,
    SUM(CASE WHEN days_relative_to_due > 0 THEN amount ELSE 0 END) AS late_payments
FROM payment_timing
GROUP BY DATE_TRUNC('month', payment_date)
ORDER BY collection_month;

What This Returns

Monthly cash collection summary. avg_days_relative_to_due is key: negative means payments arriving before due date (great!), positive means late payments. The split between early_payments, on_time_payments, and late_payments shows your collection effectiveness at a glance.