Help Building DCF Model

I'm currently trying to build my own DCF model. I've studied the Rosenbaum format as laid out in his Investment Banking: Valuation, Leveraged Buyouts, and Mergers & Acquisitions.

Does anyone have any excel files of DCF models they've made themselves or at work that I could go off of? I would like to get a feel for another one.

Anything will help!

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)

Comments (49)

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Mar 29, 2016 - 6:10am

DCF Model Help? (Originally Posted: 05/21/2014)

Hello!

I would like to become more proficient and confident at creating some DCF models, but I have been having some troubles.

The attached screenshot is for a DCF I attempted to make for Proctor and Gamble. Does anyone know why my FCF in years 2009-2013 is different than what was reported on the 10k? If anyone has any idea on what part I messed up on I would very much so appreciate it.

Secondly, does anyone have any advise for creating accurate forecasts of CAPX & changes in net operating working capital?

Thanks.

Mar 29, 2016 - 6:11am

Wow that's a very colorful template...

I would say the differential would be in how you're accounting for EBITDA/EBIT. Those are both very malleable definitions and people can adjust them with whatever they like. If you have their 10k, then you need to read the footnotes and what the company includes as part of their operating income/EBITDA. For FCF, dividends is often subtracted as part of the calculations. Net working capital can also be defined in different ways.

PM me if you have any further questions.

Mar 29, 2016 - 6:13am

Just trying to break into IB and must perfect DCF modelling the right way. (Originally Posted: 09/20/2016)

Pretty much what the title says. I need desperate help to perfect DCF. Resources that i've found online haven't really helped much. The best resource i've found so far comes from the valuation master Demodaran himself but I wanted to see if I can have an actual IB analyst explain to me the steps of the valuation themselves such as estimating growth of FCF and other items in a DCF Analysis. Is it even possible to see the work of an actual analysts' DCF analysis in excel file to study? PLEASE HELP and Thank you for helping.

Mar 29, 2016 - 6:14am

The model itself is easy just look up on the internet. What is more difficult is coming up with the growth rates. When I started looking into DCF I thought that its some kind of a magic wand that tells me exactly what a company is worth. Then it sunk into me that the 'fair' value of the company is merely determined by the difference between WACC and a hypothetical FCF growth rate (smaller the difference, more valuable the company).

It's good to know DCF and once you understand the logic it becomes fairly simple (besides coming up with growth rates), so make sure you also know about the multiples, bonds, capital structure and the actual job you are applying to.

Mar 29, 2016 - 6:15am

There are literally four lines - how can it be so hard to learn?

EBIT
-taxes
+D&A
+/-change in working capital
-capex

Add up discounted values.

Boom.

  • 1
Mar 29, 2016 - 6:19am

DCF for Class, Need Help (Originally Posted: 11/20/2010)

Hi,

