Valuation Model in Excel: Build It Cell by Cell and Defend Sensitivity Tables in Interviews
A valuation model does not become “advanced” because it has 15 tabs, macros, and a dark blue theme. It becomes useful when one small assumption - growth, margin, WACC, exit multiple - can be traced cell by cell to the final equity value without breaking the logic.
- A valuation model is a linked financial logic engine: assumptions drive forecasts, forecasts drive cash flows, cash flows drive enterprise value, and adjustments drive equity value.
- The most common interview model is a DCF: project free cash flow, discount it using WACC, add terminal value, subtract net debt, divide by shares.
- Build cell by cell in this order: historicals, operating assumptions, financial statements, free cash flow, discount rate, terminal value, equity bridge, sensitivity tables.
- Sensitivity tables do not “prove” a valuation. They show how fragile or robust the valuation is when key assumptions change.
- The two most important sensitivity drivers are usually WACC and terminal growth or exit multiple, because terminal value often dominates DCF value.
- A good model separates hardcoded inputs from formulas, uses consistent signs, and has checks for balance sheet balance, cash flow links, and circularity.
- In interviews, explain the model like a story: business drivers first, Excel mechanics second, valuation output last.
The Big Picture: A Valuation Model Is a Linked Logic Machine
Think of valuation as a chain, not a single formula. If revenue growth, margins, reinvestment, tax, and risk are internally consistent, the output earns trust; if one assumption floats unsupported, the final price target is just spreadsheet decoration.
Core Explanation: How to Build the Model Cell by Cell
The safest way to build a valuation model is to move from business reality to Excel output. Do not start with the discount rate. Start with how the company actually makes money.
The Model Blocks You Must Know
A clean model has separate blocks. In interviews, naming these blocks shows that you understand both finance and spreadsheet hygiene.
The Cell Logic: What Each Formula Is Really Doing
Excel formulas should mirror business logic. For example, revenue should not be a random hardcode in Year 3; it should normally be last year's revenue multiplied by one plus growth. Capex should not float independently unless there is a reason; it should often connect to revenue, asset base, or capacity plans.
The Valuation Loop: Build, Check, Interpret, Refine
Real modeling is not linear. You build the first pass, the checks fail, the implied valuation looks unreasonable, and then you refine the assumptions. That loop is not a weakness; it is how good analysts remove errors and force the model to match business reality.
The Metrics and Assumptions That Matter Most
Valuation is assumption-sensitive. A good answer names the exact drivers, formula, and sanity range instead of saying “I will change some assumptions.” Ranges below are broad interview sanity checks; the right number must always be benchmarked to the industry, company maturity, and macro environment.
Worked Example: A Simple DCF With a Sensitivity Table
Use this as your mental template. These are hypothetical numbers for learning, not a recommendation for any company.
Step 1 - Forecast FCFF: Assume free cash flow to firm is 100 in Year 1, 115 in Year 2, and 132 in Year 3.
Step 2 - Choose WACC and terminal growth: Base case WACC is 11 percent. Terminal growth is 4 percent.
Step 3 - Calculate terminal value: Terminal value = Year 3 FCFF x (1 + g) / (WACC - g) = 132 x 1.04 / (0.11 - 0.04) = 1,961.
Step 4 - Discount cash flows: PV of Year 1, Year 2, Year 3 FCFF, and terminal value is approximately 1,714 enterprise value.
Step 5 - Bridge to equity: If net debt is 200 and shares are 100, equity value per share is approximately (1,714 - 200) / 100 = 15.14.
The base case value is 15.14, but the defensible range could be much wider. That is the point of a sensitivity table: it shows the value is not a fixed truth; it is a range driven by assumptions.
Definitions You Must Be Able to Say
- DCF valuation: “The value of any asset is the present value of the expected cash flows on that asset.” - Aswath Damodaran
- Free cash flow to firm: Cash flow available to all capital providers after taxes, reinvestment, and working capital needs.
- WACC: The blended required return of debt and equity providers, weighted by their share in capital structure.
- Terminal value: The value of cash flows beyond the explicit forecast period.
- Sensitivity analysis: A method that shows how valuation output changes when one or more key assumptions change.
Tata Technologies: Sensitivity Discipline in a High-Expectation IPO
Tata Technologies shows why valuation models need sensitivity tables when growth expectations, cyclicality, and peer multiples all matter at once.
Situation: Tata Technologies, an Indian engineering and product development services company, came to public markets in 2023 amid strong investor interest in automotive engineering, electric mobility, and outsourced R&D services. The story was attractive, but not risk-free: revenue visibility, customer concentration, auto-cycle exposure, margin sustainability, and global engineering demand all affected fair value.
The move: A serious valuation model for such a company should not rely on one aggressive revenue growth number or one peer multiple. The model must connect growth to client demand, margins to utilisation and talent cost, reinvestment to scaling needs, and discount rate to business risk. Then it should run sensitivities around WACC, terminal growth, margin, and peer multiple.
Outcome or lesson: The lesson is not that the stock was “cheap” or “expensive” in one universal sense. The lesson is that IPO valuation requires a range. The primary driver of value was expected growth in engineering services, especially linked to mobility and digital product development. Supporting drivers included brand trust from the Tata ecosystem, client relationships, operating margins, and public-market demand for differentiated engineering services companies.
So what: A one-cell valuation would miss the story. A cell-by-cell model forces the analyst to ask: what exactly must go right for the implied value to make sense?
How AI Changes Valuation Models and Sensitivity Tables
AI does not replace valuation judgment; it compresses the time spent collecting information, checking formulas, and generating scenarios. The analyst still owns the assumptions.
- Faster source extraction: AI tools can summarise annual reports, investor presentations, concall transcripts, and risk factors so you can identify revenue drivers, margin pressures, capex plans, and debt changes faster.
- Scenario generation: AI can help draft base, bear, and bull cases by linking assumptions to real business triggers - for example, lower utilisation, higher wage inflation, delayed capacity expansion, or stronger pricing.
- Model audit support: LLMs can review formula logic, identify inconsistent signs, flag hardcoded values inside formulas, and suggest checks. They should not be trusted blindly for final numbers.
Load the company annual report, investor presentation, and your valuation notes into NotebookLM. Ask: “Create a base, bear, and bull DCF assumption set for revenue growth, EBITDA margin, capex intensity, working capital, WACC, and terminal growth. For each assumption, cite the document section that supports it.” Then build the model yourself in Excel and verify every cited source.
Interview Relevance
“Walk me through how you would build a valuation model in Excel for a listed Indian company. Where would you use sensitivity tables?”
Use the phrase “I would first make the operating model defensible, then let valuation follow.” It signals that you are not treating DCF as a mechanical Excel exercise.
Common Mistake
The mistake that costs candidates is building sensitivity tables around random variables while the base model itself is not logically linked. Interviewers catch this quickly because the model looks sophisticated but cannot answer “why does this assumption move value?” The one-line fix: first build a clean driver-based model, then sensitize only the assumptions that genuinely control value.
What to Revise Next
Once you can build and defend a valuation model, move to transaction models where valuation becomes an investor return or earnings-impact question.