Teach selector controls, lookup chain anatomy, KPI formulas, and reference meanings
How the Scenario Switch Drives Every KPI
This lesson's Excel move is a selector-driven output chain. You choose scenario and method once, and lookups feed COGS, ending inventory, and KPI tiles automatically.
Shared Setup
Keep Drivers (Scenario assumptions), MethodSummary (scenario + method outputs), and KPI (turnover + days) in separate sheets. This separation keeps formulas readable and debugging fast.
Key control cells: SelectedScenario, SelectedMethod, and SelectedKey = SelectedScenario & "|"& SelectedMethod.
Step 1: Pull Scenario Inputs
Lookup UnitsSold and other assumptions from Drivers by scenario name: XLOOKUP(SelectedScenario, Drivers[Scenario], Drivers[UnitsSold]).
Step 2: Pull Method Outputs
Use the composite key to fetch method-specific COGS and ending inventory: XLOOKUP(SelectedKey, MethodSummary[Key], MethodSummary[COGS]) and XLOOKUP(SelectedKey, MethodSummary[Key], MethodSummary[EndingInventory]).
Step 3: Build KPI Chain
Convert selected outputs into decision metrics: Turnover = COGS / AverageInventory and DaysOnHand = 365 / Turnover.
Step 4: Validate Trust
Add visible checks so the model proves itself before interpretation: IF(ABS((SelectedCOGS+SelectedEndingInventory)-GAFS)<0.01,"Balanced","Check").
Professional Expectations (Not Scored in This Lesson)
- Use structured table references instead of fixed ranges.
- Use clear names for control cells and output rows.
- Add validation and error handling as standard model quality.
Which scenario and method are we evaluating right now?
What Excel Is Doing
Reads SelectedScenario and SelectedMethod from dropdown cells on Inputs.
Why This Matters
Every downstream lookup and KPI tile depends on these two control values.
Formula Pattern
SelectedKey = SelectedScenario & "|" & SelectedMethodFirst Setup Trap to Check
If dropdown labels do not exactly match table text, lookups fail.
Reference Meanings in This Block
SelectedScenario: The scenario dropdown choice (Base/Stretch/Downside).SelectedMethod: The method dropdown choice (FIFO/LIFO/Weighted Average).SelectedKey: Combined key used to pull one method row quickly.
| Reference | Plain Meaning | Example Use |
|---|---|---|
Drivers[UnitsSold] | The UnitsSold column in the Drivers table for all scenarios. | XLOOKUP(SelectedScenario, Drivers[Scenario], Drivers[UnitsSold]) |
MethodSummary[COGS] | All COGS values in the summary table. | Lookup returns COGS for the selected scenario+method row. |
[@COGS] | COGS in this specific MethodSummary row. | Used when creating row-level checks inside the table. |
MethodSummary[BalanceCheck] | All row-level pass/fail check values in the summary table. | Quickly scan which rows still need debugging. |
Quick self-check: active block is Controls. Explain one table reference and one row reference out loud before moving on.
In Phase 3 you will rehearse the exact lookup chain sheet-by-sheet before opening the live workbook.
Quick check now: explain the difference between MethodSummary[COGS] and [@COGS].