Excel Modeling Conventions

A financial model is only as useful as it is understandable, auditable, and error-resistant. Most financial models in Indian equity research, investment banking, and corporate finance are built in Excel. The difference between a model that is a robust analytical tool and one that is a black box prone to errors often comes down to one thing: whether the builder followed consistent conventions.

Conventions create a shared language. When a colleague opens a model with blue-coloured inputs and black-coloured formulas, they immediately know what to trust and what to edit. When every assumption is in a clearly labelled inputs section, auditing is fast. When formulas only reference other cells rather than hard-coding numbers in the middle of a formula, errors are easy to find.

Building models with discipline from day one, even for simple analyses, is one of the highest-leverage skills a finance professional can develop. Bad model hygiene is the source of a disproportionate share of costly errors.

Core conventions:

Colour coding (industry standard):

• Blue: Hard-coded inputs (numbers typed directly)
• Black: Formulas (derived from other cells)
• Green: Links to other sheets/workbooks
• Red: Error checks or flag conditions

Never hard-code in formulas:

• Wrong: =A5*0.25 (the 25% is invisible and unauditable)
• Right: Put 25% in a named input cell and reference it: =A5*$B$3

Structure:

• Inputs tab → separate from calculations → separate from outputs
• Each section clearly labelled with headers
• Time flows left to right (columns = years/quarters)
• Line items flow top to bottom in logical order (P&L → Balance Sheet → Cash Flow)

Circular references:

• Avoid where possible. When unavoidable (WACC depends on debt balance, which depends on WACC), handle via iterative calculation (Excel Options → Enable Iterative Calculation) with a maximum iteration count
• Always flag circulars explicitly

Error checks:

• Balance sheet check: Assets − Liabilities − Equity = 0 (flag in red if not)
• Cash check: Opening cash + net cash flow = Closing cash
• Include a dedicated checks row that is green when correct, red when off

Documentation:

• Every assumption should have a source (analyst judgment, Bloomberg, company guidance)
• Date-stamp the model
• Version control the file name (Model_v1.0, Model_v2.3)