Level 3ModelingPractice, 4 min
A price and volume sensitivity table
The task
Profit is (price minus unit cost) times units, minus fixed costs. In B3, fill the whole table with one formula: prices run down A3:A7, volumes across B2:F2, and the costs sit in H1 and H2.
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Profit | Unit cost | $14 | |||||
| 2 | Price \ Units | 500 | 1,000 | 1,500 | 2,000 | 2,500 | Fixed costs | $12,000 |
| 3 | $20 | |||||||
| 4 | $24 | |||||||
| 5 | $28 | |||||||
| 6 | $32 | |||||||
| 7 | $36 |