So I'm doing a DCF model for a class where we have to decide if its worth investing in a new piece of equipment that's expected to decrease labor costs and capacity (thus increase amount of product we can sell) based on the NPV of investing in the project. I have a few questions that I can use your help on:

  1. In order to construct this DCF, we need to figure out the free cash flows for each year and discount them by the WACC, correct? Furthermore, to calculate these free cash flows, we calculate revenues minus variable costs only (no fixed costs), minus depreciation, minus taxes to get earnings, then add back depreciation (there's no interest or amort to worry about) and subtract or add an increase/decrease respectively in net working capital. Is that correct, or did we do something wrong here?

  2. Since we want the INCREMENTAL revenue from investing in the new project, is it OK to calculate the NPV of not investing in the project (i.e. baseline sales without the increased demand and labor costs prior to investing in the project) and then subtract this from the total cash flows from investing in the project to get the net incremental NPV or do we need to go line by line and find the incremental cash flows from investing in the project?

Thanks

Mar 29, 2016 - 6:21am

OK so for number 2, you're saying to just calculate NPVs from both and then subtract it out and not mix the two by saying something like I get X many more products with the thing - the Y number that I would have had previously, right?

Also, I looked at that site, but it didn't say anything about including fixed costs in the thing, or if we're supposed to use free cash flows vs operating income for doing the DCF

Mar 29, 2016 - 6:22am

DCF NOOB HELPP! (Originally Posted: 04/28/2015)

Hello all,

I am brand new to the whole valuation and finance community and was wondering if I could have some input on my model.

I started out by doing the whole EBIT method but want to start with just a simple CFO-Capex=FCF type of model. (Scroll down the spread sheet to see CFO-Capex = CFO) The company I am looking at is Under Armour. My intrinsic value is coming out really low. I was wondering if the cause of this is because the company is still fairly new and DCF wouldnt be the best model or if my DCF is just wrong (i think it's the latter). I think I got down the wacc calculation and growth i just kept it simple at 10%. Please let me know.

Thanks in advance.

Mar 29, 2016 - 6:24am

Okay, ill try to just forecast FCF with the growth rate. As for WACC, I compared it to bloomberg prof and it was actually a tad higher. But are you saying WACC seems low because the company is growing and will probably take on more debt? I was thinking of making my WACC about 10%. Lastly, I just discount the terminal value?
Thanks for the response!

Mar 29, 2016 - 6:26am

You'll get a ton of different values for wacc from different sources. I would say your wacc isn't what is driving your valuation lower than it should be. Your main problem is your slow fcf growth over the projection period as I said previously. Taking a quick look at underarmour....revenue has grown around 25% each year for the last few years while your projections don't reflect that. Take a look at macabacus on how to build operating models. i personally think underarour is overvalued so I wouldn't be surprised if your equity value doesn't come close to the current share price

Mar 29, 2016 - 6:27am

A couple of things.

  1. If you find FCF this way you are finding FCF to equity which will result in an equity value not enterprise/firm value.
  2. Don't find FCF this way. Operating CF will take into account extra adjustments sometimes that you don't want.
  3. Per #1 since you are calculating equity value and not enterprise value, you shouldn't subtract debt from your value.
  4. Your WACC isn't necessarily too low, however, again, since you are calculating FCF to equity, you shouldn't be calculating WACC because there is no debt involved. You should just be calculating cost of equity, which you can do easily enough with a basic CAPM calculation (for simplification purposes you would do "cost of equity=risk free rate+Beta(market risk premium)
  5. For some reason you grew your capex at strange rates compared to your CFO.

Take another crack at it and I and others would be happy to look at it again.

*different people use different sources for this-I prefer a historical long term treasury rate around 5.5%
** you would want to use levered beta since you are calculating equity value, but don't worry about that right now-you can easily find the company's beta online
*** this is calculated as "Avg equity returns-risk free rate"-again different folks will use different market risk premiums but an often used value is 5-6%.

"I know you think you understand what you thought I said but I'm not sure you realize that what you heard is not what I meant."

  • 3
Mar 29, 2016 - 6:29am

Hey Redacted, I was just reading up on equity value vs enterprise value and got some good information. I have a little enterprise value calculation in my spread sheet and will start to use it. Also, I was thinking about going with the EBIT way to calculate FCF. I have that EBIT calculation on my spread sheet but felt my numbers were off. But as you were saying, Operation CF takes into account extra adjustments so that's probably why it's off.

Your number 3 point you were talking about how I took debt out of my firm value correct? I was thinking you subtract out debt because equity value should be only value to equity investors? I'm not sure if I am talking about equity value in the right sense.

As for my WACC/ Cost of equity calculation, I will try to mess around the with risk free and risk premium. I was taking the 10 year treasury rate @ 1.90% and bumped it up to 2.10% cause it seemed low. As for risk premium, I agree with the 5-6% and in essence this can be different because it is the extra return one expects above the risk free rate correct? So if I have a Risk free of 5.5% for example and I expect a 11.5% return overall my risk premium would be 6%?

Thanks again for your help and response I am trying to learn as much as I can and hopefully my spreadsheet wasn't to messed up!

Mar 29, 2016 - 6:30am

You are correct that you should subtract out debt from firm value to get equity value to investors. However, the difference between calculating equity value or firm value when doing a DCF comes down to interest expense which determines whether you are using levered or unlevered free cash flow. If you calculate FCF using net income (which you technically did since you used CFO which is derived from net income) then you have already taken interest expense out of the cash flow. That means that debt investors have already been compensated. At that point what is left over goes to equity holders. Therefore you are directly calculating equity value.

When you do a DCF starting from EBIT (and then NOPAT) you have not taken out interest and therefore debt investors have not yet been compensated and you are using unlevered FCF. The value you get from a DCF starting from EBIT will be firm/enterprise value and you will need to subtract debt from that in order to get equity value.

As for risk free rate, since long term rates are in a period of historic lows, most people (not all) will feel that it is inappropriate to use them to determine WACC. That's why a historic average of approx 5% can be reasonably used instead of the current approx 2%.

You are correct in your thinking about the market risk premium.

"I know you think you understand what you thought I said but I'm not sure you realize that what you heard is not what I meant."

  • 3
Mar 29, 2016 - 6:31am

Hi ChasedGod, just curious, why do you use a growth rate of 25% for CapEx in 2015, and why 5% for the next years? Why not use the 10% Growth Rate you have in B28?

Mar 29, 2016 - 6:32am

Hey hiperfly, I'm actually not to sure.. I think i was thinking that under armour would invest heavily in Capex in the first forecast year and kind of ease up after. But going from 25% to 5% is pretty drastic. So I decided I would just grow the FCF by 10% or 15% instead of growing CFO and Capex. I am also planning to change the way i calculated the FCF.

Thanks!

Mar 29, 2016 - 6:33am

Can someone help explain DCF? (Originally Posted: 05/18/2016)

I know the steps of 1) Project out free cash flows -usually 5 years. 2) Find terminal value. 3) Apply discount rate through WACC. 4) Discount cash flow back to year 0.

