Business valuation modeling involves using Excel to estimate the value of a company, typically by forecasting future performance and determining its intrinsic value. Common methods include Discounted Cash Flow (DCF), Comparable Company Analysis, and Precedent Transactions.
step-by-step guide, along with templates and formulas, to build a business valuation model in Excel.
The DCF method calculates the present value of projected cash flows, discounted at the company’s cost of capital.
This method involves comparing the target company to similar companies based on valuation multiples like EV/EBITDA, P/E, and EV/Revenue.
Similar to CCA, but uses multiples from past M&A transactions to value the target.
Valuations are based on the company’s net assets (Assets - Liabilities).
Create an Inputs/Assumptions sheet for key variables:
| Variable | Cell Reference | Value | Notes |
|-----------------------------|--------------------|-----------------|----------------------------------------|
| Revenue Growth Rate (%) | B2 | 10% | Annual growth rate for revenue. |
| EBITDA Margin (%) | B3 | 20% | Percentage of revenue. |
| Tax Rate (%) | B4 | 25% | Corporate tax rate. |
| Depreciation (% of Revenue) | B5 | 5% | Annual depreciation as a % of revenue. |
| Capital Expenditures (CapEx)| B6 | $50,000 | Fixed annual cost. |
| Working Capital (% Revenue) | B7 | 15% | Working capital as % of revenue. |
| Discount Rate (WACC) (%) | B8 | 10% | Weighted Average Cost of Capital. |
| Terminal Growth Rate (%) | B9 | 3% | Growth rate beyond projection period. |
Forecast the company’s financial performance over 5 years.
| Year | 1 | 2 | 3 | 4 | 5 |
|----------------------|----------------|----------------|----------------|----------------|----------------|
| Revenue ($) | =1000000 | =B2(1+B2) | =C2(1+B2) | =D2(1+B2) | =E2(1+B2) |
| EBITDA ($) | =B2B3 | =C2B3 | =D2B3 | =E2B3 | =F2B3 |
| Depreciation ($) | =B2B5 | =C2B5 | =D2B5 | =E2B5 | =F2B5 |
| EBIT ($) | =B4-B5 | =C4-C5 | =D4-D5 | =E4-E5 | =F4-F5 |
| Taxes ($) | =B6B4 | =C6C4 | =D6D4 | =E6E4 | =F6F4 |
| Net Income ($) | =B4-B6 | =C4-C6 | =D4-D6 | =E4-E6 | =F4-F6 |
| FCF ($)* | =B7+B8-B9 | =C7+C8-C9 | =D7+D8-D9 | =E7+E8-E9 | =F7+F8-F9 |
Calculate the company’s value at the end of the projection period using the Gordon Growth Model:
[
{Terminal Value} = \frac{{FCF}_{n+1}} / {{WACC} - {g}}
]
Formula in Excel:
excel
=F10*(1+$B$9)/($B$8-$B$9)
Discount each year’s Free Cash Flow (FCF) and the Terminal Value (TV) to present value using the discount rate (WACC):
| Year | 1 | 2 | 3 | 4 | 5 |
|----------------------|----------------|----------------|----------------|----------------|----------------|
| Discount Factor | =1/(1+$B$8)^1 | =1/(1+$B$8)^2 | =1/(1+$B$8)^3 | =1/(1+$B$8)^4 | =1/(1+$B$8)^5 |
| PV of FCF ($) | =B10B11 | =C10C11 | =D10D11 | =E10E11 | =F10F11 |
| PV of Terminal Value ($)* |
| | | | =Terminal Value * F11 |
Calculate the enterprise value (EV) by summing the present values of all cash flows and the terminal value.
Formula:
excel
=SUM(B12:F12)+PV of Terminal Value
Subtract net debt from the enterprise value to calculate the equity value.
| Metric | Value ($) |
|-----------------------|-----------------------|
| Enterprise Value (EV) | =SUM(PV of FCF + TV) |
| Net Debt | (Total Debt - Cash) |
| Equity Value | =Enterprise Value - Net Debt |
Divide the equity value by the total number of shares outstanding.
Formula:
excel
=Equity Value / Shares Outstanding
Collect financial data for similar companies.
| Company | EV ($M) | EBITDA ($M) | EV/EBITDA |
|-------------------|-------------|-----------------|---------------|
| Company A | 10,000 | 1,000 | =B2/C2 |
| Company B | 12,000 | 1,200 | =B3/C3 |
| Company C | 9,000 | 900 | =B4/C4 |
| Average | | | =AVERAGE(D2:D4)|
Use the average multiple to value the target company.
| Metric | Value ($M) |
|-------------------|----------------|
| EBITDA (Target) | 800 |
| EV/EBITDA Multiple| =Average(D2:D4)|
| Enterprise Value | =EBITDA * Multiple |
Collect data on similar transactions.
| Transaction | EV ($M) | Revenue ($M) | EV/Revenue |
|-------------------|-------------|------------------|----------------|
| Transaction A | 500 | 100 | =B2/C2 |
| Transaction B | 600 | 120 | =B3/C3 |
| Transaction C | 550 | 110 | =B4/C4 |
| Average | | | =AVERAGE(D2:D4)|
Use the average EV/Revenue multiple to estimate the target company’s value.
| Metric | Value ($M) |
|-------------------|----------------|
| Revenue (Target) | 150 |
| EV/Revenue Multiple| =Average(D2:D4)|
| Enterprise Value | =Revenue * Multiple |
Links to other sheets: Green.
Error-Checking Tools:
excel
=Assets - (Liabilities + Equity)Use Excel's Trace Precedents/Dependents for debugging.
Sensitivity Analysis:
| Year | FCF ($) | Discount Factor | PV of FCF ($) |
|----------|-------------|---------------------|-------------------|
| Year 1 | 100,000 | =1/(1+WACC)^1 | =FCFDiscount Factor|
| Year 2 | 120,000 | =1/(1+WACC)^2 | =FCFDiscount Factor|
| Terminal Value | =FCF(1+g)/(WACC-g) | =Discount Factor | =TVDiscount Factor|
Using Microsoft Excel for business valuation modeling provides flexibility and transparency. By following structured steps for DCF, comparable analysis, or precedent transactions, you can build accurate models.