0:00

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.

Â