1
00:00:00,000 --> 00:00:08,000
Alan Kring Productions in association with Emergent Light Studio presents

2
00:00:08,000 --> 00:00:16,000
the Illinois State Collegiate Compendium, Academic Lecture in Business and Economics.

3
00:00:16,000 --> 00:00:24,000
This is Business Finance, FIL 240 for Autumn Semester 2023.

4
00:00:24,000 --> 00:00:32,000
Today, Project Free Cash Flow Analysis.

5
00:00:32,000 --> 00:00:41,000
We will go through some terminology and then I will begin a worksheet in Excel.

6
00:00:41,000 --> 00:00:45,000
So have your Excel out and ready to climb into it.

7
00:00:45,000 --> 00:00:50,000
And I'll finish it up with some more examples on Wednesday.

8
00:00:50,000 --> 00:00:55,000
As I said before, and you'll see this for yourself,

9
00:00:55,000 --> 00:01:03,000
setting up a template exactly is, you can't really do that because there could be a varying number of years.

10
00:01:03,000 --> 00:01:12,000
And I could write macros to do that, but the macros wouldn't survive very long here on the servers of the school.

11
00:01:12,000 --> 00:01:15,000
So what I'll do is, we'll go through it.

12
00:01:15,000 --> 00:01:21,000
I'll upload it to Canvas this evening so that you can have it to look through.

13
00:01:21,000 --> 00:01:31,000
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.

14
00:01:31,000 --> 00:01:39,000
But first now we look at the numbers and see what kind of happiness has proceeded today.

15
00:01:39,000 --> 00:01:41,000
And it wasn't much happiness.

16
00:01:41,000 --> 00:01:45,000
The markets were just absolutely dull.

17
00:01:45,000 --> 00:01:47,000
Hardly anything was going on.

18
00:01:47,000 --> 00:01:53,000
As you can see, the Dow was up a raging 0.16%.

19
00:01:53,000 --> 00:01:58,000
The S&P was down a paltry 0.08%.

20
00:01:58,000 --> 00:02:05,000
And the NASDAQ, well actually the NASDAQ kind of started tailing off there at the end, 0.22%.

21
00:02:05,000 --> 00:02:08,000
But it was a really quiet day on the street.

22
00:02:08,000 --> 00:02:18,000
And if you look over here at the S&P 500, the volume was barely half of what it is on a typical day.

23
00:02:18,000 --> 00:02:22,000
Essentially, investors are just staying on the sidelines.

24
00:02:22,000 --> 00:02:28,000
There's not much buy and sell activity, relatively speaking.

25
00:02:28,000 --> 00:02:36,000
So it's just everyone's waiting to see if good news comes or bad news comes.

26
00:02:36,000 --> 00:02:42,000
Of course, crude oil is up some today, but it's still well below where it had been.

27
00:02:42,000 --> 00:02:49,000
And we're still waiting for gasoline prices to catch up with the recent drop in crude prices.

28
00:02:49,000 --> 00:02:51,000
But there's that.

29
00:02:51,000 --> 00:02:53,000
Gold had a little bit of a surge there.

30
00:02:53,000 --> 00:02:58,000
You see, right after the midday, it popped for some reason.

31
00:02:58,000 --> 00:03:02,000
Probably some conspiracy theory popped up.

32
00:03:02,000 --> 00:03:08,000
And so the gold bugs grabbed some gold, and then they didn't do any more for the rest of the day.

33
00:03:08,000 --> 00:03:09,000
Nothing to worry about there.

34
00:03:09,000 --> 00:03:15,000
Now, the 10-year bond, the yields spiked up, but they just couldn't hold it.

35
00:03:15,000 --> 00:03:17,000
They just dropped right back down.

36
00:03:17,000 --> 00:03:20,000
So in other words, yields spike up, price drops.

37
00:03:20,000 --> 00:03:22,000
It was a sell-off.

38
00:03:22,000 --> 00:03:30,000
But then, there was right not too long after that, about an hour after that, the buying on bonds began again.

39
00:03:30,000 --> 00:03:35,000
Because of the price it dropped, so there was bargain hunting.

40
00:03:35,000 --> 00:03:41,000
And so buying drove the price up, and then the yield went down.

41
00:03:41,000 --> 00:03:43,000
That's good news, of course.

42
00:03:43,000 --> 00:03:51,000
Interest rates are lower, and it's because of the drain of expected inflation from the risk-free rate.

43
00:03:51,000 --> 00:04:00,000
And all of the rates are showing signs of easing up on their expectation of inflation.

44
00:04:00,000 --> 00:04:10,000
Now, the Pound Sterling, well, interest rates, yields on our debt, U.S. debt, going down.

45
00:04:10,000 --> 00:04:13,000
So that means that the dollar weakens.

46
00:04:13,000 --> 00:04:18,000
The euro strengthened a little bit against the dollar, and so did the pound.

47
00:04:18,000 --> 00:04:22,000
Heaven knows what's going on with the Japanese yen these days.

48
00:04:22,000 --> 00:04:29,000
But over on the other side of the world, first the Nikkei had a strong opening bell pop,

49
00:04:29,000 --> 00:04:39,000
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.

50
00:04:39,000 --> 00:04:43,000
And then the sun set in Tokyo, and it rose in London.

51
00:04:43,000 --> 00:04:48,000
London had a little surge, and then again at the end it had a little surge.

52
00:04:48,000 --> 00:04:53,000
But boy, that sure didn't do anything over here on our side of the Atlantic.

53
00:04:53,000 --> 00:05:00,000
We started out down, and then crawled our way, groveled back up to about even for the day.

54
00:05:00,000 --> 00:05:02,000
So it's kind of hard to say.

