Alan Kring Productions in association with Emergent Light Studio presents
the Illinois State Collegiate Compendium, Academic Lecture in Business and Economics.
This is Business Finance, FIL 240 for Autumn Semester 2023.
Today, Project Free Cash Flow Analysis.
We will go through some terminology and then I will begin a worksheet in Excel.
So have your Excel out and ready to climb into it.
And I'll finish it up with some more examples on Wednesday.
As I said before, and you'll see this for yourself,
setting up a template exactly is, you can't really do that because there could be a varying number of years.
And I could write macros to do that, but the macros wouldn't survive very long here on the servers of the school.
So what I'll do is, we'll go through it.
I'll upload it to Canvas this evening so that you can have it to look through.
And then I'll build another model on Wednesday just so you can see how it's done for a quiz or for the final exam.
But first now we look at the numbers and see what kind of happiness has proceeded today.
And it wasn't much happiness.
The markets were just absolutely dull.
Hardly anything was going on.
As you can see, the Dow was up a raging 0.16%.
The S&P was down a paltry 0.08%.
And the NASDAQ, well actually the NASDAQ kind of started tailing off there at the end, 0.22%.
But it was a really quiet day on the street.
And if you look over here at the S&P 500, the volume was barely half of what it is on a typical day.
Essentially, investors are just staying on the sidelines.
There's not much buy and sell activity, relatively speaking.
So it's just everyone's waiting to see if good news comes or bad news comes.
Of course, crude oil is up some today, but it's still well below where it had been.
And we're still waiting for gasoline prices to catch up with the recent drop in crude prices.
But there's that.
Gold had a little bit of a surge there.
You see, right after the midday, it popped for some reason.
Probably some conspiracy theory popped up.
And so the gold bugs grabbed some gold, and then they didn't do any more for the rest of the day.
Nothing to worry about there.
Now, the 10-year bond, the yields spiked up, but they just couldn't hold it.
They just dropped right back down.
So in other words, yields spike up, price drops.
It was a sell-off.
But then, there was right not too long after that, about an hour after that, the buying on bonds began again.
Because of the price it dropped, so there was bargain hunting.
And so buying drove the price up, and then the yield went down.
That's good news, of course.
Interest rates are lower, and it's because of the drain of expected inflation from the risk-free rate.
And all of the rates are showing signs of easing up on their expectation of inflation.
Now, the Pound Sterling, well, interest rates, yields on our debt, U.S. debt, going down.
So that means that the dollar weakens.
The euro strengthened a little bit against the dollar, and so did the pound.
Heaven knows what's going on with the Japanese yen these days.
But over on the other side of the world, first the Nikkei had a strong opening bell pop,
and then it just piddled away, and by the time the day was over there, it was back down to pretty much where it started.
And then the sun set in Tokyo, and it rose in London.
London had a little surge, and then again at the end it had a little surge.
But boy, that sure didn't do anything over here on our side of the Atlantic.
We started out down, and then crawled our way, groveled back up to about even for the day.
So it's kind of hard to say.
The good news, though, is that a survey came out how much consumers are planning to spend for the holiday season.
And it was favorable.
I guess it was average household plans to spend more than $1,600 for Christmas season up from last year.
And no, that doesn't mean we're each going to get $1,600 in presents.
But at the same time, strong holiday showing that takes us into the new year in good shape,
and the economy rolling along in recovery or early expansion.
Good news.
But just to look at a couple of companies to have a look at what to keep you fresh on, looking at the market data.
Look first at Walmart.
At Walmart, it's about half as risky as the market in a well-diversified portfolio.
Very safe stock.
Price-earnings ratio is right about good value.
And it's profitable, $5.20 a share.
Not a very spectacular dividend.
So just to have a quick look.
See, a one-year holding period return based on Yahoo's projection of the price in one year,
you'd have $168.89 in one year, divided by buying it right now at $167.68 minus one.
And then multiply that by 100.
You've got less than 1% increase capital gain on the stock.
That sucks.
Even, well, yeah, it's a low-risk stock, but still.
0.72% gain.
So, well, let's add in that wild dividend of 1.37%.
And you get a total holding period return for one year on Walmart of a lousy 2.09%.
That's really weak.
And yes, this is a low-risk stock, but that's still pretty darn weak.
Feeling around for something else that we might want to look into.
Trying to think of something else that would be...
I might have done this one before.
Okay, Yum Brands.
Yum is a restaurant holding company.
Now, the restaurant scene is in turmoil because there are some restaurant chains that are in bad trouble right now.
Burger King, three of its major franchisees all went bankrupt.
And that means Burger King, the central company which uses the franchise model, is not doing well right now.
But on the other hand, you've got other companies like McDonald's which doesn't rely as heavily on the franchise model.
We'll see.
But Yum sort of is a broad-based look at the restaurants.
And if you look at them, about as risky as the market, 1.04 beta, little undervalued, good earnings, profitable $5.26 a share, and it's a dividend, 1.92%.
Looking at their one-year holding period return, we would do $143.43 projected to be the price in one year divided by buying it today at $125.31 minus one, and then times the result by $100.
So the capital gain, the rise in stock, is a decent 14.46%.
And then you add in that little dividend, 1.92%, and you get a total one-year holding period return of 16.38%.
That's more like it.
That's kind of what we would expect the market to be earning about right now, and this stock is right about at market beta.
Little riskier.
So that one might be something worth looking at for a well-diversified portfolio if you want to participate in the restaurant scene.
But typically as a recovery gets underway toward an expansion, you'll see more going to restaurants.
And so if you believe that we are in a recovery, well, restaurants wouldn't be a bad investment, as would other things too.
But anyway, that is the look of the market right now.
And now to the fun of doing some spreadsheets.
But before I do that, I want to take one last pass at risk.
And this is looking at risk from the perspective of projects.
And we can look at three, we can talk about three different kinds of risk.
And I've talked about two of these, or one of them, definitely.
There's standalone risk.
This is what I talk about when I talk about sandboxing the project.
You want to look at this project as if it is its own company.
Project on its own, incremental cash flows.
The only revenues and costs of the project, cash flows, and all that.
And the measure you would use for that, first one would be the standard deviation.
Just plain old Excel, S equals STDEV of the free cash flows.
See how much they swing.
Standard deviation.
In other words, sigma.
Another one you can use would be the coefficient of variation.
Which is the standard deviation of the free cash flows divided by the average of the free cash flows.
Now, a second course in corporate finance.
We would beat the heck out of coefficient of variation.
Every line on the projected income statement, we would run the CV on it.
So that we would see how the different lines, what their individual volatilities are.
But overall, standard deviation of the free cash flows.
Or you could do the standard deviation of the free cash flows you've projected.
Divided by the average of the free cash flows you were projecting.
I mean, you put the numbers into Excel and then you just type in STDEV and all that kind of stuff.
Okay, that's standalone risk.
That's one of the ways we can measure the risk of a project.
Another way we can do it is to take what is called the corporate approach.
Corporate risk.
In this one, we step away from the sandbox model and we look at the project
as one investment in the company's portfolio of investments.
Portfolio of projects.
Okay, you're trying to mean by that.
Look, the project is going to have volatility up and down.
But if you think about it from a portfolio perspective,
this project's variations are going to come in the context of the variations in free cash flow
of the company's existing projects.
So in other words, the eye here would be on, look at the correlation between this project's free cash flows
and the other company project's free cash flows.
In other words, this project on its own might have a lot of ups and downs.
But maybe there are other projects in the company that have ups and downs
that somewhat cancel out this project's ups and downs.
It's like stocks in a portfolio.
One stock in a portfolio is going to go up and down.
But other projects, if their correlations aren't very strong,
they could help cancel out the ups and downs of each other's free cash flow.
It was just like I talked about with portfolio diversification in stocks.
You put different stocks in and they tend to help each other out
in terms of diminishing that diversifiable, that non-systematic risk.
And that's a legitimate thing to think about in this too.
Now, here's the other side of that.
Yeah, this project may have free cash flows that are sort of not very well correlated
with the free cash flows of other company projects.
However, most companies, well, I should be a little careful about this.
Most companies tend to have projects that are of the same type.
A company has a core competence.
And therefore, you will have projects that tend to have free cash flows
that would tend to behave the same way.
So even if you get a new model of car,
well, yeah, its free cash flows will go up and down.
But if you're building other cars,
theirs will tend to do the same thing to a certain extent.
So the correlations will be high so they won't cancel out each other's volatility very much at all.
Most companies don't grab projects that are way off the radar of what they've already been doing.
Taking on a project that's completely different,
yeah, that would help with your corporate risk,
but you're going to have to learn something new,
do something new that might be not what you really can do very well.
The stories about companies that have tried to go outside of their core competence are many,
and usually they end rather badly.
I could pull out dozens of examples.
One, JCPenney.
Many years ago, before JCPenney's big problems set in seriously,
it was a well-respected, heavy-duty retailer of apparel and such things as that.
No problem with that.
So they had a lot of cash.
Well, what they could have done, which they should have done,
was clean up their stores, modernize them,
get started on moving towards the 21st century while they were still in the 20th century.
But they didn't do that.
They decided, hey, let's get into a cash cow business.
So they bought a cherry-picking boutique insurance company.
It was called Educators and Executives,
and it catered to higher-income, low-risk drivers, auto insurance, E&E.
And so JCPenney said, well, we'll just acquire that, and we'll put our expertise into it,
and we'll make big bucks, spend our money on a sure bet.
Well, they did that, and that was a big disaster,
because first of all, being a retailer, their first thought was,
let's get the revenues of this insurance company higher.
Let's push the revenue.
And so in order to do that, they started offering the insurance to more people, more households.
Well, that meant that they were going to take on more risk than what E&E had had with Educators and Executives.
You were getting lower and somewhat higher risk.
And so suddenly, their premiums started coming in much better,
but then the payouts started happening at a much higher level and rate.
And so they suddenly saw, oh, well, let's fix that.
We'll just get more premiums in.
So they started selling insurance to even higher-risk clients.
And so then revenues came back, but then the payout ratio started going through the roof.
And by the time it was over, JCPenney had gotten creamed,
because it was in a business where its business model worked,
and then it tried to apply that business model to an industry where that didn't work.
And that's the idea. The problem is that most corporations aren't stupid enough to do that.
They'll stay with projects within a core of competence,
which means the free cash flows are going to tend to be highly correlated.
And so the corporate risk is going to be approximately,
rather, the project risk is going to be approximately what it was before.
There's another kind of risk we can look at, market risk.
This one is controversial.
I mean, from a pure economic standpoint, it makes sense.
But at the same time, okay, here's how it works.
You, madam, you're a wealthy, high-powered investor, Wall Street type of person.
Well, you see, I'm the company.
And I say, well, why am I bothered by the risk of this project?
You're the investor. If you don't like what we're doing, then sell the damn stock.
But if you like what we're doing, you'll keep with us.
That's your problem to decide whether this risk of this new project is worth it or not
for your investment purposes. It's not ours as a company.
We will do what we, in our business, best business judgment,
considers the maximization of shareholder wealth.
It is your job as an informed investor to decide whether that's the right thing or not.
That's the market perspective.
Why are you, well, it's overstating it.
Why are you, C-suite at the company, worried about this?
This is Wall Street's job to evaluate and to allocate, do portfolio allocation
based upon your assessment of the risk of this project in the company.
In other words, we wash our hands of this whole issue here in a way.
We obviously want to do free cash flow analysis and all that good stuff,
but in the end, it's the market that's going to decide for us.
Now, the problem with that idea, there are a couple.
The first one is, this is elitist AF.
What about the average, normal Joe investor like you?
You don't have the tools to do this kind of analytics.
You're just going to buy stocks. Yeah, I'm going to buy a stock today.
Or you're going to be a day trader and think you know what you're doing.
But at the same time, this market approach is very elitist.
Only Wall Street matters.
Unfortunately, that's probably true.
But there you are.
Welcome to the age of democratization of diminishment.
Okay, now the other flaw in it is that you are absolving the company of one of the big things.
Look, if this is too risky for that sophisticated investor,
she and all of her fellow sophisticated investors are going to sell our stock.
That's going to drive the price of the stock down.
Now, if you remember what we did with the component cost of capital,
remember price is in the denominator of the cost of equity.
So if they sell the stock, the price goes down.
And so the cost of equity capital and therefore the weighted average cost of capital goes up.
We're not doing ourselves any favor if we piss her off and all of her wealthy friends
and they drive the stock price down because that drives up our weighted average cost of capital.
And then guess what?
When we do our free cash flow analysis, the whack that we use to discount the free cash flows,
we're going to create a condition where it's higher than what we use
because we're going to make it higher through driving the stock price down.
That kind of takes you around a little bit there.
But if you think about it for a while and play this podcast 10 or 20 times, it begins to make sense.
But anyway, enough of that.
So just always keep those things in mind.
Now, I'm going to do a simple free cash flow analysis for you.
And I'll use Excel to do this.
And then we'll bring it back up and I'll do another one or two on Wednesday just so you get used to it.
Because once you get the hang of it, all you really do is like a template.
You just plug in numbers and see what comes out of the mess that has been created by it.
So to do this, let me put up some numbers for you.
And remember the formula for free cash flow, revenues minus cost times one minus the tax rate, and all that good stuff.
So let me pour through some.
Now you notice something here.
I created...
Okay, good.
I created a sheet one.
Now sheet one will be the free cash flow analysis.
Well, let's try clicking on it to rename the sheet.
Sheet one will be the free cash flow analysis.
Sheet two is going to be the inputs.
So as I do in almost everything, I am separating the formulas from the numbers.
And that will give me the ability to change the numbers.
I could do a sensitivity analysis.
Well, what happens if the revenues are 10% higher or 5% lower?
So that second sheet is always what we do in Excel.
And this is true actually even in web page design.
You separate the formatting of a web page from the text that you want in the pretty pictures you want in the page.
We do this separation a lot these days.
It actually, truth be told, goes clear back to gaming in the 1980s and early 90s.
But inputs.
Okay, so what we'll do is we're going to break this thing down.
We're going to do a couple of things that will make it a little easier to do.
We're going to do equipment cost first.
So the equipment is going to cost, let's say, $280,000.
Along with that, the equipment eligible for 100% bonus depreciation.
In other words, now I may slip in my wording, but this is the old schedule 179,
where there was some equipment that you could just expense it the year you bought it.
Just expense it.
So in this case,
and now we're going to do the tax rate here, and for lack of greater creativity, we're going to do 28,000.
25% just to make the numbers rounder.
You can do 21% later.
Now, oh, I forgot up here.
Now the equipment eligible for the bonus is $280,000.
In other words, we can depreciate the entire cost in the first year.
So the after tax cost of the equipment would be equal to the whole thing, 200 B2,
times, open parenthesis, 1 minus the tax rate, $210,000.
Okay, now the next thing we're going to do is I'm going to drop down one row.
We're going to talk about changes in net operating working capital.
And we're going to do two.
One is inventory.
We're going to have to buy $25,000 in inventory.
That's going to drain free cash.
But we're also going to increase our accounts payable by $5,000.
So our net change in net operating working capital is going to be the $25,000
minus the $5,000.
Let me do something over here.
You don't have to write this in.
I'll upload this.
Equals formula, text, open parenthesis, that.
That way the formula is there for you to see.
Now we're going to drop a row and then put effects on operations.
And under this one, let me do something here.
Effects on operations.
So we'll sales in units.
Say we'll have 100,000 units sold.
And price per unit, $2.
So gross revenues will be equal to 100,000 units per year times $2 per unit.
So, salvage value.
Savage value, salvage value, say 25,000.
Now, the book value, you depreciated it all the first year.
So the book value is zero.
So, taxable salvage value
is going to be $25,000. Well, let me put that right here.
Savage value minus book value will be equal to that 25,000,
line B15 minus, zero dollars, B16.
Savage value after taxes will be the taxable,
well, will equal the salvage value minus 25% times the salvage value.
So in other words, after we pay the taxes on that $25,000,
we get to keep contributing to the last year's free cash flow, $18,750.
So here we go.
Year, free cash flow will go zero, one, two, three, four years.
So the free cash flow at the beginning will be the negative of the
after-tax cost of the equipment minus the net change in working capital,
which will give $230,000.
Formula text.
Year one would be equal to your gross revenue minus,
oh, I didn't put something in there, I apologize.
On the inputs, insert a row on the inputs between 14 and 15.
Forgot the variable cost, let's say it's 60%.
Apologies for that.
Let me see if I can get all the formulas in there for you to see.
Now, apologies, let's try this again.
Go over to your free cash flow analysis,
and that will equal your gross revenues,
which was on the input sheet, cell B14.
I'm going to make that an absolute reference, F4, so that as I drag that
formula down on the first sheet, it won't change the location of that.
Minus 60%, absolute reference, that's cell B15 times the gross revenues.
Cell B14, and again, as an absolute reference.
That's $80,000.
Now I'm going to drag this cell B3 on the analysis sheet down clear to 4,
and I'm going to have to put some extra stuff into 4.
Therefore, you get your last year's income, the $80,000.
But you also get some other things, so I'm going to edit the formula for cell B6,
because I also have to add back, plus go over here to the inputs,
because my inventory will be recovered, but I will have to pay off my accounts payable.
So I have to add this back at the end, in the last year.
I get to have the money back from paying for that inventory every year,
so I get to add back $25,000, but I also have to pay off that continuing rolling accounts payable of $5,000.
And also, I will get back my salvage after tax salvage value, $18,750.
So in year 4, I do that right.
I got my last year's revenue, minus 60% of the revenue,
and I get back in my net working capital, and I get my salvage after tax salvage value.
So there you go.
Now in the inputs, I'll do one last thing here on cell A21, I'll type in the weighted average cost of capital.
And in B21, I'll say, let's say our WAC is 10.00%, 10%.
So now we're finished. Front-free cash flow, I can do the net present value.
The NPV of the project is going to equal and sell B8.
First, I have to take, I add, I take the initial investment, negative $230,000, plus net present value, NPV,
open parenthesis, sells B3 through B6. Whoa, oh, I forgot my weighted average cost of capital.
I'll say it from the beginning again.
So B8 equals the initial investment, negative, which is B2, plus the net present value,
I forgot to put in the weighted average cost of capital.
Open the parenthesis, go to inputs, and get the cell B21, the WAC, for the discount rate.
And then, then I go back over and take cells B3 through B6.
Close the parenthesis, $39,811.
If I want, I can do the internal rate of return, which would equal IRR,
and you just take cells B2 through B6,
which gives us, oh shut up, a 17.45% internal rate of return,
all above the weighted average cost of capital.
That is the whole thing, right there.
Now, the key here is that these would be the numbers for a basic free cash flow analysis,
which is how far I would go on a quiz or the final exam.
The only difference would be the number of years that might be involved.
You might have to put in years 0 through 5, years 0 through 6, something like that,
in order to have enough for the, if I said the life of the project is six years.
Matter of fact, something I want to do here, I try to remember whether I did it or not.
Let me see.
I'm going to put in a line here, insert, add in the inputs worksheet for row 20.
I'm going to put in life of the project, just as a reminder of how many years you have to put in.
Four years.
And then it's $4, four years for heaven's sake.
There you go.
And that, basically, the inputs side of it is like a mini template.
But again, it's not a true template because free cash flow analysis would have to be modified
for the total number of years of the project.
Now, there's a way I can put that in here, but it's a little bit on the heavy duty side.
I could do it like this.
See that year one there in FCF?
I could say equals sequence.
Sequence C. Sequence
rows
four
comma start one step one.
My ass.
Looks like I didn't do that one right.
I thought I was going to show you a neat trick.
Why is it crabbing about that?
B20.
Yeah, life of the project's fine.
If I take these out.
Why is it saying the spell, did you say?
So, you're saying two?
Oh, I see.
A, no B20.
And then what?
Oh, I get it.
I did not know that.
It doesn't overwrite, does it?
Ouch.
Well, learned something there.
I guess if I keep that, it could be a full blown template.
Huh, isn't that interesting?
Well, you learn something new every day.
I may be able to create a full blown template now.
If I, as long as I remember to put in the life of the project for years,
I can actually give it the rest of this.
And it'll just spit it out.
And then all you have to do is fill in the numbers over here.
And you're in business.
I might be able to build that.
I'll see if I can.
But for now, I'll just upload it as it is to Canvas like this so that you at least have a working model of what happens.
But the good thing about this is that you can now do essentially scenario analysis, very simple scenario analysis,
change the tax rate, you change the gross, the variable cost from 60% to 50% or whatever you wish.
And you could even change your book value at the end and all that kind of good stuff.
So there's something there.
So anyway, and you could change your weighted average cost of capital.
But obviously, as this project is shown here, it is definitely a positive NPV project.
And the internal rate of return is well above the weighted average cost of capital.
And you could even do it like this, equals if NPV greater than zero, then accept.
Otherwise, reject.
You could even throw that in there to make it look really fancy.
And those are pretty easy to do.
That's it.
That's all there is to doing a free cash flow analysis.
And again, this is a really simple one.
Flat revenues and all that good stuff and using schedule one, or bonus 100% depreciation to do it.
But nevertheless, even if you build it more complexly, we usually start with a simple one
and then just stack stuff into it.
But for your purposes, if you know how to do this, you've got yourself a good shot
at what looks like a really hard question on the final exam.
And as far as that goes, that's all I have for you today.
I thank you.