Algorithm/Calculation to minimize error in the finding the best curve

[mannem_teja]

Hi All!

Please check the workbook below. I am trying to calculate the "Continue Rate" for each month so that the variance between calculated repeating customers and observed repeating customers is as minimum as possible.
Basically the continue rate is defined as 'how many customers in one month will stay as customers in the next month, or the second month and so on'.
So, is there any method to minimise the variance with some constraints such as the continue rate cannot be over 100% or below 0%, the subsequent month cannot have more continue rate than the previous month? For example, the Apr'20 continue rate curve for 2nd month cannot be more than 100%. And similarly the 3rd month's rate cannot be over 97% and so on.

Here is the workbook if you want to take a closer look : algo try v1.xlsx

Any help is appreciated! Thank you.

algo try v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2		Start Month	Apr'20
3		End Month	Mar'22
4
5			Apr'20	May'20	Jun'20	Jul'20	Aug'20	Sep'20	Oct'20	Nov'20	Dec'20	Jan'21	Feb'21	Mar'21	Apr'21	May'21	Jun'21	Jul'21	Aug'21	Sep'21	Oct'21	Nov'21	Dec'21	Jan'22	Feb'22	Mar'22
6		Starting Customers Each Month	13	19	14	9	8	10	10	29	15	20	16	18	8	8	26	19	10	15	9	18	14	14	10	4
7		Observed Repeating Customers	23	23	40	32	35	28	40	42	77	69	76	80	71	68	73	101	92	97	93	99	89	83	77	81
8
9		Calculated Repeating Customers	0	12	30	43	50	56	63	71	95	106	122	132	143	145	146	164	175	176	182	181	189	192	196	194
10		%Variance	80%
11
12		Continue Rate	Apr'20	May'20	Jun'20	Jul'20	Aug'20	Sep'20	Oct'20	Nov'20	Dec'20	Jan'21	Feb'21	Mar'21	Apr'21	May'21	Jun'21	Jul'21	Aug'21	Sep'21	Oct'21	Nov'21	Dec'21	Jan'22	Feb'22	Mar'22
13		1	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
14		2	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%	97%
15		3	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%	93%
16		4	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%	90%
17		5	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%	86%
18		6	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%	83%
19		7	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%	79%
20		8	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%	76%
21		9	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%	73%
22		10	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%	69%
23		11	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%	66%
24		12	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%	62%
25		13	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%	59%
26		14	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%	55%
27		15	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%	52%
28		16	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%	48%
29		17	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%	45%
30		18	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%	42%
31		19	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%	38%
32		20	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%
33		21	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%	31%
34		22	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%	28%
35		23	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%
36		24	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%	21%
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C2
D5:Z5	D5	=EDATE(C5,SEQUENCE(,DATEDIF(C2,C3,"M")))
C9:Z9	D9	=SUM(D40:D62)
C10	C10	=SUM(C9:Z9)/SUM(C7:Z7)-1
C12	C12	=C2
D12:Z12	D12	=EDATE(C5,SEQUENCE(,DATEDIF(C2,C3,"M")))
B14:B36	B14	=B13+1
Dynamic array formulas.