Modeling01
Excel for finance
The conventions below exist because financial models get handed between people under time pressure. They are not aesthetics. A model that follows them can be audited by a stranger in ten minutes, and a model that does not takes an afternoon and usually contains an error somebody eventually finds in front of a client.
Color, which carries information
| Means | Example | |
|---|---|---|
| Blue font | A hardcoded input you can change | Revenue growth of 3.0% |
| Black font | A formula calculated on this sheet | = Revenue x Margin |
| Green font | A link to another sheet in the same file | = Assumptions!B12 |
| Red font | A link to another file, which is fragile | = [Comps.xlsx]Sheet1!C4 |
The value is diagnostic. Scan a column: if it is all black, it is a calculation. If there is a blue cell in the middle of it, someone overrode the formula and probably forgot. That one visual check catches more errors than any formula audit.
Structure
- One row, one time period, consistent across every sheet. If column H is 2027 on the , it is 2027 everywhere. Models that shift periods between sheets produce errors that survive review because the formula looks right.
- Assumptions live in one place. A single sheet, or a clearly separated block at the top. Someone changing a driver should never have to hunt.
- No hardcodes inside formulas. Ever. If a number appears in a formula, it belongs in a cell.
- Flags, not IF trees. A row of 1s and 0s multiplied through beats nested conditionals, because you can see the switch.
- Left to right, top to bottom. A formula that references a cell below and to the right of itself is a model you will not be able to follow next month.
The shortcuts that get used
| Does | |
|---|---|
| F2 | Edit the cell and show its precedents highlighted |
| F4 | Cycle absolute and relative references while editing |
| Alt + = | Insert a SUM over the range above or to the left |
| Ctrl + [ | Jump to the cells this formula depends on |
| Ctrl + ] | Jump to the cells that depend on this one |
| Alt + E, S, V | Paste values only, killing a formula deliberately |
| Ctrl + Shift + arrow | Select to the end of a contiguous range |
| Ctrl + ` | Show every formula on the sheet at once |
Ctrl + [ is the one worth practicing first. Following backwards is how you actually understand a model somebody else built, and it is faster than reading formulas.
Error traps
Build these before you need them, because you will need them.
- A balance check row. Assets less liabilities and , on every forecast column, with conditional formatting that turns red on anything above a rounding threshold. This is not optional.
- A cash flow tie. Ending cash on the less the cash line on the .
- A summary of every check at the top of every sheet. One cell that reads OK or ERROR, so a broken model announces itself rather than waiting to be found.
- A circuit breaker. A single cell holding 1 or 0, multiplied through the circular part of the .
Test yourself
01Someone hands you a model. What do you check in the first two minutes?
Whether the balance sheet balances, whether there are hardcodes inside formulas, and whether the color convention is being followed. The first tells you if it works, the second tells you whether it will survive a changed assumption, and the third tells you how carefully it was built. A model with black numbers scattered through formula rows has been patched, and patches are where errors live.
02Why does a hardcode inside a formula matter so much?
Because it is invisible. A cell reading =B12*1.03 looks like a formula and contains an assumption nobody can find, change, or sensitize. When someone asks what happens at 5% growth, the model answers with the 3% still buried in it. Put every assumption in its own labeled cell and reference it.
03What does a circuit breaker do?
It is a switch, usually a single cell holding 1 or 0, that multiplies through the circular part of the model. Flip it to zero and the loop breaks, letting you clear a circular reference error without rebuilding. Any model with iterative calculation turned on needs one, because a circular model that hits an error state fills with zeros and stays there.
Every figure in this lesson that names a company comes from Target Corporation's Form 10-K for Fiscal 2025, the year ended 2026-01-31, filed 2026-03-11. Educational material, not investment advice.