Monday, August 15, 2011

Collection Effectiveness Index – A Good Measure?

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!

9 comments:

  1. Dear David,

    Your 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

    ReplyDelete
  2. Yuriy,

    You 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!

    ReplyDelete
  3. 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.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete