Dynamic Scenario Dashboards

The models you have built across Section 3 are powerful but scattered — assumptions on one sheet, forecasts on another, scenario summaries on a third. A dashboard pulls everything together into a single, visually coherent view that a stakeholder can read in sixty seconds without opening a single formula. This lesson shows you how to build one that updates automatically when the scenario changes.

What Makes a Dashboard Dynamic

A static dashboard is a formatted table of numbers. A dynamic dashboard is connected to live data — every number, chart, and indicator updates automatically when the underlying assumptions change. In your model, the trigger is the Active_Scenario dropdown you built in Lesson 3.3. Change it from Expected Case to Worst Case and every element on the dashboard responds instantly.

Three things make this possible:

• Linked output cells — every number displayed on the dashboard is a formula pointing to a calculation in the model, never a typed value;
• Chart data ranges referencing Tables — charts built on Excel Table columns update automatically when the underlying data changes, with no manual intervention;
• Conditional formatting tied to thresholds — color indicators respond to value changes automatically, turning red when a metric falls below a threshold and green when it exceeds one.

Designing the Dashboard Layout

A one-page dashboard for scenario forecasting has four zones:

• Zone 1 — Scenario Control Panel (top left)

  The Active_Scenario dropdown, the five current assumption values, and a clear label showing which scenario is active. This is the only area where the user interacts with the dashboard;

• Zone 2 — KPI Summary Cards (top right)

  Four to six headline metrics displayed as large numbers with small labels: Total Forecast Revenue, Total Forecast Profit, Gross Margin %, and Revenue vs 2024 variance. Each card has a color indicator — green if above target, red if below;

• Zone 3 — Forecast Chart (middle)

  A line chart showing 36 months of historical revenue plus 12 months of forecast, with the active scenario's projection highlighted and the confidence bounds visible. This chart updates automatically when the scenario changes;

• Zone 4 — Scenario Comparison Table (bottom)

  A compact table showing all three scenarios side by side for the key output metrics, with the active scenario's column highlighted. This gives context — the user can see not just what the current scenario projects but how it compares to the other two.

Building the KPI Summary Cards

Each KPI card is a merged cell group containing two elements: a large bold number and a small label below it. The number is a formula. The background color is controlled by conditional formatting.

Total Forecast Revenue card:

• Large cell (merged A1:C2): =SUM(Forecast_2025[Assumption_Forecast])

• Format: $#,##0, Bold, Font size: 24, Center aligned.

• Label cell below (merged A3:C3): "Total Forecast Revenue 2025";

• Format: Gray text, Font size: 10, Center aligned.

Revenue vs 2024 Variance card:

=SUM(Forecast_2025[Assumption_Forecast]) - SUMIF(Business_Data[Year], 2024, Business_Data[Revenue])

Format as currency with + / − sign: $#,##0;-$#,##0, or use TEXT formula: =IF(variance>0, "▲ "&TEXT(variance,"$#,##0"), "▼ "&TEXT(ABS(variance),"$#,##0")).

Conditional formatting for KPI cards:

