Model Design Before Formulas: Build Financial Models Interviewers Can Trust
The board pack is due in an hour, the valuation swings wildly after one assumption changes, and nobody can tell whether the error is in revenue, working capital or debt. That is when you learn the real rule of financial modelling: the formula is rarely the first problem - the structure is.
- Model design means planning the workbook structure, logic, controls and conventions before writing formulas.
- The clean mental model is question - drivers - assumptions - schedules - outputs - checks.
- Keep inputs, calculations and outputs separate; never bury assumptions inside formulas.
- Use consistent conventions: one formula per row, left-to-right time flow, colour coding, clear units and visible checks.
- A good model is flexible, auditable, consistent and decision-oriented, not just mathematically correct.
- Before trusting a model, check balance sheet imbalance, error count, hardcoded formulas, broken links and duplicated assumptions.
- The interview trap: candidates start with Excel functions instead of explaining the model architecture.
The Big Picture: A Model Is a Decision Machine, Not a Spreadsheet
A financial model converts a business question into a structured chain of assumptions, calculations and outputs. The discipline is to make that chain visible, so another person can audit it, change it and trust it.
The Core Idea: Design Is the Model's Operating System
Most weak models fail for four reasons: assumptions are hidden, formulas are inconsistent, outputs cannot be traced, and checks are missing. Model design prevents these failures before they enter Excel.
Think of model design as answering five questions before typing formulas:
The Model Design Pyramid: Build from Discipline to Decision
Do not start at the top of the pyramid. A pretty dashboard is useless if the assumptions are scattered and the calculations are not auditable. Build from the foundation upward.
The Six-Step Process to Design a Model Before Formulas
The Workbook Structure That Interviewers Expect
There is no single compulsory template, but strong finance teams usually separate the model by function. This makes the workbook easier to review and reduces accidental edits.
Conventions: Small Rules That Prevent Big Errors
Conventions are not cosmetic. They tell a reviewer what can be changed, what is calculated, what comes from another sheet and what must be investigated.
Worked Example: One Growth Assumption, Two Model Designs
Suppose FY24 revenue is 100. You expect volume to grow 8% and price to grow 3% in FY25. The mathematics is simple: FY25 revenue = 100 × 1.08 × 1.03 = 111.24. The design choice is the real issue.
The interview insight: the same number can be produced by a poor model and a good model. The good model makes the number traceable.
Quality Checks: What to Measure Before You Trust the Model
Model quality is not a feeling. Before using outputs, measure whether the workbook is internally consistent and reviewable.
Definitions You Should Be Able to Say Cleanly
- Financial model: A structured calculation tool that forecasts financial outcomes from business assumptions and operating drivers.
- Model design: The planned structure, logic, conventions and controls that make a model understandable, flexible and auditable.
- Assumption: An editable input value used by formulas to calculate future outcomes.
- Driver: A business variable that causally explains another number, such as price, volume, churn, utilization or store count.
- Audit trail: The visible path showing where an output came from and which assumptions control it.
- FAST principle: A modelling standard idea that models should be flexible, appropriate, structured and transparent.
Trent's Zudio: Model Design for a Store-Rollout Business
Trent's Zudio shows why a retail model should be built around store rollout, unit economics and inventory discipline - not one blended revenue-growth assumption.
Trent, part of the Tata Group, has drawn investor attention because of Zudio's rapid value-fashion expansion in India. A lazy model would forecast revenue by applying one overall growth rate. That misses the actual economics of the business.
The stronger model begins with store count and store maturity. A new store does not behave like a mature store. Sales productivity, gross margin, rental cost, inventory intensity and staff cost can vary by format, city and maturity. So the model needs separate schedules for store openings, same-store sales, gross margin, operating expenses, working capital and capex.
The primary driver in this case is the repeatable value-fashion store model and rollout engine. Supporting drivers include sharp price architecture, local sourcing, assortment discipline, inventory turns and format-level cost control. The lesson for modelling is powerful: when the business model is operationally specific, the financial model must be driver-specific.
How AI Changes Model Design Before Formulas
AI does not remove the need for modelling discipline. It raises the standard because it can accelerate structure, documentation and auditing - while also creating new risks if you accept outputs blindly.
- Driver-tree drafting: Tools like ChatGPT or Claude can convert an annual report, investor presentation or business description into a first-pass driver tree. For a retailer, it may suggest store count, same-store sales, gross margin, rent and inventory turns.
- Formula and logic audit: AI copilots can help identify inconsistent formulas, hardcoded constants, missing checks and unclear sheet names. The human modeller must still verify every flagged issue.
- Scenario design: AI can propose base, upside and downside scenarios from business risks, but assumptions must be tied to real evidence such as filings, management commentary or market data.
Use NotebookLM or Claude before opening Excel: upload the company's annual report or investor presentation, ask for a driver tree, sheet architecture and audit checklist, then build only after reviewing the logic yourself. Do not upload confidential company data.
Interview Relevance
Question: “If I ask you to build a financial model for a company, what would you do before writing formulas?”
Use one sentence that signals maturity: “I would first design the model so every key output can be traced back to a visible business driver.”
Common Mistake
The biggest mistake is starting with formulas and Excel functions before explaining the model architecture. It costs candidates because interviewers hear technical activity, not modelling judgement. One-line fix: start with the decision, driver tree, sheet structure and checks - then talk formulas.
What to Revise Next
Once the design discipline is clear, move from structure to execution. Revise Excel for Finance: The Functions & Shortcuts Used Every Day to build faster, then study Building the Assumptions & Driver Sheet That Runs the Model to make your inputs interview-ready.