Here is the mistake that has blown up more real models than any other, and it leaves no error indicator whatsoever. A projection row (revenue across ten years, say) should be one formula, written once and filled across. Cell D10 is =C10*(1+D$4), E10 is =D10*(1+E$4), and so on: the same relationship in every period. The catastrophe is when someone, fixing a number under deadline pressure, types a hardcoded value or a different formula into one cell in the middle of the row: G10 becomes a plugged 1200000 instead of =F10*(1+G$4). Now the row looks continuous, every cell shows a plausible number, and the model is silently wrong from that column onward, because every later period compounds off the broken cell.
Why it is so deadly: Excel flags a #REF!, a #DIV/0!, a circular reference, but it will never warn you that one cell in a row disagrees with its neighbours. The row is visually seamless. The only defences are discipline (never type into a calculation row; change the input that drives it) and detection (a tool that compares each cell in a row to its neighbours).
Advertisement
How do you find and prevent inconsistent formulas?
Detection, keyboard-first. Two professional habits. First, Show Formulas (Ctrl+ the grave accent, or Formulas ▸ Show Formulas, Alt, M, H) flips the whole sheet to display formulas instead of results, run your eye across a row and an inconsistent cell leaps out because its formula *looks different.* Second, **Go To Special ▸ Row differences**: select the row, F5 ▸ Alt+S` ▸ Row differences, and Excel selects every cell that differs from the reference cell's pattern, the lone plug lights up instantly. The model-checker add-in from EX6.04 automates exactly this scan across an entire model. Build the reflex now: a projection row is one formula; if one cell disagrees with its neighbours, that is a bug until proven otherwise.
Prevention, keyboard-first. Enter a row once and fill it, type the formula in the first period, then select the row and Ctrl+R (Fill Right) so every cell is provably identical. When an assumption changes, change the blue input cell it points to, never the black formula. If a single period genuinely needs a different treatment (a one-off charge in Year 3), don't plug it into the row, add an explicit, labelled, blue input line for it and reference that, so the exception is visible and documented rather than buried.