Learn Final System Output and Dashboard Logic | Scenario Modeling and Interactive Decision Systems

Swipe to show menu

The workbook now contains dynamic reporting, scenario modeling, financial analysis, and KPI systems. In this chapter, bring everything together into a single Dashboard interface that controls the entire model.

Dashboard Architecture

The Dashboard acts as the interface layer between:

User inputs;
Calculation logic;
Final outputs.

Users interact with the Dashboard instead of navigating backend worksheets directly.

Dynamic KPI Logic

Dashboard KPIs respond dynamically to:

Region selection;
Month selection;
Active scenario selection.

Multiple functions work together to build contextual outputs:

FILTER
SUMIFS
COUNTIFS
XLOOKUP
CHOOSE

Step 1 Create the Dashboard Sheet

Create a new worksheet named:

Dashboard

Move the sheet to the first tab position.

Step 2 Build the Input Layer

Create the following input fields:

Input	Source
Region	Data Validation from Reference_Lists
Month	Validation list from 1–12

These inputs will drive all Dashboard KPIs.

Step 3 Display the Active Scenario

Type:

=CHOOSE(Scenarios!$B$6, Scenarios!B2, Scenarios!B3, Scenarios!B4)

Scenarios!$B$6: active scenario selector;
Scenarios!B2:B4: scenario labels.

The displayed scenario updates automatically based on the selected input.

Step 4 Build the Revenue KPI

Type:

=SUMIFS(Sales_Data[Revenue], Sales_Data[Region], B2, Sales_Data[Month], B3)

B2: selected region;
B3: selected month.

The KPI dynamically filters Revenue based on user inputs.

Step 5 Build the Profit KPI

Type:

=SUMIFS(Sales_Data[Profit], Sales_Data[Region], B2, Sales_Data[Month], B3)

The KPI returns filtered Profit for the selected context.

Step 6 Build the Order Count KPI

Type:

=COUNTIFS(Sales_Data[Region], B2, Sales_Data[Month], B3)

The KPI returns the number of matching transactions.

Step 7 Build the Top Product KPI

Type:

=XLOOKUP(MAX(FILTER(Sales_Data[Revenue], Sales_Data[Region]=B2)), Sales_Data[Revenue], Sales_Data[Product], "No data")

FILTER(...): isolates matching region data;
MAX(...): identifies the highest Revenue value;
XLOOKUP(...): retrieves the matching product.

Step 8 Build the Projected Revenue KPI

Type:

=SUMIFS(Sales_Data[Revenue], Sales_Data[Region], B2, Sales_Data[Month], B3) * CHOOSE(Scenarios!$B$6, Scenarios!$C$2, Scenarios!$C$3, Scenarios!$C$4) * CHOOSE(Scenarios!$B$6, Scenarios!$D$2, Scenarios!$D$3, Scenarios!$D$4)

Base Revenue: filtered transactional output;
First CHOOSE(...): pricing multiplier;
Second CHOOSE(...): volume multiplier.

Projected Revenue now responds dynamically to the selected scenario.

Step 9 Format the KPI Layer

Apply appropriate formatting: