Discounted Cash Flow Analysis

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

1. Enter net sales, total costs, EBITDA, Depreciation & Amortization for each year, which will sum up to EBIT.
2. Enter taxes paid, in this example 30% is used, but it varies from country to country
3. 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

1. Enter Account receivables,
2. Inventory and
3. Prepaid expenses and other
4. This information will sum up to Total Current Assets
5. Enter Account payable,
6. Accrued Liabilities and
7. Other Current Liabilities
8. This will sum up Total Current Liabilities
9. 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

1. Make projections of future sales by looking at historical values – In this example the business has had an annual organic growth between 14% and 21% implying a CAGR of 16%. This is normally a good measure for future estimates. However, we have spoken to the management of this company and they have a financial target of 6.900 in 2014 and that is why we use an annual growth of 8% in this example.
2. EBITDA margins – look at historical values. This company has had EBITDA margins in the range of 13.5 – 15% in the past few years. In our example we have chosen to use an average value of the historical information in the projection period, implying 14.3%. This number will determine EBITDA in the projection period.
3. Depreciation and amortization – look at historical depreciation in relation to sales and use an average from these year to use in the projection period. In this case, it was quite simple, we used 1.8% of sales.
4. Taxes – use either the historical tax level or the business tax applied in your region. We have used 30% in this example.
5. CAPEX – Determine capital expenditures that you believe the company will have in the future. This is quite difficult to estimate, therefore, use an average of the last five years in relation to sales. In our example the capex actually decreased between 2009 and 2010, which might be inappropriate. However, after a short discussion with the management of the company we still decided to use 2.3% of sales

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

1. Estimate total current assets in the projection period. Use the average during the past four years in relation to sales
2. Estimate total current Liabilities in the projection period. Use the average during the past four years in relation to sales
3. The net working capital will now be calculated automatically based on your above input
4. 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
5. 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:
Debt to Total Capitalization: 30%
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

1. Identify a couple of listed companies that are similar to the one that you want to estimate business value upon
2. Enter the listed companies beta which can be found via a database such as Bloomberg
3. Enter the Market Value of debt of these companies. The market value of debt is the same as book value of debt
4. Enter the market cap for the traded peers
5. Enter the marginal tax rate
6. All this information can be found in the database. Now the model will calculate the Beta and unlevered and levered Beta and put as input in the valuation model

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

1. Enter the risk free rate. This is the same rate as the 10-year treasury bond and can most likely be found on your government’s website
2. Enter the market risk premium – this is used to adjust for specific company risk and should be 7.1% according to: Ibbotson
3. The Levered Beta is given from previous excercise
4. Now add a size premium according to Ibbotson as well. We have used 1.7% since our company is pretty small
5. Now enter cost of debt, the rate which your company gets to borrow money at. If you are not sure, you can calculate the rate by dividing interest paid during the last financial year with total debt
6. Enter the tax-rate for the company’s current country – so that the tax-shield deduction can be calculated

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

1. Make sure the WACC is correct according to step 6
2. The Discount Period is set according to the mid year method and could be leaved as is since the cash flow is evenly generated through the year
3. The Discount Factor is calculated with WACC and the chosen Discount Period
4. The present value of the free cash flow is now automatically generated/li>

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.

Most values are already given as can be seen below:

Steps

1. Perpetuity growth rate is the rate at which the economy is expected to grow, this is normally 2.5% or 3%
2. Make sure the implied exit multiple isn’t 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.

Other Valuation methods

• LBO Model
• Comparable Companies Valuation
• Precedent transaction Valuation

External guides and resources

• Discounted Cash Flow Analysis
• Discounted cash flow via Wikipedia
• A Tutorial on the Discounted Cash Flow Model for Valuation of Companies