55
00:05:02,000 --> 00:05:12,000
The good news, though, is that a survey came out how much consumers are planning to spend for the holiday season.

56
00:05:12,000 --> 00:05:16,000
And it was favorable.

57
00:05:16,000 --> 00:05:26,000
I guess it was average household plans to spend more than $1,600 for Christmas season up from last year.

58
00:05:26,000 --> 00:05:30,000
And no, that doesn't mean we're each going to get $1,600 in presents.

59
00:05:30,000 --> 00:05:38,000
But at the same time, strong holiday showing that takes us into the new year in good shape,

60
00:05:38,000 --> 00:05:44,000
and the economy rolling along in recovery or early expansion.

61
00:05:44,000 --> 00:05:46,000
Good news.

62
00:05:46,000 --> 00:05:55,000
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.

63
00:05:55,000 --> 00:05:59,000
Look first at Walmart.

64
00:05:59,000 --> 00:06:08,000
At Walmart, it's about half as risky as the market in a well-diversified portfolio.

65
00:06:08,000 --> 00:06:10,000
Very safe stock.

66
00:06:10,000 --> 00:06:15,000
Price-earnings ratio is right about good value.

67
00:06:15,000 --> 00:06:19,000
And it's profitable, $5.20 a share.

68
00:06:19,000 --> 00:06:21,000
Not a very spectacular dividend.

69
00:06:21,000 --> 00:06:25,000
So just to have a quick look.

70
00:06:25,000 --> 00:06:31,000
See, a one-year holding period return based on Yahoo's projection of the price in one year,

71
00:06:31,000 --> 00:06:48,000
you'd have $168.89 in one year, divided by buying it right now at $167.68 minus one.

72
00:06:48,000 --> 00:06:57,000
And then multiply that by 100.

73
00:06:57,000 --> 00:07:04,000
You've got less than 1% increase capital gain on the stock.

74
00:07:04,000 --> 00:07:05,000
That sucks.

75
00:07:05,000 --> 00:07:08,000
Even, well, yeah, it's a low-risk stock, but still.

76
00:07:08,000 --> 00:07:13,000
0.72% gain.

77
00:07:13,000 --> 00:07:19,000
So, well, let's add in that wild dividend of 1.37%.

78
00:07:19,000 --> 00:07:28,000
And you get a total holding period return for one year on Walmart of a lousy 2.09%.

79
00:07:28,000 --> 00:07:30,000
That's really weak.

80
00:07:30,000 --> 00:07:37,000
And yes, this is a low-risk stock, but that's still pretty darn weak.

81
00:07:37,000 --> 00:07:43,000
Feeling around for something else that we might want to look into.

82
00:07:43,000 --> 00:07:47,000
Trying to think of something else that would be...

83
00:07:47,000 --> 00:07:50,000
I might have done this one before.

84
00:07:50,000 --> 00:07:52,000
Okay, Yum Brands.

85
00:07:52,000 --> 00:07:55,000
Yum is a restaurant holding company.

86
00:07:55,000 --> 00:08:05,000
Now, the restaurant scene is in turmoil because there are some restaurant chains that are in bad trouble right now.

87
00:08:05,000 --> 00:08:11,000
Burger King, three of its major franchisees all went bankrupt.

88
00:08:11,000 --> 00:08:23,000
And that means Burger King, the central company which uses the franchise model, is not doing well right now.

89
00:08:23,000 --> 00:08:37,000
But on the other hand, you've got other companies like McDonald's which doesn't rely as heavily on the franchise model.

90
00:08:37,000 --> 00:08:39,000
We'll see.

91
00:08:39,000 --> 00:08:46,000
But Yum sort of is a broad-based look at the restaurants.

92
00:08:46,000 --> 00:09:04,000
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%.

93
00:09:04,000 --> 00:09:26,000
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.

94
00:09:26,000 --> 00:09:34,000
So the capital gain, the rise in stock, is a decent 14.46%.

95
00:09:34,000 --> 00:09:46,000
And then you add in that little dividend, 1.92%, and you get a total one-year holding period return of 16.38%.

96
00:09:46,000 --> 00:09:48,000
That's more like it.

97
00:09:48,000 --> 00:09:57,000
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.

98
00:09:57,000 --> 00:09:59,000
Little riskier.

99
00:09:59,000 --> 00:10:14,000
So that one might be something worth looking at for a well-diversified portfolio if you want to participate in the restaurant scene.

100
00:10:14,000 --> 00:10:22,000
But typically as a recovery gets underway toward an expansion, you'll see more going to restaurants.

101
00:10:22,000 --> 00:10:35,000
And so if you believe that we are in a recovery, well, restaurants wouldn't be a bad investment, as would other things too.

102
00:10:35,000 --> 00:10:39,000
But anyway, that is the look of the market right now.

103
00:10:39,000 --> 00:10:44,000
And now to the fun of doing some spreadsheets.

104
00:10:44,000 --> 00:10:48,000
But before I do that, I want to take one last pass at risk.

105
00:10:48,000 --> 00:10:57,000
And this is looking at risk from the perspective of projects.

106
00:10:57,000 --> 00:11:02,000
And we can look at three, we can talk about three different kinds of risk.

107
00:11:02,000 --> 00:11:07,000
And I've talked about two of these, or one of them, definitely.

108
00:11:07,000 --> 00:11:11,000
There's standalone risk.

109
00:11:11,000 --> 00:11:18,000
This is what I talk about when I talk about sandboxing the project.

110
00:11:18,000 --> 00:11:25,000
You want to look at this project as if it is its own company.

111
00:11:25,000 --> 00:11:36,000
Project on its own, incremental cash flows.

