DCF Analysis Input Sheet A Practical Guide

What Is a DCF Input Sheet?

A Discounted Cash Flow (DCF) analysis estimates the present value of a business or an investment by forecasting future cash flows and discounting them back to todays dollars. The input sheet is the backbone of that model it collects every assumption, projection, and parameter that drives the calculation. A wellstructured input sheet makes the model transparent, easier to audit, and simple to update when new information arrives.

Core Components of the Sheet

1. Forecast Period & Horizon

• Years to Forecast: Typically 510 years for mature companies; shorter for highgrowth startups.
• Terminal Year: The year after the explicit forecast where a terminal value is applied.

2. Revenue Assumptions

• Base Year Revenue: The most recent audited or reported figure.
• Growth Drivers: Market size, share gain, pricevolume mix, or contract renewals.
• Growth Rate: Yearbyyear percentage or a single CAGR applied over the forecast.

3. Operating Expenses

• Cost of Goods Sold (COGS) often expressed as a % of revenue.
• Sales, General & Administrative (SG&A) can be a flat amount or % of revenue.
• Research & Development (R&D) important for tech or biotech firms.
• Depreciation & Amortization linked to capital assets schedule.

4. Working Capital

Workingcapital changes affect cash flow. Common inputs are Days Sales Outstanding (DSO), Days Inventory Outstanding (DIO), and Days Payable Outstanding (DPO). The sheet should allow you to adjust each component and automatically calculate the net workingcapital change.

5. Capital Expenditures (CapEx)

Separate CapEx for growth versus maintenance. Growth CapEx is often tied to revenue growth, while maintenance CapEx can be a fixed % of prioryear PP&E.

6. Tax Rate

Use an effective tax rate based on historical averages or statutory rates adjusted for tax shields and carryforwards.

7. Discount Rate (WACC)

The Weighted Average Cost of Capital combines the cost of equity (CAPM) and the aftertax cost of debt. Include inputs for riskfree rate, market risk premium, beta, debt ratio, and cost of debt.

8. Terminal Value Method

• Perpetuity Growth Model: Assumes a constant growth rate beyond the forecast.
• Exit Multiple Method: Applies a chosen EBITDA or EBIT multiple.

9. Sensitivity & Scenario Controls

Include cells for high/lowcase assumptions (e.g., growth 10%, discount rate 1%) that feed into a datatable or scenario analysis.

Building the Sheet StepbyStep

Step 1 Set Up a Clean Layout

Keep inputs on the left side of the worksheet, calculations in the middle, and results on the right. Use a consistent colour scheme (light shading for input cells) and lock formula cells to prevent accidental overwrites.

Step 2 Input Historical Data

Pull the last three to five years of incomestatement, balancesheet, and cashflow figures. This historical base validates assumptions and provides growth trends.

Step 3 Define Growth Drivers

For each revenue stream, list the driver (e.g., unit sales, price per unit) and attach a growth assumption. Use a separate table for driverlevel forecasts if the business is multisegment.

Step 4 Calculate Pro Forma Financials

Apply the revenue, expense, and tax assumptions to produce projected Income Statements, Balance Sheets, and CashFlow Statements for each forecast year. Ensure that depreciation matches the CapEx schedule and that changes in working capital flow through the cashflow statement.

Step 5 Derive Free Cash Flow (FCF)

FCF = Operating Cash Flow CapEx. Use the cashflow statements Cash from Operations line, subtract projected CapEx, and adjust for any nonrecurring items.

Step 6 Discount the Cash Flows

Apply the WACC to each years FCF: Present Value = FCF / (1 + WACC)^n. Sum the present values for the explicit period, then add the discounted terminal value.

Step 7 Create Sensitivity Tables

Use Excels Data Table feature to show how the valuation changes with different growth rates and discount rates. This visual aid is essential for presentations.

Common Pitfalls & How to Avoid Them

• Overoptimistic growth assumptions: Base forecasts on realistic market research and adjust for competitive pressures.
• Ignoring workingcapital needs: Small changes in DSO or DPO can swing cash flow by millions; model them explicitly.
• Mixing units: Keep all monetary values in the same currency and scale (e.g., millions) to avoid rounding errors.
• Hardcoding numbers: Reference cells whenever possible; this keeps the model flexible.
• Neglecting tax effects on debt: Remember to apply the tax shield to interest expense when calculating WACC.
• Failing to test for circular references: Enable iterative calculation only when necessary and document the reason.

BestPractice Tips

1. Use named ranges for key inputs they make formulas easier to read.
2. Separate assumptions from calculations; a single Assumptions tab simplifies updates.
3. Document sources next to each input (e.g., Company 10K, p.12 or IBISWorld report).
4. Apply consistent formatting blue fill for inputs, grey for formulas, green for outputs.
5. Run a stress test by varying one assumption at a time and observing the impact on valuation.
6. Versioncontrol the file add a date and version number on the first page.

Sample Input Sheet Layout (HTML Representation)

Category	Parameter	Value	Units / Notes
Revenue	Base Year Revenue	150.0	Million USD (FY2024)
	Annual Growth Rate	6.5%	Forecast Years 15
	Growth Rate Year610	3.0%	Gradual slowdown
Operating Expenses	COGS (% of Rev.)	38.0%
	SG&A (% of Rev.)	15.0%
	R&D (% of Rev.)	5.0%
	Depreciation (% of PP&E)	8.0%	Based on straightline schedule
Working Capital	DSO (days)	45	Assumed constant
	DIO (days)	30
	DPO (days)	40
CapEx	Growth CapEx (% of Rev.)	4.0%	Years15
Tax	Effective Tax Rate	21.0%	Based on historic average
WACC	Cost of Equity	9.2%	CAPM: 2.5% RF + 1.25.5% ERP
	Cost of Debt (aftertax)	4.8%	5% pretax (121%)
	Debt/Equity Ratio	0.45
	Overall WACC	7.6%	Weighted average
Terminal Value	Perpetuity Growth Rate	2.5%	Longrun GDP growth estimate

Conclusion

A disciplined