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:

	1	2	3	4	5	6
Cohort	1	0,28110628	0,209170244	0,174504558	0,143120272	0,117897221
Customers	1 758	4 271	11 452	21 506	51 751	54 719

<colgroup><col width="65" span="7" style="width: 65pt;"></colgroup><tbody>
</tbody>

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

<colgroup><col width="65" span="13" style="width: 65pt;"></colgroup><tbody>
</tbody>

 

[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