# Lecture 09: Scenario & Sensitivity Analysis — Practice Exercises Guide

**Session:** 9 | **Topic:** Scenario & Sensitivity Analysis  
**CILO:** CILO 2 | **Difficulty:** Intermediate  
**Prerequisites:** Completed Lecture 08 (Three-Statement Integration) with a balanced model

---

## Files Provided

| File | Description |
|------|-------------|
| `lecture-09-sensitivity-data.csv` | Base-case financial data + Bear/Bull assumptions + tornado data |
| `lecture-09-scenario-template.csv` | Blank templates for data tables, scenario output, tornado chart |

---

## Exercise 1: CHOOSE-Based Scenario Switch (Beginner)

**Objective:** Set up a three-case scenario switch using the CHOOSE function

### Instructions

1. Open a new Excel workbook and import the "Assumptions" section from `lecture-09-sensitivity-data.csv`
2. Create a scenario selector cell (e.g., cell C3) with values 1, 2, or 3
3. Replace each hardcoded assumption with a CHOOSE formula:
   ```
   =CHOOSE($C$3, Bear_value, Base_value, Bull_value)
   ```
4. Build a simple income statement using these assumptions (Revenue = $500M base)
5. Verify outputs match the scenario comparison table in Section 3.3

### ✅ Answer Check

| Scenario | Revenue | Gross Margin | EBIT | Net Income |
|----------|---------|-------------|------|------------|
| Bear (1) | $515M | 38% | $88M | ~$55M |
| Base (2) | $540M | 42% | $129M | ~$86M |
| Bull (3) | $575M | 45% | $168M | ~$115M |

---

## Exercise 2: One-Variable Data Table (Beginner)

**Objective:** Build a one-variable sensitivity table for Revenue Growth vs. Net Income

### Instructions

1. Using your model from Exercise 1, identify:
   - Input cell: Revenue Growth rate (e.g., cell C5)
   - Output cell: Net Income
2. In a blank area, list growth rates in a column: −5%, 0%, 5%, 8%, 10%, 15%, 20%
3. Link the top-right corner cell to your Net Income output
4. Select the range and create a one-variable data table:
   - Data → What-If Analysis → Data Table
   - Column input cell = your Revenue Growth cell
5. Apply conditional formatting (color scale) to the results

### ✅ Answer Check (approximate)

| Rev Growth | Net Income |
|-----------|------------|
| −5% | ~$52M |
| 0% | ~$64M |
| 5% | ~$76M |
| 8% | ~$86M |
| 10% | ~$88M |
| 15% | ~$100M |
| 20% | ~$112M |

---

## Exercise 3: Two-Variable Data Table (Intermediate)

**Objective:** Build a two-variable sensitivity table: Revenue Growth × Gross Margin → Net Income

### Instructions

1. Identify two input cells:
   - Column input: Revenue Growth (e.g., cell C5)
   - Row input: Gross Margin (e.g., cell C6)
2. Set up the grid:
   - Left column: Revenue Growth rates (−5% to 20%, step 5%)
   - Top row: Gross Margins (35%, 38%, 42%, 45%, 50%)
3. Link the top-left corner to Net Income
4. Select the entire range → Data Table:
   - Row input cell = Gross Margin cell
   - Column input cell = Revenue Growth cell
5. Apply a Green-Yellow-Red color scale conditional formatting

### ✅ Answer Check

At Revenue Growth = 8%, Gross Margin = 42%: Net Income ≈ $75.8M (base intersection)  
At Revenue Growth = 20%, Gross Margin = 50%: Net Income ≈ $140M (best case cell)

---

## Exercise 4: Goal Seek & Break-Even Analysis (Intermediate)

**Objective:** Use Goal Seek to find break-even and target values

### Questions to Answer

1. **Target NI:** What revenue growth rate is needed for Net Income = $75M?
2. **Break-even:** What revenue growth rate results in Net Income = $0?
3. **Margin sensitivity:** What gross margin is needed for EBIT = $100M (keeping revenue growth at 8%)?

