Mattakushi432 Financial Modeling
Model Architecture
Tab Structure (Standard)
1. Assumptions ← All inputs in one place
2. Revenue ← Top-line build
3. COGS ← Direct costs
4. OpEx ← Operating expenses by department
5. P&L ← Summary income statement
6. Cash Flow ← Cash movements
7. Balance Sheet ← Assets / liabilities
8. KPIs ← Dashboard metrics
9. Scenarios ← Base / bull / bear
10. Sensitivity ← Two-variable tables
Rule: Never hardcode numbers in formula cells. Every number must trace to Assumptions tab.
P&L Structure
Revenue
- New ARR (new logos × ACV)
- Expansion ARR (upsell + cross-sell)
- Churned ARR (logo churn × ACV)
= Net New ARR
+ Beginning ARR
= Ending ARR
÷ 12
= MRR
COGS
- Hosting / infrastructure
- Customer success salaries (if included)
- Professional services
= Gross Profit
Gross Margin % = Gross Profit / Revenue
Operating Expenses
- Sales & Marketing (S&M)
- Research & Development (R&D)
- General & Administrative (G&A)
= Total OpEx
EBITDA = Gross Profit - OpEx
EBITDA Margin % = EBITDA / Revenue
SaaS Metrics
ARR / MRR
ARR = MRR × 12
MRR = sum of all active subscription monthly values
Net Revenue Retention (NRR) =
(Beginning MRR + Expansion - Contraction - Churn) / Beginning MRR
Gross Revenue Retention (GRR) =
(Beginning MRR - Contraction - Churn) / Beginning MRR
Target benchmarks:
NRR > 120% = Excellent (enterprise)
NRR > 110% = Good (mid-market)
NRR > 100% = Acceptable (SMB)
GRR > 90% = Healthy
Churn
Logo Churn Rate = Churned Customers / Beginning Customers
Revenue Churn Rate = Churned MRR / Beginning MRR
Monthly Churn → Annual Churn: 1 - (1 - monthly_churn)^12
Acceptable ranges:
Enterprise SaaS: < 5% annual logo churn
Mid-market: < 10% annual logo churn
SMB: < 20% annual logo churn
Unit Economics
CAC (Customer Acquisition Cost)
Blended CAC = (S&M spend in period) / (New customers acquired in period)
Fully-loaded CAC includes:
- Sales salaries + commissions + benefits
- Marketing spend (paid, events, content)
- Marketing tools and tech stack
- Sales tools (CRM, enrichment, sequencing)
- Allocated overhead (rent, IT)
Payback Period (months) = CAC / (ACV/12 × Gross Margin %)
Target: < 18 months (SaaS), < 12 months (high-velocity)
LTV (Lifetime Value)
LTV = ACV × Gross Margin % / Annual Churn Rate
Example:
ACV = $24,000
Gross Margin = 75%
Annual Churn = 10%
LTV = $24,000 × 0.75 / 0.10 = $180,000
LTV:CAC Ratio:
> 3x = Healthy
> 5x = Strong
< 2x = Unsustainable — fix CAC or churn first
Cash Flow Projection
Direct Method (13-Week Rolling)
Week | Beginning Cash | Cash In | Cash Out | Ending Cash
-----|----------------|---------|----------|------------
1 | $500,000 | $45,000 | $80,000 | $465,000
2 | $465,000 | $20,000 | $75,000 | $410,000
...
Cash In:
+ Customer collections (invoiced - DSO adjustment)
+ New bookings (when paid, not when signed)
+ Financing proceeds
Cash Out:
- Payroll (bi-weekly)
- Vendor payments (net-30 cycle)
- Rent / utilities
- One-time expenses
Runway Calculation
Monthly Burn = Total Cash Out (operating, not financing)
Net Burn = Monthly Burn - Monthly Revenue Collected
Runway = Cash Balance / Net Burn Rate
Include in burn: all operating expenses + capex
Exclude from burn: loan repayments (those reduce cash but aren't burn)
Scenario Analysis
Three-Scenario Template
Assumption | Bear | Base | Bull
--------------------|-------|-------|------
New logos/month | 5 | 10 | 18
ACV ($) | 18K | 24K | 30K
Gross margin | 68% | 75% | 78%
Annual churn | 15% | 10% | 6%
S&M as % revenue | 55% | 40% | 32%
Methodology: Hold one variable fixed, flex others proportionally. Name scenarios after a business assumption, not just "optimistic" — e.g., "Enterprise Pivot" vs "SMB Focus".
Sensitivity Table (Two-Variable)
Annual Churn Rate
ACV 5% 10% 15% 20% 25%
$15,000 $225K $112K $75K $56K $45K ← LTV
$24,000 $360K $180K $120K $90K $72K
$36,000 $540K $270K $180K $135K $108K
$48,000 $720K $360K $240K $180K $144K
Build in Excel/Sheets using DATA TABLE (two-variable) or Google Sheets TABLE.
Key Benchmarks (SaaS 2024)
| Metric | Seed | Series A | Series B | Public |
|---|---|---|---|---|
| ARR Growth | > 2x | > 2x | > 80% | > 30% |
| Gross Margin | > 60% | > 65% | > 70% | > 75% |
| Magic Number | > 0.5 | > 0.75 | > 1.0 | > 1.0 |
| Burn Multiple | < 3x | < 2x | < 1.5x | < 1x |
| NRR | > 100% | > 110% | > 115% | > 120% |
Magic Number = Net New ARR / Prior Quarter S&M Spend Burn Multiple = Net Burn / Net New ARR
Model Checklist
- All inputs in dedicated Assumptions tab with units labeled
- No hardcoded numbers in formula cells
- Revenue tied to cohort model (not top-down only)
- Cash flow ties to P&L (net income + non-cash ± working capital)
- Three scenarios modeled (base, bull, bear)
- Sensitivity table on two key drivers
- Monthly for Year 1-2, quarterly for Year 3-5
- Actuals vs forecast variance tracking built in
- Circular reference check (audit → trace precedents)
- Assumptions sourced and dated in comments