Football Field Case Study - DCF Range
- 06:12
Use the DCF minimax method to calculate the implied enterprise value of a target company, and how to automate the labels for the valuation range using the text function and references to the data table inputs. How to adjust the DCF valuation by adding the present value of the synergies as a percentage of the target's LTM revenues.
Glossary
DCF LTM transaction multiplesTranscript
We've done our multiples. Next we're going to do our DCF. Now the good thing about the DCF min and max is that the implied enterprise value is just a simple min function. So I'm gonna do the minimum for the DCF valuation and you can need to make a decision about what range you take. I'm actually gonna take the kind of inner circle there, the shaded area, because it will be a slightly tighter range by doing that. So I've got a low point of 75 billion and if I do the maximum again of that slightly inner section there. Now what would be quite useful to do is to have a label concatenated label that says, okay, that 75 number relates to this assumption for wac. And this assumption for the long term growth rate is, I'm gonna do equals open quotes, WACC and then twp ampersands. And then I'm going to put open quotes and long-term growth space quotes. I've got the label there and I can just copy that right now what I want to do is I want to reference the ranges on the other sheet. So I want an automatic label. So what I'm going to do here, where we've got that little piece of glue is I'm gonna put another piece of glue because I want to do a a reference here. And for the WACC, what I want to do is take the minimum of the WACC range here for those three cells. Because remember it's just the shaded area we've got. Okay? And then at the end what I'm going to do is put another piece of glue and I'm going to do the minimum of the long-term growth rate range, which is across here. Now when I hit enter, what you will see is that I will get like these long, long decimal numbers and of course that's just garbage. So there's a little magic tool here, which is the text function. And the text function allows you to format that answer. And I'm gonna do it using the standard kind of formatting, which is gonna be 0.0% and you've gotta put that in quotes and close, then hit enter. So can you see now it's just taking that decimal and apply the format to it. I also need it to do the long-term growth rate. So I'm gonna put a text function wrap around that. So take the answer to that formula and apply 0.0% in quotes as that format. So now you can see you've got a pretty nice labeled that's automatically updated. So if that data table changes, your label will automatically update, which is kind of pure joy from a kind of an efficiency perspective because you don't need to keep changing your labels. I'm going to just do the same thing for the next formula and I'll do it quite quickly. I'll go in and I'll put in another ampersand and I'll do text open quotes or open parentheses. And the value in this case is gonna be the maximum of the WACC range. Okay? But I want to apply the 0.0% formula to this. If you are getting confused, you probably just want to do this in separate components. You can see that I've got an error message already and that's because I have missed out my, I put a quote in the wrong place. Let me just put a quote there and then that should let's see why this is getting, there we go. Okay, I was missing a second quote there. There we go. So that works. I'm put a space there and then and a space there and a space there, and then a bit of glue and then text and then the maximum. And this is the long-term growth rate. So it's a little confusing when you first do this and you just kind of get used to it, but you've gotta be slightly careful. So take that, the answer to that number comma, open quotes and apply the following percentage, 0.0% close parent again. And it's all got a little error. So let me just fix this. So that needs to have a quote at the end and let's hit enter and we are missing a little bit of a space there. Bit of space. There we go. So we've got really nice automated labels and that's the last automation I'm gonna do for the labels there. But we've done our standalone DCF methodology, but I want to now do the DCF including the synergies. So I'm going to just apply the synge value to both the min and the max number for the DCF valuation. So I'm gonna go to my synge and I'm gonna get my assumption for the synergies as a percentage of the targets LTM revenues. And I'm just going to put my formulas in so you can see what I've done.
And then the value of the DCF synergies. I'm just going to take one number. So the present value of the DCF synergies, it's just going to be that one number. You could of course do a range if you wanted to, but it makes it a bit more complex. But then I'm going to take the implied enterprise value plus the synergy number to get a range, a kind of controlled valuation range based on a DCF, which is between 89 and 96 billion Euros.