📌 Stage 1 · Plan Architecture · Assumptions · INDEX and MATCH scenario switch · 35–45 min

One dropdown cell drives every driver in the model

Learning goals

  • Explain why a three-scenario plan beats a single-point forecast
  • Read the INDEX/MATCH formula that copies the chosen column into the Active column
  • List which drivers move between scenarios and which stay fixed
  • Switch the scenario cell and predict what happens downstream

Concepts

Why scenarios beat one number

A single forecast says “we will bill $690,000.00.” A scenario plan says “here is the year if everything lands, the year we are actually planning, and the year we must survive.” The Assumptions sheet holds that spread in three columns — Best (C), Base (D), Worst (E) — for eleven drivers, and one Active column (F) that the rest of the workbook reads.

Rule of thumb for a four-person studio: Base is what you budget, Best is what you chase, Worst is what you must still pay rent through. The planning decision (the July hire, the build-out) only has to work in Base and Worst; it gets to pay off in Best.

The switch mechanism: INDEX and MATCH

Cell C5 holds the scenario name as a dropdown value. Each driver row copies the matching column with INDEX/MATCH:

=INDEX($C14:$E14,1,MATCH($C$5,$C$13:$E$13,0))

MATCH($C$5,$C$13:$E$13,0) finds the position of the scenario name in the header row (Best=1, Base=2, Worst=3). INDEX($C14:$E14,1,that position) returns the value from that column of row 14. The 0 forces an exact match. Everything downstream reads column F only, so flipping one cell re-points the whole model.

💡 Tips:

  • In Excel 365 you could write =XLOOKUP($C$5,$C$13:$E$13,C14:E14) — this course sticks to INDEX/MATCH so every Excel version since 2010 can run it.
  • Keep the header row (row 13) exactly as delivered: MATCH looks up the scenario names there. Renaming “Base” breaks every Active cell.

What moves, and what does not

Six demand and cost drivers move with the scenario: retainer clients under contract (3 / 3 / 2), average monthly retainer fee ($7,000.00 / $6,500.00 / $6,000.00), project engagements per month (5 / 4 / 4), average project fee ($10,000.00 / $9,500.00 / $8,500.00), contractor cost ratio (22% / 25% / 28%), and health insurance per employee ($520.00 / $560.00 / $620.00).

Three timing drivers move as well: collected in month billed (50% / 40% / 30%), collected two months out (5% / 15% / 25%), and vendor invoices paid in month (80% / 70% / 60%).

Fixed across scenarios: the employer payroll tax rate (9.5%, a statutory planning blend of FICA plus FUTA/SUTA) and the 45% one-month collection share, which is the NET 30 core that does not change.

How to read the Active column

Column F is the only column other sheets reference — Assumptions!$F$14 is retainer clients, $F$15 the retainer fee, $F$16 and $F$17 the project count and fee, $F$18 the contractor ratio, $F$19 to $F$21 the collection curve, $F$22 the vendor payment share, $F$23 the payroll tax rate, and $F$24 the health premium. Under Base, F14 shows 3 and F15 shows $6,500.00. Type Worst into C5 and F14 drops to 2 instantly.

Practice

Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. Cell F14 is blank: retype the INDEX/MATCH formula that pulls the active retainer-client count.

  1. Open Assumptions and inspect the switch — The exercise opens on Assumptions. Cell C5 shows Base with a dropdown; row 13 holds the header Best / Base / Worst / Active / Notes; drivers sit on rows 14 to 24.
  2. Retype the Active formula in F14 — Cell F14 (Active value for “Retainer clients under contract”) is empty. Type =INDEX($C14:$E14,1,MATCH($C$5,$C$13:$E$13,0)) and confirm. It should return 3 under the Base scenario.
  3. Test the switch — Click C5 and pick Worst from the dropdown. F14 should change to 2 and F15 to $6,000.00. Pick Best: F14 is 3 and F15 is $7,000.00. Pick Base again to restore the cached plan.
  4. Check one downstream echo — Open Revenue Plan. C8 (January retainer billings) should read $16,575.00 under Base = 3 clients × $6,500.00 × the 0.85 January index. Switch Assumptions C5 to Worst and it should drop to $10,200.00 = 2 × $6,000.00 × 0.85.
  5. Leave the plan on Base — Set C5 back to Base and confirm Revenue Plan C8 returns to $16,575.00. All later lessons assume the cached Base plan.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Retyped the INDEX/MATCH formula in F14 and it returned 3 (retainer clients, Base)
  • ☐ Switched C5 through Best, Base, and Worst and watched column F follow
  • ☐ Can name the six demand/cost drivers and three timing drivers that move between scenarios
  • ☐ Restored Base: Revenue Plan C8 shows $16,575.00 again