Finance Videos

Make a Cohort Analysis in Excel

The video walks viewers through constructing a dynamic cohort analysis template in Excel, enabling businesses to track customer behavior, revenue generation, and acquisition profitability across monthly cohorts. It begins by defining assumptions—initial customers, month‑over‑month growth, average revenue per user, acquisition cost, and retention rates—then demonstrates how to lock these cells and propagate calculations for each cohort’s size, revenue, total cohort revenue, and customer‑lifetime revenue. The tutorial shows formulas for CAC, LTV, and LTV‑to‑CAC multiples, emphasizing the use of absolute references (F4) and fill‑down/right shortcuts. The presenter highlights practical examples, such as an 8 % growth rate, $50 ARPU, $75 CAC, and an 85 % monthly retention, illustrating how a 5× LTV‑to‑CAC ratio signals profitability while noting that the ratio declines as data gaps appear. He also promotes a free “50 Excel hacks” template from HubSpot and demonstrates charting retention trends and applying conditional formatting for visual clarity. By providing a reusable model, the tutorial equips startups and analysts with a quick way to test pricing, marketing, and retention scenarios, though it warns of limitations like lack of discounting, static ARPU, and uniform retention curves. Understanding these metrics helps prioritize growth levers and justify investment decisions on pricing, acquisition spend, and retention initiatives.