• Rule 1: If value >= Revenue_Target → green fill (#E2EFDA), dark green text;
• Rule 2: If value < Revenue_Target → red fill (#FCE4D6), dark red text;
• Rule 3: If value >= 0 and < Revenue_Target → yellow fill (#FFF2CC), dark yellow text.

Building the Dynamic Forecast Chart

The forecast chart on the dashboard is not a new chart — it is a copy of the master forecast chart you built in Lesson 2.2, linked to the same data. Changes to the Active_Scenario flow through the assumption model into the Forecast_2025 table and update the chart automatically.

1. Click on your existing forecast chart → Ctrl+C to copy;
2. Click on the dashboard sheet → Ctrl+V to paste;
3. Right-click the pasted chart → Move Chart → Object in [Dashboard sheet];
4. Resize to fill Zone 3.

Format the chart specifically for dashboard use — strip out everything that doesn't communicate directly:

• Remove: gridlines, legend (replace with direct data labels), chart border;
• Keep: the history/forecast boundary marker, the confidence band, axis labels;
• Add: a dynamic title linked to the Active_Scenario cell:
  • Click the chart title → type = in the formula bar → click the Active_Scenario cell;
  • The title now reads "Worst Case Forecast" or "Best Case Forecast" automatically.

Building the Scenario Comparison Table

The comparison table shows all three scenarios side by side so the active scenario always has context. Build it using direct references to the scenario assumptions table from Lesson 3.3 — not to Scenario Manager, which is static.

Worst Case Expected Case Best Case Total Revenue formula formula formula Total Profit formula formula formula Gross Margin % formula formula formula vs 2024 Revenue formula formula formula vs 2024 Profit formula formula formula

For each cell, temporarily switch the Active_Scenario dropdown to that scenario, note the output cell address, then write a direct reference. Alternatively, use INDEX/MATCH directly against the scenario reference table:

Worst Case Total Revenue: =SUMPRODUCT formula using Growth_Rate from Worst Case column × Base_Revenue

Or more practically — use three helper columns off-screen that each run the full forecast using hardcoded scenario values, and reference those totals in the comparison table.

Highlight the active scenario column dynamically using conditional formatting:

• Rule: if column header = Active_Scenario → apply blue fill, white bold text;
• Formula-based rule for the Expected Case column header cell (e.g. C1): =C$1=Active_Scenario.

Adding a Scenario Narrative Box

Numbers without context are incomplete. Add a text box below the KPI cards that displays a short narrative description of the active scenario — the business conditions it represents.

Use a formula-driven cell rather than a static text box:

=IF(Active_Scenario="Worst Case",
    "Market contraction: declining revenue, compressed margins, elevated discounting.",
    IF(Active_Scenario="Best Case",
       "Growth scenario: above-trend expansion, improved margins, reduced discounting.",
       "Base case: continuation of 2022–2024 trends across all key metrics."))

Format this cell with a light gray border, italic text, and a subtle background fill to distinguish it visually from the data cells.

Linking Everything to One Control

Before finalizing the dashboard, trace through every element and confirm it responds to the Active_Scenario dropdown.

Dashboard Element	Updates When Scenario Changes?
KPI Summary Cards	Yes — via Assumption_Forecast formulas
KPI Card Colors	Yes — via conditional formatting on live values
Forecast Chart	Yes — chart data linked to Forecast_2025 Table
Chart Title	Yes — linked to Active_Scenario cell
Scenario Comparison Table	No — shows all three always (by design)
Active Scenario Highlight	Yes — conditional formatting rule
Narrative Text Box	Yes — IF formula reads Active_Scenario

The one element that intentionally does not change is the Scenario Comparison Table — it always shows all three scenarios because its purpose is to provide context for whichever one is active.

Task 1: Build the Dashboard Shell

Goal: Create the four-zone layout and populate it with linked KPI cards.

1. Insert a new sheet and name it Dashboard. Right-click the tab and choose Tab Color — use the same dark blue as your header rows to make it stand out.
2. Set all column widths to 2 and all row heights to 15 to create a fine grid — this makes precise cell merging much easier.
3. Sketch the four zones using merged cells with light border outlines: Scenario Control (top left), KPI Cards (top right), Forecast Chart (middle), Comparison Table (bottom).
4. Build the Scenario Control zone: paste a link to the Active_Scenario dropdown, add labels for each assumption, and link each assumption value to the corresponding named cell.
5. Build four KPI cards for Total Revenue, Total Profit, Gross Margin %, and Revenue vs 2024. Use merged cells, large font sizes, and currency / percentage formatting.
6. Apply three-color conditional formatting to each KPI card value using Revenue_Target and Profit_Target as thresholds.

🔧 Task 2: Add the Chart, Comparison Table, and Narrative

Goal: Complete the dashboard with the dynamic forecast chart, scenario comparison, and narrative text.

1. Copy your master forecast chart from the Forecast_Input sheet and paste it into Zone 3 of the dashboard. Resize it to fill the zone cleanly.
2. Link the chart title to the Active_Scenario cell so it reads "[Scenario Name] Revenue Forecast — 2025".
3. Build the Scenario Comparison Table in Zone 4 with all three scenarios as columns. Reference total revenue and profit outputs for each scenario.
4. Apply conditional formatting to highlight the active scenario column using =column_header_cell=Active_Scenario as a formula-based rule.
5. Add the narrative formula cell below the KPI cards using the IF formula from this lesson. Format it with italic text and a subtle background.
6. Final test: cycle through all three scenarios using the Active_Scenario dropdown and confirm that every element — KPI values, card colors, chart, chart title, active column highlight, and narrative text — updates correctly each time.