112
00:11:36,000 --> 00:11:49,000
The only revenues and costs of the project, cash flows, and all that.

113
00:11:49,000 --> 00:11:59,000
And the measure you would use for that, first one would be the standard deviation.

114
00:11:59,000 --> 00:12:06,000
Just plain old Excel, S equals STDEV of the free cash flows.

115
00:12:06,000 --> 00:12:10,000
See how much they swing.

116
00:12:10,000 --> 00:12:14,000
Standard deviation.

117
00:12:14,000 --> 00:12:17,000
In other words, sigma.

118
00:12:17,000 --> 00:12:34,000
Another one you can use would be the coefficient of variation.

119
00:12:34,000 --> 00:12:50,000
Which is the standard deviation of the free cash flows divided by the average of the free cash flows.

120
00:12:50,000 --> 00:12:57,000
Now, a second course in corporate finance.

121
00:12:57,000 --> 00:13:03,000
We would beat the heck out of coefficient of variation.

122
00:13:03,000 --> 00:13:09,000
Every line on the projected income statement, we would run the CV on it.

123
00:13:09,000 --> 00:13:18,000
So that we would see how the different lines, what their individual volatilities are.

124
00:13:18,000 --> 00:13:27,000
But overall, standard deviation of the free cash flows.

125
00:13:27,000 --> 00:13:35,000
Or you could do the standard deviation of the free cash flows you've projected.

126
00:13:35,000 --> 00:13:43,000
Divided by the average of the free cash flows you were projecting.

127
00:13:43,000 --> 00:13:50,000
I mean, you put the numbers into Excel and then you just type in STDEV and all that kind of stuff.

128
00:13:50,000 --> 00:13:52,000
Okay, that's standalone risk.

129
00:13:52,000 --> 00:13:56,000
That's one of the ways we can measure the risk of a project.

130
00:13:56,000 --> 00:14:03,000
Another way we can do it is to take what is called the corporate approach.

131
00:14:03,000 --> 00:14:05,000
Corporate risk.

132
00:14:05,000 --> 00:14:21,000
In this one, we step away from the sandbox model and we look at the project

133
00:14:21,000 --> 00:14:37,000
as one investment in the company's portfolio of investments.

134
00:14:37,000 --> 00:14:48,000
Portfolio of projects.

135
00:14:48,000 --> 00:14:53,000
Okay, you're trying to mean by that.

136
00:14:53,000 --> 00:14:57,000
Look, the project is going to have volatility up and down.

137
00:14:57,000 --> 00:15:01,000
But if you think about it from a portfolio perspective,

138
00:15:01,000 --> 00:15:10,000
this project's variations are going to come in the context of the variations in free cash flow

139
00:15:10,000 --> 00:15:13,000
of the company's existing projects.

140
00:15:13,000 --> 00:15:41,000
So in other words, the eye here would be on, look at the correlation between this project's free cash flows

141
00:15:41,000 --> 00:16:02,000
and the other company project's free cash flows.

142
00:16:02,000 --> 00:16:09,000
In other words, this project on its own might have a lot of ups and downs.

143
00:16:09,000 --> 00:16:15,000
But maybe there are other projects in the company that have ups and downs

144
00:16:15,000 --> 00:16:19,000
that somewhat cancel out this project's ups and downs.

145
00:16:19,000 --> 00:16:22,000
It's like stocks in a portfolio.

146
00:16:22,000 --> 00:16:26,000
One stock in a portfolio is going to go up and down.

147
00:16:26,000 --> 00:16:31,000
But other projects, if their correlations aren't very strong,

148
00:16:31,000 --> 00:16:38,000
they could help cancel out the ups and downs of each other's free cash flow.

149
00:16:38,000 --> 00:16:44,000
It was just like I talked about with portfolio diversification in stocks.

150
00:16:44,000 --> 00:16:49,000
You put different stocks in and they tend to help each other out

151
00:16:49,000 --> 00:16:57,000
in terms of diminishing that diversifiable, that non-systematic risk.

152
00:16:57,000 --> 00:17:02,000
And that's a legitimate thing to think about in this too.

153
00:17:02,000 --> 00:17:07,000
Now, here's the other side of that.

154
00:17:07,000 --> 00:17:17,000
Yeah, this project may have free cash flows that are sort of not very well correlated

155
00:17:17,000 --> 00:17:21,000
with the free cash flows of other company projects.

156
00:17:21,000 --> 00:17:28,000
However, most companies, well, I should be a little careful about this.

157
00:17:28,000 --> 00:17:37,000
Most companies tend to have projects that are of the same type.

158
00:17:37,000 --> 00:17:41,000
A company has a core competence.

159
00:17:41,000 --> 00:17:47,000
And therefore, you will have projects that tend to have free cash flows

160
00:17:47,000 --> 00:17:51,000
that would tend to behave the same way.

161
00:17:51,000 --> 00:17:55,000
So even if you get a new model of car,

162
00:17:55,000 --> 00:17:59,000
well, yeah, its free cash flows will go up and down.

163
00:17:59,000 --> 00:18:01,000
But if you're building other cars,

164
00:18:01,000 --> 00:18:05,000
theirs will tend to do the same thing to a certain extent.

165
00:18:05,000 --> 00:18:15,000
So the correlations will be high so they won't cancel out each other's volatility very much at all.

166
00:18:15,000 --> 00:18:23,000
Most companies don't grab projects that are way off the radar of what they've already been doing.

167
00:18:23,000 --> 00:18:27,000
Taking on a project that's completely different,

168
00:18:27,000 --> 00:18:31,000
yeah, that would help with your corporate risk,

169
00:18:31,000 --> 00:18:36,000
but you're going to have to learn something new,

