Building Forecast Dashboards

Every model you have built across Sections 1 through 4 is technically complete but operationally scattered. A senior stakeholder does not open four sheets and trace formula chains — they open one page and expect to understand the business position in under a minute. This lesson shows you how to design and build a professional forecast dashboard that surfaces the right information at the right level of detail, updates automatically, and works for both analytical and executive audiences.

What Separates a Dashboard from a Report

A report presents data. A dashboard drives decisions. The distinction is not cosmetic — it determines every design choice you make.

A report can be long, detailed, and static. A dashboard must be short, selective, and live. Every element on a dashboard earns its place by answering a specific question a decision-maker needs answered. If you cannot name the question a chart or number answers, it does not belong on the dashboard.

Three principles govern every design decision in this lesson:

• Hierarchy — the most important information must be immediately visible without scrolling or clicking. KPI cards at the top, supporting detail below, raw data never visible at all;
• Context — a number without context is meaningless. $4.2 million revenue means nothing without knowing whether that is above or below target, better or worse than last year, and trending up or down. Every metric needs its reference point built in;
• Action — a dashboard that shows everything is fine requires no action. A dashboard that shows a problem requires a decision. Design for the problem case — make deviations from plan impossible to miss.

Designing the Dashboard Architecture

A professional forecast dashboard for this model has five layers, arranged top to bottom:

• Layer 1 — Control Bar

  A single row at the very top containing the scenario selector, a reporting period selector, and a last-updated timestamp. Everything below responds to these controls;

• Layer 2 — KPI Strip

  Five to seven headline metrics displayed as cards across the full width of the sheet. Each card shows the metric value, its variance from plan, and a directional indicator. These answer the first question any stakeholder asks: are we on track?

• Layer 3 — Primary Chart

  One large chart occupying the center of the dashboard — the 36-month history plus 12-month forecast with the active scenario highlighted. This answers the second question: where are we going?

• Layer 4 — Secondary Charts

  Two or three smaller charts side by side showing supporting dimensions — revenue by region, margin trend, cash closing balance. These answer the third question: what is driving the headline?

• Layer 5 — Exception Table

  A compact table at the bottom showing only the metrics that are outside acceptable ranges — red variances, funding gaps, utilization alerts. This answers the fourth question: what needs attention right now?

Building the Control Bar

The control bar is the most important row on the dashboard — it is the only place where the user interacts with the model. Keep it minimal and clearly labeled.

Scenario Selector — link directly to the Active_Scenario dropdown from Lesson 3.3:

Cell B1: Data Validation dropdown linked to Active_Scenario named cell
Label in A1: "Active Scenario:"
Format B1: bold, blue border, prominent font size

Reporting Period Selector — a dropdown controlling which time period the KPI cards and variance calculations reference:

Cell D1: Data Validation list: "Full Year 2025", "Q1 2025", "Q2 2025", "H1 2025"
Name this cell: Report_Period
Label in C1: "Report Period:"

Last Updated Timestamp:

Cell F1: =TEXT(NOW(), "DD-MMM-YYYY HH:MM")
Label in E1: "Last Updated:"
Format: italic, gray text

Building the KPI Strip

Each KPI card occupies a merged cell block and contains three elements: the metric value, the variance from plan, and a status indicator. Build them as a reusable pattern — design one card completely, then copy and adapt.

Card structure (example: Total Forecast Revenue):

Merged cells A3:C4 — Metric Value:
=IF(Report_Period="Full Year 2025",
    SUM(Forecast_2025[Assumption_Forecast]),
    SUMIFS(Forecast_2025[Assumption_Forecast],
           Forecast_2025[Quarter_Label], Report_Period))
Format: $#,##0  Bold  Font size 28  Center aligned

Merged cells A5:C5 — Variance from Plan:
=[@Metric_Value] - [@Budget_Value]
Format: $#,##0;($#,##0)  Font size 12
Conditional formatting: positive = green text, negative = red text

Merged cells A6:C6 — Label:
"Total Forecast Revenue"
Format: Font size 9  Gray  Center aligned  All caps

Status indicator — add a formula-driven symbol next to each card value:

=IF([@Variance]>0, "▲", IF([@Variance]<0, "▼", "—"))

Format the triangle characters: green for ▲, red for ▼, gray for —. These give an immediate visual signal that works even when the numbers are too small to read at a glance.

The five KPI cards for this dashboard:

Card	Metric	Reference Point
1	Total Forecast Revenue	vs Budget
2	Total Forecast Profit	vs Budget
3	Gross Margin %	vs 2024 Actual
4	Cash Closing Balance (worst month)	vs Cash_Minimum_Buffer
5	Required FTE vs Current FTE	vs Current Headcount

Building the Primary Forecast Chart

The primary chart on the dashboard is the single most important visual in the entire model. It must show three things simultaneously: where the business has been, where it is going, and how confident we are in that projection.

Data series:

Series 1 — Historical Revenue (solid blue line):
Source: Forecast_Input_Table[Total_Revenue]  Months 1–36

Series 2 — Active Scenario Forecast (dashed blue line):
Source: Forecast_2025[Assumption_Forecast]  Months 37–48

Series 3 — ETS Upper Bound (thin dashed gray line):
Source: Forecast_2025[ETS_Upper]

Series 4 — ETS Lower Bound (thin dashed gray line):
Source: Forecast_2025[ETS_Lower]

Series 5 — Baseline Forecast (thin dotted gray line):
Source: Forecast_2025[Baseline_Forecast]

Chart formatting for dashboard use:

Remove: chart border, gridlines, legend (use direct labels instead)
Add:    direct data labels on the last historical point and last forecast point
        vertical reference line at the history/forecast boundary
        shaded area between ETS_Upper and ETS_Lower (confidence band)
Title:  linked to a formula: ="Revenue Forecast — "&Active_Scenario&" | "&Report_Period

Creating the confidence band shading:

Excel does not have a native shaded area between two lines. Simulate it using a stacked area chart trick:

1. Add a helper series: Lower_Bound values as one series;
2. Add another helper series: Upper_Bound minus Lower_Bound as the stacked layer;
3. Format the Lower_Bound series: No fill, No border (invisible base);
4. Format the Upper_Minus_Lower series: Light blue fill at 20% transparency, No border;
5. This creates a shaded band between the two bounds.

Building the Secondary Charts

Three secondary charts support the primary view. Each answers one specific question and occupies roughly one-third of Layer 4 width.

Chart A — Revenue by Region (Clustered Bar):

Shows 2024 actuals vs 2025 forecast for each of the five regions side by side. Immediately reveals which regions are growing, flat, or contracting in the forecast.

Data source: Revenue_Forecast sheet, regional breakdown table
Series 1: 2024 Actual Revenue by Region (solid bars)
Series 2: 2025 Forecast Revenue by Region (lighter bars, same color family)
Sort: descending by 2024 actual — largest region always at the top

Chart B — Margin Trend (Line with Area):

Shows Gross Margin % across all 48 months (36 history + 12 forecast). A narrowing margin in the forecast period is one of the most important warning signals in the model.

Data source: combined historical and forecast margin columns
Historical margin: Forecast_Input_Table[Gross_Margin_Pct] (calculated in Lesson 4.2)
Forecast margin:   Forecast_2025[Gross_Margin_Pct]
Format: line chart with subtle area fill below — margin compression is immediately visible
Add reference line at Gross_Margin named assumption value

Chart C — Cash Closing Balance (Column with Reference Line):

Shows the monthly cash closing balance across all 12 forecast months as columns, with a horizontal reference line at Cash_Minimum_Buffer. Any column below the line is a funding gap — visible immediately.

Data source: Cash flow table Closing_Balance column
Format: conditional colors — blue above buffer, yellow between buffer and zero, red below zero
Reference line: add Cash_Minimum_Buffer as a flat line series on secondary axis

Building the Exception Table

The exception table at the bottom of the dashboard shows only the metrics requiring attention — it is empty when everything is on track and populates automatically when thresholds are breached. It is the most actionable element on the dashboard.

Build it using IF and FILTER logic to surface only problem rows:

Exception logic for each metric:

