# Scenario Planning

A driver-based scenario model: three parallel 12-month P&Ls - Base, Upside, Downside - share a common month-1 starting point and compound revenue, opex, capex, customers, and tax at scenario-specific rates, with a Comparison sheet that condenses every line to a YTD delta vs Base.

- Canonical: https://finamodel.com/templates/scenario-planning
- Excel download: https://finamodel.com/templates/scenario-planning.xlsx
- Category: Corporate Finance
- Model type: Operating model
- Difficulty: Beginner
- Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Founders, Operators
- Tags: scenario planning, fp&a, p&l, sensitivity, drivers

## Overview

A scenario planning model takes a single operating starting point and projects three parallel 12-month P&Ls under Base, Upside, and Downside driver settings, then rolls each line to a YTD view side-by-side with deltas vs Base in dollars and percent. The workbook is built around an Assumptions sheet that holds every driver as a named range, three P&L sheets in identical row layouts so the comparison reads cleanly across scenarios, and a Comparison sheet that condenses everything into one decision-ready page.

Each scenario shares the same month-1 revenue, opex, capex, and customer base. Divergence comes only from growth and intensity rates: revenue monthly growth, COGS as percent of revenue, opex monthly growth, scenario tax rate, capex monthly growth, and customer monthly growth. The P&L flows from Revenue → COGS → Gross profit → Operating expense → EBITDA → Cash tax → After-tax EBITDA → CapEx → Free cash flow. Cash tax is floored at zero with MAX(0, EBITDA) × tax rate so a loss-making Downside month does not generate a phantom refund. Active customers and ARPU sit below the FCF block for unit-economics commentary.

The Comparison sheet sums each P&L row across the 12-month horizon to produce YTD Revenue, Gross profit, Operating expense, EBITDA, Cash tax, CapEx, and Free cash flow per scenario, with Upside vs Base and Downside vs Base deltas in dollars and percent (divide-by-zero-guarded). A margins block computes YTD gross margin, EBITDA margin, and FCF margin per scenario plus the percentage-point deltas, and a customer block reads ending customers and ending ARPU from period 12. CFOs, FP&A teams, founders, and operators use the template for annual planning, board pre-reads, and covenant stress testing - anywhere the question is "what happens to EBITDA and free cash flow if our assumptions move".

## What's included

- Assumptions sheet with common month-1 bases plus per-scenario growth and intensity rates
- Base_PL, Upside_PL, Downside_PL: three parallel 12-month P&Ls in identical row layouts
- Revenue → COGS → Gross profit → Opex → EBITDA → Cash tax → After-tax EBITDA → CapEx → Free cash flow per scenario
- Active customer count and ARPU per scenario for unit-economics commentary
- Comparison sheet with YTD totals and deltas (Upside vs Base, Downside vs Base) in dollars and percent
- Margin block (Gross, EBITDA, FCF) and period-12 customer metrics on the Comparison sheet
- Assumptions sheet with common month-1 bases plus per-scenario rates for revenue, COGS, opex, tax, capex, and customers

## Scenario Planning: How the Base, Upside and Downside Cases Are Built

This scenario planning template is a driver-based financial model that runs three parallel twelve-month P&Ls — Base, Upside and Downside — from a single month-one anchor. Each scenario then compounds revenue, operating costs, capital expenditure, customers and tax at its own growth rates, producing a comparison of year-to-date deltas against Base.

### Shared starting point and scenario-specific growth rates

Every scenario collapses to identical month-one figures, using the same revenue, customer, COGS percentage, capital expenditure and total operating expense inputs.

- This shared anchor means differences between scenarios come only from growth rates applied in subsequent months, not from a different starting position.

- Revenue can be modelled either as price times volume, with average revenue per user and customer growth compounding separately, or as a blended monthly growth rate.

- Operating expenses split across salaries, marketing, general and administrative, and research and development lines, each compounding at scenario-specific rates.

### From revenue to free cash flow

EBITDA is revenue less COGS and total operating expenses. Interest is added from a single debt tranche, and tax is calculated with a net operating loss carryforward, meaning tax is floored at zero and prior losses can offset taxable income subject to a cap.

- Working capital uses days sales outstanding, days payable outstanding and days inventory outstanding to derive receivables, payables and inventory, with the change in net working capital subtracted from free cash flow. Free cash flow equals net income less capital expenditure and the change in net working capital.

