A model is easier to check and hand over when anyone can see at a glance what was typed and what is calculated. Most banks and finance teams use the same color code:
- Blue font: inputs, the numbers you type and might change, such as a growth rate.
- Black font: formulas that calculate on the same sheet.
- Green font: formulas that pull from another sheet, such as
=Inputs!B3.
Keep assumptions together on an Inputs sheet or in one block, and point formulas at them. Never type a number inside a formula, as in =C5*1.06. That's a hard-code: it hides an assumption no one can see or change.
Write one formula per row that works for every year, then fill it right with Ctrl+R. Years go in a header row: type the first year and add 1 for the rest.
When every year uses the same input, lock it with F4 (Cmd+T on a Mac) so it stays put as you fill: $B$3. When the input changes by year and sits in the same column, leave it relative. Coloring cells is tedious, and it's the first thing to slip under time pressure, so color as you go.