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

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

3
00:00:17,000 --> 00:00:24,000
This is Business Finance, FIL 240 for spring semester 2024.

4
00:00:24,000 --> 00:00:29,000
Today, Risk and Return.

5
00:00:29,000 --> 00:00:42,000
This subject is mathy, but I will show you in Excel some kind of like stupid pet tricks

6
00:00:42,000 --> 00:00:47,000
that are good for doing risk return types of problems.

7
00:00:47,000 --> 00:00:52,000
It's not really so much a template as it's a way of approaching it.

8
00:00:52,000 --> 00:00:57,000
How to do formulas in Excel for different situations.

9
00:00:57,000 --> 00:01:06,000
I will also in this lecture show you how you can use Excel to access databases online.

10
00:01:06,000 --> 00:01:12,000
To pull in data so that you've got giant amounts of data to do formulas

11
00:01:12,000 --> 00:01:18,000
without actually going into keying in numbers from the database and all that.

12
00:01:18,000 --> 00:01:22,000
Those are really useful for a lot of projects.

13
00:01:22,000 --> 00:01:28,000
One thing that I should mention before we get down to the main part of the lecture

14
00:01:28,000 --> 00:01:33,000
is that the world is being driven by data now.

15
00:01:33,000 --> 00:01:37,000
It's just like everything is data, data driven.

16
00:01:37,000 --> 00:01:45,000
In fact, artificial intelligence learns and does giant data

17
00:01:45,000 --> 00:01:53,000
bases, millions and millions of data points to get a model to work the way we want it to.

18
00:01:53,000 --> 00:01:58,000
With the artificial intelligence, oftentimes what it will do is it will see,

19
00:01:58,000 --> 00:02:06,000
let's say, a million data points for a type of formula or a model.

20
00:02:06,000 --> 00:02:12,000
What it will do is it will take like maybe 900,000 of those data points

21
00:02:12,000 --> 00:02:16,000
to build an algorithm.

22
00:02:16,000 --> 00:02:23,000
Then it will take that other 100,000 and it will test how good it is

23
00:02:23,000 --> 00:02:29,000
with that prediction using that data that it didn't use to create the model.

24
00:02:29,000 --> 00:02:33,000
This is how a lot of chat GPTs do this all the time.

25
00:02:33,000 --> 00:02:40,000
A lot of data models, almost all AI, does this.

26
00:02:40,000 --> 00:02:48,000
For us as human beings, as it were, the non-AIs, we do this too.

27
00:02:48,000 --> 00:02:54,000
It's one of the things that you will do in business is to create a model.

28
00:02:54,000 --> 00:02:59,000
We'll create the model and build it using a bunch of data,

29
00:02:59,000 --> 00:03:05,000
but we'll save some of the data and see if the model can predict properly

30
00:03:05,000 --> 00:03:11,000
and hit those other data points relatively well.

31
00:03:11,000 --> 00:03:17,000
That's a lot of what goes on in the high-powered world of business these days.

32
00:03:17,000 --> 00:03:23,000
What I'm trying to convince you as I do all of this is that math is a lot

33
00:03:23,000 --> 00:03:31,000
of what we do these days, quantitative, and Excel is our way of doing all of this.

34
00:03:31,000 --> 00:03:38,000
Bear with me because it actually can be useful to you for courses you'll take after this,

35
00:03:38,000 --> 00:03:43,000
for doing research papers and term papers for other courses.

36
00:03:43,000 --> 00:03:46,000
Anyway, first we do a look at the numbers.

37
00:03:46,000 --> 00:03:55,000
As you can see, the numbers for the markets today are just on the grouchy side.

38
00:03:55,000 --> 00:04:01,000
It's another one of those days that I've been showing those to you lately where

39
00:04:01,000 --> 00:04:09,000
you don't have any major movement up or down, but you do have a slight grouchiness.

40
00:04:09,000 --> 00:04:13,000
All of these are bear indicators. You see all that red.

41
00:04:13,000 --> 00:04:18,000
So the markets are not in a really bad mood,

42
00:04:18,000 --> 00:04:22,000
but they are certainly not chipper or grouchy.

43
00:04:22,000 --> 00:04:25,000
So what does that say to us?

44
00:04:25,000 --> 00:04:33,000
It simply says that right now the markets don't have a clear direction from here.

45
00:04:33,000 --> 00:04:37,000
Remember, everything is driven by our expectations of the future,

46
00:04:37,000 --> 00:04:42,000
and that's what's going on every time you look at the numbers.

47
00:04:42,000 --> 00:04:45,000
They have nothing to do with what has already happened.

48
00:04:45,000 --> 00:04:48,000
What is being shown to us is not a good thing.

49
00:04:48,000 --> 00:04:52,000
They have nothing to do with what has already happened.

50
00:04:52,000 --> 00:04:58,000
What is being shown to us is what we see as our best estimate of what is about to happen

51
00:04:58,000 --> 00:05:04,000
or what is going to happen, and we don't have a very clear picture right now.

52
00:05:04,000 --> 00:05:08,000
We do know that the economy is on the upswing,

53
00:05:08,000 --> 00:05:13,000
but that's already in the numbers that have been posted.

54
00:05:13,000 --> 00:05:18,000
That's already happened, the view that there is a good time ahead.

55
00:05:18,000 --> 00:05:22,000
But from there, are we seeing that it's going to be better than we think

56
00:05:22,000 --> 00:05:25,000
or it's going to be worse than we think?

57
00:05:25,000 --> 00:05:29,000
That's what we're seeing here, is that we can't see clearly

58
00:05:29,000 --> 00:05:34,000
from the expectations we have already created for the future.

59
00:05:34,000 --> 00:05:40,000
And that gets into the subject of today's lecture, which is on risk and return.

60
00:05:40,000 --> 00:05:48,000
And one of the first things that I'll do is I will informally define risk for you.

61
00:05:48,000 --> 00:05:52,000
But looking here, there's nothing really to be said from these numbers.

62
00:05:52,000 --> 00:05:58,000
The Dow is down a fraction, just out of four hundredths of a percent.

63
00:05:58,000 --> 00:06:01,000
That's really the same as saying it's flat.

64
00:06:01,000 --> 00:06:07,000
The S&P 500 is down a little more, 0.11 percent, but nothing dramatic.

65
00:06:07,000 --> 00:06:16,000
And then the NASDAQ is down a little more, 0.14 percent, but again, nothing major.

66
00:06:16,000 --> 00:06:19,000
It's not telling us anything big.

67
00:06:19,000 --> 00:06:24,000
Now, going over here, looking quickly at crude,

68
00:06:24,000 --> 00:06:31,000
it's in a new trading range from 81 to maybe 88, somewhere in there.

69
00:06:31,000 --> 00:06:34,000
It's on the lower end of that, and as you can see,

70
00:06:34,000 --> 00:06:38,000
it's been tailing all day long.

71
00:06:38,000 --> 00:06:42,000
One thing about, and I can't remember whether I've told you or not,

72
00:06:42,000 --> 00:06:49,000
commodity markets don't have the traditional opening bell at 8.30 a.m.

73
00:06:49,000 --> 00:06:53,000
or something like that, closing at 3.30 or whatever.

74
00:06:53,000 --> 00:06:55,000
They don't have that.

75
00:06:55,000 --> 00:07:00,000
Each commodity's market has its own cycle of the day.

76
00:07:00,000 --> 00:07:05,000
And the crude market has been operating since last night.

77
00:07:05,000 --> 00:07:08,000
That spark chart you see there,

78
00:07:08,000 --> 00:07:13,000
the beginning point would have been sometime in the night last night.

79
00:07:13,000 --> 00:07:19,000
And so they're well into their trading day right now.

80
00:07:19,000 --> 00:07:26,000
And as you can see, it's been dropping through their trading day today.

81
00:07:26,000 --> 00:07:31,000
It was up there, I think it was like 83 or something like that,

82
00:07:31,000 --> 00:07:34,000
when it opened up late last night,

83
00:07:34,000 --> 00:07:38,000
and it's been petering downward slowly all through the day.

84
00:07:38,000 --> 00:07:39,000
Good news.

