← Financial Modeling in Excel from Scratch
Lesson
Practice: a timeline, absolute references, and a check flag
Build a forecast row on a timeline of years with an absolute reference to an assumption, calculate the total with the SUM function, and add a check flag with IF.
A forecast row on a timeline of years
One formula per row: filling across, $, and a check flag
What we're calculating: revenue by year. The years run across the columns, and the assumption sits in its own cell above. An example with the numbers from lesson 1.2: B1 holds 5% growth, and B3 holds first-year revenue of 1,000. Next year's revenue in C3: =B3*(1+$B$1), result 1,050. Fill the formula to the right, and D3 gets =C3*(1+$B$1) by itself, result 1,102.5: the reference to the previous year moved, the reference to the assumption stayed put.
The $ sign locks part of a reference:
• $B$1 — both the column and the row;
• $B1 — only the column, which is enough for filling to the right;
• B$1 — only the row: filled to the right, the reference becomes C$1.
Without $, the reference B1 in D3 turns into C1 — an empty cell, so growth becomes zero, and D3 shows 1,050 instead of 1,102.5.
The total for all the years is =SUM(B3:F3). A check flag is a formula that reports by itself whether the numbers tie out: =IF(condition,"OK","ERROR"). The first argument is the test, such as “sum of the quarters = annual revenue,” the second is what to show if it's true, the third is what to show if it isn't. Text in a formula goes in quotes.
Money amounts after division and exponents can differ by fractions of a cent, so they're compared with a tolerance: ABS(F8-B5)<0.01 — the ABS function takes the difference without its sign. Put the flag next to the totals so an error shows up right away.
Example after filling to the right
The reference to the previous year moves; the locked reference to the assumption stays on B1.
| Cell | With $ — correct | Without $ — error |
|---|---|---|
| C3 | =B3*(1+$B$1) → 1,050 | =B3*(1+B1) → 1,050 |
| D3 | =C3*(1+$B$1) → 1,102.5 | =C3*(1+C1) → 1,050C1 is empty — growth 0 |
Lesson notes
One formula per row: filling across, $, and a check flag
What we're calculating: revenue by year. The years run across the columns, and the assumption sits in its own cell above. An example with the numbers from lesson 1.2: B1 holds 5% growth, and B3 holds first-year revenue of 1,000. Next year's revenue in C3: =B3*(1+$B$1), result 1,050. Fill the formula to the right, and D3 gets =C3*(1+$B$1) by itself, result 1,102.5: the reference to the previous year moved, the reference to the assumption stayed put.
The $ sign locks part of a reference:
• $B$1 — both the column and the row;
• $B1 — only the column, which is enough for filling to the right;
• B$1 — only the row: filled to the right, the reference becomes C$1.
Without $, the reference B1 in D3 turns into C1 — an empty cell, so growth becomes zero, and D3 shows 1,050 instead of 1,102.5.
The total for all the years is =SUM(B3:F3). A check flag is a formula that reports by itself whether the numbers tie out: =IF(condition,"OK","ERROR"). The first argument is the test, such as “sum of the quarters = annual revenue,” the second is what to show if it's true, the third is what to show if it isn't. Text in a formula goes in quotes.
Money amounts after division and exponents can differ by fractions of a cent, so they're compared with a tolerance: ABS(F8-B5)<0.01 — the ABS function takes the difference without its sign. Put the flag next to the totals so an error shows up right away.