Level 1: Model basics · Exercise 11 of 60 · Pro

CAGR and an index rebased to 100

A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.

The task

Six years of revenue are in row 5. Fill row 6 with an index that sets the first year to 100 and scales every year to it (write B6 once with a locked reference, then fill right). B8 already counts the years of growth. In B9, work out the compound annual growth rate: last year divided by first year, raised to the power of 1 over the years of growth, minus 1.

What this exercise teaches

An index strips out scale so two series of different size can be compared on one chart. The compound rate answers a different question than the average of the yearly growth rates, which overstates growth when the yearly rates bounce around.

A hint if you get stuck

The index divides every year by the same first year, so lock it: $B$5. For the growth rate, raising to the power of 1/B8 is the same as taking the B8-th root.