### Instructions

1. Go to Data → What-If Analysis → Goal Seek
2. Set cell = Net Income, To value = 75000000, By changing = Revenue Growth
3. Record the answer, then Cancel to revert
4. Repeat for To value = 0 (break-even)
5. Repeat for EBIT target with Gross Margin as the changing cell

### ✅ Answer Check (approximate)

| Question | Answer |
|----------|--------|
| NI = $75M | Rev Growth ≈ 10.5% |
| NI = $0 (break-even) | Rev Growth ≈ −25% (company is profitable across plausible range) |
| EBIT = $100M | Gross Margin ≈ 46% |

---

## Exercise 5: Tornado Chart & Dashboard (Advanced)

**Objective:** Build a complete tornado chart and sensitivity dashboard

### Instructions

1. Import the "Tornado_Data" from `lecture-09-sensitivity-data.csv`
2. For each driver, calculate the spread:
   ```
   Spread = |NI at High − NI at Low|
   ```
3. Sort drivers by spread (largest first)
4. Create a horizontal bar chart:
   - Insert → Chart → Bar → Stacked Bar
   - First series (invisible): MIN(NI at Low, NI at High) — set fill to "No Fill"
   - Second series: The range from low to high
5. Add labels showing the dollar impact
6. Create a dashboard sheet combining:
   - Scenario selector with conditional formatting
   - Three-case output summary table
   - Two-variable data table with heat map
   - Tornado chart

### ✅ Answer Check — Tornado Spread Calculations

| Driver (sorted) | NI at Low | NI at High | Spread |
|----------------|-----------|------------|--------|
| Revenue Growth | $52.1M | $112.0M | $59.9M |
| Gross Margin | $48.2M | $98.5M | $50.3M |
| CapEx % Rev | $68.5M | $96.8M | $28.3M |
| DSO | $72.8M | $91.5M | $18.7M |
| Interest Rate | $83.2M | $94.8M | $11.6M |
| Tax Rate | $79.5M | $88.6M | $9.1M |

### Key Insight

Revenue growth and gross margin together account for **~73%** of the total sensitivity — these are the two assumptions management should monitor most closely.

---

## Grading Rubric

| Component | Points | Criteria |
|-----------|--------|----------|
| CHOOSE Formulas | 20 | All 7 assumptions correctly use CHOOSE with absolute reference |
| One-Variable Table | 15 | Correct corner link, proper column input cell selection |
| Two-Variable Table | 20 | Correct row/column input cells, results within 5% of answers |
| Goal Seek | 15 | All 3 questions answered correctly |
| Tornado Chart | 15 | Properly sorted, correct spread calculations, professional formatting |
| Dashboard Layout | 15 | Clean layout, conditional formatting, all elements present |
| **Total** | **100** | |

---

## Common Errors to Watch For

1. ** forgetting the `$` in `CHOOSE($C$3,...)` — without absolute reference, the switch won't work when copied
2. **Swapping row/column inputs** in two-variable data tables — remember: ROW input = top row values, COLUMN input = left column values
3. **Not linking the corner cell** in data tables — it must be a formula `=Net_Income`, not a hardcoded value
4. **Using Goal Seek on a hardcoded cell** — the "Set cell" must contain a formula
5. **Tornado chart not sorted** — always sort by spread (descending) for proper tornado shape

---

*Next Session: [Lecture 10 — DCF Valuation I](../lectures/lecture-10-dcf-valuation-1.html)*
<task_progress>
- [x] Explore course outline for Session 9 content
- [x] Create lecture-09.html (main landing page)
- [x] Create section-01-scenario-basics.html
- [x] Create section-02-data-tables.html
- [x] Create section-03-scenario-manager.html
- [x] Create section-04-goal-seek.html
- [x] Create section-05-sensitivity-dashboard.html
- [x] Create practice files for Session 9
- [x] Create practice exercises guide
- [ ] Update index.html with Session 9 link
</task_progress>
</write_to_file>