85
00:07:39,000 --> 00:07:42,000
Unfortunately, it's still higher than it was a few weeks ago

86
00:07:42,000 --> 00:07:46,000
when it was in that 72 to 79 trading range.

87
00:07:46,000 --> 00:07:47,000
It's above that.

88
00:07:47,000 --> 00:07:49,000
So you're going to see gas prices.

89
00:07:49,000 --> 00:07:54,000
And as I had said, it's now showing up,

90
00:07:54,000 --> 00:07:59,000
gas prices a little higher than what they were a couple of weeks ago.

91
00:07:59,000 --> 00:08:02,000
It's nothing, nothing's a killer.

92
00:08:02,000 --> 00:08:07,000
Now, gold, it looks to me,

93
00:08:07,000 --> 00:08:10,000
and I've got some backing for this from others,

94
00:08:10,000 --> 00:08:14,000
that gold is now in its own new trading range.

95
00:08:14,000 --> 00:08:19,000
Several months ago, it would not get above $2,000 an ounce.

96
00:08:19,000 --> 00:08:20,000
Well, it busted through that.

97
00:08:20,000 --> 00:08:24,000
Now it seems to have a resistance level

98
00:08:24,000 --> 00:08:28,000
somewhere a little below $2,200 an ounce.

99
00:08:28,000 --> 00:08:31,000
You see how it pops, it's just bouncing around.

100
00:08:31,000 --> 00:08:32,000
It's not really doing anything.

101
00:08:32,000 --> 00:08:40,000
It's just bouncing around about right now $2,161.

102
00:08:40,000 --> 00:08:44,000
But so there's nothing, we can't see anything from gold.

103
00:08:44,000 --> 00:08:47,000
If gold were skyrocketing, well, that's bad news

104
00:08:47,000 --> 00:08:50,000
because investors are running away from stocks and bonds,

105
00:08:50,000 --> 00:08:52,000
but that's not what's happening.

106
00:08:52,000 --> 00:08:55,000
Coming over here to the 10-year bond,

107
00:08:55,000 --> 00:09:00,000
okay, the yields are dropping, fortunately, again.

108
00:09:00,000 --> 00:09:02,000
It's not a huge amount.

109
00:09:02,000 --> 00:09:05,000
It's just two basis points, but that's good news.

110
00:09:05,000 --> 00:09:06,000
Yields are going down.

111
00:09:06,000 --> 00:09:07,000
That means prices are going up,

112
00:09:07,000 --> 00:09:11,000
which means that their investors are buying into bonds.

113
00:09:11,000 --> 00:09:15,000
It's nothing, no major movement.

114
00:09:15,000 --> 00:09:17,000
It's not like a flight to quality or anything.

115
00:09:17,000 --> 00:09:21,000
But still there's some purchasing of bonds

116
00:09:21,000 --> 00:09:24,000
as a safe harbor kind of investment,

117
00:09:24,000 --> 00:09:27,000
but it's nothing, I mean, two basis points.

118
00:09:27,000 --> 00:09:29,000
It's anything exciting.

119
00:09:29,000 --> 00:09:33,000
But yields going down, that's always good for the economy.

120
00:09:33,000 --> 00:09:38,000
Interest rates getting a little bit lower.

121
00:09:38,000 --> 00:09:41,000
Now over on the other side of the world,

122
00:09:41,000 --> 00:09:43,000
Japan actually started out down,

123
00:09:43,000 --> 00:09:46,000
and it crawled back up by the end of the day trading,

124
00:09:46,000 --> 00:09:49,000
which was last night for us.

125
00:09:49,000 --> 00:09:52,000
It was up about two-thirds of a percent,

126
00:09:52,000 --> 00:09:55,000
so that's a decent day up.

127
00:09:55,000 --> 00:10:01,000
Fairly modest, but decent bull market started to form,

128
00:10:01,000 --> 00:10:05,000
especially in the later part of the trading over in Japan.

129
00:10:05,000 --> 00:10:11,000
Now London just doesn't want to seem to do anything.

130
00:10:11,000 --> 00:10:14,000
It's down and then it's up, but as you can see,

131
00:10:14,000 --> 00:10:18,000
by the end it was just sitting about where it started.

132
00:10:18,000 --> 00:10:21,000
And so they're having kind of the same thing over there

133
00:10:21,000 --> 00:10:23,000
that we're having here.

134
00:10:23,000 --> 00:10:28,000
No clear direction on where it's going to go from here.

135
00:10:28,000 --> 00:10:31,000
So if we don't have a good idea,

136
00:10:31,000 --> 00:10:35,000
no one's going to trade heavily bull or bear.

137
00:10:35,000 --> 00:10:38,000
You just sort of wait until you get a better signal

138
00:10:38,000 --> 00:10:42,000
about what's going to happen.

139
00:10:42,000 --> 00:10:46,000
It's kind of hard to say much more than that

140
00:10:46,000 --> 00:10:48,000
about the markets today.

141
00:10:48,000 --> 00:10:50,000
We're in a holding pattern,

142
00:10:50,000 --> 00:10:54,000
and it may last another day, another week.

143
00:10:54,000 --> 00:10:58,000
It could hold actually like this for as much as a month

144
00:10:58,000 --> 00:11:05,000
before we get a clearer picture of what's going to happen next.

145
00:11:05,000 --> 00:11:11,000
But one thing is for sure, and I've emphasized this before,

146
00:11:11,000 --> 00:11:17,000
even though the news is full of political stuff, dramatic,

147
00:11:17,000 --> 00:11:22,000
while we just barely almost didn't have a budget agreement

148
00:11:22,000 --> 00:11:25,000
or this presidential candidate can't come up

149
00:11:25,000 --> 00:11:27,000
with a half a billion dollar bond

150
00:11:27,000 --> 00:11:30,000
or this candidate is getting senile,

151
00:11:30,000 --> 00:11:33,000
that really doesn't affect markets all that much

152
00:11:33,000 --> 00:11:36,000
unless it's something insanely dramatic.

153
00:11:36,000 --> 00:11:39,000
Politics is not what we do.

154
00:11:39,000 --> 00:11:43,000
We are looking at the productivity of an economy,

155
00:11:43,000 --> 00:11:45,000
the wealth of the nation,

156
00:11:45,000 --> 00:11:49,000
and its prospects for prosperity in the future.

157
00:11:49,000 --> 00:11:52,000
The political scene, if you get a newsletter

158
00:11:52,000 --> 00:11:57,000
or some advice columnist saying or talking head,

159
00:11:57,000 --> 00:12:00,000
going on about the politics

160
00:12:00,000 --> 00:12:04,000
as if that has a lot to do with the markets,

161
00:12:04,000 --> 00:12:06,000
get away from them.

162
00:12:06,000 --> 00:12:09,000
They're just creating sensationalism.

163
00:12:09,000 --> 00:12:12,000
The people who've got the money, who do the investment,

164
00:12:12,000 --> 00:12:15,000
who do the heavy, big investments,

165
00:12:15,000 --> 00:12:18,000
that politics is in Washington.

166
00:12:18,000 --> 00:12:21,000
We're in the fire and shrapnel

167
00:12:21,000 --> 00:12:27,000
at the corner of Wall Street and my portfolio's value.

168
00:12:27,000 --> 00:12:36,000
I'm going to start out with a couple of broad things here.

169
00:12:36,000 --> 00:12:40,000
And just simply, let me take that off the board here

170
00:12:40,000 --> 00:12:42,000
for just a little bit.

171
00:12:42,000 --> 00:12:44,000
Terminology.

172
00:12:44,000 --> 00:12:46,000
And some of this I've brought up before,

173
00:12:46,000 --> 00:12:48,000
and some of it's a little bit new.

174
00:12:48,000 --> 00:12:53,000
The first thing that I want to bring up to you is risk again.

175
00:12:53,000 --> 00:13:03,000
Risk.

176
00:13:03,000 --> 00:13:08,000
Risk in our world, and this is not a terribly formal definition,

177
00:13:08,000 --> 00:13:12,000
but it captures the essence of what we consider risk.

178
00:13:12,000 --> 00:13:19,000
Risk, first of all, has to do with the variety of possible outcomes.