• Revenue variance: =IF(ABS(Rev_Variance_Pct)>0.05, "Revenue "&TEXT(Rev_Variance_Pct,"0.0%")&" vs plan", "")
• Margin compression: =IF(Forecast_Margin<Gross_Margin-0.03, "Margin compressed "&TEXT(Forecast_Margin,"0.0%")&" vs "&TEXT(Gross_Margin,"0.0%")&" assumption", "")
• Cash gap: =IF(MIN(Cash_Closing_Balance)<0, "Cash funding gap "&TEXT(MIN(Cash_Closing_Balance),"$#,##0")&" in "&Gap_Month, "")
• Utilization: =IF(MAX(Utilization_Rate)>0.95, "Peak utilization "&TEXT(MAX(Utilization_Rate),"0%")&" in "&Peak_Month, "")
• Hiring lag: =IF(Hiring_Already_Late>0, Hiring_Already_Late&" hire(s) behind schedule", "")

Collect all non-empty exception messages into a visible table using IFERROR and index logic, or simply stack them in consecutive rows with the blank rows hidden using a filter.

Format the exception table with a red left border and light red background — it must be visually distinct from everything else on the dashboard.

Dashboard Usability Rules

Before finalizing, apply these seven rules that separate professional dashboards from amateur ones:

• No raw data visible — if a number on the dashboard requires explanation, it is either in the wrong place or formatted incorrectly. The dashboard is not a data table;
• Consistent color language — blue for forecast, gray for history, green for favorable, red for unfavorable. Never use color decoratively. Every color must mean something and mean the same thing everywhere;
• No 3D charts — three-dimensional chart formatting distorts proportions and makes values harder to read. Flat, clean chart styles only;
• No pie charts for time series — pie charts cannot show trends. Use line or bar charts for any metric that changes over time;
• Every number has a unit — "$4.2M" not "4.2". "42.0%" not "42.0". Units in the label if not in the number itself;
• Print-ready layout — set the print area to the dashboard sheet and confirm it fits on one landscape A4 or Letter page. A dashboard that only works on screen is not a production deliverable;
• Version and ownership — add a small footer with the model version, the owner's name, and the data source. A dashboard without provenance cannot be trusted in a governance context.

🔧 Task 1: Build the Control Bar and KPI Strip

Goal: Create the interactive control layer and five headline KPI cards that form the top section of the dashboard.

1. On your existing Dashboard sheet from Lesson 3.5, clear the current layout and redesign it using the five-layer architecture from this lesson.
2. Build the Control Bar in row 1: link the scenario selector to Active_Scenario, add the Report_Period dropdown with four period options, and add the NOW() timestamp.
3. Build the five KPI cards in rows 3–6 using the merged cell structure from this lesson. Use the Report_Period selector to control whether cards show full-year or quarterly values.
4. Add status indicator triangles to each card and apply conditional formatting — green for favorable variance, red for unfavorable.
5. Add a thin horizontal border below row 6 to visually separate the KPI strip from the chart layers below.
6. Test all five cards across all three scenarios and both period selections — confirm every value updates correctly and every triangle points in the right direction.

Task 2: Build the Chart Layers and Exception Table

Goal: Add the primary forecast chart, three secondary charts, and the exception table to complete the dashboard.

1. Build the primary forecast chart using all five data series from this lesson. Implement the confidence band shading using the stacked area trick. Link the chart title to the Active_Scenario and Report_Period cells.
2. Build Chart A (Revenue by Region) using the regional breakdown from your Revenue_Forecast sheet. Sort regions by 2024 actual descending.
3. Build Chart B (Margin Trend) covering all 48 months with the historical/forecast boundary marked. Add a reference line at the Gross_Margin assumption value.
4. Build Chart C (Cash Closing Balance) with conditional column colors and the Cash_Minimum_Buffer reference line.
5. Build the exception table using the five IF formulas from this lesson. Apply the red left border and light red background formatting.
6. Apply the seven usability rules: remove all 3D effects, verify color consistency, confirm every number has a unit, set the print area to one landscape page, and add a footer with version, owner, and data source.