Building customer cohort chart with dynamic renewal rates and periods

Hi guys. Need one big help for my work. I am trying to forecast a month on month subscription revenue model in MS excel. There are two subscription plans - annual and quarterly. The subscription pricing for each plan changes month on month. I have also assumed some renewal rates that change month on month. So, effectively the renewal rate decides how many of the quarterly/annual subscribers will renew each time after 3/12 months respectively. Through this approach I want to calculate the active subscribers each month in quarterly and annual subscription. I want to then multiply the monthly charge (divide by 3/12) of the applicable price (of the month they renewed/bought the plan) to the active subscribers in that month to arrive at the monthly revenue.

I can imagine that understanding my requirement will be difficult so I have done the working in a sample data for a few months for the quarterly plan to help understand what I need. I have highlighted the cells where I want to do these calculations - currently it's more manual with functions that can't be dragged along. It's in the attached file


Thanks in advance!

I applied some SCAN functions, see if it is the way to go.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Yes, tks Ali.