Как посчитать Cash Conversion Cycle в SQL

Зачем CCC

CCC показывает сколько дней деньги «zaperti» в operations. Покупка → inventory → продажа → cash. Чем меньше CCC — тем меньше working capital нужен.

Формула

CCC = DIO + DSO - DPO

DIO (Days Inventory Outstanding) = 365 × Avg Inventory / COGS
DSO (Days Sales Outstanding) = 365 × Avg AR / Revenue
DPO (Days Payable Outstanding) = 365 × Avg AP / COGS

DIO + DSO - DPO

• DIO: сколько дней inventory «лежит» до продажи
• DSO: сколько дней клиент платит после shipping
• DPO: сколько дней мы платим suppliers после receiving

Cash «leaves» когда платим suppliers (DPO), returns когда клиент платит (DSO).

Базовый расчёт

WITH financial_stats AS (
    SELECT
        AVG(inventory_value) AS avg_inventory,
        SUM(cogs) AS annual_cogs,
        AVG(accounts_receivable) AS avg_ar,
        SUM(revenue) AS annual_revenue,
        AVG(accounts_payable) AS avg_ap
    FROM monthly_financials
    WHERE month >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    365 * avg_inventory / NULLIF(annual_cogs, 0) AS dio,
    365 * avg_ar / NULLIF(annual_revenue, 0) AS dso,
    365 * avg_ap / NULLIF(annual_cogs, 0) AS dpo,
    (365 * avg_inventory / NULLIF(annual_cogs, 0))
    + (365 * avg_ar / NULLIF(annual_revenue, 0))
    - (365 * avg_ap / NULLIF(annual_cogs, 0)) AS ccc
FROM financial_stats;

Тренды

WITH quarterly AS (
    SELECT
        DATE_TRUNC('quarter', month) AS quarter,
        AVG(inventory_value) AS avg_inv,
        SUM(cogs) AS quarterly_cogs,
        AVG(accounts_receivable) AS avg_ar,
        SUM(revenue) AS quarterly_rev,
        AVG(accounts_payable) AS avg_ap
    FROM monthly_financials
    GROUP BY 1
)
SELECT
    quarter,
    91.25 * avg_inv / NULLIF(quarterly_cogs, 0) AS dio,
    91.25 * avg_ar / NULLIF(quarterly_rev, 0) AS dso,
    91.25 * avg_ap / NULLIF(quarterly_cogs, 0) AS dpo,
    (91.25 * avg_inv / NULLIF(quarterly_cogs, 0))
    + (91.25 * avg_ar / NULLIF(quarterly_rev, 0))
    - (91.25 * avg_ap / NULLIF(quarterly_cogs, 0)) AS ccc
FROM quarterly
ORDER BY quarter;

Частые ошибки

Ошибка 1. End-of-period vs avg balances. Avg — more accurate (seasonal). EOP — snapshot.

Ошибка 2. Включить non-trade AR / AP. Trade only. Loans / investments separate.

Ошибка 3. Negative CCC. В Amazon / Apple — negative. They get paid before paying suppliers. Excellent.

Ошибка 4. CCC by component. Decomposing — какой component driving change.

Ошибка 5. Annualize quarterly numbers. Q1 alone × 4 ≠ annual. Use TTM.

Связанные темы

• Как посчитать inventory turnover в SQL
• Как посчитать COGS в SQL
• Как посчитать revenue в SQL
• Как посчитать burn rate в SQL

FAQ

Какой CCC ok?

Negative — incredible (Amazon ~-20). 30-60 days — good. 90+ days — capital intensive.

Negative CCC means?

You're paid before paying suppliers. Free working capital.

CCC growing — bad?

Yes. Cash tied up longer. Need more working capital.

CCC by component drilling?

Each component (DIO, DSO, DPO) drives different actions. DIO down — faster inventory. DSO down — faster collections.

CCC for services?

DSO main (no inventory). DPO для overhead.