Build the real multi-sheet scenario-switch workbook with selector and KPI checks
Build the Scenario-Switch Workbook by Sheet
Build a selector-driven workbook where scenario and method choices flow into outputs, KPI tiles, and checks. This phase is assessed on lookup chain correctness and KPI interpretation.
Download unit07-lesson06-student.xlsx
Workbook tabs: Inputs, Drivers, MethodSummary, Outputs, KPI, Checks, Dashboard.
Formulas are displayed as text so you can inspect structure before building in Excel.
Control cells for scenario, method, and key.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Control | Value | Formula / Source | Purpose | Check |
| 2 | SelectedScenario | Base | Data Validation | Scenario selector | Base/Stretch/Downside |
| 3 | SelectedMethod | FIFO | Data Validation | Method selector | FIFO/LIFO/Weighted Average |
| 4 | SelectedKey | Base|FIFO | =SelectedScenario&"|"&SelectedMethod | Lookup key | Matches MethodSummary[Key] |
| 5 | UnitSellingPrice | 40 | Input | Revenue calculation | Positive value |
| 6 | BeginningInventory | 180 | Input | Average inventory KPI | From source inventory |
| 7 | GAFS | 1150 | Input | Cost conservation target | Used in checks |
| 8 | |||||
| 9 | |||||
| 10 |
Scenario assumptions table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Scenario | UnitsSold | DefaultMethod | AsOfDate | Comment |
| 2 | Base | 35 | FIFO | 2026-03-31 | Baseline demand |
| 3 | Stretch | 39 | LIFO | 2026-03-31 | Higher demand |
| 4 | Downside | 32 | Weighted Average | 2026-03-31 | Lower demand |
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
One row per scenario+method output.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Key | Scenario | Method | COGS | EndingInventory | BalanceCheck |
| 2 | Base|FIFO | Base | FIFO | 690 | 460 | =D2+E2 |
| 3 | Base|LIFO | Base | LIFO | 770 | 380 | =D3+E3 |
| 4 | Base|Weighted Average | Base | Weighted Average | 731.82 | 418.18 | =D4+E4 |
| 5 | Stretch|FIFO | Stretch | FIFO | 778 | 372 | =D5+E5 |
| 6 | Stretch|LIFO | Stretch | LIFO | 850 | 300 | =D6+E6 |
| 7 | Stretch|Weighted Average | Stretch | Weighted Average | 815.45 | 334.55 | =D7+E7 |
| 8 | Downside|FIFO | Downside | FIFO | 624 | 526 | =D8+E8 |
| 9 | Downside|LIFO | Downside | LIFO | 710 | 440 | =D9+E9 |
| 10 | Downside|Weighted Average | Downside | Weighted Average | 669.09 | 480.91 | =D10+E10 |
Lookup chain from selectors to current outputs.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Output | Value | Formula | Meaning | Source |
| 2 | SelectedUnitsSold | 35 | =XLOOKUP(SelectedScenario,Drivers[Scenario],Drivers[UnitsSold]) | Scenario demand | Drivers sheet |
| 3 | SelectedCOGS | 690 | =XLOOKUP(SelectedKey,MethodSummary[Key],MethodSummary[COGS]) | Scenario+method COGS | MethodSummary sheet |
| 4 | SelectedEndingInventory | 460 | =XLOOKUP(SelectedKey,MethodSummary[Key],MethodSummary[EndingInventory]) | Scenario+method EI | MethodSummary sheet |
| 5 | GAFS | 1150 | Input constant | Cost conservation target | Shared inventory layers |
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Turnover and days-on-hand formula chain.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | KPI | Value | Formula | Interpretation | Check |
| 2 | Revenue | 1400 | =SelectedUnitsSold*UnitSellingPrice | Sales dollars | UnitsSold x price |
| 3 | Gross Margin % | 50.7 | =(Revenue-SelectedCOGS)/Revenue | Profitability | Format as percent |
| 4 | Average Inventory | 320 | =(BeginningInventory+SelectedEndingInventory)/2 | Inventory base | Used for turnover |
| 5 | Inventory Turnover | 2.16 | =SelectedCOGS/AverageInventory | Velocity | Higher means faster flow |
| 6 | Days on Hand | 169.3 | =365/InventoryTurnover | Holding duration | Lower means faster flow |
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Visible trust checks before recommendation.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Check | Status | Formula Pattern | Purpose | Action |
| 2 | GAFS Balance | Balanced | =IF(ABS((SelectedCOGS+SelectedEndingInventory)-GAFS)<0.01,"Balanced","Check") | Cost conservation | Debug lookup source row if failed |
| 3 | SelectedKey Found | OK | =IFNA(XLOOKUP(SelectedKey,MethodSummary[Key],MethodSummary[COGS]),"Missing Key") | Lookup health | Fix key text mismatch |
| 4 | Units Sold Valid | OK | =IF(SelectedUnitsSold>0,"OK","Check Drivers") | Scenario setup | Review Drivers row |
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Executive display linked to Outputs + KPI.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tile | Displayed Value | Source Cell | Purpose | Check |
| 2 | Scenario | Base | #NAME? | Context | Matches selector |
| 3 | Method | FIFO | #NAME? | Context | Matches selector |
| 4 | COGS | 690 | FIFO | Primary cost metric | Matches Outputs |
| 5 | Turnover | 2.16 | 2.16 | Velocity metric | Matches KPI |
| 6 | Days on Hand | 169.3 | 169.3 | Holding metric | Matches KPI |
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Block 1: Inputs + Drivers
- Create scenario and method dropdown controls.
- Create the Drivers table with UnitsSold per scenario.
- Build SelectedKey from scenario + method.
Checkpoint: changing controls updates SelectedKey correctly.
Block 2: MethodSummary + Outputs
- Build scenario+method summary rows for COGS and Ending Inventory.
- Lookup SelectedUnitsSold from Drivers by scenario.
- Lookup SelectedCOGS and SelectedEndingInventory from MethodSummary by key.
Checkpoint: Base|FIFO returns COGS 690 and Ending Inventory 460.
Block 3: KPI Sheet
- Calculate revenue from units sold and selling price.
- Calculate gross margin, average inventory, turnover, and days on hand.
- Format KPI outputs for dashboard readability.
Checkpoint: Base|FIFO turnover ≈ 2.16x and days on hand ≈ 169.3.
Block 4: Checks + Dashboard
- Add visible checks for GAFS balance and lookup health.
- Link dashboard tiles to Inputs, Outputs, and KPI cells.
- Verify dashboard updates from only selector changes.
Checkpoint: GAFS check shows Balanced for every selected pair.
- Selectors drive one lookup chain from Inputs to Dashboard.
- MethodSummary includes all scenario+method rows with correct outputs.
- KPI calculations update correctly for any selected pair.
- Checks surface model issues before recommendations are made.
- You can explain the formula chain sheet-by-sheet in plain language.
- Use clear names for control and output cells.
- Keep structured references consistent across sheets.
- Add validation and error handling as standard workbook quality.
Use the teacher workbook to compare your sheet structure if your lookups fail: