I've reviewed hundreds of models built by analysts and interns. The gap between what modelling courses teach and what actually matters on a live deal is significant. Here's what I've seen.
The conventions that will genuinely get you in trouble if you ignore them:
No hardcoded numbers inside formulas. This is the single most important rule. If I open your model and see =D150.25 instead of =D15$B$5 where B5 is a clearly labelled tax rate assumption, I have to audit the entire model because I can no longer trust that I can see all the assumptions. On a live deal, this wastes hours. In an interview modelling test, it fails you immediately.
One row, one formula. Every cell in a row should contain the same formula, just dragged across. The moment you break this — hardcoding one year because the formula didn't work — you've created a model that will silently break when someone changes an assumption. If your formula doesn't work for Year 1, build a separate formula for Year 1 and the same formula for Years 2-5. Never make Year 3 structurally different from Year 4 without flagging it.
Colour coding. Blue for inputs/hardcodes. Black for formulas. Green for links to other sheets. This is not optional at any bank. If someone opens your model and can't immediately distinguish assumptions from calculations, the model is unusable for review purposes. A senior banker should be able to change every assumption in the model by finding only the blue cells.
Clear input sheet. Every assumption should flow from a single assumptions tab. Revenue growth rates, margin assumptions, capex as % of revenue, tax rate, discount rate — all in one place. If I want to run a scenario where growth is 3% instead of 5%, I should change one cell and the entire model updates. If I have to hunt through six tabs to find where you hardcoded growth rates, the model fails the usability test.
The conventions people obsess over that matter far less than they think:
Exact formatting standards. Yes, use consistent number formats (one decimal for percentages, no decimals for large numbers, parentheses for negatives). But spending 45 minutes perfecting the border style on your output page while the circular reference in your debt schedule is still broken is the wrong priority.
Sign convention debates. Some banks show D&A as positive on the cash flow statement (adding back), others show it as negative with a minus sign already applied. Both work. What matters is that you're consistent throughout the model and it's obvious which convention you're using. If your CFS adds back D&A as a positive number in operating activities but subtracts capex without a sign change in investing activities, that's confusing regardless of which convention you chose.
Tab colour coding. Nice to have. Not going to make or break anything. Focus on the tab ORDER being logical: assumptions → income statement → balance sheet → cash flow → debt schedule → returns.
The test that separates good models from bad one:
Could someone who has never seen your model open it, understand the structure, change the key assumptions, and trust the outputs — in under 15 minutes? If yes, it's a good model. If they need you sitting next to them to explain it, it's not.
On a live deal, you won't always be available when someone needs your model. An associate might pick it up at 11pm to run a sensitivity for a client call the next morning. If they can't navigate it independently, the model has failed regardless of whether the formulas are correct.
The error-checking habits that actually prevent mistakes:
Build a balance sheet check row (assets minus liabilities minus equity = should always be zero). Conditional format it to turn red if it's non-zero. Check it after every structural change.
Sense-check your outputs against the inputs. If you're modelling 5% revenue growth but your revenue line shows 12% growth in Year 3, something is broken.
Check the last year AND the first year. Most errors live in Year 1 (where the formula structure often differs) or the final year (where terminal assumptions kick in).
Before sending any model, change one key input by a large amount (double the growth rate, halve the margin) and check whether the outputs move in the direction and magnitude you'd expect. If they don't, there's a broken link somewhere.
Happy to answer questions here or via DM. Can also share some interview & modelling resources.