But, I don't understand how you get the values of the steps i.e. how do you get the FCF, terminal value, etc.?

Thank you

Mar 29, 2016 - 6:35am

Help - Pulling data for a DCF (Originally Posted: 10/27/2010)

When I pull data from 10ks and 10qs, what calculations would I be expected to do as an analyst or summer intern? I'm pulling data for a dcf

also do ER analys spread comps? is that the same thing as ratio analysis?

god im so confused with all this terminology. is there a book that explain ER in detail?

Mar 29, 2016 - 6:36am

You probably won't be doing any calculations as an intern if I had to guess. You probably will just be plugging the numbers into a model, coming up with a growth rate, and then passing it up to your analyst or associate to let them check and tweak assumptions.

Mar 29, 2016 - 6:37am

To add to what Billy Ray said above me, you probably won't be doing any actual calculations. And at my bank, we don't build many models from scratch.

Most of us have built a model for different calculations / ratios / valuation techniques that we like, and then re-use it over and over again in order to save time. I would guess that as an intern you would be using a pre-existing model that someone else has built, and simply plugging in the inputs.

Mar 29, 2016 - 6:38am

Help - DCF questions? (Originally Posted: 02/20/2013)

Ok so I'm working on a DCF for a competition at my school. Currently trying to calculate the WACC, I have the % of Equity, % of Debt, and Cost of Equity fine, but how do I get cost of Debt? I looked up the company and they have no bonds, and 2 bank loans, neither of which have a market value, so I don't know how to calculate the YTM for them.

Mar 29, 2016 - 6:39am

Look at comparable company debt issuance (more comparable the better obviously, but also the more recent the better). That being said, if you have no debt on the BS and you aren't issuing any in your DCF it won't make a difference in the valuation since when you weight the debt portion it will equal 0 since you'll have Debt(0)/(Debt+Equity)(0+x).

Good luck.

Edit: disregard the last part -- read over your paragraph quickly. Summary: comparable company issuance.

"They are all former investment bankers that were laid off in the economic collapse that Nancy Pelosi caused. They have no marketable skills, but by God they work hard."
  • 1
Mar 29, 2016 - 6:40am

Damodaran has a synthetic credit rating/spread system on his site based on interest coverage ratios. check it out
not the greatest system ever, since its just based on that one metric, but it something to look at.

