DCF Valuation Model Template

Description
Discounted Cash Flow (DCF) Valuation is a fundamental financial model used to estimate the value of an investment or business based on its expected future cash flows. This valuation method is grounded in the principle that the value of an asset is equal to the present value of its future cash flows, adjusted for the time value of money. DCF valuation is widely used in finance for assessing the intrinsic value of companies, projects, and investments, providing a rigorous and comprehensive approach to investment decision-making.

This file contains a comprehensive three-statement financial model, incorporating the Balance Sheet, Profit and Loss Account, Cash Flow Statement, and additional supporting schedules, outlined as follows:

1. Input (Assumption) Tab: This section consolidates key assumptions related to revenue projections, operating costs, capital expenditures (Capex), depreciation rates, debt structure, working capital, and tax rate assumptions.
2. Balance Sheet and Profit and Loss Account: The Balance Sheet and Profit and Loss Account are constructed based on the historical data from the past four years, along with other assumptions derived from the supporting schedules. A five-year financial projection is provided, supported by both vertical and horizontal analysis for deeper insight.
3. Revenue Model Tab: This tab includes data on annual capacity, utilization, and pricing, from which revenue is projected. The revenue projections are directly linked to the Profit and Loss Account.
4. Working Capital Schedule: This schedule outlines the calculation of current assets such as trade receivables, inventory, and other current assets, based on days outstanding. It also forecasts current liabilities, including trade payables and other liabilities. Additionally, projections for working capital loans are included.
5. Asset Schedules: Individual asset schedules are prepared and consolidated into the total Property, Plant, and Equipment (PPE). The PPE schedule is split into two parts: one for PPE and depreciation as per the Companies Act, and another for tax-related calculations in accordance with applicable tax laws.
6. Equity Schedule: This schedule reflects the breakdown of share capital, retained earnings, and other equity components. Profit after tax is linked to retained earnings/other equity, with the corresponding impact reflected in the Balance Sheet.
7. Debt Schedule: The debt schedule captures loan additions, repayments, and calculates quarterly loan repayment schedules, which are subsequently reflected in the annual repayment tab.
8. Tax Calculation: Taxes are calculated on taxable income, adjusted for depreciation and various factors such as carried-forward business losses, unabsorbed depreciation.
9. Charts: Visual representations are provided for key metrics, including Revenue, COGS, EBITDA, EBIT, PAT, and Total Debt, along with their respective margin percentages.
10. DCF Valuation: In the Discounted Cash Flow (DCF) valuation section, Free Cash Flow to the Firm (FCFF) is calculated, followed by the determination of equity value after appropriate adjustments. Sensitivity analysis for both equity value and value per share is also included.