What the collection effectiveness index measures
The collection effectiveness index (CEI) scores, as a percentage, how much of the money you could have collected in a period you actually collected. Unlike DSO it is not distorted by a change in sales.
DSO has one weakness: a quiet month makes it look worse and a busy month makes it look better, whatever your collecting did. CEI removes sales from the question and scores only the collecting. That makes it the fairer number to judge a period by.
The CEI formula, and why it is fairer than DSO
CEI = (opening receivables + credit sales - closing receivables) / (opening receivables + credit sales - closing current receivables) x 100.
The top of the fraction is what you collected. The bottom is what was available to collect: money that is still inside its terms was never collectable, so it comes out. DSO does not do that, which is why a month with unusually high sales flatters DSO even when nothing about your collecting changed.
CEI example, step by step
With the figures already in the calculator: $52,000 owed at the start, $140,000 of credit sales, $44,000 owed at the end, of which $31,000 is not yet due.
- Collected: 52,000 + 140,000 - 44,000 = $148,000.
- Available to collect: 52,000 + 140,000 - 31,000 = $161,000.
- CEI: 148,000 / 161,000 x 100 = 91.9%.
Collection effectiveness index in Excel
Put opening receivables in B1, credit sales in B2, closing receivables in B3 and the not-yet-due part of the closing balance in B4. The CEI is =(B1+B2-B3)/(B1+B2-B4), formatted as a percentage. Copy the row down with one line per month to see the trend.
What a good score is
Above 80% is the rule of thumb most credit guides use, and the closer to 100% the better. Because the ratio already allows for how much there was to collect, it compares between periods of different size more fairly than DSO does.
A score that falls while DSO stays flat is worth investigating. It usually means sales grew and hid a collections problem.
Use it alongside the aging, not instead of it
CEI scores a period. The aging report shows where the risk sits right now. A business can post a decent CEI while carrying one very old debt that will never be paid, because one invoice makes little difference to a ratio and all the difference to a small business. Read both.
Related calculators
- DSO calculatorDays sales outstanding, and what shortening it releases
- AR turnover calculatorThe turnover ratio, and the days to collect behind it
- AR aging calculatorThe balance owed, split by how overdue it is
Questions people ask
What is the collection effectiveness index?
CEI scores how much of the money that was available to collect you actually collected in a period, as a percentage. It excludes receivables still inside their terms, since those were never collectable.
What is the CEI formula?
Opening receivables plus credit sales minus closing receivables, divided by opening receivables plus credit sales minus current receivables, multiplied by 100.
How do you calculate CEI?
Add opening receivables and credit sales. Subtract closing receivables to get what you collected, and separately subtract the not-yet-due part of the closing balance to get what was available. Divide the first by the second and multiply by 100.
What is a good CEI score?
Above 80% is the usual rule of thumb, and the closer to 100% the better. Track your own trend month to month as well, since a falling score is a signal whatever the level.
How is CEI different from DSO?
DSO is distorted by changes in sales: a busy month shortens it whatever your collecting did. CEI takes sales volume out of the question and scores only the collecting.
What counts as current receivables?
The part of your closing balance that has not yet reached its due date. It was not available to collect during the period, so it is excluded from the denominator.
How do I calculate CEI in Excel?
With opening receivables in B1, credit sales in B2, closing receivables in B3 and closing current receivables in B4, use =(B1+B2-B3)/(B1+B2-B4) and format the cell as a percentage.
How often should I calculate CEI?
Monthly is the usual period, because it is short enough to catch a problem early. A quarter or a year works the same way with that period's figures.
Can CEI be over 100%?
Not with consistent figures. The not-yet-due part of the closing balance can never be larger than the whole closing balance, so the top of the fraction is never bigger than the bottom. A result over 100% means one of the four figures is wrong.