170
00:18:36,000 --> 00:18:43,000
do something new that might be not what you really can do very well.

171
00:18:43,000 --> 00:18:50,000
The stories about companies that have tried to go outside of their core competence are many,

172
00:18:50,000 --> 00:18:55,000
and usually they end rather badly.

173
00:18:55,000 --> 00:18:58,000
I could pull out dozens of examples.

174
00:18:58,000 --> 00:19:01,000
One, JCPenney.

175
00:19:01,000 --> 00:19:08,000
Many years ago, before JCPenney's big problems set in seriously,

176
00:19:08,000 --> 00:19:19,000
it was a well-respected, heavy-duty retailer of apparel and such things as that.

177
00:19:19,000 --> 00:19:21,000
No problem with that.

178
00:19:21,000 --> 00:19:24,000
So they had a lot of cash.

179
00:19:24,000 --> 00:19:28,000
Well, what they could have done, which they should have done,

180
00:19:28,000 --> 00:19:33,000
was clean up their stores, modernize them,

181
00:19:33,000 --> 00:19:40,000
get started on moving towards the 21st century while they were still in the 20th century.

182
00:19:40,000 --> 00:19:42,000
But they didn't do that.

183
00:19:42,000 --> 00:19:48,000
They decided, hey, let's get into a cash cow business.

184
00:19:48,000 --> 00:19:56,000
So they bought a cherry-picking boutique insurance company.

185
00:19:56,000 --> 00:19:58,000
It was called Educators and Executives,

186
00:19:58,000 --> 00:20:12,000
and it catered to higher-income, low-risk drivers, auto insurance, E&E.

187
00:20:12,000 --> 00:20:18,000
And so JCPenney said, well, we'll just acquire that, and we'll put our expertise into it,

188
00:20:18,000 --> 00:20:22,000
and we'll make big bucks, spend our money on a sure bet.

189
00:20:22,000 --> 00:20:27,000
Well, they did that, and that was a big disaster,

190
00:20:27,000 --> 00:20:33,000
because first of all, being a retailer, their first thought was,

191
00:20:33,000 --> 00:20:38,000
let's get the revenues of this insurance company higher.

192
00:20:38,000 --> 00:20:41,000
Let's push the revenue.

193
00:20:41,000 --> 00:20:52,000
And so in order to do that, they started offering the insurance to more people, more households.

194
00:20:52,000 --> 00:21:01,000
Well, that meant that they were going to take on more risk than what E&E had had with Educators and Executives.

195
00:21:01,000 --> 00:21:06,000
You were getting lower and somewhat higher risk.

196
00:21:06,000 --> 00:21:11,000
And so suddenly, their premiums started coming in much better,

197
00:21:11,000 --> 00:21:17,000
but then the payouts started happening at a much higher level and rate.

198
00:21:17,000 --> 00:21:23,000
And so they suddenly saw, oh, well, let's fix that.

199
00:21:23,000 --> 00:21:25,000
We'll just get more premiums in.

200
00:21:25,000 --> 00:21:30,000
So they started selling insurance to even higher-risk clients.

201
00:21:30,000 --> 00:21:37,000
And so then revenues came back, but then the payout ratio started going through the roof.

202
00:21:37,000 --> 00:21:44,000
And by the time it was over, JCPenney had gotten creamed,

203
00:21:44,000 --> 00:21:49,000
because it was in a business where its business model worked,

204
00:21:49,000 --> 00:21:57,000
and then it tried to apply that business model to an industry where that didn't work.

205
00:21:57,000 --> 00:22:03,000
And that's the idea. The problem is that most corporations aren't stupid enough to do that.

206
00:22:03,000 --> 00:22:08,000
They'll stay with projects within a core of competence,

207
00:22:08,000 --> 00:22:13,000
which means the free cash flows are going to tend to be highly correlated.

208
00:22:13,000 --> 00:22:20,000
And so the corporate risk is going to be approximately,

209
00:22:20,000 --> 00:22:29,000
rather, the project risk is going to be approximately what it was before.

210
00:22:29,000 --> 00:22:40,000
There's another kind of risk we can look at, market risk.

211
00:22:40,000 --> 00:22:46,000
This one is controversial.

212
00:22:46,000 --> 00:22:53,000
I mean, from a pure economic standpoint, it makes sense.

213
00:22:53,000 --> 00:22:59,000
But at the same time, okay, here's how it works.

214
00:22:59,000 --> 00:23:08,000
You, madam, you're a wealthy, high-powered investor, Wall Street type of person.

215
00:23:08,000 --> 00:23:11,000
Well, you see, I'm the company.

216
00:23:11,000 --> 00:23:19,000
And I say, well, why am I bothered by the risk of this project?

217
00:23:19,000 --> 00:23:26,000
You're the investor. If you don't like what we're doing, then sell the damn stock.

218
00:23:26,000 --> 00:23:31,000
But if you like what we're doing, you'll keep with us.

219
00:23:31,000 --> 00:23:39,000
That's your problem to decide whether this risk of this new project is worth it or not

220
00:23:39,000 --> 00:23:44,000
for your investment purposes. It's not ours as a company.

221
00:23:44,000 --> 00:23:49,000
We will do what we, in our business, best business judgment,

222
00:23:49,000 --> 00:23:54,000
considers the maximization of shareholder wealth.

223
00:23:54,000 --> 00:24:04,000
It is your job as an informed investor to decide whether that's the right thing or not.

224
00:24:04,000 --> 00:24:08,000
That's the market perspective.

225
00:24:08,000 --> 00:24:12,000
Why are you, well, it's overstating it.

226
00:24:12,000 --> 00:24:18,000
Why are you, C-suite at the company, worried about this?

