I have a problem that I suspect is very common situation that others have faced and solved.
Visualize a membership health club. Every month new members join. But over time members drop out. There is a predictable pattern at which members drop their membership (lets call it the "decay rate curve"). Assume that this curve does not change - it is applicable to a block of members who joined last July, and is equally applicable to members expected to join next December. Given a monthly projection of new memberships sold, how do you calculate the expected number of total active members in each future month?
To be more explicit, the December 05 membership will contain very few of the people who joined in January 05 (most have quit), plus some of the people who joined in February 05, plus . . . . plus almost all of the people who joined in November 05. Then I need to figure out how many people I expect to have as members in January 06, and then in February 06, etc.
It has to be a simple calculation grid, possibly using array logic? Possibly using the sumproduct function? Is there a good non-VBA answer?
Does anyone have a sample of the solution?
Edit: I forgot to say, I'm using Excel 2002, but can go to 2003 if I have to.
Visualize a membership health club. Every month new members join. But over time members drop out. There is a predictable pattern at which members drop their membership (lets call it the "decay rate curve"). Assume that this curve does not change - it is applicable to a block of members who joined last July, and is equally applicable to members expected to join next December. Given a monthly projection of new memberships sold, how do you calculate the expected number of total active members in each future month?
To be more explicit, the December 05 membership will contain very few of the people who joined in January 05 (most have quit), plus some of the people who joined in February 05, plus . . . . plus almost all of the people who joined in November 05. Then I need to figure out how many people I expect to have as members in January 06, and then in February 06, etc.
It has to be a simple calculation grid, possibly using array logic? Possibly using the sumproduct function? Is there a good non-VBA answer?
Does anyone have a sample of the solution?
Edit: I forgot to say, I'm using Excel 2002, but can go to 2003 if I have to.