179
00:13:19,000 --> 00:13:24,000
If I know what an outcome is going to be,

180
00:13:24,000 --> 00:13:28,000
there's only one possible outcome, there's no risk.

181
00:13:28,000 --> 00:13:33,000
But if there are two possible outcomes,

182
00:13:33,000 --> 00:13:38,000
then the term risk becomes meaningful to us.

183
00:13:38,000 --> 00:13:44,000
So the first thing about risk would be how many outcomes?

184
00:13:44,000 --> 00:13:54,000
How many outcomes?

185
00:13:54,000 --> 00:13:59,000
Now, that's not enough, but that's a start.

186
00:13:59,000 --> 00:14:04,000
As long as there's more than one possible outcome,

187
00:14:04,000 --> 00:14:09,000
then risk plays in.

188
00:14:09,000 --> 00:14:16,000
But, and the next one is how different are the outcomes?

189
00:14:16,000 --> 00:14:32,000
How different are the possible outcomes?

190
00:14:32,000 --> 00:14:35,000
If you've got a hundred possible outcomes,

191
00:14:35,000 --> 00:14:40,000
but they're all virtually the same, that's not a lot of risk.

192
00:14:40,000 --> 00:14:43,000
That's not a lot of risk.

193
00:14:43,000 --> 00:14:46,000
But you could have two outcomes.

194
00:14:46,000 --> 00:14:50,000
Okay, madam, you are either going to live or die.

195
00:14:50,000 --> 00:14:54,000
Well, that's a rather different outcome, you know, right there.

196
00:14:54,000 --> 00:14:57,000
So, in other words, if, like a stock,

197
00:14:57,000 --> 00:15:03,000
well, the stock could go up 11% or it could go up 11.1%.

198
00:15:03,000 --> 00:15:08,000
You know, that's not, that's minimal risk.

199
00:15:08,000 --> 00:15:12,000
But there is risk because there's more than one possible outcome,

200
00:15:12,000 --> 00:15:14,000
but that's not much.

201
00:15:14,000 --> 00:15:19,000
But if the outcomes, even a few, if they're widely separated,

202
00:15:19,000 --> 00:15:24,000
then there is considerable risk involved.

203
00:15:24,000 --> 00:15:30,000
Another part of this is how,

204
00:15:30,000 --> 00:15:37,000
and I want to use the word clustered,

205
00:15:37,000 --> 00:15:41,000
are the outcomes.

206
00:15:41,000 --> 00:15:48,000
That goes back to what I said in the first one.

207
00:15:48,000 --> 00:15:55,000
You could have some really, really bad or good outcomes,

208
00:15:55,000 --> 00:16:01,000
but most of the possible outcomes are really close together.

209
00:16:01,000 --> 00:16:04,000
Then that would, so, for example,

210
00:16:04,000 --> 00:16:11,000
if I were to draw a normal distribution,

211
00:16:11,000 --> 00:16:18,000
here's one where, here are two.

212
00:16:18,000 --> 00:16:26,000
Most of the probabilities in the second one are right around the center.

213
00:16:26,000 --> 00:16:29,000
But in that second one, there's a spread,

214
00:16:29,000 --> 00:16:34,000
there's more probability in these tails,

215
00:16:34,000 --> 00:16:43,000
and so there's more risk involved in that one.

216
00:16:43,000 --> 00:16:46,000
And there are a few others that are involved here in risk,

217
00:16:46,000 --> 00:16:49,000
but we'll get into those later.

218
00:16:49,000 --> 00:16:55,000
But that kind of gives you the idea of what in general we consider to be risk.

219
00:16:55,000 --> 00:17:00,000
A risky portfolio would be one where the outcomes,

220
00:17:00,000 --> 00:17:03,000
obviously there will be different possible outcomes,

221
00:17:03,000 --> 00:17:10,000
but there is a good chance that some of those outcomes are going to kill us

222
00:17:10,000 --> 00:17:14,000
and some of them are going to really help us out a lot,

223
00:17:14,000 --> 00:17:22,000
as opposed to an investment where most of the outcomes are very close to the expected value,

224
00:17:22,000 --> 00:17:25,000
the average, as it were.

225
00:17:25,000 --> 00:17:39,000
Now, this term, this first term,

226
00:17:39,000 --> 00:17:44,000
there are two types of risk that the book brings up.

227
00:17:44,000 --> 00:17:47,000
There's actually a third one here, too,

228
00:17:47,000 --> 00:17:51,000
and we'll kind of ignore that one for the time being.

229
00:17:51,000 --> 00:17:56,000
And this term, standalone risk,

230
00:17:56,000 --> 00:18:00,000
really never showed up in textbooks,

231
00:18:00,000 --> 00:18:03,000
and I don't recall even hearing the term,

232
00:18:03,000 --> 00:18:07,000
until maybe a decade ago, and now it's kind of the popular term.

233
00:18:07,000 --> 00:18:15,000
The standalone risk is the risk of that individual thing.

234
00:18:15,000 --> 00:18:23,000
In stock, for example, the standalone risk would be the risk of that stock's returns.

235
00:18:23,000 --> 00:18:27,000
And by the way, we talk about returns instead of prices,

236
00:18:27,000 --> 00:18:29,000
but one way or the other.

237
00:18:29,000 --> 00:18:45,000
Standalone risk, that is the overall variation that's possible in the returns to that stock.

238
00:18:45,000 --> 00:18:56,000
Now, the other one, portfolio risk, is that stock in a portfolio,

239
00:18:56,000 --> 00:19:04,000
because it will not, that stock's contribution to the portfolio will be mitigated,

240
00:19:04,000 --> 00:19:09,000
its own risk will be mitigated by the other stocks in the portfolio.

241
00:19:09,000 --> 00:19:17,000
So one stock, you might have a stock that goes, its returns go like that,

242
00:19:17,000 --> 00:19:27,000
but you might have another stock in that portfolio whose returns go like that.

243
00:19:27,000 --> 00:19:34,000
And so they're going to temper, they're going to cancel out part of each other's risk.

244
00:19:34,000 --> 00:19:38,000
This is something I'll show you on Monday, that when you're making,

245
00:19:38,000 --> 00:19:42,000
if you want to build your own portfolio, one of the things that you want to do is look for stocks

246
00:19:42,000 --> 00:19:47,000
that don't have a high correlation in the returns.

247
00:19:47,000 --> 00:19:50,000
They actually are poorly correlated.

248
00:19:50,000 --> 00:19:58,000
That way, they kill off each other's individual weirdness.

249
00:19:58,000 --> 00:20:01,000
And that's one of the things that we have to watch out for.

250
00:20:01,000 --> 00:20:06,000
When we look at an individual stock, we have to say, okay,

251
00:20:06,000 --> 00:20:12,000
the management of this company is all over the board and crazy and all that kind of stuff.

252
00:20:12,000 --> 00:20:15,000
So I'm not going to invest in that stock.

253
00:20:15,000 --> 00:20:20,000
Not necessarily a good thing to think, because you could get another stock

254
00:20:20,000 --> 00:20:28,000
that actually helps cancel out some of the standalone risk of the company that's crazy.

255
00:20:28,000 --> 00:20:32,000
So putting stocks together, and this is something that is not just with stocks,

256
00:20:32,000 --> 00:20:36,000
it's all through the universe, in fact.

257
00:20:36,000 --> 00:20:41,000
Healthcare works the same way. Healthcare insurance.

258
00:20:41,000 --> 00:20:58,000
You see, I'm going to tell you about your life.

259
00:20:58,000 --> 00:21:11,000
This is, on the vertical axis, is healthcare cause.

260
00:21:11,000 --> 00:21:17,000
And on the horizontal axis is time.

261
00:21:17,000 --> 00:21:21,000
Now in your case, you are a sickly little baby.

262
00:21:21,000 --> 00:21:27,000
You were born, you were just a pain in the ass, always in the hospital, not very happy.

263
00:21:27,000 --> 00:21:34,000
And so your cause kind of came up, but then you started getting okay, feeling better.

264
00:21:34,000 --> 00:21:39,000
Then you got into your toddler years, got some broken bones.

265
00:21:39,000 --> 00:21:42,000
Then you started to normalize out a little bit.