227
00:24:18,000 --> 00:24:24,000
This is Wall Street's job to evaluate and to allocate, do portfolio allocation

228
00:24:24,000 --> 00:24:30,000
based upon your assessment of the risk of this project in the company.

229
00:24:30,000 --> 00:24:36,000
In other words, we wash our hands of this whole issue here in a way.

230
00:24:36,000 --> 00:24:40,000
We obviously want to do free cash flow analysis and all that good stuff,

231
00:24:40,000 --> 00:24:45,000
but in the end, it's the market that's going to decide for us.

232
00:24:45,000 --> 00:24:50,000
Now, the problem with that idea, there are a couple.

233
00:24:50,000 --> 00:24:54,000
The first one is, this is elitist AF.

234
00:24:54,000 --> 00:24:59,000
What about the average, normal Joe investor like you?

235
00:24:59,000 --> 00:25:02,000
You don't have the tools to do this kind of analytics.

236
00:25:02,000 --> 00:25:08,000
You're just going to buy stocks. Yeah, I'm going to buy a stock today.

237
00:25:08,000 --> 00:25:12,000
Or you're going to be a day trader and think you know what you're doing.

238
00:25:12,000 --> 00:25:17,000
But at the same time, this market approach is very elitist.

239
00:25:17,000 --> 00:25:20,000
Only Wall Street matters.

240
00:25:20,000 --> 00:25:24,000
Unfortunately, that's probably true.

241
00:25:24,000 --> 00:25:26,000
But there you are.

242
00:25:26,000 --> 00:25:31,000
Welcome to the age of democratization of diminishment.

243
00:25:31,000 --> 00:25:44,000
Okay, now the other flaw in it is that you are absolving the company of one of the big things.

244
00:25:44,000 --> 00:25:49,000
Look, if this is too risky for that sophisticated investor,

245
00:25:49,000 --> 00:25:54,000
she and all of her fellow sophisticated investors are going to sell our stock.

246
00:25:54,000 --> 00:25:57,000
That's going to drive the price of the stock down.

247
00:25:57,000 --> 00:26:00,000
Now, if you remember what we did with the component cost of capital,

248
00:26:00,000 --> 00:26:05,000
remember price is in the denominator of the cost of equity.

249
00:26:05,000 --> 00:26:10,000
So if they sell the stock, the price goes down.

250
00:26:10,000 --> 00:26:18,000
And so the cost of equity capital and therefore the weighted average cost of capital goes up.

251
00:26:18,000 --> 00:26:23,000
We're not doing ourselves any favor if we piss her off and all of her wealthy friends

252
00:26:23,000 --> 00:26:29,000
and they drive the stock price down because that drives up our weighted average cost of capital.

253
00:26:29,000 --> 00:26:31,000
And then guess what?

254
00:26:31,000 --> 00:26:37,000
When we do our free cash flow analysis, the whack that we use to discount the free cash flows,

255
00:26:37,000 --> 00:26:43,000
we're going to create a condition where it's higher than what we use

256
00:26:43,000 --> 00:26:49,000
because we're going to make it higher through driving the stock price down.

257
00:26:49,000 --> 00:26:51,000
That kind of takes you around a little bit there.

258
00:26:51,000 --> 00:26:58,000
But if you think about it for a while and play this podcast 10 or 20 times, it begins to make sense.

259
00:26:58,000 --> 00:27:01,000
But anyway, enough of that.

260
00:27:01,000 --> 00:27:04,000
So just always keep those things in mind.

261
00:27:04,000 --> 00:27:11,000
Now, I'm going to do a simple free cash flow analysis for you.

262
00:27:11,000 --> 00:27:13,000
And I'll use Excel to do this.

263
00:27:13,000 --> 00:27:22,000
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.

264
00:27:22,000 --> 00:27:26,000
Because once you get the hang of it, all you really do is like a template.

265
00:27:26,000 --> 00:27:33,000
You just plug in numbers and see what comes out of the mess that has been created by it.

266
00:27:33,000 --> 00:27:42,000
So to do this, let me put up some numbers for you.

267
00:27:42,000 --> 00:27:50,000
And remember the formula for free cash flow, revenues minus cost times one minus the tax rate, and all that good stuff.

268
00:27:50,000 --> 00:27:53,000
So let me pour through some.

269
00:27:53,000 --> 00:27:56,000
Now you notice something here.

270
00:27:56,000 --> 00:28:01,000
I created...

271
00:28:01,000 --> 00:28:03,000
Okay, good.

272
00:28:03,000 --> 00:28:06,000
I created a sheet one.

273
00:28:06,000 --> 00:28:12,000
Now sheet one will be the free cash flow analysis.

274
00:28:12,000 --> 00:28:16,000
Well, let's try clicking on it to rename the sheet.

275
00:28:16,000 --> 00:28:29,000
Sheet one will be the free cash flow analysis.

276
00:28:29,000 --> 00:28:35,000
Sheet two is going to be the inputs.

277
00:28:35,000 --> 00:28:43,000
So as I do in almost everything, I am separating the formulas from the numbers.

278
00:28:43,000 --> 00:28:47,000
And that will give me the ability to change the numbers.

279
00:28:47,000 --> 00:28:50,000
I could do a sensitivity analysis.

280
00:28:50,000 --> 00:28:56,000
Well, what happens if the revenues are 10% higher or 5% lower?

281
00:28:56,000 --> 00:29:02,000
So that second sheet is always what we do in Excel.

282
00:29:02,000 --> 00:29:07,000
And this is true actually even in web page design.

283
00:29:07,000 --> 00:29:15,000
You separate the formatting of a web page from the text that you want in the pretty pictures you want in the page.

