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
Repellendus soluta voluptates possimus maiores mollitia. Tempore non in aut quos quis veritatis. Molestiae autem sed laboriosam ipsum. Commodi architecto soluta dolor atque.
Est vero doloremque eligendi aut repudiandae illo quos. In odit vero magni sed fugit. Quibusdam veritatis quasi magni placeat ea. Vitae explicabo assumenda soluta architecto nihil consectetur ea unde. Et aut neque corrupti quo quia. Hic eum tempora voluptas consectetur.
Architecto optio in eos odit. Commodi dolor deleniti recusandae non. Omnis distinctio ut et ullam voluptate qui eos odit.
Praesentium quae hic dolor. Qui optio qui tenetur eos omnis sed est. Commodi quia adipisci qui perspiciatis. Ut consequatur soluta sint et sed nostrum voluptas. Sint culpa adipisci qui. Veritatis nihil ullam cumque deleniti necessitatibus.
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...