Objectives

Section Learning Objectives

📥 Hands-on: Building Visual Dashboards

Download the provided CSV template to practice creating an executive Tornado Chart ranking the sensitivity of Infosys Net Income.

Download CSV Data
5.1

Tornado Charts

📖What Is a Tornado Chart?

A tornado chart (also called a sensitivity bar chart) ranks each input by its impact on the output. Wider bars = bigger impact. It visually shows which assumptions matter most, helping decision-makers focus on key risks.

🌪️ Example: Tornado Chart for Infosys Net Income

Each bar shows how much Net Income changes when the input swings from its low to high value:

Billing Rates
±₹2,500 Cr
Utilization Rate
±₹1,800 Cr
Subcontracting
±₹1,200 Cr
Attrition Impact
±₹900 Cr
USD/INR FX
±₹700 Cr

Insight: Billing Rates and Utilitization are the most critical assumptions — they drive 80%+ of the uncertainty in Infosys Net Income.

📝 Worked Example

Building the Infosys Tornado Chart

Scenario: You've downloaded the Infosys CSV. You see the Low and High Net Income limits when applying a ±10% shock to 5 distinct variables. To present this to the CFO, you need to build a Tornado Chart ranking the absolute spread of each variable.

Question: How do you calculate the spread and configure Excel to output the Tornado structure seen above?
1
Calculate the Spread
In cell D5 (next to the High NI), write the formula: =ABS(C5-B5). Drag this down for all 5 variables. This calculates the absolute gap (spread) between the high and low outcomes.
2
Sort Variables by Spread
Highlight the table consisting of the Labels and the Calculated Spreads. Go to Data → Sort and sort by the Spread column from Largest to Smallest. This guarantees your widest impact variable (Billing Rates) is at the top.
3
Insert the Horizontal Bar Chart
Highlight only the sorted Labels and the Spreads.
Go to Insert → Chart → 2D Clustered Bar Chart (Horizontal).
To make it look like a tornado, right-click the Vertical Axis, format it, and check Categories in reverse order so the widest bar stays at the top.
Result: A sleek executive-ready Tornado Chart clearly highlighting the operational levers the CFO needs to micro-manage!
5.2

Conditional Formatting Heat Maps

🗺️ Adding Color to Data Tables

Transform a plain two-variable data table into a professional heat map using conditional formatting:

Step 1: Select all data cells in your two-variable table (exclude headers)

Step 2: Home → Conditional Formatting → Color Scales

Step 3: Choose "Green-Yellow-Red" or "Blue-White-Red" scale

Step 4: (Optional) Add data bars or icon sets for additional clarity

Result: A color-coded matrix where green = favorable, red = unfavorable
Readers instantly see the risk/reward landscape!
5.3

Executive Summary Layout

📊 Professional Dashboard Structure

An investment banking-quality sensitivity dashboard includes these elements:

1. Scenario Selector

Prominent switch cell with conditional formatting (🔴🟡🟢)

2. Key Outputs Panel

Revenue, EBITDA, NI, Cash for Bear/Base/Bull side by side

3. Data Tables

Two-variable tables with heat map conditional formatting

4. Tornado Chart

Ranked bar chart showing key driver sensitivity

✅Presentation Tips

• Place the dashboard on a dedicated "Outputs" tab — never mix with inputs
• Use consistent formatting: blue for base, green for bull, red for bear
• Include a date stamp and model version number
• Add a "Key Assumptions" text box listing the main drivers
• Always include the balance check (Assets − Liabilities − Equity = 0) visible on the dashboard

Summary

Session 9 — Complete Summary

  • Sensitivity Analysis: Change 1–2 inputs at a time using Data Tables (one-variable and two-variable)
  • Scenario Analysis: Change multiple inputs simultaneously using CHOOSE switches (Bear/Base/Bull)
  • Goal Seek: Reverse-engineer one input to hit a target output; Solver for multiple inputs + constraints
  • Dashboard: Tornado charts rank drivers by impact; heat maps color-code data tables for instant visual analysis
← Previous Section Lecture Overview ↑