SaaS Financial Model Spreadsheet Template

Purpose

This recipe produces a complete, integrated SaaS financial model in a 7-tab spreadsheet workbook covering MRR waterfall (new, expansion, contraction, churn, reactivation), cohort retention analysis, three-statement financials (P&L, balance sheet, cash flow), cap table with SAFE/convertible note modeling, and base/bull/bear scenario toggles. The output is investor-grade — suitable for board reporting, fundraising decks, and internal planning from pre-revenue through Series A. [src1]

Prerequisites

• Pricing structure defined — tier names, monthly/annual prices, expected distribution across tiers
• Historical MRR data (if post-revenue) — monthly breakdown from billing system (Stripe, Chargebee, or manual) with new, expansion, contraction, and churned MRR
• Operating expense estimates — headcount plan (roles, salaries, start dates), tool/infrastructure costs, marketing budget
• Cap table inputs (if modeling equity) — founder splits, any existing SAFEs/convertible notes, option pool size
• Google Sheets or Excel — free Google account sufficient
• 3-6 hours of focused time — model construction is sequential; interrupted work produces broken links

Constraints

• MRR waterfall must decompose into five distinct components (New, Expansion, Contraction, Churn, Reactivation) — a single “net new MRR” line hides whether growth comes from acquisition or retention. [src4]
• Cohort analysis requires month-by-month tracking per acquisition cohort. Rolling averages obscure retention decay patterns that investors scrutinize. [src3]
• Three-statement model must be fully linked: P&L net income feeds cash flow, ending cash feeds balance sheet. Any break invalidates the model. [src5]
• All assumptions must live in a single dedicated tab. Formula cells must never contain hardcoded numbers. [src1]
• CAC payback benchmarks shifted in 2025: median is now 15 months across B2B SaaS. Models using pre-2024 benchmarks appear unrealistically optimistic. [src6]

Tool Selection Decision

Which path?
├── User is non-technical AND wants fastest setup
│   └── PATH A: Template-Based — Google Sheets with pre-built template
├── User is semi-technical AND wants customizable model
│   └── PATH B: Build in Sheets — Google Sheets from scratch using this recipe
├── User wants dedicated FP&A tool AND has budget
│   └── PATH C: Dedicated Platform — Causal, Mosaic, or Jirav
└── User is analyst/developer AND wants maximum control
    └── PATH D: Excel Power Build — Excel with VBA macros and data connections

Execution Flow

Step 1: Create Workbook Structure and Assumptions Tab

Duration: 30-45 minutes · Tool: Google Sheets or Excel

Create the 7-tab workbook skeleton. The Assumptions tab is the control center — every other tab reads from it. Blue cells indicate editable inputs; black/grey cells are computed. Include a scenario dropdown (Base/Bull/Bear) at cell B1 that toggles all scenario-dependent assumptions via INDEX/MATCH formulas.

Assumptions tab sections:
- Scenario Toggle (dropdown: Base / Bull / Bear)
- Revenue Assumptions (pricing tiers, tier distribution, customer acquisition rates, retention rates)
- Unit Economics (CAC, gross margin target, LTV:CAC target)
- Operating Expenses (headcount plan by role/month/salary, non-headcount costs)
- Financing (existing cash, planned raise amount/month/valuation)

Verify: Every blue input cell has a value. No formula cells contain hardcoded numbers. Scenario dropdown switches between three columns of assumptions. · If failed: Check that INDEX/MATCH formulas reference the scenario dropdown cell with an absolute reference ($B$1).

Step 2: Build MRR Waterfall

Duration: 45-60 minutes · Tool: Google Sheets / Excel

The MRR waterfall tracks monthly recurring revenue movements across five components. This is the most important tab for SaaS investors. [src4] Build a 36-column waterfall: Beginning MRR, (+) New MRR, (+) Expansion MRR, (-) Contraction MRR, (-) Churn MRR, (+) Reactivation MRR = Ending MRR. Below the waterfall, compute ARR, MoM growth, NRR, GRR, and Quick Ratio.

Key formulas:
  New customers(N) = ROUND(Month1_customers × (1 + growth_rate)^(N-1), 0)
  New MRR = New_customers × blended_ARPU
  Expansion MRR = Beginning_MRR × expansion_rate
  Churn MRR = Beginning_MRR × churn_rate
  NRR = (Beginning - Contraction - Churn + Expansion) / Beginning
  Quick Ratio = (New + Expansion) / (Contraction + Churn)

Verify: Ending MRR of Month N = Beginning MRR of Month N+1. NRR should be 95-115% for a healthy model. [src2] · If failed: Check that churn applies to full base, not just new customers.

Step 3: Build Cohort Retention Analysis

Duration: 45-60 minutes · Tool: Google Sheets / Excel

Build two triangular cohort tables: logo retention (percentage of customers retained per cohort per month) and revenue retention (MRR per cohort over time). Apply conditional formatting (green > 90%, yellow 80-90%, red < 80%) for visual pattern detection. [src3]

Logo retention M(N) = M(N-1) × (1 - monthly_logo_churn)
Revenue per cohort M(N) = M(N-1) × (1 - gross_churn + expansion_rate)
NRR (from cohort) = Sum of M12 revenue / Sum of M0 revenue

Verify: NRR from cohort data should match NRR from MRR Waterfall tab. · If failed: Cross-check expansion rate against benchmarks — median NRR is 100-110% for B2B SaaS.