284
00:29:15,000 --> 00:29:18,000
We do this separation a lot these days.

285
00:29:18,000 --> 00:29:26,000
It actually, truth be told, goes clear back to gaming in the 1980s and early 90s.

286
00:29:26,000 --> 00:29:28,000
But inputs.

287
00:29:28,000 --> 00:29:33,000
Okay, so what we'll do is we're going to break this thing down.

288
00:29:33,000 --> 00:29:37,000
We're going to do a couple of things that will make it a little easier to do.

289
00:29:37,000 --> 00:29:54,000
We're going to do equipment cost first.

290
00:29:54,000 --> 00:30:10,000
So the equipment is going to cost, let's say, $280,000.

291
00:30:10,000 --> 00:30:32,000
Along with that, the equipment eligible for 100% bonus depreciation.

292
00:30:40,000 --> 00:30:49,000
In other words, now I may slip in my wording, but this is the old schedule 179,

293
00:30:49,000 --> 00:30:54,000
where there was some equipment that you could just expense it the year you bought it.

294
00:30:54,000 --> 00:30:59,000
Just expense it.

295
00:30:59,000 --> 00:31:06,000
So in this case,

296
00:31:06,000 --> 00:31:17,000
and now we're going to do the tax rate here, and for lack of greater creativity, we're going to do 28,000.

297
00:31:17,000 --> 00:31:21,000
25% just to make the numbers rounder.

298
00:31:21,000 --> 00:31:24,000
You can do 21% later.

299
00:31:24,000 --> 00:31:26,000
Now, oh, I forgot up here.

300
00:31:26,000 --> 00:31:33,000
Now the equipment eligible for the bonus is $280,000.

301
00:31:33,000 --> 00:31:43,000
In other words, we can depreciate the entire cost in the first year.

302
00:31:43,000 --> 00:32:05,000
So the after tax cost of the equipment would be equal to the whole thing, 200 B2,

303
00:32:05,000 --> 00:32:23,000
times, open parenthesis, 1 minus the tax rate, $210,000.

304
00:32:23,000 --> 00:32:30,000
Okay, now the next thing we're going to do is I'm going to drop down one row.

305
00:32:30,000 --> 00:32:46,000
We're going to talk about changes in net operating working capital.

306
00:32:46,000 --> 00:32:48,000
And we're going to do two.

307
00:32:48,000 --> 00:32:57,000
One is inventory.

308
00:32:57,000 --> 00:33:16,000
We're going to have to buy $25,000 in inventory.

309
00:33:16,000 --> 00:33:20,000
That's going to drain free cash.

310
00:33:20,000 --> 00:33:41,000
But we're also going to increase our accounts payable by $5,000.

311
00:33:41,000 --> 00:34:04,000
So our net change in net operating working capital is going to be the $25,000

312
00:34:04,000 --> 00:34:15,000
minus the $5,000.

313
00:34:15,000 --> 00:34:17,000
Let me do something over here.

314
00:34:17,000 --> 00:34:18,000
You don't have to write this in.

315
00:34:18,000 --> 00:34:22,000
I'll upload this.

316
00:34:22,000 --> 00:34:51,000
Equals formula, text, open parenthesis, that.

317
00:34:51,000 --> 00:35:10,000
That way the formula is there for you to see.

318
00:35:10,000 --> 00:35:22,000
Now we're going to drop a row and then put effects on operations.

319
00:35:22,000 --> 00:35:30,000
And under this one, let me do something here.

320
00:35:30,000 --> 00:35:36,000
Effects on operations.

321
00:35:36,000 --> 00:35:50,000
So we'll sales in units.

322
00:35:50,000 --> 00:36:05,000
Say we'll have 100,000 units sold.

323
00:36:05,000 --> 00:36:30,000
And price per unit, $2.

324
00:36:30,000 --> 00:36:55,000
So gross revenues will be equal to 100,000 units per year times $2 per unit.

325
00:36:55,000 --> 00:37:17,000
So, salvage value.

326
00:37:17,000 --> 00:37:35,000
Savage value, salvage value, say 25,000.

327
00:37:35,000 --> 00:37:50,000
Now, the book value, you depreciated it all the first year.

328
00:37:50,000 --> 00:38:00,000
So the book value is zero.

329
00:38:00,000 --> 00:38:21,000
So, taxable salvage value

330
00:38:21,000 --> 00:38:37,000
is going to be $25,000. Well, let me put that right here.

331
00:38:37,000 --> 00:38:52,000
Savage value minus book value will be equal to that 25,000,

332
00:38:52,000 --> 00:39:15,000
line B15 minus, zero dollars, B16.

333
00:39:15,000 --> 00:39:28,000
Savage value after taxes will be the taxable,

334
00:39:28,000 --> 00:39:49,000
well, will equal the salvage value minus 25% times the salvage value.

335
00:39:49,000 --> 00:39:55,000
So in other words, after we pay the taxes on that $25,000,

336
00:39:55,000 --> 00:40:09,000
we get to keep contributing to the last year's free cash flow, $18,750.

337
00:40:09,000 --> 00:40:18,000
So here we go.

338
00:40:18,000 --> 00:40:40,000
Year, free cash flow will go zero, one, two, three, four years.

339
00:40:40,000 --> 00:40:56,000
So the free cash flow at the beginning will be the negative of the

340
00:40:56,000 --> 00:41:12,000
after-tax cost of the equipment minus the net change in working capital,

341
00:41:12,000 --> 00:41:40,000
which will give $230,000.

342
00:41:40,000 --> 00:41:50,000
Formula text.

343
00:41:50,000 --> 00:42:06,000
Year one would be equal to your gross revenue minus,