266
00:21:42,000 --> 00:21:46,000
And then you found out about girls.

267
00:21:46,000 --> 00:21:52,000
And then you calmed down, then college, drugs.

268
00:21:52,000 --> 00:21:57,000
Then you started maturing, you didn't get as many illnesses,

269
00:21:57,000 --> 00:22:00,000
started getting the herd immunities.

270
00:22:00,000 --> 00:22:04,000
And so then you got to your 40s.

271
00:22:04,000 --> 00:22:13,000
There was high blood pressure, diabetes, Viagra.

272
00:22:13,000 --> 00:22:19,000
And then you got into your older age, kidney problems.

273
00:22:19,000 --> 00:22:24,000
And finally you die.

274
00:22:24,000 --> 00:22:33,000
Now, let's look at your life on the same line.

275
00:22:33,000 --> 00:22:45,000
Well, you see, on that same line, you started out, you were born, just a healthy baby, just a few vaccinations.

276
00:22:45,000 --> 00:22:51,000
But then you had a childhood illness.

277
00:22:51,000 --> 00:22:55,000
But then everything started doing okay.

278
00:22:55,000 --> 00:23:00,000
And then you found out about boys, and then you got over that.

279
00:23:00,000 --> 00:23:05,000
Then you got into your 20s, you were still pretty healthy.

280
00:23:05,000 --> 00:23:11,000
And then you had to have some liposuction.

281
00:23:11,000 --> 00:23:18,000
And then you got a little bit of high cholesterol, heart problems.

282
00:23:18,000 --> 00:23:20,000
You live longer.

283
00:23:20,000 --> 00:23:22,000
And then you die.

284
00:23:22,000 --> 00:23:25,000
Yes, you'll live longer than he will, statistically.

285
00:23:25,000 --> 00:23:28,000
Trust me, it's okay for us to die first.

286
00:23:28,000 --> 00:23:37,000
But you understand, though, if I start putting hundreds and hundreds and hundreds, thousands of these profiles,

287
00:23:37,000 --> 00:23:52,000
all on the same grid, over and over, eventually the average smooths out.

288
00:23:52,000 --> 00:23:54,000
That's called risk pooling.

289
00:23:54,000 --> 00:24:01,000
The more you put in, I don't see, and that was one of the problems was for ages,

290
00:24:01,000 --> 00:24:08,000
insurance companies, well, we're going to not do those people because they have higher incidence of heart attack.

291
00:24:08,000 --> 00:24:13,000
But they had lower incidence of other things.

292
00:24:13,000 --> 00:24:18,000
Cherry picking for this kind of risk pooling is a bad idea.

293
00:24:18,000 --> 00:24:23,000
The more you put in, the smoother it gets.

294
00:24:23,000 --> 00:24:29,000
And so the health insurance company has a predictable cost level per year.

295
00:24:29,000 --> 00:24:39,000
And so setting premium levels, the healthcare costs plus some profit and all that, is much more certain.

296
00:24:39,000 --> 00:24:48,000
That portfolio risk showing up again, we see it all the time, all through the universe.

297
00:24:48,000 --> 00:24:54,000
The individual atoms that are bouncing around in this room, I mean, they're maniacs.

298
00:24:54,000 --> 00:25:00,000
They're just wild, but you put all of that together and suddenly it is beautiful.

299
00:25:00,000 --> 00:25:07,000
It is a smooth distribution of air pressure through the room.

300
00:25:07,000 --> 00:25:09,000
I had an experience with this many years ago.

301
00:25:09,000 --> 00:25:16,000
One of the worst things that ever happened to me when I was in my father years with children,

302
00:25:16,000 --> 00:25:21,000
one of my kids said, Daddy, I want to join the orchestra at school.

303
00:25:21,000 --> 00:25:23,000
Okay, what do you want to learn?

304
00:25:23,000 --> 00:25:28,000
She said something horrible. She said, I want to learn the violin.

305
00:25:28,000 --> 00:25:35,000
Do you know what it sounds like, a violin, in the hands of someone who doesn't know how to play a violin?

306
00:25:35,000 --> 00:25:38,000
I mean, it was terrible.

307
00:25:38,000 --> 00:25:41,000
And then she said, we're going to have our Christmas concert.

308
00:25:41,000 --> 00:25:48,000
We're all going to go and we're going to play our violins, our violas, our cellos all together for Christmas presentation.

309
00:25:48,000 --> 00:25:52,000
I thought, Jesus, take me now.

310
00:25:52,000 --> 00:26:00,000
So I went in there and there they were all seated.

311
00:26:00,000 --> 00:26:03,000
God, this is going to be rough.

312
00:26:03,000 --> 00:26:07,000
And then the conductor came in, tap, tap, tap.

313
00:26:07,000 --> 00:26:10,000
And I thought, here it comes.

314
00:26:10,000 --> 00:26:13,000
And it was gorgeous. It was incredible.

315
00:26:13,000 --> 00:26:15,000
Why?

316
00:26:15,000 --> 00:26:19,000
Because their mistakes were canceling each other out.

317
00:26:19,000 --> 00:26:23,000
And you got the sound of the herd.

318
00:26:23,000 --> 00:26:25,000
We do that all the time.

319
00:26:25,000 --> 00:26:33,000
Choirs, I mean, the individual members are horrible, but when they sing together, it sounds really nice.

320
00:26:33,000 --> 00:26:39,000
That's part of why you sound so damn good when you're singing with the radio in your car,

321
00:26:39,000 --> 00:26:45,000
because your mistakes are being canceled out by whoever's singing on the radio.

322
00:26:45,000 --> 00:26:47,000
This whole thing is a principle of the universe.

323
00:26:47,000 --> 00:26:55,000
And in our world of finance, it is a crucial part of investment strategy.

324
00:26:55,000 --> 00:26:58,000
Diversify your portfolio.

325
00:26:58,000 --> 00:27:03,000
So you are no longer facing the standalone risk of individual stocks.

326
00:27:03,000 --> 00:27:08,000
You're facing the risk of the entire portfolio.

327
00:27:08,000 --> 00:27:15,000
With all the stocks canceling each other out to some extent in their wildest moves,

328
00:27:15,000 --> 00:27:22,000
you get this risk pooling phenomenon occur.

329
00:27:22,000 --> 00:27:25,000
But it goes beyond that.

330
00:27:25,000 --> 00:27:27,000
We have to take it beyond that.

331
00:27:27,000 --> 00:27:30,000
We have to say, OK, how do we measure risk?

332
00:27:30,000 --> 00:27:34,000
And how do we measure those other parts of risk?

333
00:27:34,000 --> 00:27:38,000
Where is the most likely event to occur?

334
00:27:38,000 --> 00:27:45,000
How likely is the most likely event to occur? And all that kind of fun stuff.

335
00:27:45,000 --> 00:27:51,000
That's where we get into some metrics.

336
00:27:51,000 --> 00:28:05,000
The first measure we will need is a measure of central tendency.

337
00:28:05,000 --> 00:28:12,000
In other words, where is the most likely place that it's going to land?

338
00:28:12,000 --> 00:28:19,000
Now, it may never land there, but we know that most of them land around that place.

339
00:28:19,000 --> 00:28:22,000
So we have central tendency measures.

340
00:28:22,000 --> 00:28:32,000
Now, the one that is the most likely for you to have heard of is the average.

341
00:28:32,000 --> 00:28:37,000
We'd mark that by an X with a little bar symbol over it.

342
00:28:37,000 --> 00:28:40,000
X bar.

343
00:28:40,000 --> 00:28:50,000
And the one that you would get on a calculator, and this is just fancy notation,

344
00:28:50,000 --> 00:28:53,000
so you can have it in your notes as you do cool things.

345
00:28:53,000 --> 00:29:02,000
The X bar equals 1 over N times the sum from the first to the last data point

346
00:29:02,000 --> 00:29:05,000
of each data point added up.

347
00:29:05,000 --> 00:29:06,000
That's all.

348
00:29:06,000 --> 00:29:09,000
Add them up and divide by the number of data points.

349
00:29:09,000 --> 00:29:16,000
This is a fancy way of showing that.

