Section Learning Objectives
📥 Hands-on: Stress-testing with Data Tables
Download the provided CSV template to practice building one- and two-variable Data Tables in Excel based on a foundational Reliance Retail Income Statement.
One-Variable Data Tables
A one-variable data table shows how one output (e.g., Net Income) changes across different values of one input (e.g., Revenue Growth Rate). It's the simplest form of sensitivity analysis.
Setting up a 1D Sensitivity Table
Question: Walk through the exact Excel steps to produce an auto-calculating 1D Data Table mapping Revenue Growth to Net Income.
Step 2: In the top-right corner cell of your planned table (Cell E6), link directly to the target output: `=Net_Income_Cell`
Step 3: Select the entire block spanning the inputs and the empty result column plus the corner formula (D6:E12).
Step 4: Go to
Data → What-If Analysis → Data Table.Step 5: In the dialog box, leave "Row input cell" blank. For "Column input cell", click the Revenue Growth base input cell (
B2).Step 6: Click OK to generate the array.
📊 Expected Output: Reliance Retail Net Income
| Rev Growth → | ₹1,083 Cr |
|---|---|
| −5% | ₹839 Cr |
| 0% | ₹933 Cr |
| 5% | ₹1,027 Cr |
| 8% (Base) | ₹1,083 Cr |
| 15% | ₹1,215 Cr |
| 20% | ₹1,308 Cr |
Notice: No matter how many rows you add, Excel recalculates Net Income for each growth assumption instantly.
Two-Variable Data Tables
A two-variable data table shows how one output changes across combinations of two inputs — one in rows, one in columns. This creates a matrix (grid) of results.
Setting up a 2D Sensitivity Matrix
Question: Trace the process to build a 2-variable Data Table, ensuring you map the row/column inputs accurately.
Step 2: List Gross Margin values across the top row (e.g., E6:I6).
Step 3: In the exact top-left corner intersecting the row and column headers (Cell D6), link to the output: `=Net_Income_Cell`.
Step 4: Highlight the entire matrix area (D6:I12).
Step 5: Go to
Data → What-If Analysis → Data Table.Step 6: Warning! Row Input = the variable listed across the TOP row (Gross Margin,
B3). Column Input = the variable listed down the LEFT column (Rev Growth, B2).Step 7: Click OK to populate the sensitivity matrix.
📊 Expected Output: Reliance Retail Net Income
| ₹1,083 Cr | 35% | 38% (Base) | 40% | 42% | 45% |
|---|---|---|---|---|---|
| −5% | ₹554 Cr | ₹839 Cr | ₹1,029 Cr | ₹1,219 Cr | ₹1,504 Cr |
| 0% | ₹633 Cr | ₹933 Cr | ₹1,133 Cr | ₹1,333 Cr | ₹1,633 Cr |
| 5% | ₹712 Cr | ₹1,027 Cr | ₹1,237 Cr | ₹1,447 Cr | ₹1,762 Cr |
| 8% (Base) | ₹759 Cr | ₹1,083 Cr | ₹1,299 Cr | ₹1,515 Cr | ₹1,839 Cr |
| 15% | ₹870 Cr | ₹1,215 Cr | ₹1,445 Cr | ₹1,675 Cr | ₹2,020 Cr |
| 20% | ₹949 Cr | ₹1,308 Cr | ₹1,548 Cr | ₹1,788 Cr | ₹2,148 Cr |
The intersection of 8% Rev Growth and 38% Gross Margin correctly yields the Base Net Income of ₹1,083 Cr.
Common Data Table Mistakes
- Forgetting the corner link: The top-left (one-variable) or top-left corner (two-variable) MUST contain a formula linking to your output cell. Without it, the table returns zeros.
- Swapping row/column inputs: In a two-variable table, the "row input cell" corresponds to values across the TOP ROW, and "column input cell" corresponds to values down the LEFT COLUMN. Getting these backwards gives wrong results.
- Editing table cells manually: Data table cells are array formulas — you cannot edit individual cells. To change, delete the entire result area and recreate.
- Calculation set to Manual: Data tables won't update if Excel is in Manual calculation mode. Go to Formulas → Calculation Options → Automatic.