Hey guys,
This is my first attempt at creating a DCF Model. For some reason my numbers are way too high. Target's share price is currently around $70 per share and my model shot out $511. Obviously my extremely high enterprise value is causing this but I cannot seem to find where I messed up.
Any help is greatly appreciated guys.
| Attachment | Size |
|---|---|
| target_dcf_model.xls 22.5 KB | 22.5 KB |
| +33 | How do we get as sharp as possible? | 11 | 3d |
| +23 | What do you think of tiktoker Jinginvests | 9 | 14h |
| +19 | Alcoholism | 4 | 3d |
| +15 | Upselling yourself when you’re unqualified | 2 | 3d |
| +14 | How Are Cornell 7 / GC Members Affecting Your Bank or Firm? | 5 | 4d |
| +11 | Del | 2 | 2d |
| +10 | PWP Piper deal terminated, ruined my chances with a girl | 4 | 1d |
| +2 | Being able to DnD is God Mode | 3 | 1h |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
I'm just looking at it on my phone but I can tell it is your terminal value - it is way too high. Are you sure you calculated it properly?
Your cost of equity seems really low... I think the low beta distorts it a lot. If you look at CoE on a PE basis, it should be closer to 8%.
I don't see anything wrong with formulas but I just did a quick check
yea that makes sense. I just shot to Yahoo Finance to find the beta, wasn't really sure if that is the way to go.
I gave a look at your model, take my view with a grain of salt.
1) ebitda is not always equal to ebit + d&a.
3)5% cost of capital seems too low for such a small business.
I put a wacc of 10% and fixed the calculation of ebitda and I got about $150 per share.
I just fixed my cost of equity, it is up to 8.93% which brought the share price to around $230. Can you explain how you adjusted the WACC to 10%?
I set the WACC arbitrarly at 10%. A business with 70mln of revenues would have wacc in the double digits.
should my cost of equity be even higher than 8.9-9 to bump up the WACC? And how would I go about doing so? I am trying to figure out how to link it all so it's just not me inputing "10%" for the WACC.
Thanks masters
They are right. I just checked again on my comp and it seems your calculations are right. The terminal value is so high because the discount rate is so low - that seems to be the problem.
thanks JMar,
What should I flip around to get my discount rate higher to bring the terminal value and share price down?
Both equity and debt could have higher costs... baseline equity would be inverse of P/E (in this case ~1/13).... I'm not familiar with their capital structure, but think the debt should be closer to 5%.
You didn't get the beta wrong. Your beta is right. It's just not RIGHT for the CAPM calc.
are both the costs of equity and debt known? or is it part of the forecast and every model has a different value for those inputs? sorry if that was a stupid question lol
Think about it..... both costs are what the company "pays" to market to get equity or debt funding. Would you, as an investor, charge the same to all companies?
Every company has a different story and capital structure, so the numbers should be different. That doesn't meant they aren't KNOWN though. That's part art, part science. Cost of debt is observable by seeing where the debt trades (or where comps trade). Cost of equity is observed with CAPM (or not, as you saw with your model), with inverse of P/E, or with comps. The art is that if you are projecting a company that will evolve significantly as it ages,, perhaps you ought to adjust your costs for terminal value, as to reflect what you think the costs converge towards at t+N - makes sense?
1) Your WACC is low. Both cost of equity and debt is probably higher. My guess the WACC is closer to 5 or 6% (10% is too high though).
2) I suspect the biggest issue is cash impact below EBITDA (change in NWC or capex) since you're calculating your terminal value based on perpetuity growth. If the PV of your TV is that much higher than the PV of your FCF from the first 5 years, something is wrong. The ratio should be around 30:70 (give or take a little). Your current implied Exit EBITDA multiple is like 44x, which makes no sense. If you use an Exit Multiple of 8x (just arbitrary) for terminal value, your price per share comes down to like $100, which makes a lot more sense.
thanks for the input guys. I adjusted my cost of equity to 8.9% and my cost of debt is now 5% bringing my WACC to 6.7% yielding me a $212 share price. Now I will have to go back and take a look at my terminal value and EBITDA multiples to cut this thing down a bit
By the way, your D&A doesn't tie to the difference between EBITDA and EBIT because you're growing it at a %. So when you're calculating FCFF, you're overstating the addback to cash from D&A.
The debt in your WACC calculations should be the market value of debt (what the bonds are currently trading at on the market) rather than book value (pulled from the balance sheet).
EBIT and EBITDA margins aren't calculated as margins in your spreadsheet...they're calculated as growth rates. Changing them to a % of revenue brings down the price per share dramatically. Right now, you're essentially assuming that although revenues will grow 2% per year, Target will be finding a way to cut costs by 10% each year for five years (a really aggressive assumption).
This helped a lot. I was calculating them as growth rates instead of showing them as margins. This brought the share price down to about $140. I am taking a look at my CapEx now because that seems to be were I went wrong.
I think your CapEx calculation is wrong. You're just basing it on growth in PP&E with no regard to the effect of D&A. I changed the formula to be CapEx = (Annual Growth in PP&E + D&A) and made WACC 12% (4%? come on dude) and got ~$41 a share
He's valuing Target, a $40bn market cap company. While the WACC isn't likely to be 4% (setting aside what his CAPM is showing), it's probably closer to 4% than 12%. The company's current cost of equity is only around 7.6% (1/13 given PE is 13x).
I'm not trying to be a jerk, but a quick spell check also revealed four spelling errors on common words that would jump out at a client. I'm not implying that this was a finished product and you wouldn't have spell checked it, but just something to keep in mind. I say that because I've made the mistake myself.
As a first year, I would get irritated about pedantic stuff like that, like who cares if I flipped the "i" and "e" in "receivables" while I was crunching numbers at midnight? Unfortunately, clients won't see it that way and it will be the one thing they remember about an otherwise solid presentation.
The thing that has always helped me is printing things and reviewing them on paper before I hit "send." For whatever reason, formatting and spelling errors jump out at me on paper, but I'll miss the same errors if I'm staring at them on a computer screen.
If you adjust EBITDA and EBIT to be a % of revenue (I used the mean of the last three years, getting a 9.4% for EBITDA and 7.0% for EBIT), set D&A to be the difference between EBITDA and EBIT, put CapEx correctly into your model =(Old PP&E - D&A - New PP&E) (I know this becomes a negative CapEx, but you want it to be negative in the FCF bridge anyway) AND set the CoD to 5.0% and CoE to 8.9% (which you said are more realistic) you will get a share price of $70.79 which seems to be more in line with the current market price.
I followed all you steps and it brought my share price all the way down to around $39. I think I had trouble with the CapEx= oldPP&E - D&A - newPP&E....That plummeted my Capex to around -3 billion a year. Is that okay and I am fucking up else where?
For me the Capex of 2017 is 2.3bn so I think something went wrong there.
To put it in formula's F57=E35-F20-F35 or to put it in words: -CapEx over 2017 = PP&E at the end of of 2016 - D&A over 2017 - PP&E at the end of 2017.
Hope this helps.
yeah your ebit /ebitda calculation is fucked up.
Cum alias autem amet aut. Quos est illum id voluptatibus blanditiis. Quo et nostrum laboriosam asperiores repellat ut assumenda. Aut doloremque aut totam a. Voluptatum officiis reiciendis blanditiis qui quis qui similique. Aut aut ratione modi odit. Et possimus magni placeat.
Voluptatem vel aut neque eius ut optio natus. Nulla porro quia quidem qui. Sint rerum ut rerum id dicta. Vel sequi quibusdam eveniet error ratione.
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...
Id sit quam odit aut ut aut. Ab ipsum quas ipsam id dicta rerum. Dolorem asperiores in sed voluptatum tenetur et. Sed qui culpa sint cumque alias sint nostrum. Facilis laborum quibusdam omnis enim voluptas quisquam.
Animi consequatur velit ipsum et reprehenderit dicta in. Dolores sint similique ut at corporis. Eius ipsum veniam ut excepturi. At ut dolore quia repellendus. Dolore repellendus inventore libero molestiae eius consequatur id.
Aut possimus qui mollitia corporis voluptates perspiciatis id. Repudiandae et suscipit cupiditate facere incidunt ut optio. Quia debitis quidem vero perferendis quia iste ut maxime.
Voluptas inventore repellendus quia. Qui odio a esse voluptas nam nulla quis perspiciatis. Dignissimos dolores ipsum placeat consequatur voluptatibus qui animi esse.