Building Scenario and Sensitivity Analysis in Excel Models

# Scenario & Sensitivity Analysis Build Guide — SaaS DCF

## Step-by-Step Walkthrough

**1. Set Up the Scenario Input Block**
Create a dedicated "Assumptions" sheet. In rows 5–7, list the three drivers; in columns D–F, list Base/Bull/Bear values:

| Driver (C) | Base (D) | Bull (E) | Bear (F) |
|---|---|---|---|
| Revenue Growth | 25% | 35% | 15% |
| Gross Margin | 75% | 80% | 68% |
| Churn | 8% | 5% | 12% |

**2. Create the Scenario Selector Dropdown**
In cell `C2`, add Data Validation → List → Source: `Base,Bull,Bear`. This is your master toggle.

**3. Convert Selection to an Index**
In `C3`, translate the text into a column number:
```excel
=MATCH(C2,{"Base","Bull","Bear"},0)
```

**4. Pull Active Scenario Values with CHOOSE or INDEX**
In the "Live Assumptions" column `G5:G7`, reference the selected case:
```excel
=CHOOSE($C$3,D5,E5,F5)
```
Or equivalently:
```excel
=INDEX(D5:F5,$C$3)
```
Use `G5:G7` as the *only* inputs feeding your DCF — never hardcode.

**5. Link the DCF to Live Assumptions**
On your DCF sheet, reference `Assumptions!$G$5` for growth, `$G$6` for gross margin, `$G$7` for churn. Your Enterprise Value cell (e.g., `DCF!$B$50`) now updates dynamically when the dropdown changes.

**6. Build a Scenario Summary Output Table**
To view all three cases side-by-side without toggling, use a helper table that temporarily substitutes each scenario. Simplest method — use `CHOOSE` with scenario-specific EV calcs, or leverage **Scenario Manager** (Data → What-If Analysis → Scenario Manager → Add each case referencing `G5:G7`, then Summary).

**7. Construct the Two-Variable Sensitivity Table (WACC × Terminal Growth)**
On a "Sensitivity" sheet:
- Place WACC values across the top row (e.g., `C10:I10`: 8%–12%)
- Place terminal growth down the left column (`B11:B17`: 1%–4%)
- In the **top-left corner cell `B10`**, link to the EV output:
```excel
=DCF!$B$50
```
- Select the full range `B10:I17` → Data → What-If Analysis → **Data Table**
  - Row input cell: your WACC driver cell (e.g., `DCF!$B$48`)
  - Column input cell: your terminal growth cell (e.g., `DCF!$B$49`)

**8. Add Conditional Formatting to the Output**
Highlight the sensitivity grid with a 3-color scale (red→yellow→green) to visualize EV ranges quickly.

---

## Best Practices

- **Color coding:** Blue font = hardcoded inputs, Black = formulas, Green = cross-sheet links. Yellow fill for the scenario dropdown cell.
- **Naming conventions:** Use Named Ranges (`scn_Growth`, `scn_Margin`, `wacc`, `g_terminal`) via Formulas → Name Manager for readable formulas.
- **Audit checks:** Add a check row: `=SUM(G5:G7)-CHOOSE(C3,SUM(D5:D7),SUM(E5:E7),SUM(F5:F7))` should equal zero.
- **Isolate inputs:** All toggles live on one sheet; DCF sheet should contain zero hardcoded assumptions.
- **Lock structure:** Protect the sensitivity table (F9 recalcs only) and document the row/column input cells in a comment.
- **Sanity test:** Bull EV > Base EV > Bear EV — if not, trace your CHOOSE/INDEX wiring.

# Excel Scenario Analysis Guide for SaaS DCF Model

## Step 1: Structure Your Assumption Inputs

Create a dedicated **Assumptions** sheet with three columns per variable:

| Metric | Base | Bull | Bear |
|--------|------|------|------|
| Revenue Growth Y1 | 25% | 40% | 15% |
| Gross Margin | 75% | 78% | 72% |
| Churn Rate | 5% | 3% | 8% |
| WACC | 10% | 8% | 12% |
| Terminal Growth | 3% | 4% | 2% |

Place these in cells A1:D6.

## Step 2: Create a Scenario Selector

In cell **B8**, add a Data Validation dropdown:
- Go to Data > Validation
- Allow: List
- Source: `Base,Bull,Bear`

In cell **B9**, create a formula to convert text to column position:
```excel
=MATCH(B8,{"Base","Bull","Bear"},0)
```

## Step 3: Build Dynamic Assumption References

In your **Model** sheet, reference assumptions dynamically using CHOOSE:

```excel
=CHOOSE($Assumptions.$B$9, $Assumptions.$B$2, $Assumptions.$C$2, $Assumptions.$D$2)
```

This formula references the scenario selector and pulls the corresponding assumption value. Copy this pattern for each input variable (revenue growth, margins, churn).

**Alternatively, use INDEX/MATCH** for more flexibility:
```excel
=INDEX($Assumptions.$B$2:$D$2, MATCH($Assumptions.$B$8, {"Base","Bull","Bear"}, 0))
```

## Step 4: Build Your DCF Model

Create a standard 5-year projection:
- **Row 15**: Year 1–5 revenue using the dynamic growth rate formula
- **Row 20**: Operating expenses (scaled from revenue)
- **Row 25**: Free cash flow calculations
- **Row 30**: NPV calculation using the dynamic WACC assumption

Example revenue formula in C15:
```excel
=C14*(1 + INDEX($Assumptions.$B$2:$D$2, CHOOSE($Assumptions.$B$9, 1, 1, 1)))
```

## Step 5: Build the Sensitivity Table (Two Variables)

