Objectives

Section Learning Objectives

3.1

The Three-Case Framework

📥 Hands-on: Building Scenario Summaries

Download the provided CSV template to practice building the output summary for Tata Motors dynamically using Data Tables linked to a Scenario Dropdown.

Download CSV Data

📉 Bear Case (1)

Pessimistic but plausible

Rev Growth: 3%
Gross Margin: 38%
CapEx % Rev: 12%
DSO: 65 days
Expected NI: ₹5,500 Cr

📊 Base Case (2)

Most likely estimates

Rev Growth: 8%
Gross Margin: 42%
CapEx % Rev: 10%
DSO: 58 days
Expected NI: ₹8,600 Cr

📈 Bull Case (3)

Optimistic but realistic

Rev Growth: 15%
Gross Margin: 45%
CapEx % Rev: 8%
DSO: 50 days
Expected NI: ₹11,500 Cr
3.2

The "Data Table" Scenario Output Summary

📖What It Does

You've built the CHOOSE toggles to change the model. Now, you want to see all three scenarios (Bear, Base, Bull) side-by-side without having to manually flip the switch. We can use a 1-variable Data Table connected to the scenario selector to generate an entire Output Summary table simultaneously!

📝 Worked Example

Building the Tata Motors Output Summary

Scenario: You've built out the Tata Motors financials. Cell C9 is the Active Scenario switch (1=Bear, 2=Base, 3=Bull). You want to build a summary table comparing Revenue, EBIT, and Net Income across all 3 scenarios.

Question: How do you use Excel Data Tables to force the model to calculate all 3 scenarios simultaneously and output them into a tidy side-by-side table?
1
Lay out the Table Structure
Down the left side, put your scenario numbers: 1, 2, and 3 in rows.
Across the top row (offset by one cell), link to your model outputs: =Revenue, =EBIT, =Net_Income.
2
Select the Range
Highlight the entire grid encompassing the input numbers (1,2,3), the output links at the top, and the empty space in the middle.
3
Run the Data Table
Go to Data → What-If Analysis → Data Table.
Because your inputs (1,2,3) are listed down a column, click into Column input cell.
Select your Active Scenario Switch (Cell C9).
Excel instantly runs the entire model as 1 (Bear), saves the outputs, runs it as 2 (Base), saves, and runs 3 (Bull) – populating your summary matrix perfectly!

📊 Final Result: Tata Motors Output Summary

Scenario # → Revenue EBIT Net Income
1 ₹1,03,000 Cr ₹8,800 Cr ₹5,500 Cr
2 ₹1,08,000 Cr ₹12,900 Cr ₹8,600 Cr
3 ₹1,15,000 Cr ₹16,800 Cr ₹11,500 Cr

Why this is powerful: Any time you change an underlying assumption (e.g. increase the Base Case tax rate), the data table automatically recalculates all inputs and updates the side-by-side summary without you changing the active switch!

← Previous Section Next: Goal Seek & Solver →