About this lesson
Mixed references: one formula for a whole sensitivity table
A mixed reference locks only the column ($A5) or only the row (B$4). In a two-way table, =$B$1*(1+$A5)*B$4 always reads growth from column A and margin from row 4, so one formula fills the whole grid. Press F4 on Windows or ⌘T on Mac while editing to cycle a reference through $A$1, A$1, $A1 and A1.
=$B$1*(1+$A5)*B$4What you'll practise
Build Kookaburra Retail's FY28 EBITDA table for every growth and margin case from a single formula, using $A5 and B$4.
4 steps · 40 points · the cells outlined in gold below
- mixed references
- absolute references
- sensitivity tables
- fill right
- fill down
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | FY27 revenue ($m) | 486.0 | |||
| 2 | |||||
| 3 | FY28 EBITDA ($m) | EBITDA margin → | |||
| 4 | Revenue growth ↓ | 10% | 12% | 14% | 16% |
| 5 | 0% | ||||
| 6 | 3% | ||||
| 7 | 6% | ||||
| 8 | 9% | ||||
| 9 |