SaaS Metrics Dashboard Excel: Track MRR, Churn, LTV and Growth

Move beyond basic revenue tracking with a comprehensive SaaS metrics dashboard that gives you real insights into business health and growth opportunities.

Why SaaS Metrics Matter

Traditional business metrics don't capture SaaS dynamics:

Revenue recognition differs (MRR vs. one-time sales)
Customer lifetime value is critical
Churn has exponential impact
Growth requires specific tracking

Key Insight: A 5% reduction in monthly churn can increase company valuation by 25-50%.

Essential SaaS Metrics Framework

Tier 1: Revenue Metrics (Board Level)

Monthly Recurring Revenue (MRR)
Annual Recurring Revenue (ARR)
Average Revenue Per User (ARPU)
Revenue Churn Rate

Tier 2: Customer Metrics (Operational)

Customer Acquisition Cost (CAC)
Customer Lifetime Value (LTV)
LTV:CAC Ratio
Payback Period

Tier 3: Growth Metrics (Strategic)

Net Revenue Retention (NRR)
Quick Ratio
Magic Number
Rule of 40

Tier 4: Product Metrics (Tactical)

Monthly Active Users (MAU)
Feature Adoption Rates
Customer Health Scores
Support Ticket Trends

Building Your SaaS Dashboard

1. Data Collection Structure

Date: [2025-12-01]
Customer_ID: [CUST-001]
Plan: [Professional]
MRR: [$99]
Signup_Date: [2025-06-15]
Churn_Date: [if applicable]
Expansion_MRR: [$0]
Contraction_MRR: [$0]

2. Monthly Recurring Revenue (MRR) Calculation

Starting_MRR = SUM(All_Active_Subscriptions)
+ New_MRR = SUM(New_Customers * Their_MRR)
+ Expansion_MRR = SUM(Upgrades - Downgrades)
- Churned_MRR = SUM(Cancelled_Subscriptions)
= Ending_MRR

MRR Components:

New Business MRR
Expansion MRR (upgrades, add-ons)
Contraction MRR (downgrades)
Churned MRR (cancellations)

3. Customer Churn Calculations

Customer_Churn_Rate = Customers_Lost_This_Month / Customers_Start_of_Month
Revenue_Churn_Rate = MRR_Lost_This_Month / MRR_Start_of_Month
Gross_Churn = Total cancellations
Net_Churn = Gross_Churn - Expansion from existing customers

4. Customer Lifetime Value (LTV)

LTV = (ARPU * Gross_Margin) / Customer_Churn_Rate

Alternative calculation (more accurate):

Monthly_Retention_Rate = 1 - Monthly_Churn_Rate
LTV = ARPU * Gross_Margin * (1 / (1 - Monthly_Retention_Rate))

5. Customer Acquisition Cost (CAC)

CAC = Total_Sales_Marketing_Costs / New_Customers_Acquired

Cost components:

Marketing spend
Sales salaries and commissions
Software tools
Overhead allocation

6. LTV:CAC Ratio

LTV_CAC_Ratio = LTV / CAC

Benchmarks:

< 1: Unsustainable
1-3: Typical
>3: Excellent
>5: Exceptional (but may indicate under-investing in growth)

Advanced SaaS Analytics

Cohort Analysis

Track customer groups by signup month:

Cohort_Month = EOMONTH(Signup_Date, 0)
Months_Since_Signup = DATEDIF(Signup_Date, Report_Date, "M")
Cohort_Retention = COUNTIFS(Cohort_Month, "=Cohort", Churn_Date, ">Report_Date") / Cohort_Size

Net Revenue Retention (NRR)

Measures growth from existing customers:

NRR = (Starting_MRR + Expansion - Contraction - Churn) / Starting_MRR

NRR Interpretation:

<100%: Shrinking business
100%: Stable
100-120%: Good
>120%: Excellent
>130%: World-class

Quick Ratio

Measures growth efficiency:

Quick_Ratio = (New_MRR + Expansion_MRR) / (Contraction_MRR + Churned_MRR)

Quick Ratio Benchmarks:

<1: Business is shrinking
1-2: Slow growth
2-4: Healthy growth
>4: Very efficient growth

Magic Number

Sales efficiency metric:

Magic_Number = (Current_Quarter_Revenue - Previous_Quarter_Revenue) * 4 / Previous_Quarter_Sales_Marketing_Spend

Magic Number Interpretation:

<0.5: Inefficient spending
0.5-0.75: Moderate efficiency
0.75-1.0: Good efficiency
>1.0: Excellent efficiency

Real-World Case Study: Scaling from $10K to $100K MRR

Company: B2B SaaS tool with 3 pricing tiers

Starting Point (Month 1):

MRR: $10,250
Customers: 142
ARPU: $72
Monthly Churn: 4.2%
CAC: $420

Dashboard Insights Revealed:

Churn concentration: 68% of churn from lowest tier
Expansion opportunity: Only 12% of customers used add-ons
CAC imbalance: Enterprise CAC 3x higher but LTV 8x higher
Quick ratio: 1.8 (needed improvement)

Action Plan from Dashboard:

Product: Improve onboarding for low-tier customers
Pricing: Introduce annual plans with discount
Sales: Focus on mid-tier (best LTV:CAC ratio)
Marketing: Content targeting specific use cases

Results after 12 months:

MRR: $98,500 (861% growth)
Monthly Churn: 1.8% (57% reduction)
LTV:CAC Ratio: 4.2 (from 2.1)
Quick Ratio: 3.4 (from 1.8)
NRR: 118% (from 96%)

Dashboard Visualization Techniques

1. MRR Waterfall Chart

Visualize MRR movements
Show new vs. expansion vs. churn
Track monthly progression

2. Cohort Retention Heat Map

Show retention by signup month
Identify problematic cohorts
Track long-term customer value

3. LTV:CAC Scatter Plot

Plot customers by acquisition cost and value
Identify most profitable segments
Optimize marketing spend

4. Metric Trend Lines

Show metric progression over time
Identify improvement trends
Set realistic targets

Template Features

Automated Calculations

All key SaaS metrics pre-calculated
Dynamic date ranges
Scenario analysis tools
Benchmark comparisons

Data Import Options

CSV import from billing systems
API connections (Stripe, Chargebee, etc.)
Manual entry templates
Historical data migration

Reporting Capabilities

Executive summary reports
Investor update packages
Team performance dashboards
Board meeting materials

Alert System

Churn rate warnings
CAC threshold alerts
NRR target notifications
Metric anomaly detection

Best Practices for SaaS Metrics

Data Quality

Single source of truth for customer data
Regular data validation checks
Clear definitions for all metrics
Historical data preservation

Analysis Frequency

Daily: Key revenue metrics
Weekly: Growth and churn trends
Monthly: Comprehensive review
Quarterly: Strategic analysis

Decision Support

Use metrics for pricing decisions
Inform product roadmap priorities
Guide marketing spend allocation
Support fundraising efforts