Create a table showing Enterprise Value across WACC (rows) and Terminal Growth (columns).

**Row headers** (WACC): 6%, 8%, 10%, 12%, 14% (cells A45:A50)
**Column headers** (Terminal Growth): 1%, 2%, 3%, 4%, 5% (cells B44:F44)

In cell **B45**, use INDIRECT to dynamically reference assumptions:
```excel
=IFERROR(NPV(INDEX($Assumptions.$B$5:$D$5, $Assumptions.$B$9), 
  $Model.$C$25:$F$25) / (1 + WACC)^0 + TV / (1 + WACC)^5, "")
```

For a true two-way table, use **Data > What-If Analysis > Data Table**:
1. Set up formula in top-left cell referencing WACC and Terminal Growth
2. Select row/column input cells
3. Excel auto-fills combinations

## Step 6: Summary Output

Create a **Results** sheet displaying:
- **Enterprise Value** (links to NPV calculation)
- **Key metrics by scenario** (Revenue Y5, EBITDA margin, FCF)

```excel
=INDEX($Model.$C$35:$E$35, MATCH($Assumptions.$B$8, {"Base","Bull","Bear"}, 0))
```

## Best Practices

| Element | Action |
|---------|--------|
| **Naming** | Name ranges: `Scenario_Selector`, `Base_Assumptions`, `Bull_Assumptions` |
| **Color Coding** | Blue = Base, Green = Bull, Red = Bear assumptions |
| **Audit Trail** | Add formula auditing with Trace Precedents (Formulas > Trace) |
| **Freeze** | Freeze assumption rows so they're always visible while scrolling |
| **Error Handling** | Wrap formulas in IFERROR to catch circular references |

---

This setup allows instant scenario switching via dropdown while maintaining a clean, auditable model structure.

Here is a step-by-step guide to building scenario and sensitivity analysis for your DCF model in Excel.

### Building the Scenario Analysis

#### Step 1: Structure Scenario Assumptions
On a dedicated "Inputs" or "Assumptions" sheet, create a table to house your variables. This structure separates your scenarios, making them easy to manage and audit.

*   In cell `B1`, create a label: "Scenario Selector".
*   In cells `B4:E4`, enter the headers: `Driver`, `Base`, `Bull`, `Bear`.
*   In cells `B5:B7`, list your drivers: `Revenue Growth Rate`, `Gross Margin`, `Churn`.
*   Populate the values for each case in cells `C5:E7`.

| | B | C | D | E |
| :--- | :--- | :--- | :--- | :--- |
| **1** | **Scenario Selector** | **Base** | | |
| **2** | **Scenario Index** | 1 | | |
| **3** | | | | |
| **4** | **Driver** | **Base** | **Bull** | **Bear** |
| **5** | Revenue Growth Rate | 20.0% | 30.0% | 10.0% |
| **6** | Gross Margin | 75.0% | 80.0% | 70.0% |
| **7** | Churn | 5.0% | 3.0% | 8.0% |

#### Step 2: Create a Dropdown Selector
The dropdown allows you to toggle between scenarios.

1.  Select cell `C1`. Go to **Data > Data Validation**.
2.  Under "Allow:", choose **List**.
3.  In the "Source:" box, type: `Base,Bull,Bear` and click OK.
4.  In cell `C2`, use the `MATCH` function to convert the text selection into a number (1, 2, or 3). This number will drive the `CHOOSE` function.
    ```excel

1. **Create Scenario Selector**: In cell `B2`, use Data Validation (Data > Data Validation > List) with source `"Base,Bull,Bear"`. Label A2 as "Scenario".

2. **Build Assumptions Table**: In `A4:D7`:
   ```
   A4: Variable     B4: Base    C4: Bull    D4: Bear
   A5: Rev Growth   B5: 20%     C5: 30%     D5: 10%
   A6: Gross Margin B6: 75%     C6: 80%     D6: 70%
   A7: Churn        B7: 5%      C7: 3%      D7: 8%
   ```
   (Extend rows for Yr1-5 if needed; use arrays like `{20%,25%,...}`.)

3. **Link Model Inputs**: In input cells (e.g., `F5` for Rev Growth):
   ```
   =INDEX($B$5:$D$5, MATCH($B$2, $B$4:$D$4, 0))
   ```
   Copy to `F6` (Gross Margin), `F7` (Churn). Reference `F5:F7` in DCF revenue/churn formulas (e.g., Rev_t = Rev_{t-1} * (1 + F5)).

4. **Calculate Enterprise Value (EV)**: In `F10`, build DCF formula referencing inputs (e.g., NPV + Terminal Value). EV updates on scenario toggle.

5. **One-Way Scenarios (Optional CHOOSE Alt)**: For numeric toggle (1=Base,2=Bull,3=Bear), use:
   ```
   =CHOOSE($B$2, B5, C5, D5)  // In F5
   ```

6. **Two-Variable Sensitivity Table**: In `H15:M25`:
   - `H15`: `=F10` (EV link).
   - Row 15 (`I15:M15`): WACC variants (8%,9%,...,12%).
   - Col H (`H16:H25`): Terminal growth (1%,1.5%,...,5%).
   - Select `H15:M25` > Data > What-If Analysis > Data Table.
   - Row input: WACC cell (e.g., `F8`); Col input: Term Growth cell (e.g., `F9`).

**Best Practices** (45 words):  
- Name ranges (e.g., `RevGrowth= F5`); blue inputs, black calcs, green links.  
- Color-code scenarios (Base=gray, Bull=green, Bear=red).  
- Audit: Trace Precedents, F9 recalc; protect sheets; version control with Scenario Manager summary.  

(298 words)