Hi, welcome back to finance for non-finance professionals. In this video, we're going to take the tools that we've learned in the first four videos or so of week one and build a practical example using discounted cash flows, or DCF. I promised you we would start doing some real nuts to bolts valuation and here's a video where we're going to do exactly that. And at the end of the video build a spreadsheet model to do the valuation. Okay, here's the example. Let's say we had a well in the desert and I want you, the student, to sort of join my Mergers & Acquisitions team. What we're going to do is, we're going to out, we're going to put a bid on this well. We're going to think about what's a fair price to pay for this asset. Okay, so how much is it worth? How much should we pay for it? Well, you might remember from our discussion of discounted cash flow, that's going to depend on how much cash is coming in, the timing of that cash, and the opportunity cost, or the discount or interest rate. Okay, so let's say the well is, we dig a hole and it's dry. There's no water in the well. Okay, how much is it worth? Well, I don't know, if we got accountants in here they might have an answer based on sort of depreciation of the shovel and all kinds of interesting findings. But from a finance perspective what is it that we're looking for? We're looking for cash coming in, in the future, when is it coming in? Okay, if the well is dry there is no cash coming in. So what's the right answer for what this well is worth? It's worth zero, it's worth nothing, right? If there's no cash coming in in the future, it's not worth anything. Okay, let's say instead though the well is connected to an active source of water. Okay, so now we can think about there is stuff coming in. If there's water coming in from the well over time, let's think about putting a price on it. So what would we need? What kind of information? What will be the value drivers? Well, how much water is there? Let's say it's infinite, 20 million cubic tons of water, it doesn't matter. Huge amount of water, huge well. Okay, how much does it produce? Let's say it produces one gallon per day, it's a slow well. Okay, how much cash flow can we get from that one gallon per day? Let's say there's one, I don't know, postal worker, that comes by the well every day, and he'll pay for that gallon of water. Net of cost. Let's say the total cash flow that we can get, total profit, is $1.50 a day. Okay, so now we've got some things to work with, right? We know how much cash is coming in, a $1.50. When is it coming in? It's coming in a day, every day. How long is that cash going to come in for? An infinite amount of time. Okay, let's think about whether we can put an actual practical price for going out and putting a bid on that asset. Let's do some DCF valuation and figure out a real price for this thing. Okay, what's a realistic price? Let's map out the cash flows for the timeline. So what have I got? I've got a series of cash, a $1.50 coming in every single day out to infinity. What am I going to do with that? Well, if I add it up, a $1.50 plus $1.50 plus $1.50 plus $1.50, if I did that an infinite number of times. What's a $1.50 times infinity? Well, it's infinity. So is a well paying a $1.50 a day worth an infinite amount of money? That doesn't seem right, doesn't feel right. Of course, that's probably not true. Okay, so what are we missing by adding up the cashflows? Of course, we're missing discounting, right? That $1.50 coming in ten years from now one day isn't worth a $1.50, it's worth way less than that. Remember that exponential discounting that happened back in our present value lecture? Okay, so if we think about that, that discounting, that exponential discounting that happens that makes the cashflow worth less, less, less, over time means that those values, those $1.50s coming in year after year after year, the value of those today is going to taper off as time goes out to the future. Well, let's think about from a practical standpoint, how we would go about doing that. We'll build a spreadsheet model. Let's assume the discount rate is 15%, and what we're going to do is, since we've got a lot of $1.50s in there, let's see how much it's worth. If we could do it pen and paper, we'd have to discount the 1.50 for a day, discount the 1.50 for another day. We'd have thousands and thousands of $1.50s to discount. And it would take us a long time. That's where the power of a spreadsheet model is going to come in handy because it's going to be easy to copy rows down all the way down in Excel and see when the value of those sort of taper off. So, what are the cash flows? What are those cash flows worth today? And add up all those present values. That's how we're going to put a value on this well. We could do it pen and paper, but it's going to be a lot easier, and what people really do in practice is build spreadsheet models to do these kinds of DCF valuations. So, let's move now to the spreadsheet and work through the example in practice. Okay, I've set up the spreadsheet model for us to think about how much this well is going to be worth. What we need to do is map out the cash flows and then discount them, and then add them up. I've got my assumptions for the model up here on the top. My assumptions are what the discount rate is and how much cash flow is coming in. And it's a $1.50 not per year, but per day. Let's get that right. Okay, so $1.50 a day and a 10% discount rate. What we're going to do now is map out in Excel what those cash flows are, discount them into the present, and then figure out the discounted cash flow. And that's going to be our answer, the output up here. So, the structure of the model is that I have my assumptions up top, my output over here. And down here, we're going to do the cooking. We're going to take the inputs, take the assumptions, do the mechanics and then come up with the output. So, what are our cash flows going to be every year? Our cash flows every year are going to be a $1.50 a day and I'm going to put dollar signs in front of that in order to lock that cell in so that it's the same when I copy it down. And time let's just go 365 days a year. So how much cash is that per year? That's cash of $547 a year. And now all I'm going to do is, when this little button down here becomes a little plus sign, see how it's a big plus sign and then a little plus sign. I'm going to double click, and that copies that down all the way. Now how far am I going down? I'm going down 200 years. Now, what we'll see when we do the discounting is, whether or not that really matters. But let's assume we said the well was going to live for an infinite amount of time. For now, let's just say 200 years is close enough to infinity. Okay, so now what we're going to do is we're going to take the present value of each and every year of cash flows. So I'm going to say equals the cell next door divided by, and let's just use our formula. 1 + 10%, and again, I'm going to put dollar signs in front of that so that it stays the same when I copy it down, that cell reference isn't going to change. And then I'm going to take it to the exponent of the year. Okay, so that $547.50 coming in at the end of year 1 is worth $497.73 today. Now, I'm going to hit Ctrl+C, Ctrl+V, and copy that down one cell, and let's double check the formula and make sure it's working. In the next cell down, two years out, yeah, that's good. I'm taking the $547.50 in year 2, I'm discounting it at 10% and raising that to the second power. So that formula's copied down well. The 547 coming in two years from now is worth 452. I'm going to again go from big plus sign to little plus sign, big to little, double click and copy that formula down. Now what do we see happening as we go further and further out in time? Just like we saw in that chart, the more years I go out, the less that money becomes. By the time I'm 30 years out, that $547 is only worth 30 bucks. As I go out further and further and further, let's go out 50 years, that's worth $4.66, that exponential discounting is really kicking in. By the time I go down 200 years, it's zero. So we really don't need to go out much further than that because we're adding zero by the time we get out there. In fact, of course there is some value there, it's 0.0000000 something or other. But we can pretty much ignore once we get past a certain point. Okay, now all I have to do to figure out the value of the well from a discounted cash flow perspective is add up. And that's what I've done up here, is just sum. So, let's do it together. =sum. And I'm going to go and highlight all these cells. And as I sum up all those cells all the way down, that's going to give me the answer, the sum of the discounted cash flows. I'll just do this, I would say a 100 years or so. Whoops, let's go up and see what we did wrong. There we go. That's the sum from C8 to C207, which gives me the sum of the discounted cash, the answer is $5,475. Now, remember you were on my Mergers and Acquisitions team, we were going to go out and make a bid on the well. How much is the well worth? We've got a real answer to that question now. With a 10% discount rate and cash flow of $1.50 a year, how much was a fair price for that well? The answer's $5,475. What would happen if the cash flows were a little bit higher? Well, that's easy, we could just change that. If we're bringing in $2 a day, it'd be worth $7,300. If it were only bringing in a $1.00 a day, it'd be worth $3,650. The nice thing is, once we've built this spreadsheet model, we've got the flexibility to change the assumptions. If the discount rate was a lot lower, if I were a lot more willing to be patient, The well would be worth a lot more. If the discount rate were higher, The well would be worth a lot less. So again remember the assumptions of our discounted cash flow analysis. What's going to drive the basic valuation is how much cash is coming in, when is it coming in, and how heavily am I discounting it? In the simple example that we had of a $1.50 a day for infinity, discounted at 10%, the answer is $5,475. All right, so now you've built your very first spreadsheet model for solving valuation in Excel or any spreadsheet model really. So that's great, because a lot of what finance practitioners do in practice is build spreadsheet models for doing financial valuation. And so, now we've walked through a very simple, but practical, DCF application. And if you were playing along at home, you could build your own spreadsheet model for doing that kind of DCF valuation. All that we're going to do in future DCF valuations is add a little bit more complexity, or a few more wrinkles. But the basic concept of figuring out the timeline of the cash flows, discounting them, and adding them up is going to be the same no matter what application we're doing. So this is a really, really good basic foundation for a lot of what we're going to be continuing to do in the class.