- Cash then rolls forward by adding free cash flow and subtracting debt principal, and debt amortises by a constant monthly principal payment.

### Live scenario selector and comparison outputs

A dropdown on the assumptions sheet selects the active scenario, and the live P&L uses that selection to pull the matching driver values without rewiring formulas. The static Base, Upside and Downside P&Ls remain as separate reference sheets.

- The comparison sheet gathers year-to-date totals for each scenario, calculates an expected case as a probability-weighted blend, and shows upside and downside deltas versus Base in both currency and percentage terms. It also reports gross, EBITDA and free cash flow margins, plus ending customers and ending cash.

- A checks sheet validates that month-one anchors match across scenarios, probability weights sum to one hundred percent, and cash and debt floors are respected.

### Sensitivity analysis and practical use

A sensitivity sheet runs a one-way tornado across ten drivers, flexing each by a default twenty percent to estimate the impact on full-year free cash flow, with the top three drivers surfaced on the cover. Two-way grids show how revenue responds to average revenue per user growth against customer growth, and how EBITDA margin responds to revenue growth against COGS percentage.

- The headcount sheet tracks opening full-time equivalents through hires and attrition to ending full-time equivalents, and provides a salary cost benchmark alongside the P&L salary line. The model includes a debt service coverage ratio check and a cash runway calculation.

- This template suits users evaluating how different growth assumptions affect cash generation, profitability and covenant compliance over a twelve-month horizon.

## Built for annual planning

When the budget review needs a stretch case and a stress case alongside the plan, this template lays all three on identical sheets so the comparison reads as one decision rather than three forecasts.

## Designed for shared anchoring

Every scenario starts from the same month-1 revenue, opex, capex, and customer base - divergence comes only from growth and intensity rates, so the deltas trace to a small number of policy levers.

## Audit-friendly mechanics

Every input is a named range, cash tax is floored at zero, the comparison percent-delta formulas guard against divide-by-zero, and the workbook passes static-value, self-reference, and dead-assumption scans.

## Built for annual planning

When the budget review needs a stretch case and a stress case alongside the plan, this template lays all three on identical sheets so the comparison reads as one decision rather than three forecasts.

## Designed for shared anchoring

Every scenario starts from the same month-1 revenue, opex, capex, and customer base - divergence comes only from growth and intensity rates, so the deltas trace to a small number of policy levers.

## Audit-friendly mechanics

Every input is a named range, cash tax is floored at zero, the comparison percent-delta formulas guard against divide-by-zero, and the workbook passes static-value, self-reference, and dead-assumption scans.

## Workbook structure

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: month-1 bases plus per-scenario rates for revenue, COGS, opex, tax, capex, and customers.

- Revenue M1 plus Base / Upside / Downside monthly growth
- COGS % of revenue per scenario
- Opex M1 plus per-scenario monthly growth
- Tax rate per scenario
- CapEx M1 plus per-scenario monthly growth
- Active customers M1 plus per-scenario monthly growth

### Base_PL

12-month P&L compounding the Base scenario drivers.

- Revenue, COGS, Gross profit
- Operating expense, EBITDA
- Cash tax (floored at zero), After-tax EBITDA
- CapEx, Free cash flow
- Active customers and ARPU

### Upside_PL

12-month P&L compounding the Upside scenario drivers - same row layout as Base.

- Higher revenue growth, lower COGS %, sharper customer growth
- Same P&L structure for one-to-one comparison
- CapEx growth typically lifted to support the growth case

### Downside_PL

12-month P&L compounding the Downside scenario drivers - same row layout as Base.

- Slower revenue growth, higher COGS %, slower customer growth
- Same P&L structure for one-to-one comparison
- Cash tax floor at zero prevents phantom refunds on EBITDA losses

### Comparison

YTD totals side-by-side with deltas vs Base in dollars and percent, plus margin and customer metrics.

- YTD Revenue, Gross profit, Opex, EBITDA, Cash tax, CapEx, FCF per scenario
- Upside vs Base and Downside vs Base in dollars and percent (DIV/0-guarded)
- YTD gross margin, EBITDA margin, FCF margin per scenario
- Period-12 ending customers and ending ARPU per scenario

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: month-1 bases plus per-scenario rates for revenue, COGS, opex, tax, capex, and customers.

