Calculating actual numbers based on cohorts

tiredcreator

New Member
Joined
Aug 29, 2019
Messages
1
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:

123456
Cohort10,281106280,2091702440,1745045580,1431202720,117897221
Customers1 7584 27111 45221 50651 75154 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,563
Office Version
365
Platform
Windows
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
 
Last edited:

Forum statistics

Threads
1,084,967
Messages
5,380,881
Members
401,704
Latest member
DravenExcel

Some videos you may like

This Week's Hot Topics

Top