mannem_teja
New Member
- Joined
- Mar 13, 2022
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
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.
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. |