DCF model

A DCF valuation is a valuation method where future cash flows are discounted to present value. The valuation approach is widely used within the investment banking and private equity industry.

DCF Valuation step by step guide for free

This tutorial is quite simple and straight on to give you a good understanding of the DCF model and how it works. If you want to take your modeling to the next level I can highly recommend this modeling package which I believe is the best available financial modeling tutorial on the market.

To start Download our free DCF model template in Excel

Download this DCF model template so that you can calculate the value of a business. All input will be described in the DCF model tutorial below.

Step 1 Enter historical financial information in the DCF valuation

Enter historical information

Enter the historical information of the company you wish to value, this information can be found in an annual report or can be ordered via this link for example. It depends on what company you wish to estimate value of.  The CAGR (Compounded Annual Growth Rate) and the percentage numbers will be calculated automatically when you have entered all information. Below is a picture of the information you should fill in:

Steps

Enter net sales. total costs, EBITDA. Depreciation & Amortization for each year, which will sum up to EBIT . Enter taxes paid, in this example 30% is used, but it varies from country to country Enter Capex (Capital Expenditure) which is the annual investments for the company each year. This is normally specified in the Annual Report under Cash Flow Statement. If you cannot find the information in the Annual Report you can also take the difference from two years in tangible assets. For example, if the company had tangible assets of 100 in year 2006 and  110 in 2007, the company spent 10 on investments (CAPEX ) during 2007.

The historical information will be used to make likely forecasts of sales growth and EBITDA margins which will be performed in coming steps.

Step 2 Enter historical working capital

In step two we are entering historical information. This is needed in order to make good prediction of future working capital needed. The working capital is such an important and difficult input, which needs some extra attention.

In the picture below we have circled the information you should supply in order to calculate the change in net working capital. The first circle shows the outcome of the information supplied:

Steps

Enter Account receivables, Inventory and Prepaid expenses and other This information will sum up to Total Current Assets Enter Account payable , Accrued Liabilities and Other Current Liabilities This will sum up Total Current Liabilities The total Net Working Capital will now be calculated automatically in the model

If you have followed this step by step tutorial this far, and enjoyed it, I would very much appreciate if you could help me promote this website by clicking the google and Facebook symbols below to recommend this site to others:

Step 3 Make future projections

The projections in the DCF model have large impact on the valuation, therefore, this step is extremely important. We will now use the historical information as a base in order to make good and likely projections of the future.

In the picture below we have circled the information you should supply. However read the instructions below the picture before you make your assumptions and input.

Steps

Step 4 Calculate Unlevered Free Cash Flow, DCF model

We shall now calculate the unlevered free cash flow, but first we need to make some assumptions regarding the working capital and estimate the needs in the projection period.

See comments below picture:

Steps

Estimate total current assets in the projection period. Use the average during the past four years in relation to sales Estimate total current Liabilities in the projection period. Use the average during the past four years in relation to sales The net working capital will now be calculated automatically based on your above input The difference (increase or decrease) between e.g. 2010 and 2009 will now be subtracted or added to the cash flow. A growing business will normally take on more working capital for each year, which will lower the free cash flow The Free Cash Flow can now be calculated for every year in the projection period!

Step 5 Target Capital Structure and Beta

This section is for you who have access to a database such as Bloomberg or Reuters. If you do not have such access, you can type in:

Equity to Total Capitalization: 70%

This is the most common assumption when determining capital structure in a business valuation model. However, the below described method is more accurate and preferred if you have all the needed tools .

The picture describes the input we have made in our example valuation, which is further described below:

Assumptions and input

Step 6 Determine WACC

The capital structure is given from the previous step and works as a base for determining wacc in the calculation below.

Assumptions for WACC

Step 7 Present value of free cash flow

Next step is to calculate the present value of the generated cash flows in the projection period.

Steps

Step 8 Calculate Terminal Value

The terminal value has the largest impact on the valuation and it is extremely important that this input is correct performed.

Steps

Perpetuity growth rate is the rate at which the economy is expected to grow, this is normally 2.5% or 3% Make sure the implied exit multiple isnt too high, since that probably means your assumptions are too aggressive in the terminal year. Another way of judging if this value is too high, is if you put it in the relation of the later calculated enterprise value. If the terminal value is more than 80% of the enterprise value, it is likely that something is wrong with your assumptions

Step 9 Enterprise Value

The DCF valuation is almost done, you have made all the inputs required and the enterprise value is already calculated. Now we will try to describe the results and make sensitivity analysis.

Below are the results in our valuation example:

Comments

In the results above you can see the enterprise value of the business and some multiples on the 2010 years estimated results. You should also enter debt, cash and outstanding shares to get additional information on your valuation.

Step 10 DCF Sensitivity Analysis

With this sensitivity analysis you can see how the valuation changes with different assumptions and changes in input.

This is our sensitivity analysis:

Comments

To perform a sensitivity analysis like this, you should copy the exact value of your WACC, EBITDA %, Perpetuity Growth and annual sales growth in the middle of each row and column. The numbers that you should replace are dark blue and bold.

Valuation Range

It is now time to decide the valuation range for the company. In the example valuation we decide the range by changes in WACC with 1% up and one percent down which gives a range between approximately 5 000 6 000!

What now? Further reading

If you want to become a real Investment Banker and master financial models, I have two recommendations for you:

The first recommendation is by far the best financial modeling guide available on the market. If you can afford it you should get it, since it will help you understand all aspects of valuation. Link »

The other recommendation I have is the book Investment Banking by Rosenbaum and Pearl which is a fantastic book with some great tutorials and it is less expensive than previous mentioned tutorial. It describes the dcf model as well as other valuation approaches extensively and also gives you a complete DCF template in Excel included with the book. The tutorials in the book are well written and easy to follow.