- Revenue M1 plus Base / Upside / Downside monthly growth
- COGS % of revenue per scenario
- Opex M1 plus per-scenario monthly growth
- Tax rate per scenario
- CapEx M1 plus per-scenario monthly growth
- Active customers M1 plus per-scenario monthly growth

### Base_PL

12-month P&L compounding the Base scenario drivers.

- Revenue, COGS, Gross profit
- Operating expense, EBITDA
- Cash tax (floored at zero), After-tax EBITDA
- CapEx, Free cash flow
- Active customers and ARPU

### Upside_PL

12-month P&L compounding the Upside scenario drivers - same row layout as Base.

- Higher revenue growth, lower COGS %, sharper customer growth
- Same P&L structure for one-to-one comparison
- CapEx growth typically lifted to support the growth case

### Downside_PL

12-month P&L compounding the Downside scenario drivers - same row layout as Base.

- Slower revenue growth, higher COGS %, slower customer growth
- Same P&L structure for one-to-one comparison
- Cash tax floor at zero prevents phantom refunds on EBITDA losses

### Comparison

YTD totals side-by-side with deltas vs Base in dollars and percent, plus margin and customer metrics.

- YTD Revenue, Gross profit, Opex, EBITDA, Cash tax, CapEx, FCF per scenario
- Upside vs Base and Downside vs Base in dollars and percent (DIV/0-guarded)
- YTD gross margin, EBITDA margin, FCF margin per scenario
- Period-12 ending customers and ending ARPU per scenario

## Features

- **Common starting point, scenario divergence:** Every scenario anchors to the same month-1 revenue, opex, capex, and customer base. Scenarios diverge only through growth and intensity rates, so the side-by-side comparison reads as a clean what-if rather than three unrelated forecasts.
- **Side-by-side P&L:** Three P&L sheets with identical row layouts let any reader pivot from a Comparison delta straight to the underlying scenario sheet without learning a new row order.
- **Cash-tax floor:** Cash tax is computed as MAX(0, EBITDA) × tax rate, so a loss-making Downside month does not generate a phantom refund that distorts the FCF picture.

## Use cases

- **Annual planning:** Bring a Base plan plus a stretch Upside and a stress Downside to the budget review, with every line tracing back to a single driver edit on the Assumptions sheet.
- **Board pre-read:** Hand the Comparison sheet to the board ahead of the planning meeting - YTD revenue, EBITDA, and FCF deltas vs Base on one page tells the story without forcing them through 36 months of detail.
- **Covenant stress testing:** Use the Downside scenario to pressure-test EBITDA against debt covenants - lift COGS %, slow revenue growth, and watch the FCF margin and after-tax EBITDA reset in real time.

## Frequently asked questions

### What is a scenario planning model?

A scenario planning model projects a business under multiple driver settings - typically Base, Upside, and Downside - and compares the resulting P&L and cash flow side-by-side. It is the standard FP&A artefact for annual planning reviews, board pre-reads, and covenant stress testing.

### Why do all three scenarios share month-1 values?

A shared month-1 anchor makes the divergence trace to a small number of policy levers (growth rates, COGS %, tax) rather than to disagreement about today. It also keeps the side-by-side comparison readable; the eye can compare slopes rather than re-baselined starting points.

### How is cash tax modelled?

Cash tax = MAX(0, EBITDA) × scenario tax rate. The floor at zero prevents a loss-making Downside month from generating a refund that distorts free cash flow. For NOL carry-forwards or deferred tax assets, extend with a roll-forward sheet.

### Can I add a fourth scenario or change the horizon?

Yes. The builder is parameterised by NUM_PERIODS and a SCENARIOS list. Add a new (sheet, tab colour, suffix, label) tuple, register matching named ranges on the Assumptions sheet, and the P&L and Comparison sheets pick up the new scenario automatically.

### What growth-rate deltas should I use between scenarios?

Typical spreads: Upside revenue growth roughly 2× Base monthly rate, Downside at 25–40% of Base. Opex growth often moves only modestly across scenarios (operators flex capacity, not headcount). COGS % usually widens 4–6 percentage points between Upside and Downside for a goods or product business.

## Related templates

- [Budget vs Actuals Tracker](https://finamodel.com/templates/budget-vs-actuals)
- [3 Statement Model](https://finamodel.com/templates/3-statement-model)
- [Board Reporting Pack](https://finamodel.com/templates/board-reporting)