Trying to come up with some formulas and I must have smoked to much of the good stuff in high school and college, because I'm just not getting it. I'm trying to set up a formula for goal setting with member retention and growth. Here's what I'm trying to figure out: Member count at beginning of period (period will be some number of months) - it's a known value Desired member count at end of period (again, period will be some number of months) - it's a known value Number of months in the period - it's a known value Average Member Attrition (number of members lost over the course of a month/total employee count at the beginning of the month) - it's a known value I'm trying to solve for how many members need to be added each month for x months to end up with the desired member count at the end of the period. For this example, I start with 1000 members. At the end of 6 months, I want to have at least 1050 members. I know each month I'm going to lose approximately 5% of the members that I started the month with. I need to know how many members I need to add each month to reach that goal while factoring in that I'm going to lose some each month. I know the number is 60 here because the math works, but, I had to figure it out by process of elimination. I'd like to do something like this where you fill out A B C and D and it provides E Starting member count = A Attrition = B Desired member count = C Number of months to reach goal = D (could be variable...just used 6 above as an example) Number of members that will need to be added each month to reach goal = E Is it humanly possible to come up with a formula to solve for E if you know A, B, C and D?
In this example, I solved it. Without knowing I2, you don’t know K2. I solved it by trial and error. I want to be able to solve it without trial and error.
oh i see....is there a minimum k in each row/month needed? like need to stay above some threshold? because you can just keep on decreasing and in month 6 need like 500 new members
This is fairly straightforward geometric growth. You start with 1000 and want to get to 1053, so 5.3% growth. It's over 6 periods, so 1.053^(1/6)-1 = 0.86%. Each period has a churn of 5%, so by definition the acquisition rate has to be 5.86%.
I think you pretty much got it, but I still can't figure it out. The 5.3% growth makes sense. But how did you get from 5.3% growth to 1.053 in your formula? And what the hell is that thing between the 1.053 and (1/6)? I'm trying to put something out that others could use. The user would enter B1, B2, B3, and B4. What is the formula that would be needed to produce B5? To see if your formula works, B1 = 700, B2 = 885, B3 = 6%, B4 = 9. I know the answer is 68, but I don't know what formula would go in B5 that would result in a value of 68.
1.053 is "1 + 5.3%". Said another way, your current value plus the growth. ^ means "to the power of". 3^2=9
Here's the longform (just changed the inputs from yesterday's screenshot). To be clear, the 68.47 above is a simplistic linear average - the actual mean is 67.3 when doing it longform (accounting for the geometric growth).
