JAMES P. WESTON: Hi. Welcome back to finance for non-finance professionals. I'd like to talk in this video about spreadsheet modeling. Spreadsheet modeling is a really powerful tool for the financial analyst to use for computing a lot of the capital budgeting stuff that we've been talking about in week 2. Spreadsheets are powerful, because once we build the big spreadsheet model, we've got it forever. We can then take that spreadsheet model and use it in lots of different applications just by tweaking and pulling and adding rows and cells to adapt that spreadsheet model to whatever our specific problem is. Spreadsheets have an organizational structure to them, and there's a set of best practices that we can use for financial analysis for making sure that the spreadsheet is sort of robust and the model is tight and it's well organized, and I want to talk with you a little bit about that. We're also going to go over some of the built in spreadsheet functions. We'll do our analysis in Excel, but most of the spreadsheets that are out there on the market have lots of built in finance functions, like NPV and IRR, present value and discounting and compounding. A lot of that is built into these spreadsheets, which makes doing the financial analysis that we've talked about much easier in practice. The other thing we'll do is a general layout and review of the capital budgeting tools that we've talked about so far in week 2. So let's go ahead and open up a spreadsheet and start applying some of the capital budgeting metrics that we've talked about. All right, let's take some of the capital budgeting tools that we've been talking about and let's build a simple spreadsheet model for analyzing capital budgeting tools. So what I want to do first is talk about spreadsheet organization. You can see that I've got a title up here and what I've got listed here first are the assumptions that are going to go into the model. Those include our discount rate, 7%, and we're going have an initial investment of $5,000. And that's going to generate yearly cash flows in the future for first six years of $1,500. Now, I've got all my assumptions listed up here in one place. And that's always good spreadsheet hygiene, or best practices. If we go and if I send this to a colleague or I send it to a coworker or the project gets morphed and somebody is looking at this spreadsheet five years later, having assumptions hidden in strange places throughout the spreadsheet is going to cause everybody headaches and confusion. Everything that goes into the spreadsheet, everything like all the ingredients that go into cooking this capital budgeting process are going to go right up here in the assumptions. If I assume anything else, maybe some inflation or some costs, that all has to go up here under assumptions. That's the best thing to do. Everything's in one place. That also means I know what to change. If I want to change any of the assumptions, I'm going to do that all up here in one place. OK, now below the assumptions is where we're going to do some cooking. And I've got kind of the road map lined up for you here. My first cash flow is going to be that initial $5,000. And then my subsequent cash flows are going to be $1,500. And again, I'm going to lock that cell with a dollar sign on the column and a dollar sign on the row reference so that when I copy that cell, if I just drag it across and it fills, it's always going to fill with the same cell reference. So those dollar signs make sure that I'm always copying over the exact same cell. Otherwise, it would start copying over the cells next to it. The dollar signs lock that reference. OK, good. So we're going to take this very simple set of cash flows, spending $5,000 in order to generate six years of $1,500. And let's discount them. So I'm going to take that cash flow. I'm going to discount it at 1 plus 7%, and I'm going to again lock that cell so that when I copy it over I'm still grabbing the 7%. And then I'm going to raise it to the period here. Now, you might be questioning why I'm raising the initial cash flow. But raising something to the 0 power just gives you 1, so that should just come out straight with $5,000, as it does. Then all I need to do is grab that formula and copy it over six periods. Let's double check the formula, make sure it's doing the right thing here. Good. So I'm going to grab the $1,500. I'm going to divide by 1 plus 7% raised to the first power. That's exactly what we want it to do. By the time I'm six periods out, I should be grabbing the $1,500, discounting at 7% for six periods. Looks good. Looks like everything copied over correctly. Now if I want to calculate the net present value, what am I going to do? I'm just going to sum up all those cash flows, discounted cash flows, the sum of the discounted cash flows. There we go. Good. And I get a net present value of $2,149. That's bigger than 0, so my NPV capital budgeting tool would tell me go ahead and do the project. The project's worth doing. It generates more value, which we can kind of see, right? It's kind of generating a whole bunch of money here that's going to be in well in excess of the $5,000. There's $1,000, $2,000, $3,000, $4,000, $5,000. Yeah, we're going to be generating lots of money here. Our net present value is $2,149. Now, we could also do this easily in Excel using a built in formula. Lots of spreadsheet programs have this function built in. As soon as I type in equals NPV-- you see, it already knows what to ask me for. It says, what's your discount rate? I go and grab that. And it says, what are your future values? I'm going to go and grab those. Don't grab the discounted ones. It's going to do the discounting for us. And then I'm going to tag on the initial cash flow. The NPV formula doesn't include the initial cash flow, so we need to tag that on at the end. That should give us the same answer. There you go, $2,149.81. It's easier to do it with the formula then it is to do it by banging out all the present value of the cash flows like we did in the table, but it's good to see what's going on behind the formula before we start using it. Now, one of the things I could do to find the IRR is I could start changing the discount rate, maybe if I made the discount rate 15%. No, it's still positive. Maybe if I made the discount rate 22%. No, somewhere in between. 20%? Close. There's an easier way to do it, right? I'm trying to get the NPV to equal 0. 21%? Let's go back to 7% discount rate, which is the original discount rate, and let's just ask Excel to solve it for us. All we need to do is type in equals IRR, go and grab those initial cash flows-- not the discounted cash flows, but the raw cash flows. Close that. Boom. Excel solves it for us. Any of the spreadsheet models that are out there will solve that for us. And the IRR is 19.9%. We could do a little payback on this, too, and start accumulating the cash flows. $5,000-- and then what's the cumulative cash flow? I'm going to get that $1,400. I'm going to add it to what the cash flows were in the last period. So now each period, I copy that over. I'm going to grab the new cash flow and add it to the accumulation of cash flows. Has it paid back yet? Not yet, but if I copy it over-- not yet. Copy it over-- not yet. Copy it over-- there we go. It looks like some time during year four, maybe in the first or second quarter of year four, the payback on this goes from minus to positive, which means sometime around year 4, I'm going to recoup the initial $5,000 that I spent. That makes sense, right? Because if I look at the first four periods, those add up to about $6,000. So sometime probably during the first quarter of year four, I've recouped the entire $5,000. The nice thing about building a spreadsheet model like this is that now we have it. Now we have it in sort of our utility belt of tools that we can use to do capital budgeting. If I pull up a similar project, all I need to do is pull up this spreadsheet, change some of the assumptions, maybe add a column or delete a row, and I've got sort of a built in power horse for working through any of the capital budgeting problems that we have. Looking at the net present value of the IRR at the payback, this looks like a good project. The project returns about 19.1% IRR. The NPV is bigger than 0, with a reasonable payback of around four years. I would say the project is a go, an accept project. And what we can see now is that having built in all these things into the spreadsheet, we've got a nice, flexible, adaptable, well-organized model or tool for doing all of our capital budgeting analysis. Great. So hopefully you've had a little bit of experience now building a simple spreadsheet model and figuring out sort of how the layout of a typical spreadsheet model goes, with the assumptions up top, the cooking down below, and a clearly articulated answer that depends on the assumptions. Hopefully, we've also had a little bit of review of the spreadsheet functions that we use to do the capital budgeting tools and a general layout for how spreadsheet modeling goes. In the next week, in week 3, we're going to talk about cash. We're going to talk about cash creation. We're going to go through the accounting statements and try to put the capital budgeting tools that we've talked about this week into a little bit more specific context, looking at actual financial statements from real firms.
