Building customer cohort chart with dynamic renewal rates and periods

[sambhav]

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. The file is here Google drive

Thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Building customer cohort chart with dynamic renewal rates and periods
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sambhav]

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. The file is here Google drive

Thanks in advance!

I have also posted this on Reddit. Needed to post it on multiple forums because of the urgency of the situation

 

[Peter_SSs]

Needed to post it on multiple forums because of the urgency of the situation

Just be aware that can sometimes actually work against getting a speedy reply. There will be potential helpers on all forums that may be interested in your question but not interested in searching other forums to see if the question has already been answered so will just move on to another thread. It is possible that is the case here as your thread has had more than 30 "views" but no responses related to your actual question.