What-If Analysis with Data Tables

The assumption model you built in last lesson answers one question at a time: change the growth rate, see what happens. A Data Table answers dozens of questions simultaneously — it runs your entire model across a range of input values automatically and displays all the results in a single grid. It is the fastest way to understand how sensitive your forecast is to changes in key assumptions.

What a Data Table Is

A Data Table is a what-if tool built into Excel (Data → What-If Analysis → Data Table) that takes one or two input cells, substitutes a series of values into them one at a time, recalculates the entire workbook for each substitution, and records the output in a table. You define the inputs and the output. Excel does the rest.

There are two types:

1. One-Variable Data Table — varies one assumption (e.g. Growth_Rate) across a range of values and shows how one or more outputs respond. Best for sensitivity analysis on a single driver;
2. Two-Variable Data Table — varies two assumptions simultaneously (e.g. Growth_Rate and Gross_Margin) and shows how one output responds to every combination. Best for understanding the interaction between two drivers.

Building a One-Variable Data Table

Scenario: You want to see how total 2025 forecast revenue changes as Growth_Rate varies from −5% to +20% in 1% increments.

Step 1 — Set up the input column:

In column A, list your Growth_Rate values:

• A2: -5%;
• A3: -4%;
• A4: -3%;
• ...
• A27: 20%.

Step 2 — Link the output formula one row above and one column to the right of the inputs:

B1: =SUM(Forecast_2025[Assumption_Forecast])

This is the output the table will recalculate for each Growth_Rate value.

Step 3 — Select the full table range:

Select A1:B27 (includes the blank corner cell A1, all input values, and the output formula).

Step 4 — Run the Data Table:

• Data → What-If Analysis → Data Table;
• Column input cell: Growth_Rate (leave Row input cell blank);
• Click OK.

Excel fills column B with the total 2025 revenue for each growth rate scenario. The entire column recalculates in under a second.

Adding Multiple Output Columns

A one-variable Data Table can show multiple outputs side by side — not just total revenue but also total profit, net revenue, and average monthly revenue all responding to the same Growth_Rate sweep.

• B1: =SUM(Forecast_2025[Assumption_Forecast]) → Total Revenue 2025;
• C1: =SUM(Forecast_2025[Projected_Profit]) → Total Profit 2025;
• D1: =SUM(Forecast_2025[Net_Revenue]) → Net Revenue 2025;
• E1: =AVERAGE(Forecast_2025[Assumption_Forecast]) → Avg Monthly Revenue.

Select A1:E27 and run the Data Table with the same Column input cell. All four output columns fill simultaneously.

Building a Two-Variable Data Table

Scenario: You want to see how total 2025 revenue changes across combinations of Growth_Rate (rows) and Gross_Margin (columns). This produces a grid — every cell shows the result for one specific combination of the two inputs.

Step 1 — Set up the layout:

• Corner cell (e.g. A1): =SUM(Forecast_2025[Assumption_Forecast]) ← the output formula
• Row headers (B1:H1): Gross_Margin values: 30%, 35%, 40%, 45%, 50%, 55%, 60%;
• Column headers (A2:A8): Growth_Rate values: 0%, 3%, 6%, 9%, 12%, 15%, 18%.

Step 2 — Select the full grid:

Select A1:H8 (corner formula + all row headers + all column headers + empty result cells).

Step 3 — Run the Data Table:

1. Data → What-If Analysis → Data Table;
2. Row input cell: Gross_Margin;
3. Column input cell: Growth_Rate;
4. Click OK.

Excel fills the interior of the grid — 49 cells — each showing total forecast revenue for that exact Growth_Rate / Gross_Margin combination.

Reading and Formatting the Results

A two-variable Data Table is most useful when formatted as a heat map using conditional formatting. The pattern in the colors immediately shows which combinations produce acceptable outcomes and which ones are dangerous.

1. Select the interior result cells (not the headers);
2. Home → Conditional Formatting → Color Scales;
3. Choose Green - Yellow - Red (green = highest revenue, red = lowest).

Now you can read the table diagonally — high growth with low margin may produce the same revenue as moderate growth with high margin, but very different profit. The heat map makes these trade-offs visible instantly.

Add a second conditional formatting rule to highlight any cell below your minimum acceptable revenue threshold:

• Home → Conditional Formatting → New Rule → Format cells less than [threshold value].
• Format: red fill, white bold text.

This flags every combination that would miss your revenue target — useful for communicating risk to stakeholders.

Understanding Data Table Limitations

Data Tables are powerful but have three constraints worth knowing:

• They only track one output at a time in a two-variable table. If you need to see both revenue and profit respond to two inputs simultaneously, you need two separate two-variable tables;

• They recalculate every time the workbook recalculates. In large models with many Data Tables this can slow Excel down noticeably. If this happens, go to Formulas → Calculation Options → Automatic Except for Data Tables. This stops Data Tables from recalculating on every keystroke — press F9 to force a manual recalculation when you need updated results;

• They cannot use named ranges as input cells in all Excel versions. If your Data Table throws an error, replace the named range reference in the input cell dialog with the direct cell address (e.g. Assumptions!B4 instead of Growth_Rate).

Task 1: Build a One-Variable Sensitivity Table

Goal: Show how total 2025 forecast revenue responds to a full range of growth rate assumptions.

1. In your Assumptions sheet, create a new area below the assumption block labeled "Sensitivity Analysis".
2. In column A, enter Growth_Rate values from −10% to +25% in 1% increments (36 values).
3. In cell B1 (one row above and one column to the right of the first input), enter =SUM(Forecast_2025[Assumption_Forecast]).
4. Add three more output formulas in C1, D1, and E1 for Total Profit, Net Revenue, and Average Monthly Revenue.
5. Select the full range A1:E37 and run Data → What-If Analysis → Data Table with Growth_Rate as the Column input cell.
6. Format the revenue output column with a color scale (green high, red low). Add a conditional formatting rule highlighting any result below the 2024 actual total revenue — these are the scenarios where the forecast underperforms history.

Task 2: Build a Two-Variable Interaction Table

Goal: Map total 2025 forecast revenue across combinations of Growth_Rate and Gross_Margin and identify the acceptable performance zone.

1. In a new area of your Assumptions sheet, set up the two-variable table layout: Growth_Rate values (0% to 20% in 2% increments) down column A, Gross_Margin values (25% to 60% in 5% increments) across row 1.
2. In the corner cell, enter =SUM(Forecast_2025[Assumption_Forecast]).
3. Select the full grid and run the Data Table with Growth_Rate as the Column input cell and Gross_Margin as the Row input cell.
4. Apply a Green-Yellow-Red color scale to all interior result cells.
5. Add a second conditional formatting rule flagging any cell below last year's total revenue in red with white bold text.
6. Draw a text box on the table labeling the green zone "Outperforms 2024" and the red zone "Underperforms 2024" — this framing makes the table immediately usable in a stakeholder presentation.