http://pages.stern.nyu.edu/~adamodar/
doc is called ratings.xls

“Success means having the courage, the determination, and the will to become the person you believe you were meant to be”
Mar 29, 2016 - 6:42am

DCF Help (Originally Posted: 04/01/2014)

Monkeys,

I'm having trouble calculating the net working capital and how it is added into the FCF. Do you take the difference between ie. 2013 and 2012 and use that value to work with or what... I did the whole model but this part I'm relatively sure I fucked up.

Thanks for any help

Mar 29, 2016 - 6:46am

DCF Help? (Originally Posted: 05/19/2014)

In my spare time, I would like to become more proficient at DCF modeling.

Upon creating a simple FCF DCF for Procter & Gamble, I ran into some issues.

Can anyone tell me why my FCF for 2009 - 2013 is not the same number as included in Procter & Gamble's 10k form? Is it because they did not add depreciation back to their unlevered net income, or did I just mess something up?

Thanks.

Mar 29, 2016 - 6:48am

DCF Problems. Help would be appreciated (Originally Posted: 06/12/2014)

If anyone could check over the attached spreadsheet and let me know if my FCF amount for 2013, ticker QCRH, is correct I would very much so appreciate it.

I have been having a hard time learning some of the facets of constructing a DCF model. I have taken various finance classes and conceptually understand the theory and what to do, but interpreting the 10k forms and extracting the real data for the model is more intricate than classroom examples.

I do not know anyone experienced in modeling that is available to help me, so this would mean a lot to me.

Asides from figuring out the correct FCF in the prior years, using FCFF approach, I don't understand why my terminal value, using the perpetuity growth model, is so large - it is making the price per share way too high. That, in conjunction with accounting for preferred stock in the WACC, has not been easy.

Any help would be very much appreciated.

Thanks.

Best Response
Mar 29, 2016 - 6:49am

Without even looking at the spreadsheet I knew you were going to have problems because QCRH is a bank holding company. Banks are very different from your typical widget manufacturing firm because their sole purpose is to lever up and make money on interest spreads. Terms like EBITDA, UFCF, etc. are completely meaningless for a bank, so you can't use the typical Finance 101 DCF model.

Instead of projecting out cashflows, you really need to forecast the regulatory capital levels, and think about how much of net income you expect regulators will allow the bank to pay out as dividends. Another approach is to do a residual income model but it's basically the same idea - the balance sheet and return on equity are the key factors for the value of a bank.

I'm sure if you do some searches on this site or google for FIG terms, bank models, etc. you can find some more info. But if you're just trying to practice building a DCF my advice would be to find a different company.

Edit: just looked at the model - one generic lesson to learn here is that your WACC and the perp growth rate
should never end up that close. In your sensitivity table when the growth rate > WACC you're essentially implying that the stock has infinite value.

Mar 29, 2016 - 6:50am

Your cost of debt is lower than the risk-free rate. I know nothing about the company you're modeling, but that doesn't wash. Especially when the company is as highly levered as it is.

Mar 29, 2016 - 6:51am

Accusamus incidunt enim unde sit labore est velit. Explicabo ut ducimus quia dolor veniam hic. Rem ducimus expedita repudiandae debitis autem dolorum quod. Non quidem qui in laboriosam explicabo nesciunt. Nulla consequatur aut sit molestiae expedita deleniti. Repellendus ut veniam animi consectetur explicabo. Voluptate porro quibusdam aliquid sit.

Mar 29, 2016 - 6:52am

Sapiente quo laboriosam similique sint. Temporibus dolores sequi sapiente. Necessitatibus sed aut repellendus nisi.

Dolor omnis laborum dolore quia dolorum harum amet a. Praesentium ea reprehenderit enim qui aut sed temporibus. Consequatur amet dolorem modi sed voluptatum voluptas qui.

Start Discussion

Total Avg Compensation

September 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (39) $363
  • Associates (220) $232
  • 2nd Year Analyst (135) $154
  • 3rd+ Year Analyst (30) $147
  • Intern/Summer Associate (103) $143
  • 1st Year Analyst (489) $135
  • Intern/Summer Analyst (378) $82