350
00:29:16,000 --> 00:29:24,000
Now, more generally, this assumes that each data point has the same probability

351
00:29:24,000 --> 00:29:28,000
of occurrence, 1 over N.

352
00:29:28,000 --> 00:29:34,000
The more general formula would be this one.

353
00:29:34,000 --> 00:29:47,000
X bar equals the sum from I equals 1 to N of each data point times the probability

354
00:29:47,000 --> 00:29:52,000
that that data point is going to happen.

355
00:29:52,000 --> 00:29:53,000
Don't panic.

356
00:29:53,000 --> 00:29:54,000
It's actually in Excel.

357
00:29:54,000 --> 00:29:56,000
It's really easy.

358
00:29:56,000 --> 00:30:02,000
And I'll show you a formula, an Excel formula that really helps with this

359
00:30:02,000 --> 00:30:06,000
but also helps with other things, too.

360
00:30:06,000 --> 00:30:07,000
Let me show you something.

361
00:30:07,000 --> 00:30:10,000
Let me show you something.

362
00:30:10,000 --> 00:30:14,000
I'm going to pull up an Excel sheet here.

363
00:30:14,000 --> 00:30:20,000
Give me a second here.

364
00:30:20,000 --> 00:30:21,000
Whoops.

365
00:30:21,000 --> 00:30:23,000
Didn't mean to do that.

366
00:30:23,000 --> 00:30:28,000
I'm going to see if I can drop the Excel sheet here and follow along with me.

367
00:30:28,000 --> 00:30:32,000
I'll put this up in your files.

368
00:30:32,000 --> 00:30:38,000
It'll be in spreadsheets classroom examples.

369
00:30:38,000 --> 00:30:40,000
So it won't be in the main spreadsheet file.

370
00:30:40,000 --> 00:30:45,000
It'll be in a subfolder because this isn't really a template.

371
00:30:45,000 --> 00:30:59,000
It's just a demonstration kind of event for you.

372
00:30:59,000 --> 00:31:06,000
Give me a second to draw this up.

373
00:31:06,000 --> 00:31:10,000
Come on.

374
00:31:10,000 --> 00:31:19,000
Give me a little extra time here.

375
00:31:19,000 --> 00:31:21,000
I don't want to do it that way.

376
00:31:21,000 --> 00:31:31,000
That's kind of weird.

377
00:31:31,000 --> 00:31:32,000
I kind of wanted to do this.

378
00:31:32,000 --> 00:31:34,000
It's opening up in SharePoint.

379
00:31:34,000 --> 00:31:40,000
I really didn't want to do that.

380
00:31:40,000 --> 00:31:48,000
Save as a copy.

381
00:31:48,000 --> 00:32:00,000
Now let me get it the right way up here.

382
00:32:00,000 --> 00:32:05,000
Come on.

383
00:32:05,000 --> 00:32:06,000
There it is.

384
00:32:06,000 --> 00:32:15,000
In Excel.

385
00:32:15,000 --> 00:32:19,000
Bear with me one moment here while I kill off something else here.

386
00:32:19,000 --> 00:32:24,000
I pulled it up as a SharePoint kind of document.

387
00:32:24,000 --> 00:32:25,000
That's not what I wanted.

388
00:32:25,000 --> 00:32:30,000
I wanted just a plain old Excel document that you could use.

389
00:32:30,000 --> 00:32:40,000
Now let me show you something here.

390
00:32:40,000 --> 00:32:43,000
And as I said, follow along with me.

391
00:32:43,000 --> 00:32:46,000
Here's some data.

392
00:32:46,000 --> 00:32:54,000
I've got five possible outcomes on a stock with probabilities for each

393
00:32:54,000 --> 00:32:56,000
of the possible outcomes.

394
00:32:56,000 --> 00:33:00,000
So in other words, this stock could go down 12%.

395
00:33:00,000 --> 00:33:04,000
There's a probability of.1 that it would do that.

396
00:33:04,000 --> 00:33:07,000
The stock could go down 3%.

397
00:33:07,000 --> 00:33:14,000
And there's a probability of.15 that it would do that.

398
00:33:14,000 --> 00:33:19,000
I've got a stock that could, the stock could also go up 5%.

399
00:33:19,000 --> 00:33:25,000
There's a probability of.3 that it would do that.

400
00:33:25,000 --> 00:33:27,000
And then there's a 10%.

401
00:33:27,000 --> 00:33:29,000
It could go up 10%.

402
00:33:29,000 --> 00:33:32,000
There's a probability of.25 of that.

403
00:33:32,000 --> 00:33:35,000
And it could go up, or it could go up 14%.

404
00:33:35,000 --> 00:33:39,000
There's a probability of.2 of that.

405
00:33:39,000 --> 00:33:47,000
Now I do understand that you had a course where you learned basic statistics.

406
00:33:47,000 --> 00:33:53,000
I'm going to assume that you have almost forgotten that.

407
00:33:53,000 --> 00:33:57,000
So I'm going to try to redo, I'm not going to say, well, you all know that.

408
00:33:57,000 --> 00:34:01,000
I'm going to say, OK, we'll go back through this.

409
00:34:01,000 --> 00:34:07,000
Now the first thing that we're going to want to do is the expected return, that X bar.

410
00:34:07,000 --> 00:34:09,000
Now I don't know if I've shown you this.

411
00:34:09,000 --> 00:34:11,000
I don't think I've shown you this.

412
00:34:11,000 --> 00:34:13,000
You may know it from someplace else.

413
00:34:13,000 --> 00:34:18,000
But what we're going to do is we're going to take that second formula,

414
00:34:18,000 --> 00:34:23,000
each of the data points times its probability, and add those up.

415
00:34:23,000 --> 00:34:34,000
So it would be negative 12% times.1 plus negative 3% times.15, et cetera.

416
00:34:34,000 --> 00:34:37,000
There's a fast way to do it in Excel.

417
00:34:37,000 --> 00:34:41,000
And like I said, I don't think I've shown it to you, but maybe I did.

418
00:34:41,000 --> 00:34:51,000
If you type in, oh, my expected return cell, B8, equals sum product,

419
00:34:51,000 --> 00:35:02,000
S-U-M-P-R-O-D-U-C-T, open parenthesis, you tell it the first array,

420
00:35:02,000 --> 00:35:13,000
which would be the return, A2 through A6, comma, the second array, B2 through B6,

421
00:35:13,000 --> 00:35:19,000
then close the parentheses, it'll do that.

422
00:35:19,000 --> 00:35:20,000
Isn't that kind of cool?

423
00:35:20,000 --> 00:35:29,000
I mean, trust me, we do some problems in here where getting that done would be just a real pain in the butt.

424
00:35:29,000 --> 00:35:36,000
But this way, it does the individual multiplications and then adds a whole mess up.

425
00:35:36,000 --> 00:35:42,000
And there's a place in the course later, before I used Excel,

426
00:35:42,000 --> 00:35:46,000
it took half of a lecture to just do a simple problem.

427
00:35:46,000 --> 00:35:51,000
This, just boom, it's there.

428
00:35:51,000 --> 00:35:59,000
So that is the average, what we typically call the average.

429
00:35:59,000 --> 00:36:14,000
However, there is actually another measure of central tendency that is called the median.

430
00:36:14,000 --> 00:36:21,000
The median, the formula for it, and traditionally I have seen, usually,

431
00:36:21,000 --> 00:36:29,000
I don't even know if there's a standard notation, I usually just use an X with a little hat over it.

432
00:36:29,000 --> 00:36:37,000
And the formula for it is actually ridiculously complicated, but the idea is simple.

433
00:36:37,000 --> 00:36:46,000
The median is the place where half of the data is below that value and half of the data is above that value.

434
00:36:46,000 --> 00:36:50,000
It's a center point.

435
00:36:50,000 --> 00:37:00,000
And what's really important is looking at the median and the average, the mean, as we call it, together.

436
00:37:00,000 --> 00:37:11,000
Because you see, if the data is perfectly symmetric, the average and the center point are the same.

437
00:37:11,000 --> 00:37:26,000
But if they're not the same...let me show you something here.

438
00:37:26,000 --> 00:37:30,000
Like a test that I give.