344
00:42:06,000 --> 00:42:10,000
oh, I didn't put something in there, I apologize.

345
00:42:10,000 --> 00:42:23,000
On the inputs, insert a row on the inputs between 14 and 15.

346
00:42:23,000 --> 00:42:44,000
Forgot the variable cost, let's say it's 60%.

347
00:42:44,000 --> 00:42:54,000
Apologies for that.

348
00:42:54,000 --> 00:43:20,000
Let me see if I can get all the formulas in there for you to see.

349
00:43:20,000 --> 00:43:24,000
Now, apologies, let's try this again.

350
00:43:24,000 --> 00:43:27,000
Go over to your free cash flow analysis,

351
00:43:27,000 --> 00:43:37,000
and that will equal your gross revenues,

352
00:43:37,000 --> 00:43:45,000
which was on the input sheet, cell B14.

353
00:43:45,000 --> 00:43:52,000
I'm going to make that an absolute reference, F4, so that as I drag that

354
00:43:52,000 --> 00:43:59,000
formula down on the first sheet, it won't change the location of that.

355
00:43:59,000 --> 00:44:22,000
Minus 60%, absolute reference, that's cell B15 times the gross revenues.

356
00:44:22,000 --> 00:44:31,000
Cell B14, and again, as an absolute reference.

357
00:44:31,000 --> 00:44:37,000
That's $80,000.

358
00:44:37,000 --> 00:44:47,000
Now I'm going to drag this cell B3 on the analysis sheet down clear to 4,

359
00:44:47,000 --> 00:45:07,000
and I'm going to have to put some extra stuff into 4.

360
00:45:07,000 --> 00:45:18,000
Therefore, you get your last year's income, the $80,000.

361
00:45:18,000 --> 00:45:25,000
But you also get some other things, so I'm going to edit the formula for cell B6,

362
00:45:25,000 --> 00:45:40,000
because I also have to add back, plus go over here to the inputs,

363
00:45:40,000 --> 00:45:53,000
because my inventory will be recovered, but I will have to pay off my accounts payable.

364
00:45:53,000 --> 00:45:59,000
So I have to add this back at the end, in the last year.

365
00:45:59,000 --> 00:46:04,000
I get to have the money back from paying for that inventory every year,

366
00:46:04,000 --> 00:46:20,000
so I get to add back $25,000, but I also have to pay off that continuing rolling accounts payable of $5,000.

367
00:46:20,000 --> 00:46:33,000
And also, I will get back my salvage after tax salvage value, $18,750.

368
00:46:33,000 --> 00:46:39,000
So in year 4, I do that right.

369
00:46:39,000 --> 00:46:47,000
I got my last year's revenue, minus 60% of the revenue,

370
00:46:47,000 --> 00:47:00,000
and I get back in my net working capital, and I get my salvage after tax salvage value.

371
00:47:00,000 --> 00:47:07,000
So there you go.

372
00:47:07,000 --> 00:47:17,000
Now in the inputs, I'll do one last thing here on cell A21, I'll type in the weighted average cost of capital.

373
00:47:17,000 --> 00:47:32,000
And in B21, I'll say, let's say our WAC is 10.00%, 10%.

374
00:47:32,000 --> 00:47:38,000
So now we're finished. Front-free cash flow, I can do the net present value.

375
00:47:38,000 --> 00:47:48,000
The NPV of the project is going to equal and sell B8.

376
00:47:48,000 --> 00:48:07,000
First, I have to take, I add, I take the initial investment, negative $230,000, plus net present value, NPV,

377
00:48:07,000 --> 00:48:20,000
open parenthesis, sells B3 through B6. Whoa, oh, I forgot my weighted average cost of capital.

378
00:48:20,000 --> 00:48:22,000
I'll say it from the beginning again.

379
00:48:22,000 --> 00:48:35,000
So B8 equals the initial investment, negative, which is B2, plus the net present value,

380
00:48:35,000 --> 00:48:38,000
I forgot to put in the weighted average cost of capital.

381
00:48:38,000 --> 00:48:48,000
Open the parenthesis, go to inputs, and get the cell B21, the WAC, for the discount rate.

382
00:48:48,000 --> 00:48:59,000
And then, then I go back over and take cells B3 through B6.

383
00:48:59,000 --> 00:49:16,000
Close the parenthesis, $39,811.

384
00:49:16,000 --> 00:49:27,000
If I want, I can do the internal rate of return, which would equal IRR,

385
00:49:27,000 --> 00:49:38,000
and you just take cells B2 through B6,

386
00:49:38,000 --> 00:49:49,000
which gives us, oh shut up, a 17.45% internal rate of return,

387
00:49:49,000 --> 00:50:03,000
all above the weighted average cost of capital.

388
00:50:03,000 --> 00:50:11,000
That is the whole thing, right there.

389
00:50:11,000 --> 00:50:20,000
Now, the key here is that these would be the numbers for a basic free cash flow analysis,

390
00:50:20,000 --> 00:50:25,000
which is how far I would go on a quiz or the final exam.

391
00:50:25,000 --> 00:50:30,000
The only difference would be the number of years that might be involved.

392
00:50:30,000 --> 00:50:38,000
You might have to put in years 0 through 5, years 0 through 6, something like that,

393
00:50:38,000 --> 00:50:44,000
in order to have enough for the, if I said the life of the project is six years.

394
00:50:44,000 --> 00:50:54,000
Matter of fact, something I want to do here, I try to remember whether I did it or not.

395
00:50:54,000 --> 00:51:03,000
Let me see.

396
00:51:03,000 --> 00:51:15,000
I'm going to put in a line here, insert, add in the inputs worksheet for row 20.

