How best to model client cohorts (subscription + churn) on monthly basis for multiple cases
Hi all, I'm really stuck on this one thing for a side project and I can't figure it out
I have a model that's using SUMIF to compile the monthly projections into annuals
(flows like 2024,2025,2026 ---- Jan 2024, Feb 2024, March 2024 .... Dec 2026)
It's a physical product so there will be churn.
First Question: Am I right in thinking that there's no shortcut and each cohort has to be modelled out individually?
Cohort 1 purchases in Jan 2024, Cohort 2 purchases in Feb 2024 etc.
*******
I have been thinking about it as:
1. Reorder flag (1-0) for each cohort [e.g.80 rows]
1.1 How to make this flexible so that from the assumptions tab if I choose "reorder every 4 months" it automatically changes? So the first cohort from Jan 2024 purchases again in April. Some offset formula?? [UPDATE: I figured it out. =OFFSET(previous cell,,-(Assumption of Month-1)]
2. Churn for each cohort
2.1. Start with 100 clients, at month 2 there's 99, at month 3 there's 98 etc [UPDATE: I figured it out. =MAX(ROUND(Previous cell*(1-(((1+Assumptions for Churn)^(1/12))-1)),2),0) // calculates churn on previous number which isnt the same cumulatively as the inputted churn % but it works]
This part is important because it flows into COGS and re-orders. If I cheat by just reducing the annual/monthly revenue by assumed churn, COGS stays high because it's not being fed the real number of orders. [UPDATE: For COGS, when calculating amount that has been sold, just did Flag * Number of customers after churn)
3. Revenue [again 80 rows]
3.1 Function of Ending # of Cohort (2.1 above) & average price of product at time (simple index match) multiplied by the reorder flag [UPDATE: Now fixed]
*******
I have been stuck on this for 2 days so any help would be appreciated!! Hope I wrote this in a coherent way
Final q: it seems like this is really repetitive and time consuming - is there a way to get 80% accurate answer with 20% effort?
END UPDATE: I fixed all of it but still curious to hear if there's a shortcut apart from creating 3 'schedules/matrices/whatever it's called'
I’ve done this before and did it the long way as you’ve approached it… huge matrix. Maybe others have a better suggestion.
thanks a lot - appreciate the reply. thought so... it would be super helpful if someone shares a shortcut haha
Culpa illum et totam maiores. Distinctio qui quaerat dolor culpa.
Vel est incidunt vitae et aut facilis. Ea est dolore dolor dolor. Voluptas ut porro qui nisi dolores voluptatem aut ipsam. Reiciendis enim doloremque est sed quasi fugit quia.
Suscipit velit quas necessitatibus cum. Incidunt quas distinctio dignissimos eos dolor similique nulla. Quia qui tempore quia aut minus recusandae rem. Ipsam sint ut quo deserunt unde est sed. Veritatis officia necessitatibus harum animi cumque ratione.
Aut tempora aut iste quo voluptas. Voluptas dolorum dolores consequatur suscipit quis dolores. Distinctio illo ut non incidunt ut sunt reiciendis. Vel est dolorem quis nemo et quia quas. Rem culpa eaque voluptatem. Eum ab sit similique ut dolorem illum molestias.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...