439
00:37:30,000 --> 00:37:40,000
I could have most of the students, 50% of them are down in here, but those few acers of the exam,

440
00:37:40,000 --> 00:37:46,000
the ones who did really well, they're going to pull the average up.

441
00:37:46,000 --> 00:37:54,000
Even though realistically half the people didn't do better than down here, these are going to affect the average.

442
00:37:54,000 --> 00:38:01,000
They won't do anything to the median, because the actual values don't matter.

443
00:38:01,000 --> 00:38:04,000
It's just how many are above halfway, how many are below halfway.

444
00:38:04,000 --> 00:38:14,000
So the median, and you'll see this later in a very clear example, but this is actually...I could say,

445
00:38:14,000 --> 00:38:17,000
well yeah, the average on this exam was pretty good.

446
00:38:17,000 --> 00:38:22,000
Well that was just because you had a small group of people who aced it.

447
00:38:22,000 --> 00:38:25,000
But the typical student didn't.

448
00:38:25,000 --> 00:38:35,000
And so comparing the mean and the median is something that is useful for a lot of purposes, including in finance.

449
00:38:35,000 --> 00:38:39,000
Or the other one.

450
00:38:39,000 --> 00:38:41,000
This is skewedness.

451
00:38:41,000 --> 00:38:44,000
This is a measure called skewedness.

452
00:38:44,000 --> 00:38:47,000
But here's another one on the other side.

453
00:38:47,000 --> 00:39:01,000
You could have some really low performers on an exam that dragged the average down.

454
00:39:01,000 --> 00:39:11,000
A lot of people did great, but if I look at the average, it's being affected by these people who barely even were there for the exam.

455
00:39:11,000 --> 00:39:19,000
So that's one of those important things that we should, as teachers, be paying a lot of attention to.

456
00:39:19,000 --> 00:39:20,000
Don't look at the average.

457
00:39:20,000 --> 00:39:25,000
As a matter of fact, typically when I'm looking at exams, I don't even look at the average.

458
00:39:25,000 --> 00:39:26,000
I look at the median.

459
00:39:26,000 --> 00:39:30,000
Half of the students were above the score, half were below the score.

460
00:39:30,000 --> 00:39:33,000
Because for me, that's what matters.

461
00:39:33,000 --> 00:39:47,000
Because if I look at the average, I'm seeing the data being skewed one way or the other by the performance of a few people on it.

462
00:39:47,000 --> 00:39:51,000
So that's that second one.

463
00:39:51,000 --> 00:39:58,000
And I call this the median is the halfway point.

464
00:39:58,000 --> 00:40:09,000
Halfway value.

465
00:40:09,000 --> 00:40:19,000
Now the next part of this

466
00:40:19,000 --> 00:40:26,000
would be the metrics of dispersion.

467
00:40:26,000 --> 00:40:35,000
Or spread, as it were.

468
00:40:35,000 --> 00:40:52,000
Overall in higher education, the one that we look at is the standard deviation.

469
00:40:52,000 --> 00:41:12,000
The standard deviation.

470
00:41:12,000 --> 00:41:16,000
This represents a lot more risk than this one does.

471
00:41:16,000 --> 00:41:19,000
The one that's flatter and lays out.

472
00:41:19,000 --> 00:41:22,000
And that's what standard deviation measures.

473
00:41:22,000 --> 00:41:28,000
How clustered, how tight the distribution is.

474
00:41:28,000 --> 00:41:34,000
And the mathematics of that one are a real joy.

475
00:41:34,000 --> 00:41:47,000
I equals one to N, the sigma, is the square root of I equals one to N of each data point minus the average squared.

476
00:41:47,000 --> 00:41:55,000
All divided by N minus one.

477
00:41:55,000 --> 00:42:01,000
I don't have to tell you, that one was a real pain in the butt.

478
00:42:01,000 --> 00:42:04,000
Before, if you do it by hand.

479
00:42:04,000 --> 00:42:10,000
I want to caution something about Excel in this regard.

480
00:42:10,000 --> 00:42:14,000
There is a standard deviation of the population.

481
00:42:14,000 --> 00:42:19,000
That would be, you would divide by N.

482
00:42:19,000 --> 00:42:33,000
The standard deviation of a sample, which is what we almost always will have, we won't have the whole population, is divided by N minus one.

483
00:42:33,000 --> 00:42:37,000
Now let me do something here.

484
00:42:37,000 --> 00:42:49,000
What I'm going to do on this one is, I'm going to take each of the data points.

485
00:42:49,000 --> 00:42:53,000
Open parenthesis equals open parenthesis on cell C2.

486
00:42:53,000 --> 00:42:55,000
Open parenthesis.

487
00:42:55,000 --> 00:43:07,000
I'm going to take the data point, in this case the first one, negative 12, minus the average, 5.15.

488
00:43:07,000 --> 00:43:10,000
Now I'm going to make that an absolute reference.

489
00:43:10,000 --> 00:43:15,000
So that I can drag it down and it won't drag down that expected value.

490
00:43:15,000 --> 00:43:26,000
Then I'm going to take the square of it.

491
00:43:26,000 --> 00:43:34,000
That's enough for that. That's as much fun as I want to do.

492
00:43:34,000 --> 00:43:46,000
But then I'm going to multiply the result times the probability, because they're not all one over, it's not each one has one over N minus one.

493
00:43:46,000 --> 00:44:00,000
In this case, and you have a problem or two like this, and you can try to use this sheet to work with it, times the probability of the occurrence of that outcome.

494
00:44:00,000 --> 00:44:06,000
Okay?

495
00:44:06,000 --> 00:44:16,000
And then I'm going to drag that result down.

496
00:44:16,000 --> 00:44:38,000
So the standard deviation would be equal to each of those, oops, the sum of those, whoa, I've got to stop here.

497
00:44:38,000 --> 00:44:56,000
The square root SQRT of the sum, this is a pain, of all those little squared minus squared things.

498
00:44:56,000 --> 00:45:04,000
And then divide the result by N minus one, which in this case would be five points minus one.

499
00:45:04,000 --> 00:45:20,000
I could do that as a count, but I won't. Just save myself some time here.

500
00:45:20,000 --> 00:45:28,000
And that's your standard deviation. You have a couple problems in the homework that asks you to do this.

501
00:45:28,000 --> 00:45:35,000
If you can try to use this model, it shouldn't be too much of a pain for you to do it.

502
00:45:35,000 --> 00:45:45,000
You just have to make sure that you use that each data point minus the average, square it.

503
00:45:45,000 --> 00:45:56,000
And then times it by the probability of each outcome, unless you don't have probabilities, and then you just divide the whole mess by N minus one.

504
00:45:56,000 --> 00:46:04,000
Matter of fact, I don't think I even need to use the N minus one here. No, I don't.

505
00:46:04,000 --> 00:46:15,000
That's why it was off. Yeah, it should be seven point eight one. I apologize. You don't need to use the N minus one if you've times by each probability.

506
00:46:15,000 --> 00:46:25,000
You use the N minus one if you don't have any probabilities given. Apologies for that.

507
00:46:25,000 --> 00:46:33,000
Now here's a problem. There's a problem with this standard deviation.

508
00:46:33,000 --> 00:46:41,000
It would be completely dependent upon the size of the returns.

509
00:46:41,000 --> 00:46:54,000
So I could look at the standard deviation of this stock, and I couldn't compare that to the standard deviation of another stock, whose returns could be very different.

510
00:46:54,000 --> 00:47:04,000
We have a measure, and I don't recall, you'll have to tell me, where we can standardize, as it were, the standard deviation.

511
00:47:04,000 --> 00:47:18,000
It's called the coefficient of variation.

512
00:47:18,000 --> 00:47:31,000
The CV. Now the coefficient of variation is really simple. All you do is take the standard deviation divided by the average.

513
00:47:31,000 --> 00:47:40,000
That takes out, that number is not a percent, it's actually just a pure number.

514
00:47:40,000 --> 00:47:48,000
And don't let Excel tell you it's a percent. It'll see you calculating using these, and it'll give you an answer, and it'll say this is a percent.