397
00:51:15,000 --> 00:51:26,000
I'm going to put in life of the project, just as a reminder of how many years you have to put in.

398
00:51:26,000 --> 00:51:36,000
Four years.

399
00:51:36,000 --> 00:51:46,000
And then it's $4, four years for heaven's sake.

400
00:51:46,000 --> 00:51:49,000
There you go.

401
00:51:49,000 --> 00:51:58,000
And that, basically, the inputs side of it is like a mini template.

402
00:51:58,000 --> 00:52:04,000
But again, it's not a true template because free cash flow analysis would have to be modified

403
00:52:04,000 --> 00:52:09,000
for the total number of years of the project.

404
00:52:09,000 --> 00:52:17,000
Now, there's a way I can put that in here, but it's a little bit on the heavy duty side.

405
00:52:17,000 --> 00:52:21,000
I could do it like this.

406
00:52:21,000 --> 00:52:25,000
See that year one there in FCF?

407
00:52:25,000 --> 00:52:37,000
I could say equals sequence.

408
00:52:37,000 --> 00:52:49,000
Sequence C. Sequence

409
00:52:49,000 --> 00:52:54,000
rows

410
00:52:54,000 --> 00:53:00,000
four

411
00:53:00,000 --> 00:53:13,000
comma start one step one.

412
00:53:13,000 --> 00:53:16,000
My ass.

413
00:53:16,000 --> 00:53:19,000
Looks like I didn't do that one right.

414
00:53:19,000 --> 00:53:20,000
I thought I was going to show you a neat trick.

415
00:53:20,000 --> 00:53:30,000
Why is it crabbing about that?

416
00:53:30,000 --> 00:53:34,000
B20.

417
00:53:34,000 --> 00:53:37,000
Yeah, life of the project's fine.

418
00:53:37,000 --> 00:53:43,000
If I take these out.

419
00:53:43,000 --> 00:53:52,000
Why is it saying the spell, did you say?

420
00:53:52,000 --> 00:54:01,000
So, you're saying two?

421
00:54:01,000 --> 00:54:06,000
Oh, I see.

422
00:54:06,000 --> 00:54:13,000
A, no B20.

423
00:54:13,000 --> 00:54:20,000
And then what?

424
00:54:20,000 --> 00:54:30,000
Oh, I get it.

425
00:54:30,000 --> 00:54:32,000
I did not know that.

426
00:54:32,000 --> 00:54:36,000
It doesn't overwrite, does it?

427
00:54:36,000 --> 00:54:37,000
Ouch.

428
00:54:37,000 --> 00:54:40,000
Well, learned something there.

429
00:54:40,000 --> 00:54:49,000
I guess if I keep that, it could be a full blown template.

430
00:54:49,000 --> 00:54:53,000
Huh, isn't that interesting?

431
00:54:53,000 --> 00:54:54,000
Well, you learn something new every day.

432
00:54:54,000 --> 00:54:59,000
I may be able to create a full blown template now.

433
00:54:59,000 --> 00:55:05,000
If I, as long as I remember to put in the life of the project for years,

434
00:55:05,000 --> 00:55:08,000
I can actually give it the rest of this.

435
00:55:08,000 --> 00:55:10,000
And it'll just spit it out.

436
00:55:10,000 --> 00:55:15,000
And then all you have to do is fill in the numbers over here.

437
00:55:15,000 --> 00:55:17,000
And you're in business.

438
00:55:17,000 --> 00:55:18,000
I might be able to build that.

439
00:55:18,000 --> 00:55:20,000
I'll see if I can.

440
00:55:20,000 --> 00:55:28,000
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.

441
00:55:28,000 --> 00:55:37,000
But the good thing about this is that you can now do essentially scenario analysis, very simple scenario analysis,

442
00:55:37,000 --> 00:55:46,000
change the tax rate, you change the gross, the variable cost from 60% to 50% or whatever you wish.

443
00:55:46,000 --> 00:55:53,000
And you could even change your book value at the end and all that kind of good stuff.

444
00:55:53,000 --> 00:55:56,000
So there's something there.

445
00:55:56,000 --> 00:56:00,000
So anyway, and you could change your weighted average cost of capital.

446
00:56:00,000 --> 00:56:08,000
But obviously, as this project is shown here, it is definitely a positive NPV project.

447
00:56:08,000 --> 00:56:15,000
And the internal rate of return is well above the weighted average cost of capital.

448
00:56:15,000 --> 00:56:33,000
And you could even do it like this, equals if NPV greater than zero, then accept.

449
00:56:33,000 --> 00:56:38,000
Otherwise, reject.

450
00:56:38,000 --> 00:56:45,000
You could even throw that in there to make it look really fancy.

451
00:56:45,000 --> 00:56:54,000
And those are pretty easy to do.

452
00:56:54,000 --> 00:56:55,000
That's it.

453
00:56:55,000 --> 00:56:59,000
That's all there is to doing a free cash flow analysis.

454
00:56:59,000 --> 00:57:01,000
And again, this is a really simple one.

455
00:57:01,000 --> 00:57:12,000
Flat revenues and all that good stuff and using schedule one, or bonus 100% depreciation to do it.

456
00:57:12,000 --> 00:57:18,000
But nevertheless, even if you build it more complexly, we usually start with a simple one

457
00:57:18,000 --> 00:57:21,000
and then just stack stuff into it.

458
00:57:21,000 --> 00:57:26,000
But for your purposes, if you know how to do this, you've got yourself a good shot

459
00:57:26,000 --> 00:57:31,000
at what looks like a really hard question on the final exam.

460
00:57:31,000 --> 00:57:34,000
And as far as that goes, that's all I have for you today.

461
00:57:34,000 --> 00:58:03,000
I thank you.