Professional modeling conventions look fussy until you audit your first broken workbook at 11 p.m. Every rule below exists because breaking it is a documented, recurring cause of real losses. The London Whale incident at JPMorgan in 2012 famously involved a risk model whose spreadsheet errors understated risk. Values had been pasted over formulas, and a sum was used where an average belonged. TransAlta lost ~$24m in 2003 to a copy-paste error in a bidding spreadsheet. Public studies of operational spreadsheets, Raymond Panko's research at the University of Hawaii among them, repeatedly find material error rates in a large share of audited workbooks. Think of the rules as your seatbelt.
How should you lay out a financial model?
Rule 1. Separate inputs, calculations, and outputs. Three tabs, named short:
| Tab | Name | Contains | Who may type here |
|---|---|---|---|
| Inputs | A | Scenario switch, driver blocks (base/bull/bear), structural inputs, historical driver ratios (computed) | You. This is the only tab where assumptions are typed |
| Calculations | M | Typed historical statements, all schedules, the three statements | Typed history in the historical columns; formulas everywhere else |
| Outputs & checks | X | The error-check panel, master status cell, summary output table | Nobody: every cell is a link or formula |
Advertisement
Small models earn a fourth tab, README. It's one screen stating the company, units (₹ crore), fiscal-year convention, color code, the circularity policy, and the date of the source filings. That's ninety seconds of writing, and it's the difference between a model and a puzzle when you or a colleague reopen the file in eight months. In the larger models still to come, each schedule graduates to its own tab. The three-layer logic never changes. Only the number of sheets it's spread across does.
Rule 2. One timeline, same column everywhere. Every tab uses the identical grid: column A = row label, column B = units, columns C through J = FY23A, FY24A, FY25A, FY26E, FY27E, FY28E, FY29E, FY30E. FY26 is column F on every tab, forever. That's what makes cross-sheet formulas fillable. =A!F15 dragged right becomes =A!G15, and because both tabs share the grid, it's always the same year talking to the same year. Near the top of each tab, add a flag row with A or E (actual/estimate) per column. It marks the frontier between fact and forecast.
How do you keep formulas consistent and easy to audit?
Rule 3. The color code. Blue font = a typed number (an input or a historical fact). Black font = a formula computing within its own sheet. Green font = a formula pulling from another sheet. The full institutional code adds red for links to other workbooks, which you should never do, and yellow cell-fill for temporary plugs, of which your finished model must contain zero. Treat the code as an audit instrument. Open any professional model, select all, and you can see the input layer glowing blue. A blue cell on the M tab outside the historical columns means something is wrong, by definition.
Rule 4. One formula per row. Write the formula once in the first forecast column, then fill right to the last. Every cell in a row is the same logic applied to a different year. The moment one year's cell differs from its neighbors, the row becomes unauditable, and you now have to read eight formulas instead of one. There's one sanctioned exception, the actual/estimate boundary. Historical columns hold typed facts, forecast columns hold the formula, and the flag row from Rule 2 marks where the switch happens. You'll meet two rows in this build where the historical cells are typed and the forecast cells calculate, and both are flagged when we get there. The build is engineered so that even the roll-forward "opening balance" rows obey the rule. You'll see the trick when you reach the PP&E corkscrew.
Rule 5. No hardcodes. A hardcode is a number typed inside a formula: =E7*1.1, =F19*0.25, =F25/365. Each one is an assumption that has gone into hiding. The scenario toggle can't see it, an auditor can't see it, and nobody can change it except by archaeology. Here's the test. If a number could ever be different, it lives in a blue input cell and the formula references it. That includes the tax rate and the minimum cash balance. It also includes the days-in-year convention, which is 365 here, but some Indian credit analysts use 360, so it belongs in an input cell as well. The only naked numbers allowed inside formulas are structural constants of arithmetic and logic: 0, 1, −1 (as in =F7/E7-1 for a growth rate, or MAX against 0). When in doubt, promote it to an input.
What conventions should every model follow?
Rule 6. Signs and units, decided once. State the model's conventions once and never vary them. All figures are ₹ crore. One decimal is displayed and full precision is retained. Round the display, never the actual values. Revenues, assets, and cash inflows are positive. Costs show as positive line items that formulas subtract, so COGS is +798.6 and the gross-profit formula does the subtracting. Cash outflows on the CFS carry their natural negative sign (capex −73.2, dividends −31.2). Mixed sign conventions are the most common source of "off by exactly 2×" check failures, and the fault-trace debugging table exploits this.
Rule 7. Nothing invisible, nothing merged. No hidden rows or columns, because hide-and-forget is how errors survive audits. Use grouping if you must collapse something. No merged cells, ever. They break fill-right, sorting, and your sanity. No formatting-as-data either. A cell being red must never be the only place a fact lives.
Do these rules work in Google Sheets and LibreOffice?
Dialect box: the three spreadsheet programs. Every formula below runs unmodified in Excel, Google Sheets, and LibreOffice Calc, with three setup notes. (1) Cross-sheet syntax. Excel and Sheets use =A!F15. LibreOffice natively writes =A.F15, with a period where the others put a bang. You can work in the period style, or better, set Tools → Options → LibreOffice Calc → Formula → Formula syntax = Excel A1, and then every formula here pastes verbatim. (2) Iterative calculation, which you need only for the circularity work near the end. In Excel: File → Options → Formulas → tick Enable iterative calculation (max iterations 100, max change 0.001). In Sheets: File → Settings → Calculation → Iterative calculation → On (100 / 0.001). In LibreOffice: Tools → Options → LibreOffice Calc → Calculate → tick Iterations (steps 100, minimum change 0.001). Leave it off until the circularity section tells you otherwise. Switch iteration on early and it will "solve" the accidental circular references you want to crash loudly, and it won't tell you. (3) Percent entry. Typing 10% and typing 0.10 give the same number, and the assumptions here are written as percentages because they're easier to read.
Naming single control cells (the scenario switch, the days convention, minimum cash) makes formulas read like sentences (=F7*A!DSO/DaysYear). All three programs support names: Formulas → Name Manager in Excel, Data → Named ranges in Sheets, Sheet → Named Ranges in LibreOffice. This build uses plain addresses with $ anchors so that every formula is unambiguous on the page. Naming them afterward is an excellent fifteen-minute upgrade.