515
00:47:48,000 --> 00:47:53,000
You say no, just turn that into a plain old number by hitting the comma up there.

516
00:47:53,000 --> 00:48:05,000
So in this case, I think I've already formatted it, equals the standard deviation, seven point eight one, divided by the expected return, five point one five.

517
00:48:05,000 --> 00:48:17,000
And there is a CV. It should have no units. That means that it is a pure number that you can compare to other coefficients of variation.

518
00:48:17,000 --> 00:48:27,000
You can't really compare the standard deviations. That's why the coefficient of variation is a much better measure of spread.

519
00:48:27,000 --> 00:48:41,000
Because in this case you could compare the returns to a penny stock, the standard deviation of returns to a penny stock, to the standard deviations of an S&P 500 stock.

520
00:48:41,000 --> 00:48:45,000
They'd be all in the same pure number units.

521
00:48:45,000 --> 00:48:58,000
But if you were to use just standard deviation, the standard deviation of the returns to an S&P 500 stock would be miles larger than the standard deviation of a penny stock bouncing around.

522
00:48:58,000 --> 00:49:08,000
But with a CV, you don't have that problem anymore.

523
00:49:08,000 --> 00:49:15,000
There is one other...oh, let me do something. I'm going to stick in something here. No, it really doesn't mean anything.

524
00:49:15,000 --> 00:49:23,000
The median is just the middle number in a run of ordered numbers.

525
00:49:23,000 --> 00:49:35,000
In this case, the middle number would be the third number of five numbers. So the median return is five percent.

526
00:49:35,000 --> 00:49:41,000
You can also do it here too. Watch. I'm going to insert a row here.

527
00:49:41,000 --> 00:49:46,000
median. Excel will calculate a median too.

528
00:49:46,000 --> 00:49:51,000
It doesn't even have to have the numbers in order for Excel to do it.

529
00:49:51,000 --> 00:50:01,000
Equals median, and you just tell it your array, A2 through A6. It should say five.

530
00:50:01,000 --> 00:50:13,000
Now, that's for an odd number. If you have an even number of data points, you take the one below and one above the halfway point and you take their average.

531
00:50:13,000 --> 00:50:19,000
And Excel will do that for you too.

532
00:50:19,000 --> 00:50:28,000
Let me show you something here before I go on. If you want to try to write down the steps.

533
00:50:28,000 --> 00:50:34,000
Excel has all these hidden Easter eggs as we call them.

534
00:50:34,000 --> 00:50:41,000
Excel has a special, what's called, analytics tool pack or analysis tool pack.

535
00:50:41,000 --> 00:50:51,000
It's not automatically in Excel, but you can insert it into Excel.

536
00:50:51,000 --> 00:51:03,000
And it will, you can just show it data and say, and that analyzes it, and it will pour out these statistics and a pile of other stuff too.

537
00:51:03,000 --> 00:51:11,000
If you want to use it. And it's really nice if you've got a bunch of data and you just want to see what the statistics are on it.

538
00:51:11,000 --> 00:51:14,000
It'll do it. Let me show you how to get to it.

539
00:51:14,000 --> 00:51:24,000
Go file on the menu bar down to options. Clear down at the bottom, options.

540
00:51:24,000 --> 00:51:37,000
You go in, you're going to get an Excel options menu. On the left side, go down and click on add ins.

541
00:51:37,000 --> 00:51:48,000
So file, options, add ins. See that first one? It's got tons of these little packs.

542
00:51:48,000 --> 00:51:54,000
The one you would want is the very top one, analysis tool pack.

543
00:51:54,000 --> 00:52:04,000
Now don't just say okay, go down here and say go. On the left side down there at the bottom, say go.

544
00:52:04,000 --> 00:52:10,000
And then it'll say okay, what do you want? Because there are a couple of different ones.

545
00:52:10,000 --> 00:52:20,000
Click the check mark on analysis tool pack. There are four different check boxes. Check the first one.

546
00:52:20,000 --> 00:52:32,000
And then say okay. And then that will show up on your menu bar.

547
00:52:32,000 --> 00:52:42,000
And you can see all kinds of different stuff. You can choose what you want.

548
00:52:42,000 --> 00:52:49,000
It'll even find data sets for you and stuff like that. It's a really nice tool pack.

549
00:52:49,000 --> 00:52:59,000
Now in some of your versions of Excel 365, it will stay there. You exit Excel, come back, it'll be there.

550
00:52:59,000 --> 00:53:08,000
In others, you have to invoke it. What I did there, every time you open Excel, it's just kind of a pain in the butt.

551
00:53:08,000 --> 00:53:15,000
I don't know why it's not always there once you choose it. But if you come back into Excel and you don't see your add ins,

552
00:53:15,000 --> 00:53:22,000
you have to go back through that process and get it back in there for your session.

553
00:53:22,000 --> 00:53:32,000
Now any analysis it does, it'll stay there even when you close. But if you want to do new stuff, you'd have to bring it back in again.

554
00:53:32,000 --> 00:53:41,000
But like I said, some Excel, some of you probably will have it once you bring it in. It'll be there whenever you open Excel.

555
00:53:41,000 --> 00:53:48,000
Other ones, it's just gone. For example, on this computer, I'm pretty sure that it, well maybe they've changed it.

556
00:53:48,000 --> 00:53:58,000
But every time I do this analysis tool pack, I have to put it back in if I've closed Excel.

557
00:53:58,000 --> 00:54:04,000
But anyway, there's that. It's useful to know.

558
00:54:04,000 --> 00:54:14,000
What was I, oh I know what I was thinking. Okay. So there are the basic outlines.

559
00:54:14,000 --> 00:54:37,000
The problem is, and of course I erased it, standalone risk and portfolio risk.

560
00:54:37,000 --> 00:54:49,000
Let me take us back to the beginning. Standalone risk is that standard deviation.

561
00:54:49,000 --> 00:54:56,000
It's just measuring the returns variation of that stock. That's all it's doing.

562
00:54:56,000 --> 00:55:14,000
Portfolio risk for a given company is beta.

563
00:55:14,000 --> 00:55:23,000
And I'll get into that one on Monday. But let me finish what I'm doing here.

564
00:55:23,000 --> 00:55:34,000
I'm going to bring up another spreadsheet to do this one. New spreadsheet.

565
00:55:34,000 --> 00:55:45,000
Now this is also on the one that it will be, it's actually in the other one, but I want to show you how to do it from the beginning.

566
00:55:45,000 --> 00:55:58,000
Let's use some real world data. As I said, the world is just being driven by data.

567
00:55:58,000 --> 00:56:11,000
The problem is that big data sets, what we need to do modeling and all that is usually we are going to need data sets that have anywhere

568
00:56:11,000 --> 00:56:18,000
from a thousand data points up to hundreds of millions of data points.

569
00:56:18,000 --> 00:56:27,000
Excel can usually handle it, but it costs money to go to those. They are subscriptions.

570
00:56:27,000 --> 00:56:39,000
You can subscribe and then you can get access to the data, but to have access to decent data on your own, that's a little without paying for it.

571
00:56:39,000 --> 00:56:48,000
Now the Federal Reserve has tons of data, which is free, and there are a few other sites where you can get some historical data.

572
00:56:48,000 --> 00:56:54,000
But let me show you one place, it's been around for a long time.

573
00:56:54,000 --> 00:57:06,000
Early in the life of this website, we were even using it in the PhD programs to get some data.

574
00:57:06,000 --> 00:57:19,000
But let me show you the place. It's called runstockone.com.

575
00:57:19,000 --> 00:57:34,000
Now here's what's neat about this site. They will give you year by year returns for a whole lot of different things from 1975 up to the most recent year,

576
00:57:34,000 --> 00:57:42,000
in this case 2023. You can get, here's one, the Dow Jones Industrial Average.

577
00:57:42,000 --> 00:57:50,000
Year by year, the one we'll be interested in are the returns, the percentage returns.

578
00:57:50,000 --> 00:58:00,000
But there it is. Go back and I can get the Standard & Poor's returns year by year by year.

579
00:58:00,000 --> 00:58:06,000
Go back and I can get the NASDAQ returns year by year.

