We have a requirement to calculate the effectiveness of converting new debt. For example, in any given month we will have New Debt which is 0-30 days old, 31-60days, 61-90days and 90+days.
New debt is termed any customer agreements moving into 0-30day arrears.
The requirement is to Identify the list of customer agreements moving into this 0-30day bucket each month, and track how many of thise same agreements are still outstanding the following month, and so on.
eg
0-30 31-60 61-90 90+
M1 100 - - -
M2 125 50 - -
M3 89 95 20 -
So we see of the 100 agreements in M1, 50% are converted, as only 50 remain outstanding in M2. There are 20 of these remaining in M3 in the 61-90 bucket.
I would have used a straight count (Monthend date as dimension) of the agreements in these ageing buckets, however there is existing data which would be useful to exclude. ie if the report starts Jan10, we dont want any customer arrears from Dec09 being included - only new debt from the start point of the report.
I am essentially looking at Set analysis, for this - to generate a list of new debt agreements in 0-30 / M1, which I would need to reference in 31-60 / M2.
Perhaps someone has done something similar in Debt analysis, customer retention, or service desk conversions?
Any thoughts would be much appreciated.
