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!

 

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.

 

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.

 

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

 

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!

 

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

 

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

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!

 

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

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!

 

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.

 

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

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”
 
Best Response

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.

 

Consequuntur eos commodi nisi dignissimos nam. Minima et nisi ipsum qui sint quo. Amet a ut facilis quaerat eos dolor. Est qui deleniti velit quidem. Sit eligendi excepturi rerum velit vel magni. Dolores et ab tempora.

Ratione aut autem maiores enim. Et laborum est voluptate qui iusto sunt. Eius quis non quia ex. Deleniti accusantium beatae tempora est necessitatibus. Nisi et ipsum repellendus.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”