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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top