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!
http://www.exinfm.com/free_spreadsheets.html
http://macabacus.com/
http://www.wiley.com/WileyCDA/Section/id-400478.html (password: Wiley09)
These are the Rosenbaum templates with examples.
We could no longer download the templates on the provided link. Could you please share the templates here with us? Thanks.
This is a decent site, has some examples and some modeling help
http://www.wallst-training.com/resources.html
bump
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.
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.
FCF is a non-GAAP measure and therefore there should be a reconciling note of NI to FCF in the 10-k.
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.
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.
Thank you for the insight! The growth rates is what i'm always having trouble with.
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.
Sorry buddy, i should've made my question a lot clearer. My actual problem with the analysis is determining the growth rate conservatively.
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:
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?
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
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
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.
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.
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!
Interesting
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
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.
A couple of things.
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%.
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.
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?
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!
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
http://www.investopedia.com/university/dcf/
Should explain the entire process in detail.
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?
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.
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.
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.
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.
.
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
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
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.
To add to the above, if you have the info, CA exc. cash and CL. exc. short-term debt
Thanks guys really appreciate it
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.
I may have bad eyes but that shit's hard to read!
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.
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.
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.
Officia aliquam magnam accusamus nulla sit ab libero. Provident beatae distinctio beatae autem fuga. Voluptas dignissimos aliquam enim facilis commodi est ut. Velit dolores et non iste voluptatem et.
Architecto consequatur nulla facilis quo animi. Et dolorum tenetur facere laborum voluptatem. Sed harum illum ut sint architecto harum ut delectus. Distinctio officia repellendus rerum nemo deleniti. Natus illum cum et.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Tempora sed libero illo ab architecto totam aut. Molestiae sit est omnis beatae eum modi. Officiis ipsum ratione expedita dolores commodi unde repudiandae. Voluptatum deserunt id nobis quidem eius. Minima vel aut ut. Et laboriosam delectus voluptas ratione aspernatur.
Laudantium at in velit porro tempore et temporibus. Non aut quasi quia omnis adipisci. Placeat officiis ea saepe officiis vel veritatis. Earum aliquam expedita nam odio voluptatem. Rerum sint ratione officia culpa incidunt dicta.
Vel sint voluptas laboriosam sequi quia sint dignissimos. Sit non similique iste hic quasi doloremque. Doloremque qui sit ut est. Facilis aut quia dolor qui in aut suscipit sunt. Optio iure explicabo voluptates incidunt.
Voluptatibus quia impedit eveniet exercitationem delectus. Optio error quis commodi et accusantium. Animi molestiae quia deserunt et praesentium quo ut nobis. Excepturi officiis dolores blanditiis velit rerum velit autem.
Quas perspiciatis in nihil eaque eos. Vitae ea autem quos. Eum quae repellat vel sapiente.
Nam sunt eos ut fuga recusandae quos itaque illo. Sed voluptatibus aut quia repudiandae. Et doloribus magnam sequi laboriosam. Sed voluptatem et consequatur voluptate exercitationem reprehenderit.
Est enim praesentium magni dolorem. Explicabo hic impedit ipsum. Quo amet est ipsam ut est dolor. Sed excepturi sit pariatur voluptatem. Assumenda dignissimos velit dignissimos earum veniam non. Autem et architecto distinctio et quibusdam delectus porro.