How to Build a Valuation Model From Scratch

By Nazli Hangeldiyeva

Building a valuation model is where the theory of stock analysis becomes a practical tool. A model is not a crystal ball; it is a structured framework for organizing assumptions about a company's future and translating those assumptions into an estimated value. The precision of the output depends entirely on the quality of the inputs and the logical coherence of the assumptions connecting them.

Professional analysts at Goldman Sachs, Morgan Stanley, and Lazard build models for every company they cover. The models follow a standard architecture: historical financial data feeds into projected financial statements, which feed into a DCF or other valuation framework. The architecture is not proprietary or secret. What distinguishes a good model from a bad one is the analyst's judgment about growth rates, margins, capital intensity, and risk, not the mechanics of the spreadsheet itself.

This walkthrough covers the process of building a complete three-statement financial model linked to a DCF valuation. The example company is a mid-cap industrial manufacturer, but the framework applies, with modifications, to companies in any sector.

Step 1: Gather Historical Data

Start by downloading or inputting five years of historical financial data from the company's 10-K filings. The three financial statements, income statement, balance sheet, and cash flow statement, provide the raw material. Organize the data in a spreadsheet with each year in a separate column, flowing left to right from oldest to newest.

Key historical data points:

• Revenue (total and by segment if available)
• Cost of goods sold
• Gross profit
• Operating expenses by category (SG&A, R&D, D&A)
• Operating income (EBIT)
• Interest expense
• Income tax expense and effective tax rate
• Net income
• Total assets, liabilities, and equity by major line item
• Operating cash flow, capital expenditures, and free cash flow
• Shares outstanding (basic and diluted)

Compute historical ratios and margins: gross margin, operating margin, net margin, return on equity, return on invested capital, revenue growth rate, and free cash flow conversion (FCF/Net Income). These ratios reveal trends and provide the foundation for projection assumptions.

Step 2: Build Revenue Projections

Revenue is the most important and most uncertain projection. Build it from the bottom up when possible, using the company's specific revenue drivers.

For a manufacturing company, revenue drivers might include:

• Addressable market size and growth rate
• Market share trends
• Pricing trends (inflation, competitive dynamics)
• New product introductions
• Geographic expansion

For a SaaS company, the drivers would be different: number of customers, average contract value, net retention rate, and new bookings.

Project revenue for 5-7 years. Start with the company's recent growth trajectory and management guidance, then adjust based on industry analysis and judgment about sustainability. Revenue growth should generally decelerate over time as the company matures and the base grows larger.

Segment-level projections are more reliable than consolidated projections for diversified companies. A company with a 15%-growth industrial automation segment and a 3%-growth legacy services segment should be modeled at the segment level, not with a blended growth assumption.

Step 3: Project the Income Statement

With revenue projected, build out the income statement line by line:

Cost of goods sold. Project as a percentage of revenue (gross margin assumption). Examine historical trends. Is gross margin expanding (operating leverage, pricing power, mix shift toward higher-margin products) or compressing (input cost inflation, competition, mix shift toward lower-margin products)?

SG&A expenses. For many companies, SG&A has a fixed component (corporate overhead, facilities) and a variable component (sales commissions, marketing). Model the fixed portion in dollar terms and the variable portion as a percentage of revenue. As revenue grows, SG&A as a percentage of revenue should decline modestly, reflecting operating leverage on the fixed costs.

Research and development. For technology and pharmaceutical companies, R&D is a critical expense. Project it as a percentage of revenue or in absolute terms based on the company's stated investment plans and competitive requirements.

Depreciation and amortization. Project based on the existing asset base (depreciating over remaining useful lives) plus depreciation on projected new capital expenditures. A simpler approach uses D&A as a percentage of prior-year gross PP&E.

Interest expense. Calculated from the projected debt balance and the company's weighted average interest rate. If the model projects debt repayment, interest expense should decline over time. If the model includes new borrowing, it increases.

Income taxes. Apply the company's sustainable effective tax rate to pre-tax income. Use the statutory rate (approximately 21% in the U.S.) unless the company has structural reasons for a different rate (tax credits, foreign income in lower-tax jurisdictions, deferred tax assets).

The result is projected net income for each year.

Step 4: Project the Balance Sheet

The balance sheet links the income statement to the cash flow statement. Key balance sheet items to project:

Accounts receivable. Project using days sales outstanding (DSO): (AR / Revenue) x 365. If historical DSO is stable at 45 days, project AR as Revenue x (45/365) for each year.

Inventory. Project using days inventory outstanding (DIO): (Inventory / COGS) x 365. A manufacturing company with 60 days of inventory maintains that ratio as revenue grows.

Accounts payable. Project using days payable outstanding (DPO): (AP / COGS) x 365. A company that pays suppliers in 40 days maintains that relationship.

