decay curve of new members to predict future membership

DD-SF

New Member
Joined
Dec 21, 2005
Messages
25
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
In each month forward progressively you would simply take the current membership as your base

each month forward would be that membership less the cumulative decay # of members + the cumulative new members # projection

You would have a fixed number as NOW

next month would be NOW - Decay expected + new members

two months out would be NOW + two months total decay + two months new members...

and roll it forward as far as you need keeping the base number fixed on a month, and rolling the projections forward. Each month you could put your actual membership in and the rolling figures could adjust...

Email me if you need help, i set up curves like this quite a bit at work.
 

DD-SF

New Member
Joined
Dec 21, 2005
Messages
25
thanks an awful lot for the quick response, but the suggestion you have is a sort of "brute force" approach - it will work fine for a few months, and that's what I've got now, but I want to be able to extend this out for a couple of years, monthly. (assume the health club example, and people stick around for a while - some drop within the first few months, and some percent of the people are still there 3 years after joining). The brute force suggestion gives me a massive formula after a year or so.

I've tried building a layered grid - one row for each month, looking like a stairstep, and adding down the columns. But, that gets pretty big and ugly. I was wondering if there was another solution.
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Not really, let's say your first cell is in A1

To add A1 to any cell that follows in the same row would by just SUM($A$1:A2) and drag the formula.

I would not have suggested a solution that would be brute force. As i said, understand the concept, and I do things like this all the time at work.
 

DD-SF

New Member
Joined
Dec 21, 2005
Messages
25
Thanks again - I'll go with that approach for now. Hope you didn't take my comment wrong - I was just wondering if there was an array based solution that might take up less space on the sheet - it will get a bit big over a lot of months.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top