Step 4: Build Three-Statement Model

Duration: 60-90 minutes · Tool: Google Sheets / Excel

Build the P&L (revenue from MRR Waterfall, COGS, gross profit, OpEx by department, EBITDA, net income), Cash Flow (operating/investing/financing with runway calculation), and Balance Sheet (assets, liabilities, equity with balance check cell that must equal $0). All three statements must be fully integrated. [src5]

Integration linkages:
  P&L Net Income → Cash Flow starting point
  Cash Flow Ending Cash → Balance Sheet Cash line
  Balance Sheet Retained Earnings = Prior RE + Net Income
  BALANCE CHECK = Total Assets - (Total Liabilities + Total Equity) → must be $0

Verify: Balance check cell shows exactly $0. · If failed: Trace: (1) Net Income matches between P&L and Cash Flow, (2) Ending Cash matches between Cash Flow and Balance Sheet, (3) Retained Earnings = prior RE + current Net Income.

Step 5: Build Cap Table with SAFE/Note Modeling

Duration: 30-45 minutes · Tool: Google Sheets / Excel

Model founding equity, SAFE instruments (pre-money and post-money), convertible notes with interest, and priced round conversion. Include an exit waterfall showing per-stakeholder returns at various exit valuations. [src7]

SAFE conversion: shares = Investment / MIN(cap_price, round_price × (1 - discount))
Post-round ownership must sum to exactly 100%

Verify: Post-round ownership sums to 100%. · If failed: Check whether post-money SAFEs are double-counted. Post-money SAFEs include themselves in the cap.

Step 6: Add Scenario Toggles and Dashboard

Duration: 30-45 minutes · Tool: Google Sheets / Excel

Wire the scenario toggle dropdown so all assumption cells update via INDEX/MATCH. Build a dashboard tab with 6 charts (ARR trajectory, MRR waterfall bar, unit economics trend, cash runway, headcount, gross margin) and a key metrics summary box comparing all three scenarios. [src8]

Scenario formula pattern:
  =INDEX({base_val, bull_val, bear_val}, MATCH($B$1, {"Base","Bull","Bear"}, 0))

Base: Median outcomes — historical growth, median churn
Bull: Top-quartile — 50% faster growth, 30% lower churn
Bear: Bottom-quartile — 50% slower growth, 50% higher churn

Verify: Toggle dropdown from Base to Bull to Bear. All charts and metrics update automatically. No #REF! or #VALUE! errors. · If failed: Check that INDEX/MATCH formulas use absolute reference to $B$1.

Output Schema

{
  "output_type": "saas_financial_model",
  "format": "XLSX or Google Sheets",
  "tabs": [
    {"name": "Assumptions", "description": "All editable inputs with scenario toggle", "required": true},
    {"name": "MRR Waterfall", "description": "Monthly MRR movements: new, expansion, contraction, churn, reactivation", "required": true},
    {"name": "Cohort Analysis", "description": "Logo and revenue retention by acquisition cohort", "required": true},
    {"name": "P&L", "description": "Income statement — monthly Year 1-2, quarterly Year 3", "required": true},
    {"name": "Cash Flow", "description": "Operating, investing, financing cash flows with runway calc", "required": true},
    {"name": "Balance Sheet", "description": "Assets, liabilities, equity with balance check", "required": true},
    {"name": "Cap Table", "description": "Ownership, SAFE conversion, exit waterfall", "required": false}
  ],
  "time_horizon": "36 months",
  "scenario_count": 3
}

Quality Benchmarks

If below minimum: Re-check all inter-tab cell references. Use Ctrl+` (grave accent) to show all formulas and trace dependencies. The most common failure is a broken link between P&L net income and the cash flow statement.

Error Handling

Cost Breakdown

Anti-Patterns

Wrong: Single-line MRR forecast without waterfall decomposition

A model showing only total MRR tells investors nothing about business health. It masks whether growth comes from new customer acquisition (expensive) or expansion revenue (efficient). Every investor will ask for the waterfall breakdown. [src2]

Correct: Always decompose MRR into five components

New, Expansion, Contraction, Churn, and Reactivation must be separate line items. This reveals Net Revenue Retention — the single most important SaaS metric for investors. NRR above 100% means the business grows even without new customers. [src4]

Wrong: Blended averages instead of cohort-level retention

Blended monthly churn of 3% looks acceptable. But cohort analysis might reveal Month 1 churn is 15% while Month 6+ churn is 1% — meaning onboarding is broken but retained customers are happy. Blended metrics hide the actionable insight. [src3]

Correct: Build cohort retention tables from Day 1

Even with assumed data pre-revenue, model retention at the cohort level. When real data arrives, replace assumptions cohort-by-cohort. This structure reveals retention patterns that blended averages obscure.

Wrong: Hardcoding assumptions in formula cells

Scattering constants across 200 formula cells makes the model impossible to audit and scenario analysis impossible.

Correct: Single Assumptions tab with all inputs in labeled, colored cells

Every variable lives in one place. Blue cells = editable inputs. Black cells = computed. This is the standard investors expect. [src1]

When This Matters

Use this recipe when a founder or finance lead needs to build an actual working SaaS financial model — not read about SaaS metrics, but construct a spreadsheet they can populate with real data, present to investors, and use for monthly planning. The output is a 7-tab integrated workbook that serves as the single source of truth for revenue projections, cash management, and fundraising preparation.