Rene Micheau, through a recent blog post, introduced a type of working capital metric to me called the Collections Effectiveness Index (CEI). This is an alternative metric to Days Sales Outstanding (DSO) that is supposed to better capture AR performance.

The calculation of CEI is:

(Beginning AR + Sales – Ending AR) / (Ending AR + Sales – Current AR) * 100.

I left a comment on this post mentioning that DSO was not the best metric for collection activity, and when I was in Asset Based Lending we used one called turnover as a better indicator.

My inner analyst decided to play with this calculation to see how it worked. To simplify things, I assume that there is 100 of AR that is all current at the beginning of the month but is due in less than 30 days, so anything not collected during the month will be past due by the end of the month. I realize that this simplification may not perfectly reflect reality, but when attempting to explain something one can simplify the irrelevant aspects (see prior blog post for further information).

What happens if we collect none of the prior months AR and have sales of 50 during the current month?

CEI = (100 + 50 – 150) / (150 + 50 – 50) = 0/150 = 0 x 100 = 0

This intuitively makes sense, as no collections should correlate to a score of 0.

What happens if we collect all of the prior months AR and have sales of 50 during the current month?

CEI = (100 + 50 –50) / (50 + 50 – 50) = 100/50 = 2 x 100 = 200

This result is inconsistent with the claim that the highest score is 100 unless there are prepayments.

What happens if we collect all of the prior months AR and have sales of 102 during the current month?

CEI = (100 + 102 –102) / (102 + 102 – 102) = 100/102 =.98 x 100 = 98

This result indicates that when sales are higher for the current month than the prior, the CEI will slightly understate the collection activity, which is 100% of receivables coming due.

What happens if we collect all of the prior months AR and have sales of 98 during the current month?

CEI = (100 + 98 –98) / (98 + 98 – 98) = 100/98 =1.02 x 100 = 102

This result indicates that when sales are lower for the current month than the prior, the CEI will slightly overstate the collection activity, which is 100% of receivables coming due.

These examples indicate that the CEI is a proxy for collection of receivables coming due, but becomes more distorted and less meaningful the more current sales diverges from past activity.

*I would love to hear your thoughts about the CEI or your stories on this topic if you have them.*

*Please take the time to subscribe, bookmark, or otherwise note your web presence and support of this blog if you are able.*

Thanks for stopping by the Treasury Cafe!

Dear David,

ReplyDeleteYour CEI calculation formula is not correct - denominator in the formula should be

(Beginning AR + Sales – Current AR)instead of (Ending AR + Sales – Current AR).

Calculating CEI by correct formula, you will get 100% of CEI in all mentioned scenarios, notwithstanding sales numbers.

Bests,

Yuriy Mykhantso

Yuriy,

ReplyDeleteYou are correct that with the formula change the examples all get back to 100.

The formula in the blog post was taken from the link in the first paragraph, apparently there are different formulas for these.

For all those interested, there is a site that will calculate the CEI (using Yuriy's formula) at:

http://www.credit-to-cash-advisor.com/Resources/FinancialFormulas/CollectionEffectivenessIndex#Calc

I verified that the CEI calculates correctly under a number of scenarios.

Thank you for the comment and furthering the discussion!

I am not certain why we need to divide the Sales by number of periods. Can anyone clarify? I believe When measuring for a period extending beyond one month, we need not divide it by N so that it gives the actual % of Collected vs. Collectible.

ReplyDeleteI do not believe in the formula above we are dividing by N

DeleteDid anyone ever answer?

ReplyDeletePlease advise on the "sales" portion of this metric - beginning month's sales or the total sales at the end of the month...

ReplyDeleteThank you!

Total sales for the period (month, quarter, year) being analyzed

DeleteIf I have the beginning balance of money owed to the company, the ending balance of what is owed to the company, amount recovered during that period of time, and the new balances owed to the company that came during that period of time. How could I calculate the CEI & DSO?

ReplyDeleteYou should be able to use the corrected formula shown earlier in the comments section of this post

Delete