Excel Customer Churn & Retention Dashboard

Description
Purpose of the tool
This Excel-based Customer (Churn) Analysis Model provides a deep, structured view of how your customer base, revenues and portfolio quality evolve over time. It tracks which customers are new, retained or lost in each period and quantifies how these movements drive total sales development.
Beyond simple churn rates, the model analyses customer concentration, portfolio stability and cohort retention, helping you understand how resilient and diversified your revenue base really is. By segmenting customers into stability classes (e.g. stable, medium, volatile) and tracking cohorts over multiple years, it highlights concentration risks, revenue dependency on key accounts and the remaining “useful life” of your customer base.

Key Outputs

• Customer base overview: number of customers, total sales and average sales per customer by year
• Customer concentration analysis (top 1, top 3/5 customers, long tail, HHI index and detailed share tables)
• Customer stability breakdown (stable vs. medium vs. volatile customers by sales and count)
• Churn & acquisition analysis:
• Waterfall views for change in number of customers and change in sales (existing vs. new vs. lost customers)
• Detailed per-customer churn classification (new, existing, departing) by year
• Cohort retention analysis:
• Customer retention curves and heatmaps by cohort (customer counts)
• Sales development by cohort, including churn % and remaining useful life (RUL) indicators
  
  

Key Inputs & Assumptions

• Annual, customer-level sales data (one row per customer, one column per year)
• Unique customer identifiers and consistent year structure
• Parameter settings for stability classes and minimum revenue thresholds in the assumptions tab
  
  

How to use

1. Set core parameters (years, currency, stability thresholds, etc.) in the Assumptions sheet.
2. Load customer-level sales into the Input sheet and ensure consistent IDs and no blank cells.
3. Review the overview, concentration, stability, churn and cohort tabs to analyse customer base development, churn and revenue sustainability.
   
   

Customization & support
The model is highly customisable – you can easily adapt thresholds, classifications, tables and charts to your specific data structure, industry or reporting standards. For more complex customisations or individual requirements (e.g. firm-specific versions, additional modules or integrations into existing workbooks), feel free to reach out and we can discuss a tailored solution.

Each download includes both a fully blank “Clean Template” for working directly with your own data and a pre-populated Example Template that illustrates the model’s logic and calculations using realistic sample data.

The screenshots provide a good first impression of the model, but they do not fully capture all functionalities and details—if you have any questions or would like to better understand specific features, feel free to reach out anytime.