Consistent Member Add Given Desired Target and Time Constraint

[tonyperkis]

Hello,

This is as much of a math question as it is an excel question but hoping someone has an idea. I am trying to determine how many members I would need to add per month to hit an ending member target given a set beginning # of members and a set attrition rate (members lost/beginning members), and a desired # of months to achieve this goal. In the example below I know that a given location that wants to hit 500 members in 12 months will need to add 54 members/month. I got to this solution via goal seek but would prefer that a user could enter their desired parameters into the yellow highlighted cell and get a formulaically derived answer. I'm spinning my wheels on this one so welcome any suggestions.

Thanks!

Member Build.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	New Brand
2	Member Build Tool
3
4
5	Store Information		XXXXX
6
7		Open Date	3/1/2019
8		Market	MN-Minnesota
9
10
11
12	Target Member Build
13
14	Enter Target Member Count				500
15	Enter # of Months to reach Target				12
16
17	Current Member Count				347
18	Current Attrition %				9.6%
19
20	Avg. Monthly Member Add Required to Hit Target				54				1	2	3	4	5	6	7	8	9	10	11	12
21								Beginning Members	347	368	387	404	419	433	446	457	467	477	485	493
22								Members Lost	(33)	(35)	(37)	(39)	(40)	(41)	(43)	(44)	(45)	(46)	(46)	(47)
23								New Member Adds	54	54	54	54	54	54	54	54	54	54	54	54
24								Ending Member Count	368	387	404	419	433	446	457	467	477	485	493	500
25
26								Target Ending Members	500	500	500	500	500	500	500	500	500	500	500	500
27								Delta	(132)	(113)	(96)	(81)	(67)	(54)	(43)	(33)	(23)	(15)	(7)	-
28
29								Member Add	186	167	150	135	121	108	97	87	77	69	61	54
30									54
31
32
Member Build Tool

Cell Formulas
Range	Formula
G20	G20	=K30
L20:V20	L20	=K20+1
K21	K21	=$G$17
L21:V21	L21	=K24
K22:V22	K22	=-$G$18*K21
K23:V23	K23	=$K$30
K24:V24	K24	=SUM(K21:K23)
K26:V26	K26	=$G$14
K27:V27	K27	=IFERROR(ROUND(K24-K26,0),"")
K29:V29	K29	=$G$14-(K$21*(1-$G$18))

Named Ranges
Name	Refers To	Cells
'Member Build Tool'!solver_adj	='Member Build Tool'!$K$30	G20, K23:V23

 

[DRSteele]

Welcome to the forum.

What you have here is a diminishing geometric series with a constant. See here for example.

The general formula for your data after being rearranged from a summation series is:

End = Start(1+rate)^periods + Constant * (1-(1+rate^periods)) / (1-(1+rate))

So,
Start = 347
Rate = -.0960
Constant = 54

End = 347(1-.096)^12 + 54*(1-.096^12) / (1-(1-.096) = 498

and that works out, ignoring rounding.

So we can solve for Constant if we know End, or 500.
Constant = [500 - 347(1-.096)^12] / [(1-.096^12) / (1-(1-.096)] = 54.2

I can't type very well and I might have messed up the text here, so I include an Excel model.

MrExcel posts18.xlsx
	B	C	D
3	start	347
4	rate	-9.60%	90.40%
5	end	500
6	constant	54
7	periods	12
8
9
10
11	Formula for End		103.3596
12			394.9503
13			498.3098
14
15	Formula for Constant		396.6404
16			7.313894
17			54.23109
Sheet66

Cell Formulas
Range	Formula
D4	D4	=1+C4
D11	D11	=C3*D4^C7
D12	D12	=C6*(1-D4^C7)/(1-D4)
D13	D13	=SUM(D11:D12)
D15	D15	=C5-D11
D16	D16	=(1-D4^C7)/(1-D4)
D17	D17	=D15/D16

 

[tonyperkis]

Welcome to the forum.

What you have here is a diminishing geometric series with a constant. See here for example.

The general formula for your data after being rearranged from a summation series is:

End = Start(1+rate)^periods + Constant * (1-(1+rate^periods)) / (1-(1+rate))

So,
Start = 347
Rate = -.0960
Constant = 54

End = 347(1-.096)^12 + 54*(1-.096^12) / (1-(1-.096) = 498

and that works out, ignoring rounding.

So we can solve for Constant if we know End, or 500.
Constant = [500 - 347(1-.096)^12] / [(1-.096^12) / (1-(1-.096)] = 54.2

I can't type very well and I might have messed up the text here, so I include an Excel model.

MrExcel posts18.xlsx
	B	C	D
3	start	347
4	rate	-9.60%	90.40%
5	end	500
6	constant	54
7	periods	12
8
9
10
11	Formula for End		103.3596
12			394.9503
13			498.3098
14
15	Formula for Constant		396.6404
16			7.313894
17			54.23109
Sheet66

Cell Formulas
Range	Formula
D4	D4	=1+C4
D11	D11	=C3*D4^C7
D12	D12	=C6*(1-D4^C7)/(1-D4)
D13	D13	=SUM(D11:D12)
D15	D15	=C5-D11
D16	D16	=(1-D4^C7)/(1-D4)
D17	D17	=D15/D16

Wow this is awesome - thank you! I'm still playing around with this but your approach is definitely working for me! I'll have to sit down and work through your logic again so I can apply this to different scenarios in the future.

Thanks again for your help - it is greatly appreciated!

 

[DRSteele]

Super! You're welcome.

if you have time, mark the above as the solution.