Named Ranges & What-If Analysis¶

As models grow, two tools keep them readable and let you explore scenarios: named ranges and what-if analysis.

A financial model exploring different scenarios

Named Ranges¶

Give a cell or range a human name, then use it in formulas.

Select E1 → type "TaxRate" in the Name Box → Enter

excel

2 lines

1=B2 * TaxRate        ← reads far better than =B2 * $E$1
2=SUM(MonthlySales)   ← vs =SUM(C2:C13)

Self-documenting formulas
Names are absolute by default (no $ needed)
Manage them in Formulas → Name Manager

Goal Seek — Work Backwards¶

"What price gives me $10,000 profit?" Let Excel solve it:

Data → What-If Analysis → Goal Seek
  Set cell:      Profit cell
  To value:      10000
  By changing:   Price cell

Excel adjusts the price until profit hits 10,000.

Data Tables — Test Many Inputs at Once¶

A Data Table shows how a result changes across a range of inputs:

| Price | Profit |
|  10   | =Profit |     One-variable data table:
|  12   |         |     Data → What-If → Data Table
|  14   |         |     → see profit at each price instantly

Scenario Manager¶

Save whole sets of inputs (Best Case, Worst Case, Expected) and switch between them:

Data → What-If Analysis → Scenario Manager → Add

Tip

Named ranges make What-If tools far clearer — "By changing: Price" beats "By changing: $B$7". Name your key inputs first.