Please help with formula within waterfall (I am stumped)

[almst791]

Hi guys, I have such a difficult time explaining theissue but will try my best and attach file for an example.

So in my example: each month a number of new customers come each month (Input),each monthly set of customers has an input on how many times they will return(Input). This will change each month.



Our assumption is that they will return every month aftertheir initial visit for x number of months. X is driven by the input above.


So each month the timeframe for them returning willchange.

I currently have modeled in a formula that will tell ushow many returning customers you will have in a given month, this formula needsrewritten every time our input on monthly returns changes. I want this to beautomated so when monthly return inputs change this will update. (Row 6).




I have created a a waterfall below that will drive row 6but do not know what formula to write into row 6 so this will work. This model will expand about 5 years; it willtake me a very long time to write formulas for each month.

Please help me if possible.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
2				Initial Customer visti	1,375	4,165	6,166	7,465	22,164	30,536	42,350	57,292	86,470	124,688	184,379	295,791
3				AVG Times Customer returns	1.5	2.0	2.0	2.0	2.5	3.0	4.0	5.0	6.5	8.0	9.5	11.0
4				Rounded up	2.0	2.0	2.0	2.0	3.0	3.0	4.0	5.0	7.0	8.0	10.0	11.0
5				Total Future returns	2,063	8,330	12,333	14,929	55,409	91,607	169,400	286,458	562,057	997,500	1,751,602	3,253,698
6				Actual customer returns (1 month lag)		1,031	5,196	10,331	13,631	25,934	49,005	91,355	130,177	179,936	304,623	545,476	< I want this to flex based on input, driven from waterfall below. Right now its just a formula that does not flex, but the logic makes sense, everytime row 3 inputs are changed the formula needs rewritten
7				Total Returns	1,375	5,196	11,363	17,796	35,795	56,470	91,355	148,647	216,648	304,623	489,002	841,267
8
9					Customer returns waterfall
10		Returns	Periods		Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
11		2,063	2	Apr		1,031	1,031
12		8,330	2	May			4,165	4,165
13		12,333	2	Jun
14		14,929	2	Jul
15		55,409	3	Aug
16		91,607	3	Sep
17		169,400	4	Oct
18		286,458	5	Nov
19		562,057	7	Dec
20		997,500	8	Jan
21		1,751,602	10	Feb
22		3,253,698	11	Mar
23					-	1,031	5,196	4,165	-	-	-	-	-	-	-	-	< I want this to feed row 6
Model

Cell Formulas
Range	Formula
F4	=ROUNDUP(F3,0)
F5	=(F2*F3)
F6	=(E5/2)
F7	=F2+F6
F11	=$B$11/$C$11
F23	=SUM(F11:F22)
G4	=ROUNDUP(G3,0)
G5	=(G2*G3)
G6	=(E5/2)+(F5/2)
G7	=G2+G6
G11	=$B$11/$C$11
G12	=$B$12/$C$12
G23	=SUM(G11:G22)
H4	=ROUNDUP(H3,0)
H5	=(H2*H3)
H6	=(G5/2)+(F5/2)
H7	=H2+H6
H12	=$B$12/$C$12
H23	=SUM(H11:H22)
I4	=ROUNDUP(I3,0)
I5	=(I2*I3)
I6	=(H5/2)+(G5/2)
I7	=I2+I6
I23	=SUM(I11:I22)
J4	=ROUNDUP(J3,0)
J5	=(J2*J3)
J6	=(I5/3)+(H5/2)
J7	=J2+J6
J23	=SUM(J11:J22)
K4	=ROUNDUP(K3,0)
K5	=(K2*K3)
K6	=(J5/3)+(I5/3)
K7	=K2+K6
K23	=SUM(K11:K22)
L4	=ROUNDUP(L3,0)
L5	=(L2*L3)
L6	=(K5/4)+(J5/3)+(I5/3)
L7	=L2+L6
L23	=SUM(L11:L22)
M4	=ROUNDUP(M3,0)
M5	=(M2*M3)
M6	=(L5/5)+(K5/4)+(J5/3)
M7	=M2+M6
M23	=SUM(M11:M22)
N4	=ROUNDUP(N3,0)
N5	=(N2*N3)
N6	=(M5/7)+(L5/5)+(K5/4)
N7	=N2+N6
N23	=SUM(N11:N22)
O4	=ROUNDUP(O3,0)
O5	=(O2*O3)
O6	=(N5/8)+(M5/7)+(L5/5)+(K5/4)
O7	=O2+O6
O23	=SUM(O11:O22)
P4	=ROUNDUP(P3,0)
P5	=(P2*P3)
P6	=(O5/8)+(N5/7)+(M5/5)+(L5/4)
P7	=P2+P6
P23	=SUM(P11:P22)
E4	=ROUNDUP(E3,0)
E5	=(E2*E3)
E7	=E2+E6
E23	=SUM(E11:E22)
B11:B22	{=TRANSPOSE(E5:P5)}
C11:C22	{=TRANSPOSE(E4:P4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mikerickson]

Why is does a row that is called "Actual ...." have formulas? (row 6)
Actuals are data not calculations.

 

[almst791]

Sorry for the confusion