SaaS three statement model with Metrics and scenario analysis

The model contains seven worksheets that flow logically from assumptions through to a visual summary. All sheets are fully integrated: changes in the Assumptions tab cascade through the Revenue Model, P&L, Cash Flow, and Balance Sheet automatically, with the Dashboard reflecting the updated outputs in real time.

1. Instructions

A complete user guide with the color-coding legend, a description of each worksheet, and key SaaS definitions including MRR, ARR, NRR, ARPU, Rule of 40, CAC, LTV, and churn. This sheet ensures you can start customizing the model immediately.

2. Assumptions & Inputs

All editable inputs are consolidated on this sheet, organized into clearly labeled sections. Key assumptions include:

Scenario selector and multipliers: A single toggle switches between Base, Bull, and Bear scenarios. Each scenario applies multipliers to revenue, customer acquisition, churn, and operating expenses, allowing you to stress-test the entire model instantly.
Pricing tiers: Monthly subscription prices for three tiers (Starter, Pro, and Enterprise), giving you flexibility to model different product configurations and price points.
Customer acquisition and growth: Year 1 new customer counts by tier and an annual growth rate that compounds across the projection period.
Churn rates: Monthly churn rates by tier, automatically annualized in the Revenue Model. Different churn assumptions for each tier reflect the reality that enterprise customers retain at higher rates than self-serve users.
Expansion revenue: A percentage of prior-year revenue captured as upsell and expansion, modeling the net revenue retention dynamic that drives efficient SaaS growth.
COGS breakdown: Hosting and infrastructure, customer support, and payment processing as percentages of revenue.
Operating expenses: Year 1 values for Sales & Marketing, Research & Development, and General & Administrative, with an annual growth rate.
Working capital: Days Sales Outstanding, Days Payable Outstanding, deferred revenue months, and prepaid expense months to drive the Balance Sheet and Cash Flow working capital adjustments.
Capital expenditures and depreciation: Annual CapEx with a growth rate, useful life assumption, and a vintage depreciation schedule that tracks each year’s investment separately.
Debt and equity: Loan amount, interest rate, and term for the debt schedule, plus initial paid-in capital and beginning cash balance to initialize the Balance Sheet.

3. Revenue Model

The Revenue Model tracks a full customer rollforward for each pricing tier across the five-year projection: beginning customers, new customers acquired, churned customers lost, and ending customers. Subscription revenue is calculated using average customers multiplied by monthly price and annualized. Expansion revenue layers on top to capture upsell dynamics. The sheet also computes key SaaS metrics including Ending MRR, ARR, Net Revenue Retention, Logo Retention Rate, and Blended Monthly ARPU.

4. Profit & Loss Statement

A full income statement flowing from Revenue through Cost of Goods Sold (hosting, support, payment processing) to Gross Profit, then through Operating Expenses (Sales & Marketing, R&D, G&A) to EBITDA, and finally through Depreciation & Amortization, EBIT, Interest Expense, and Income Tax to arrive at Net Income. Margins are calculated at every level for each year of the projection, giving investors and operators a clear picture of how the business evolves from early-stage losses to profitability at scale.

5. Cash Flow Statement

The Cash Flow Statement is divided into three sections: Operating Activities (starting with Net Income and adjusting for depreciation and working capital changes in accounts receivable, prepaid expenses, accounts payable, and deferred revenue), Investing Activities (capital expenditures), and Financing Activities (debt repayment). The net change in cash flows through to beginning and ending cash balances, allowing you to track cash runway and identify potential funding gaps.

6. Balance Sheet

A fully integrated Balance Sheet covering six periods (Beginning plus Years 1 through 5). The sheet includes:

Assets: Cash (linked from the Cash Flow Statement), Accounts Receivable, Prepaid Expenses, and PP&E Net (Gross PP&E less Accumulated Depreciation from the vintage schedule).
Liabilities: Accounts Payable, Deferred Revenue, and Debt Outstanding (linked from the amortization schedule).
Equity: Paid-in Capital and Retained Earnings (accumulated from Net Income on the P&L).
Supporting schedules: A CapEx and Vintage Depreciation schedule that tracks each year’s capital investment and its depreciation separately over the useful life, plus a Debt Amortization schedule with beginning balance, annual repayment, and ending balance.
Balance check: An automatic validation row on every period confirms that Total Assets equals Total Liabilities & Equity, ensuring the three statements remain in balance as you adjust assumptions.

7. Dashboard

A visual summary that pulls the most important outputs from the model into a single view. The Dashboard includes:

Growth metrics: ARR, ARR growth rate, total customers, and Net Revenue Retention.
Profitability: Revenue, Gross Margin, EBITDA, EBITDA Margin, Net Income, and Net Margin across all five years.
SaaS efficiency metrics: Cash Balance, Rule of 40 Score, Customer Acquisition Cost (CAC), Blended Monthly ARPU, Estimated LTV, and LTV:CAC Ratio.
Charts: An ARR bar chart and a Margin Trends line chart (Gross, EBITDA, and Net margins) that update dynamically based on the active scenario.

You must log in to submit a review.