I am struggling with a case study preparation for consultant position: I have to create a cohort analysis & P&L forecasting presentation with the data they provided. But I am not sure how to start.

Question

Need help with a case study. I need to prepare for a case study interview. The case study is about a online platform application with subscription model (assuming 1 month subscription). I was given a data set table (no. of new user and total no. of users per month from Jan 2019 to Dec 2021) where I have to develop a cohort analysis. What metrics can I derive from this data set for the cohort analysis? Any help or ideas are much appreciated!

Answer 1

Hi there!

It is difficult to help without seeing the data, and even with the data, it wouldn’t be ethical.

General suggestion: I recommend you to do the cohort analysis and look for the differences in each one of them, so that you can derive the insights from there. Google to find the appropriate metrics to this kind of business/business analysis, it won’t be hard to find.

Good luck!

Best,

Mari

Answer 2

What makes sense in that case is to analyze:

1) Ability to capture new clients

2) Ability to retain existing clients / churn

Answer 3

Hey Na😊

Happy to help! For a subscription-based online platform, a cohort analysis based on your dataset (new users + total users per month) can reveal retention and churn behavior over time—crucial for forecasting and strategic recommendations 📊

Here’s how to get started + what metrics to derive:

🔹 Step 1: Build Monthly Cohorts Each month’s new users = 1 cohort. So Jan 2019 is Cohort A, Feb 2019 is Cohort B, and so on. List each cohort in rows, and months since signup (Month 0, Month 1, Month 2…) in columns.

🔹 Step 2: Estimate Retention Since you don’t have cancellation or churn data directly, use:

New users (Month 0)
Total users = active users
You can back-calculate churn or build a retention assumption (e.g. X% of users drop off each month)

🔹 Step 3: Calculate Metrics Here are key cohort analysis metrics you can estimate:

Monthly retention rate per cohort
Churn rate = 1 - retention
Cohort size over time
Cumulative churn
Lifetime value (if ARPU is known or assumed)
Revenue forecast (combine with assumed pricing model)

🔹 Step 4: Forecast P&L Once you estimate active users per month from your cohort matrix:

Revenue = Active users × subscription price
Costs (assume fixed + variable)
Gross profit, operating profit, net income

Let me know if you’d like help building the structure or turning this into a deck—I’d be happy to walk you through the Excel structure or do a review of your slides before the interview! 😊

Best,
Alessa 📈✨