Discounted Cash Flow (DCF) and Comparables Valuation Model

This Discounted Cash Flow and comparable valuation model requires you to input the summarised historical and forecast P&L and Balance sheet data of a company and will automatically calculate a statement of net cash flow, Free Cash Flow, and will allow you to get a proper Discounted Cash Flow analysis based on the different parameters you are invited to enter. It also encourages you to conduct a valuation based on comparable transaction multiples and offer a summarized aggregated view of your valuation range.

After inputting your summarised P&L data ( Revenue, COGS, Opex, D&A, Interest costs and corporate tax) both for historical and for forecast period, the following tab of this best practice generates a graph that should help you assess the consistency of your forecast vs. historical.

You are then invited to input your balance sheet numbers on a historical and forecast basis which will allows this best practice to generate in the following tab a statement of Net Cash Flow (checking that Net Cash Flow is equal to your movement in Cash as per your balance sheet).

The following tab contains an automatically generated Free Cash Flow to the Firm (FCFF) statement that will be used in the Discounted Cash Flow (DCF) analysis.

Next, going to the Valuation part of this best practice, you will be presented with a Discounted Cash Flow model. You will be invited to look for unlevered beta of public comparable companies. You will also be asked to input a risk free rate, a tax rate, a target Debt / Equity structure that will allow to automatically calculate levered beta.

You will also need to input a market risk premium and a liquidity & Smaller cap premium to account for additional risk related to the size of your business and whether it is privately own (hence less liquid)

Finally you will indicate the cost of debt and a WACC for your business will be automatically calculated. I have included some links to online resources to help you out with some of these inputs.

After deciding on a terminal growth, the DCF model will automatically calculate a Firm value. A small table is also included to assess this DCF valuation in terms of revenue, EBITDA and EBIT multiples

The last three tabs are template to encourage you to look for comparable online (some online references are also included). They will both help you come up with a valuation range and cross check your valuation with market practice.

After everything has been completed, you can come back to the beginning of the model to see a summarised view of valuation based on different techniques used.