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:
Enter historical financial information

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:
Enter historical financial information

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.

Future Projections

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:
Unlevered Free Cash Flow

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:
Target Capital Structure

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.
WACC Assumptions

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.

Present Value of Free Cash Flow

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:
Terminal Value

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:
Enterprise Value

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:
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

External guides and resources


42 responses to “Discounted Cash Flow Analysis”

  1. Neeraj Jain

    Question about calculating levered beta, the what happens when Equity market value is negative ? where do we get levered beta from. I tried with a company and yahoo was not even showing D/E, as E was negative. in this case, how should we get relevered beta ?

  2. D S Prasad

    DCF model very nicely explained.I also enjoyed reading Rosenbaum and Pearl.

    D S Prasad
    Faculty(Finance)
    IBS,Hyderabad
    India

  3. Walt Oko

    What is the 2010- 2011 market risk premium number – you had it in this model at 7.1% according to: Ibbotson. Thanks

  4. Yash

    Dear Sir,

    I need to know the Levered Beta and Unlevered Beta for Event Management industry. Kindly let me know if you know it.

    Regards

  5. HondoBat21

    Sir/Madam:

    in business valuation report Income Approach DCF re: Mid year factor applied sample below:

    FY2010 FY2011 FY2012 FY2013 FY2014 TV
    0.5% 1.5% 2.5% 3.5% 4.5% 5.5%

    is it correct to assume that the 4.5% of FY2014 should be carried forward to TV rather than the 5.5% given by the evaluator? Kindly advise and confirm correctness of TV-5.5%. thanks.

  6. HondoBat21

    According to experience and standard business evaluation 4.5% should be carried forward to the Terminal Value (TV) and not the 5.5% which exceeds average growth rate assumptions. Kindly confirm. Thanks.

  7. P.kumar

    I want to know when to use fcff and Fcfe model.and what adjustment to regarding debt part in fcfe and fcff model

  8. sindi

    may i know do you assist with valuation for companies for academic purposes

  9. Roger

    Kumar wants to know whether to use the Free cashflow for Equity (fcfe) or the free cashflow to firm (fcff) model.

  10. Pear

    It is very useful.

    I have a few questions to ask.

    1. Can we use the company D/E ratio itself instead of industry D/E (based on your example, you used the industry D/E)?

    2. For the terminal value, why do you add only 0.5 yrs for discount period (4.5 + 0.5), why not 1 yr?

    Thank you very much.

  11. Tom

    Many thanks fo your efforts.
    I want to know can we add the value of the Fixed assets in this way to have the value of the company ????

  12. Tom

    Sir,

    I have few questions

    1.Why you didnt consider the Depreciation in the calculation of Unlevered Free Cash Flow.

    2.in determination of Net Working Capital, do I have to add the Postdated Cheques recieved from customers to the Account receivables.

    3.If I have debit or credit balances in Current assets / Liabilities from companies in the same group i.e within the same holding company, Do I have to consider these balances in calculation of Net Working Capital.

    Regards,,,

  13. Agus Smekot

    It’s very useful.

    Thanks for all efforts.
    Regards.

  14. Tom

    Dear Sir,

    No reply till now

    Thanks in advance

  15. Mou P

    To BV Pro,

    If we are supposed to valuate and analyse the cash flow of a pharma business, would it be correct to assume that a straightforward DCF analysis is not applicable?

    Would there be a better valuation model to use? I am not sure about the approach here because pharmas tend to have low revenues and high R&D costs?

    From an investors point of view why would such a pharma stock be practically attractive?

  16. Brendan

    Will this template work for a start up company, without historicals? If I have projected sales, expenses, etc for the company. What would be your recommended way to approach this type of valuation?
    Thank you

  17. Saken

    Dear BV Pro,

    Thank you for letting me use this template. I did some calculations and I have a few questions. First two are:

    1. Is Present value of Free Cash Flow in Cell Q5 a value of the business now in 2012?

    2. Is Enterprise value in Cell Q12 a value of the business in 2016?

    Is there a way to contact you besides leaving comment? ie email?

  18. Saken

    Thank you for getting back to me.

    I failed to mention that what I did is change the actual years and forecasted period. I took out 2006 completely, and changed the years to 2009-2011. For the forecased period, I changed the years to 2012-2016.

    This is basically to bring it uptodate.

    But we’ll use your original file as to not confuse both of us.

    1. So from your post I understand that Present value of Free Cash Flow (Q4) is the present value of the firm for year 2009.

    2. And Terminal Value (Q7) is the theoretical value of the business in year end 2014.

    3. What does the Enterprise Value (Q12) reflect? I thought that this reflects the value of the business in year end 2014, no?

    4. Q19 is implied share price. Times it by 10,000 shares gives us $5740, which is the Implied Equity Value. You’re saying this is what I should look at to see what the business will be worth by the end of 2014?

    I’m sorry I’m a little confused by different values. I’m just trying to find what the business is worth in the present time and future time at the end of the forecasted period. This is to help me pitch to investors, give them the values and they would pay for what share of the company.

    5. Also, what’s stopping us from adding projected numbers for 2015, 2016 etc and increasing the projected value of the business in the process?

  19. raksha verma

    i would like to know how to calculate perpetuity growth rate when the forecasted cash flows are not always increasing and having highs and lows.
    and what are these multiples exactly as in above its written implied exit multiple as 7x as i’am calculating enterprise value i need to know….can neone help me??

  20. Johnny Ice

    Why is cash not included in current assets?

  21. Emerald

    Thanks, your DCF Valuation analysis is very concise and explicit. Thanks once again.

    However, I have some pertinent questions to ask.

    a) Can this DCF model be used to value majorly commercial banks (or be used for other financial services firms too e.g Insurance)? Because i understand that Bank’s DCF valuation is peculiar because of the nature of their business model (regulation etc). Hence, if it can not,please shed more light on how to value banks using DCF.

    b) The same question about valuing oil and gas companies.

    c) When calculating the Terminal value, must the the two approaches (Perpetual Growth Method and the Exit Multiples) give the same value?

    Thanks

  22. Emerald

    Thanks.

    Again, can you do an article/write-up for this forum on how to value banks and financial institutions as well as oil and gas sector.

  23. Roger

    Have a question,

    in your DCF model, why is your formula for “Net Working Capital” adding both current assets and liabilities, shouldnt it subtract? Please take a look. It is confusing. Thanks.

  24. Roger

    okay makes sense…thank you! Also, for net change in WC, why is it that its decrease from 2006-07 when it seems like it increased from 155 to 300?

Leave a Reply