Profitability tree
Price, volume, variable cost and fixed cost across two years, with a bridge that attributes every dollar of the profit swing.
Operating profit, this year ($000s)
$27,270
Change in operating profit ($000s)
($24,130)
Operating margin, this year
10.4%
Breakeven units (000s)
2,804
Bridge check (must be zero)
–
The download carries your own inputs, keeps every formula live and colours inputs blue exactly like the template. The share link reopens this page with your assumptions in place.
Click any highlighted cell to change an assumption. Black cells are formulas — you can retype those too, using Excel syntax such as =B5*(1+B6).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Profitability case model | ||||
| 2 | Isolate the driver: which line explains the profit fall? Then read the bridge largest first. | ||||
| 3 | – | – | – | – | – |
| 4 | Driver tree | – | – | – | – |
| 5 | – | Last year | This year | Change | % change |
| 6 | Units sold (000s) | 4,200 | 3,990 | (210) | (5.0%) |
| 7 | Price per unit ($) | $68.0 | $66.0 | ($2.0) | (2.9%) |
| 8 | Variable cost per unit ($) | $41.0 | $43.0 | $2.0 | 4.9% |
| 9 | Fixed costs ($000s) | $62,000 | $64,500 | $2,500 | 4.0% |
| 10 | Revenue ($000s) | $285,600 | $263,340 | ($22,260) | (7.8%) |
| 11 | Variable costs ($000s) | $172,200 | $171,570 | ($630) | (0.4%) |
| 12 | Contribution ($000s) | $113,400 | $91,770 | ($21,630) | (19.1%) |
| 13 | Contribution per unit ($) | $27.0 | $23.0 | ($4.0) | (14.8%) |
| 14 | Operating profit ($000s) | $51,400 | $27,270 | ($24,130) | (46.9%) |
| 15 | Operating margin | 18.0% | 10.4% | (7.6%) | (42.5%) |
| 16 | Breakeven units (000s) | 2,296 | 2,804 | 508 | 22.1% |
| 17 | – | – | – | – | – |
| 18 | – | – | – | – | – |
| 19 | Profit bridge — how much of the swing each driver explains ($000s) | – | – | – | – |
| 20 | Volume effect | ($5,670) | 23.5% | – | – |
| 21 | Price effect | ($7,980) | 33.1% | – | – |
| 22 | Variable cost effect | ($7,980) | 33.1% | – | – |
| 23 | Fixed cost effect | ($2,500) | 10.4% | – | – |
| 24 | – | – | – | – | – |
| 25 | Total change in operating profit | ($24,130) | – | – | – |
| 26 | Check vs. tree (should be zero) | – | – | – | – |
| 27 | – | – | – | – | – |
| 28 | Say the driver out loud before you touch anything else. | ||||
Drills on this model
Answer out loud first, then change the cell and see how close you were.
- 1.Which single driver explains most of the profit fall? Read the bridge, then say it in one sentence.
- 2.How much would price have to rise, holding volume, to recover last year's profit?
- 3.Volume is down 5% while fixed costs rose 4%. What does that do to breakeven, and how much cushion is left?
- 4.Assume the price cut bought back 8% of volume instead of losing 5%. Was the cut worth it?