580
00:58:06,000 --> 00:58:20,000
In fact, you can get world market indexes. You can get the London Exchange, the Han Exchange, Swiss Borth.

581
00:58:20,000 --> 00:58:28,000
They're all there for you. Oh, all these different nations, you can get theirs if you wanted to.

582
00:58:28,000 --> 00:58:32,000
This is great for term papers, research papers. You look like a hero.

583
00:58:32,000 --> 00:58:40,000
Where the hell did you get all those numbers for that weird exchange on some place in the middle of nowhere?

584
00:58:40,000 --> 00:58:47,000
In fact, you can get the year by year returns to stocks of companies.

585
00:58:47,000 --> 00:58:56,000
You just first letter, then you hunt down that company, and you can get its returns on the stock year by year.

586
00:58:56,000 --> 00:59:04,000
And it's adjusted for stock splits and all that kind of stuff. In other words, it's cleaned data.

587
00:59:04,000 --> 00:59:14,000
So here's what I'm going to do. I'm going to show you a stupid pet trick in Excel.

588
00:59:14,000 --> 00:59:27,000
First things first, let's say I've got this Excel sheet and I want the returns from 1975 to 2023 for the Dow.

589
00:59:27,000 --> 00:59:37,000
The first thing I'll do is I will go over here and I'll go to the DJIA in one stock one dot com.

590
00:59:37,000 --> 00:59:47,000
The next thing you want to do is click on the URL, the web address. Control V, copy it.

591
00:59:47,000 --> 00:59:53,000
Now watch what I'm going to do. I've got, in other words, what I've gotten here. Oh, will you stop that?

592
00:59:53,000 --> 01:00:02,000
Let me try that again. Control V. I got the web address of this data.

593
01:00:02,000 --> 01:00:10,000
Now in Excel, here's what I'm going to do. Data, quit.

594
01:00:10,000 --> 01:00:18,000
Now under the data tab, I'm going to say get data.

595
01:00:18,000 --> 01:00:30,000
Down to from other resource, from other source, and I'm going to choose from the web.

596
01:00:30,000 --> 01:00:40,000
And it's going to ask me, what is the web address of this data set? Control V.

597
01:00:40,000 --> 01:00:51,000
Well, let's try that again. Control C, then go back over here. Control V. Go here.

598
01:00:51,000 --> 01:01:04,000
And you say OK. It's going to spin around for a while. I think I'm running out of room here.

599
01:01:04,000 --> 01:01:15,000
OK. Why is it having a hard time? Try that again.

600
01:01:15,000 --> 01:01:29,000
Did I key in the URL wrong? Control C. OK. Control V. Oops. OK.

601
01:01:29,000 --> 01:01:33,000
And now it's going to spin around. I must have done something wrong. OK.

602
01:01:33,000 --> 01:01:40,000
And you say, oh, it's just saying I found it. And you say connect.

603
01:01:40,000 --> 01:01:46,000
And then it's going to link right to that database. Now here's the problem.

604
01:01:46,000 --> 01:01:56,000
A lot of web pages are built on tables. The menu you see at the top, drop down, and all that, that's a table.

605
01:01:56,000 --> 01:02:00,000
And Excel is going to say, I'm seeing a couple of different tables here.

606
01:02:00,000 --> 01:02:07,000
In most cases, it won't be the first table that it finds. That's the menu thing.

607
01:02:07,000 --> 01:02:19,000
Table 1. Just click on it once. And it's showing you, well, that's table 1. Is that what you want? Oh, that sure is. Load it.

608
01:02:19,000 --> 01:02:28,000
Looky there. There's that data in Excel now. All of it.

609
01:02:28,000 --> 01:02:34,000
Well, that was fun. I'm going to call that one Dow 30.

610
01:02:34,000 --> 01:02:47,000
Now let's go and make a new sheet. I'm going to go back over here to the One Stock One, back up, and I'm going to look at the S&P 500.

611
01:02:47,000 --> 01:02:59,000
And I'm going to copy its web address, Control C. I'm going to go back here to Excel, do the same trick again.

612
01:02:59,000 --> 01:03:08,000
Data, on this new sheet, data, get a data set from another resource from the web.

613
01:03:08,000 --> 01:03:15,000
And I'm going to give it the URL, and I'm still going to say, go find it.

614
01:03:15,000 --> 01:03:23,000
And this time, table 1, is that what you want? Sure is. Load it.

615
01:03:23,000 --> 01:03:33,000
And there's the S&P 500 data. I'll give that S&P 500.

616
01:03:33,000 --> 01:03:44,000
We're going to do it one more time. Sheet. I'm going to sheet, a new sheet, and I'm going to pull the same stunt, go over here to OneStockOne.com.

617
01:03:44,000 --> 01:03:51,000
I'm going to back up and then choose the NASD NASDAQ.

618
01:03:51,000 --> 01:04:01,000
And I'm going to copy its URL, and I do know that this, we'll do this a bunch of times so that you get the hang of this because this is really a good skill to have.

619
01:04:01,000 --> 01:04:06,000
Yeah, I'm going to copy that one. Copy.

620
01:04:06,000 --> 01:04:20,000
And then I'm going to go back over here to Excel and do the whole thing one more time. Data, get data from other source, from the web.

621
01:04:20,000 --> 01:04:26,000
And this time I'm going to tell it that. Okay.

622
01:04:26,000 --> 01:04:34,000
Let's look at table 1, is that what you want? Sure is. Load it.

623
01:04:34,000 --> 01:04:38,000
And it's got that data set.

624
01:04:38,000 --> 01:04:53,000
So you have just, now I can kill off everything else, you have just put in hundreds of data points just by telling Excel where the data was.

625
01:04:53,000 --> 01:04:56,000
Now watch what I'm going to do here.

626
01:04:56,000 --> 01:05:08,000
First thing I'm going to do is, let me make this a little bigger.

627
01:05:08,000 --> 01:05:11,000
Dow 30.

628
01:05:11,000 --> 01:05:16,000
I'm going to highlight all of these, you don't need to know how to do this.

629
01:05:16,000 --> 01:05:23,000
Oh, I forgot to call this one the NASDAQ.

630
01:05:23,000 --> 01:05:28,000
So the first thing I want to do is I'm going to highlight all of these.

631
01:05:28,000 --> 01:05:48,000
And right here I'm going to write the mean, the average, the median, and the, if I had to do that.

632
01:05:48,000 --> 01:06:03,000
Oh well, never mind. Mean, median, standard deviation, and the coefficient, let's try that one more time.

633
01:06:03,000 --> 01:06:17,000
Median and coefficient of variation, and standard deviation, and the coefficient of variation.

634
01:06:17,000 --> 01:06:27,000
And I'll just do one, the Dow right now.

635
01:06:27,000 --> 01:06:42,000
All you do is just say equals average of this data right here, and there's your mean.

636
01:06:42,000 --> 01:06:50,000
Your median equals the median of that data array.

637
01:06:50,000 --> 01:06:55,000
That right there.

638
01:06:55,000 --> 01:06:57,000
Standard deviation.

639
01:06:57,000 --> 01:06:58,000
Be careful with this one.

640
01:06:58,000 --> 01:07:17,000
STDEV is the one that will divide by N minus one. Equals STDEV.

641
01:07:17,000 --> 01:07:19,000
Of that data.

642
01:07:19,000 --> 01:07:29,000
And then finally, CV.

643
01:07:29,000 --> 01:07:35,000
Huh, I wonder why it's saying that.

644
01:07:35,000 --> 01:07:39,000
ST, oh I see, STDEV.

645
01:07:39,000 --> 01:07:48,000
And the coefficient of variation equals the standard deviation divided by the average.

646
01:07:48,000 --> 01:07:55,000
And I will do the rest of this on Monday, but for right now you have a very short quiz.

647
01:07:55,000 --> 01:07:58,000
It should be available to you.

648
01:07:58,000 --> 01:08:03,000
Wait, give me just a second, I'll have to fix it up for you.

649
01:08:03,000 --> 01:08:06,000
You have a very short quiz to take.

650
01:08:06,000 --> 01:08:11,000
And then once you're finished with that, that's all I have for you today.

651
01:08:11,000 --> 01:08:21,000
I will thank you.