Property, plant, and equipment. Beginning PP&E + capital expenditures - depreciation = ending PP&E. Capital expenditures are projected based on the company's growth requirements, capacity utilization, and management's capex guidance.

Debt. Project based on current maturity schedules (found in the notes to the financial statements) and assumptions about new borrowing or repayment. Most models assume the company maintains a target capital structure.

Retained earnings. Beginning retained earnings + net income - dividends = ending retained earnings.

Other balance sheet items (goodwill, intangible assets, other assets and liabilities) are typically held flat or adjusted for specific known items (planned acquisitions, scheduled debt maturities).

Check that the balance sheet balances (assets = liabilities + equity) in every projected year. If it does not, there is an error in the model. The cash balance typically serves as the "plug" that makes the balance sheet balance.

Step 5: Project the Cash Flow Statement

The cash flow statement can be built indirectly from the income statement and balance sheet projections:

Operating cash flow:

• Start with projected net income
• Add back depreciation and amortization
• Subtract/add changes in working capital (AR, inventory, AP, other current items)
• Adjust for other non-cash items (stock-based compensation, deferred taxes)

Investing cash flow:

• Capital expenditures (already projected for the balance sheet)
• Acquisitions (if modeled)

Financing cash flow:

• Debt issuance or repayment
• Dividends paid
• Share repurchases (if modeled)
• Stock issuance proceeds

Free cash flow to the firm (FCFF):

• EBIT x (1 - tax rate) + D&A - capex - change in net working capital

This FCFF figure is the input to the DCF valuation.

Step 6: Build the DCF

With projected free cash flows in hand, the DCF calculation is straightforward:

1. Determine the discount rate. Calculate WACC using the company's cost of equity (from CAPM), cost of debt (from the yield on its bonds or borrowing rate), and target capital structure.

2. Discount projected free cash flows. For each projected year, discount FCFF back to the present: PV = FCFF / (1 + WACC)^t.

3. Calculate terminal value. Using either the perpetuity growth method (FCF in final year x (1+g) / (WACC - g)) or the exit multiple method (final year EBITDA x exit EV/EBITDA multiple).

4. Discount the terminal value. PV of terminal value = TV / (1 + WACC)^n.

5. Sum to get enterprise value. PV of projected FCFs + PV of terminal value = enterprise value.

6. Bridge to equity value. Enterprise value - net debt - minority interest + equity investments = equity value.

7. Calculate intrinsic value per share. Equity value / diluted shares outstanding.

Step 7: Run Sensitivity Analysis

No model output should be presented as a single number. The implied value is a function of assumptions, and assumptions carry uncertainty. Build a sensitivity table that shows how the implied value changes across a range of key assumptions.

The two most common sensitivity axes are:

• WACC (7% to 11%, in 0.5% increments) on one axis
• Terminal growth rate (1% to 4%, in 0.5% increments) on the other axis

This produces a matrix of implied values that defines a valuation range. If the matrix shows a range of $45 to $75 per share and the stock trades at $40, there is a margin of safety across most assumptions. If the stock trades at $65, the investment case depends on assumptions at the optimistic end of the range.

Additional sensitivity scenarios might test:

• Revenue growth rate +/- 2%
• Operating margin +/- 200 basis points
• Terminal multiple +/- 2.0x

Step 8: Cross-Check With Other Methods

A DCF model should never be used in isolation. Compare its output to:

• Comparable company analysis. Does the implied EV/EBITDA from the DCF fall within the range of peer multiples? If the DCF implies 18x EBITDA and peers trade at 10-12x, either the DCF assumptions are too aggressive or the market is undervaluing the stock.

• Historical valuation range. Has the company historically traded at a P/E of 15-20x? If the DCF implies a price consistent with 25x, the model may be too optimistic unless there is a clear reason for multiple expansion.

• Precedent transactions. If comparable companies have been acquired at 12-14x EBITDA, the DCF should produce a value in a similar neighborhood for a takeout scenario.

Convergence across methods increases confidence. Divergence demands investigation into why the methods disagree.

Model Maintenance

A model is not a one-time exercise. It should be updated quarterly as new financial data becomes available, assumptions are revised, and the competitive environment shifts. After each earnings report, update the historical data, compare actual results to the model's projections, and adjust forward assumptions where warranted.

The discipline of maintaining a model forces the analyst to confront whether their thesis is playing out. If the model projected 10% revenue growth and the company delivered 5% for two consecutive quarters, the assumptions need revision. This feedback loop between model and reality is what makes modeling a continuous learning process rather than a static calculation.

Written by

Nazli Hangeldiyeva

Co-Founder of Grid Oasis. Political Science & International Relations, Istanbul Medipol University.

View full profile →