Help Building DCF Model

cje1992's picture
Rank: Senior Baboon | banana points 246

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!

Investment Banking Interview Course

  • 7,548 questions across 469 investment banks. Crowdsourced from over 500,000 members.
  • Technical, behavioral, networking, case videos, templates. All included.
  • Most comprehensive IB interview course in the world.

Comments (49)

Jun 18, 2012

http://www.wiley.com/WileyCDA/Section/id-400478.html (password: Wiley09)

These are the Rosenbaum templates with examples.

May 19, 2014

We could no longer download the templates on the provided link. Could you please share the templates here with us? Thanks.

Jun 18, 2012

This is a decent site, has some examples and some modeling help

http://www.wallst-training.com/resources.html

Mar 29, 2016

bump

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Mar 29, 2016

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

FCF is a non-GAAP measure and therefore there should be a reconciling note of NI to FCF in the 10-k.

Mar 29, 2016

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

Thank you for the insight! The growth rates is what i'm always having trouble with.

Mar 29, 2016

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.

Mar 29, 2016

Sorry buddy, i should've made my question a lot clearer. My actual problem with the analysis is determining the growth rate conservatively.

Mar 29, 2016

For terminal growth rate? Assume inflation to be conservative.
For business growth rate? Find an industry CAGR, unless you have additional insight. The conservative way to think about topline growth is actually adding incremental revenue, not a growth rate percentage. Unless it is a very, very early stage company with explosive growth (in which case you probably don't have much positive cash flow anyway), assume that the existing customers remain and any upsell is offset by any churn in customer base. So, if a company is adding $10mm of revenue per year, keep adding $10mm with the same sales expense base. The thought process here is that the same sales team will manage the existing customers described above, and they will also keep doing what they are doing - also, the low hanging fruit have probably already been picked, so each sale will get harder and harder. This means your business growth rate goes down over time, and probably also approaches inflation as time approaches infinity (hence the TGR inflation input).

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Mar 29, 2016
  1. macabacus.com
  2. Calcuate NPV from baseline. Compare. Do not mix both models
Mar 29, 2016

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

cash flow from operations isn't usually forecasted to calculate FCF, which is resulting in very slow FCF growth over your forecasted period. Terminal value isn't added to firm value. remember to discount that. WACC seems too low.

Mar 29, 2016

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

Interesting

Mar 29, 2016

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

I agree. I did see that revenue has been growing at that 25% each year and I was just projecting low to be on the safer side. Probably to low though. I will take another look thanks again for the response.

Mar 29, 2016

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%.

Mar 29, 2016

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

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.

Mar 29, 2016

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

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

http://www.investopedia.com/university/dcf/
Should explain the entire process in detail.

Mar 29, 2016

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

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

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.

Mar 29, 2016

.

Mar 29, 2016

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

CA - CL

Do this for each year, find the difference, and it's subtracted from EBIDA along with capex to come up with the FCF.

Mar 29, 2016

To add to the above, if you have the info, CA exc. cash and CL. exc. short-term debt

Mar 29, 2016

Thanks guys really appreciate it

Mar 29, 2016

I may have bad eyes but that shit's hard to read!

Mar 29, 2016

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.

    • 1
Mar 29, 2016

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.