Collection Effectiveness Index (CEI)
Collection Effectiveness Index (CEI): The share of collectible receivables actually collected in the period. Billings stand in for credit sales. Current AR is receivables aged 30 days or less.
Also known as: CEI
Formula
CEI = (Beginning AR + Credit sales - Ending AR) / (Beginning AR + Credit sales - Ending current AR) × 100Computable definition
The canonical formula in the GASP formula grammar. The MCP server compiles it to SQL over the datasets listed below.
(sum(field.accounts_receivable) at period_start + sum(field.invoice_amount where in_period(field.invoice_date)) - sum(field.accounts_receivable) at period_end) / (sum(field.accounts_receivable) at period_start + sum(field.invoice_amount where in_period(field.invoice_date)) - sum(field.outstanding_amount where (field.snapshot_date - field.invoice_date) <= 30) at period_end)Unit: percent (the formula yields an unscaled ratio)
Notes: Billings stand in for credit sales. Current AR is receivables aged 30 days or less.
Data requirements
Grain: End-of-month receivables snapshot · Frequency: Monthly · Aggregation: snapshot (month) · Staged: no · Main source: Billing.
| Field | Dataset | Type |
|---|---|---|
accounts_receivable | balance_sheet | decimal |
invoice_amount | invoice | decimal |
invoice_date | invoice | date |
outstanding_amount | invoice | decimal |
snapshot_date | balance_sheet | date |
snapshot_date | invoice | date |
Example SQL (Postgres)
SELECT (CAST((((SELECT COALESCE(SUM(accounts_receivable), 0) FROM balance_sheet WHERE snapshot_date = :period_start) + (SELECT COALESCE(SUM(invoice_amount), 0) FROM invoice WHERE snapshot_date = :period_end AND (invoice_date >= :period_start AND invoice_date <= :period_end))) - (SELECT COALESCE(SUM(accounts_receivable), 0) FROM balance_sheet WHERE snapshot_date = :period_end)) AS DOUBLE PRECISION) / NULLIF((((SELECT COALESCE(SUM(accounts_receivable), 0) FROM balance_sheet WHERE snapshot_date = :period_start) + (SELECT COALESCE(SUM(invoice_amount), 0) FROM invoice WHERE snapshot_date = :period_end AND (invoice_date >= :period_start AND invoice_date <= :period_end))) - (SELECT COALESCE(SUM(outstanding_amount), 0) FROM invoice WHERE snapshot_date = :period_end AND (snapshot_date - invoice_date) <= 30)), 0)) AS collection_effectiveness_index_cei From an agent: with the GASP MCP server, call
lookup_metric({ "name": "Collection Effectiveness Index (CEI)" }) or generate_query({ "metric": "Collection Effectiveness Index (CEI)", "adapter": "<your adapter JSON>" }).
Benchmarks
- Below 70%: Poor
- 70-80%: Average
- Above 80%: Good