Calculating actual numbers based on cohorts

[tiredcreator]

Hi everyone!

Compiling buyers LTV model I ran into problem of calculating future periods active buyers basing on drop-off cohort & actual buyers attracted.

What I got is:

[TABLE="width: 455"]
<colgroup><col width="65" span="7" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65, align: right"]1[/TD]
[TD="width: 65, align: right"]2[/TD]
[TD="width: 65, align: right"]3[/TD]
[TD="width: 65, align: right"]4[/TD]
[TD="width: 65, align: right"]5[/TD]
[TD="width: 65, align: right"]6[/TD]
[/TR]
[TR]
[TD]Cohort[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0,28110628[/TD]
[TD="align: right"]0,209170244[/TD]
[TD="align: right"]0,174504558[/TD]
[TD="align: right"]0,143120272[/TD]
[TD="align: right"]0,117897221[/TD]
[/TR]
[TR]
[TD]Customers[/TD]
[TD="class: xl63, align: right"]1 758[/TD]
[TD="class: xl63, align: right"]4 271[/TD]
[TD="class: xl63, align: right"]11 452[/TD]
[TD="class: xl63, align: right"]21 506[/TD]
[TD="class: xl63, align: right"]51 751[/TD]
[TD="class: xl63, align: right"]54 719[/TD]
[/TR]
</tbody>[/TABLE]

Meaning that in month 1 we've attracted 1758 customers. In month 2 basing on statistics, we would expect 1758*0,28110628 customers from month 1 + 4271 new customers. In month 3: 1758*0,209170244 from month1, 4271*0,28110628 customers from month2 & 11452 new customers and so on.

What I'm willing to get is formula to calculate number of customers in a separate cell for every period according to the rule above. Is there a way to get it using formulas?

Thanks in advance
[TABLE="width: 845"]
<colgroup><col width="65" span="13" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are still looking for an answer to this please
- carefully check the descriptions below & confirm or correct. It seems a bit back-to-front to me.
- give the detailed steps for months 4 and 5 as well
- provide all the expected results (that you have worked out manually) for the sample data in post 1.

In month 2 .. we would expect 1758*0,28110628 customers from month 1 + 4271 new customers. In month 3: 1758*0,209170244 from month1, 4271*0,28110628 customers from month2 & 11452 new customers