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:31,000
Today, risk and return.

5
00:00:31,000 --> 00:00:39,000
This is a subject that Excel is extraordinarily useful.

6
00:00:39,000 --> 00:00:47,000
Building a template though, it's sort of like, it's just a matter of knowing a few formulas

7
00:00:47,000 --> 00:00:55,000
and I'm going to show you a rather valuable thing that Excel can do

8
00:00:55,000 --> 00:01:01,000
that will be useful to you possibly just well beyond this course.

9
00:01:01,000 --> 00:01:03,000
But I'll get to that in a little bit here.

10
00:01:03,000 --> 00:01:11,000
The first thing to do is of course, as always, let's look at the numbers.

11
00:01:11,000 --> 00:01:17,000
And we have an interesting situation set up here.

12
00:01:17,000 --> 00:01:27,000
If I can get to the actual numbers I want to use to start our look.

13
00:01:27,000 --> 00:01:31,000
Come on, there we go.

14
00:01:31,000 --> 00:01:39,000
You see that we have another one of those days that's kind of hard to say

15
00:01:39,000 --> 00:01:41,000
what's going on here.

16
00:01:41,000 --> 00:01:48,000
The Dow and the S&P 500 are down just a little bit.

17
00:01:48,000 --> 00:01:59,000
And the NASDAQ was down too, but it has just popped in the last few minutes.

18
00:01:59,000 --> 00:02:03,000
And the SPARK chart isn't even showing that.

19
00:02:03,000 --> 00:02:11,000
Overall, there is not much of a direction that the markets are seeing where to go from here.

20
00:02:11,000 --> 00:02:15,000
And in a case like that, you'll just see the stocks bounce around.

21
00:02:15,000 --> 00:02:24,000
As you can see, the Dow was down just a tiny bit and so is the S&P 500, a small amount.

22
00:02:24,000 --> 00:02:26,000
Nothing really major.

23
00:02:26,000 --> 00:02:29,000
There's no big, big news.

24
00:02:29,000 --> 00:02:37,000
Now, the Fed Chairman has just finished mumbling or prognosticated or saying what he has to say.

25
00:02:37,000 --> 00:02:41,000
And that seems to have really gotten the NASDAQ all kinds of happy.

26
00:02:41,000 --> 00:02:45,000
But it doesn't seem to have shown anything yet on the other two exchanges,

27
00:02:45,000 --> 00:02:50,000
although the quotes may be delayed, and that's why those haven't popped.

28
00:02:50,000 --> 00:02:58,000
The Fed's speech was overall taken positively by the markets.

29
00:02:58,000 --> 00:03:01,000
It was favorable, the reaction was.

30
00:03:01,000 --> 00:03:08,000
So that should get all of them up a little bit here in the next hour or so.

31
00:03:08,000 --> 00:03:11,000
Crude oil, it's been plunging.

32
00:03:11,000 --> 00:03:12,000
It was up there.

33
00:03:12,000 --> 00:03:17,000
It's in a range that old 72 to 79 is gone.

34
00:03:17,000 --> 00:03:25,000
Right now, its range seems to be wanting somewhere around 81 to 88 or 89.

35
00:03:25,000 --> 00:03:28,000
And it's kind of bouncing around in there.

36
00:03:28,000 --> 00:03:30,000
It's at the low end of the range.

37
00:03:30,000 --> 00:03:34,000
As you can see, now, one point to make here is that commodity markets,

38
00:03:34,000 --> 00:03:41,000
I think I've said this before, commodity markets do not work on the same clock as stock markets do.

39
00:03:41,000 --> 00:03:45,000
So the oil market has been active since last night.

40
00:03:45,000 --> 00:03:49,000
And through that time, it has been dropping somewhat.

41
00:03:49,000 --> 00:03:58,000
It was up there, I think, around 83 a barrel when their trading started last night.

42
00:03:58,000 --> 00:04:01,000
And it has been falling ever since then.

43
00:04:01,000 --> 00:04:03,000
Well, that's still not going to help the price of gasoline.

44
00:04:03,000 --> 00:04:07,000
It's still going to probably be a little on the high side.

45
00:04:07,000 --> 00:04:12,000
But that's about all there is to it.

46
00:04:12,000 --> 00:04:16,000
We just say, okay, oil prices are a little higher than they were.

47
00:04:16,000 --> 00:04:17,000
It's not terrible.

48
00:04:17,000 --> 00:04:23,000
We're seeing right now about 379 a gallon in the local area,

49
00:04:23,000 --> 00:04:30,000
up from where it had gotten earlier months ago at 339 and 349.

50
00:04:30,000 --> 00:04:32,000
But it's still not awful.

51
00:04:32,000 --> 00:04:35,000
Gold, it's just staying flat.

52
00:04:35,000 --> 00:04:37,000
There's not like any panic going on.

53
00:04:37,000 --> 00:04:46,000
To what I am seeing, gold has now built a trading range around 2100 to 2200.

54
00:04:46,000 --> 00:04:50,000
And it's staying right now tightly within that range.

55
00:04:50,000 --> 00:04:57,000
So you don't see anything like panic, gold buying causing the price of gold to go up right now.

56
00:04:57,000 --> 00:05:04,000
It's just sort of floating along, waiting like everyone else for what happens next.

57
00:05:04,000 --> 00:05:14,000
The bond market, no, it's down two basis points.

58
00:05:14,000 --> 00:05:18,000
Right now the yield is down two basis points, but down not quite so much.

59
00:05:18,000 --> 00:05:24,000
But at the same time, clearly the yield is down, which means the price is up,

60
00:05:24,000 --> 00:05:27,000
which means that there is some buying, nothing spectacular,

61
00:05:27,000 --> 00:05:32,000
but there's some buying of safe harbor in the treasuries.

62
00:05:32,000 --> 00:05:39,000
And that's probably some of that money is coming from the price of bonds going up

63
00:05:39,000 --> 00:05:41,000
because the price of stocks has settled.

64
00:05:41,000 --> 00:05:48,000
So some of the money that was there in the stocks, the stocks sold off a little bit,

65
00:05:48,000 --> 00:05:50,000
and that money was put into bonds.

66
00:05:50,000 --> 00:05:55,000
And that's why the price went up and the yield went down.

67
00:05:55,000 --> 00:06:01,000
Coming over here to the other side of the world, the Nikkei started out in kind of a bad mood,

68
00:06:01,000 --> 00:06:05,000
but through their trading day, which was our last night,

69
00:06:05,000 --> 00:06:11,000
it crawled its way up to a decent end at about two-thirds of a percent up.

70
00:06:11,000 --> 00:06:13,000
So, I mean, there's that.

71
00:06:13,000 --> 00:06:18,000
But London, very much like the United States, is just sort of bouncing around.

72
00:06:18,000 --> 00:06:25,000
It's finished the day off 0.01%, which basically means it was flat for the day.

73
00:06:25,000 --> 00:06:30,000
It's just sitting there sort of like where we have been through the day today, too.

74
00:06:30,000 --> 00:06:36,000
No clear direction in the Western world on where the economies are going.

75
00:06:36,000 --> 00:06:40,000
We already know that the economies are getting better.

76
00:06:40,000 --> 00:06:45,000
But that's already been put into the prices.

77
00:06:45,000 --> 00:06:51,000
From here, the expectations would be about what goes from that assumption.

78
00:06:51,000 --> 00:06:53,000
We've got an improving economy.

79
00:06:53,000 --> 00:06:58,000
Is it going to improve more, less, or is it going to be about what we expected?

80
00:06:58,000 --> 00:07:00,000
Right now, it's just sitting where it is.

81
00:07:00,000 --> 00:07:07,000
So, yeah, we're just sort of going to work on the idea that it's where we expected it to go,

82
00:07:07,000 --> 00:07:08,000
the economy is.

83
00:07:08,000 --> 00:07:12,000
Not better than we expected, not worse than we expected.

84
00:07:12,000 --> 00:07:15,000
And these markets are just not going to move.

85
00:07:15,000 --> 00:07:23,000
They could just stay like this kind of flat for a day or week or even a month or more.

86
00:07:23,000 --> 00:07:27,000
The markets could just sit waiting for something big to happen.

87
00:07:27,000 --> 00:07:34,000
We don't see it yet, so markets are just going to stay flat.

88
00:07:34,000 --> 00:07:44,000
Let me take you on a journey here for a little bit.

89
00:07:44,000 --> 00:07:46,000
This is about risk and return.

90
00:07:46,000 --> 00:07:51,000
To begin this, I have to talk about risk and get a little more formal.

91
00:07:51,000 --> 00:07:53,000
I'm not really super formal yet.

92
00:07:53,000 --> 00:08:02,000
But this is sort of the outline on a little bit more structured basis of the concept of risk.

93
00:08:02,000 --> 00:08:12,000
Risk is, for lack of a better way of explaining it, risk is the variation in possible outcomes.

94
00:08:12,000 --> 00:08:17,000
If there is only one outcome, then there is no risk.

95
00:08:17,000 --> 00:08:19,000
We know what's going to happen.

96
00:08:19,000 --> 00:08:27,000
But once we go to more than one possible outcome, there is risk.

97
00:08:27,000 --> 00:08:35,000
Two outcomes, you are going to live or die.

98
00:08:35,000 --> 00:08:37,000
Now that's risk.

99
00:08:37,000 --> 00:08:40,000
Today you could live or you could die.

100
00:08:40,000 --> 00:08:43,000
Well, you see, there we go.

101
00:08:43,000 --> 00:08:47,000
But there could be more outcomes.

102
00:08:47,000 --> 00:09:04,000
So risk, the first thing is about risk, how many possible outcomes.

103
00:09:04,000 --> 00:09:07,000
The second part.

104
00:09:07,000 --> 00:09:33,000
Okay, how different are those possible outcomes?

105
00:09:33,000 --> 00:09:43,000
Well, if you have two possible outcomes, like a stock could go up 8% or 8.1%, well, I mean, that's not really risk.

106
00:09:43,000 --> 00:09:47,000
It is a risk, but it's trivial.

107
00:09:47,000 --> 00:09:56,000
But if the stock could go down 18% or up 23%, well, that is a lot of difference in those outcomes.

108
00:09:56,000 --> 00:10:03,000
So how different are the possible outcomes is part of risk.

109
00:10:03,000 --> 00:10:25,000
Another part of it is how, for lack of a better term, clustered are the possible outcomes.

110
00:10:25,000 --> 00:10:36,000
How clustered are the possible outcomes?

111
00:10:36,000 --> 00:10:45,000
In other words, if almost all of the possible outcomes are very close together and you have a few outliers,

112
00:10:45,000 --> 00:10:57,000
that's a different situation from one where the outcomes are all over the place and you don't see any clustering around a specific amount.

113
00:10:57,000 --> 00:11:02,000
That would be a very different kind of risk.

114
00:11:02,000 --> 00:11:13,000
Now typically I'll draw this as a normal distribution.

115
00:11:13,000 --> 00:11:30,000
Now in the first one versus the second one, most of the outcomes are near the center.

116
00:11:30,000 --> 00:11:45,000
In the second one, there's more distribution. There's a lot of chance that outcomes could be very far away from the center.

117
00:11:45,000 --> 00:11:58,000
And so this one, this lower one, would be a riskier portfolio than this one.

118
00:11:58,000 --> 00:12:23,000
And one more, how biased are the possible outcomes? Let me explain that.

119
00:12:23,000 --> 00:12:41,000
Let me draw you three.

120
00:12:41,000 --> 00:12:56,000
That one in the middle, the one at the top rather, is symmetric. There's no appearance that some below are more likely than some above.

121
00:12:56,000 --> 00:13:08,000
In the second one, there is more of a chance of seeing outcomes below than above.

122
00:13:08,000 --> 00:13:16,000
Similarly in the third one, there is more of a chance of outcomes above than below.

123
00:13:16,000 --> 00:13:27,000
That's bias. The technical term for it is skew, skew, S-K-E-W, skewness.

124
00:13:27,000 --> 00:13:42,000
This is why, I'll get to the next one here, we have to turn this into meaningful numbers so we can compare one risk asset to another risk asset.

125
00:13:42,000 --> 00:13:57,000
So we have to have numbers. The first number that we would want is a number that measures central tendency.

126
00:13:57,000 --> 00:14:07,000
Where is the middle, as it were? Now there are two that do that.

127
00:14:07,000 --> 00:14:15,000
The first one is the most popular, and it's not necessarily the best, but it's the most popular. It's called the average.

128
00:14:15,000 --> 00:14:24,000
And I'm going to represent that with a little bar over it, X bar.

129
00:14:24,000 --> 00:14:33,000
Now this one mathematically, and I'm just writing the formula because it looks cool in your notes, don't freak out.

130
00:14:33,000 --> 00:14:48,000
It would be the sum of the data points from the first one to the last one of each data point times its probability of occurrence.

131
00:14:48,000 --> 00:14:55,000
So you take each data point times its probability and add them all up.

132
00:14:55,000 --> 00:15:02,000
That could be a real pain in the butt to do by hand. Fortunately Excel has a very cool trick in it that can make it.

133
00:15:02,000 --> 00:15:12,000
Now if all of the probabilities are the same, then this simplifies to what you are probably more familiar with,

134
00:15:12,000 --> 00:15:22,000
which is 1 over N times the sum from I equals 1 to N of the data points, X sub I.

135
00:15:22,000 --> 00:15:30,000
In other words, add them up and divide by the number. That's the one that most people are familiar with, the average.

136
00:15:30,000 --> 00:15:44,000
A little technical thing here of terminology. When I say average, it usually means you just add them up and divide by the number of data points.

137
00:15:44,000 --> 00:15:58,000
The more precise term is mean, M-E-A-N. That would be when you actually take each data point times its probability of occurrence.

138
00:15:58,000 --> 00:16:11,000
That's quite useful. The spreadsheets that I'm going to pull up here, I will load those into Canvas for you to use.

139
00:16:11,000 --> 00:16:29,000
So don't panic if you don't keep up with me here on this. But I'll start with...

140
00:16:29,000 --> 00:16:45,000
Here's a little bit of a data set. You got the return to a stock and there are five possible returns. Negative 12%, negative 3%, 5%, and 10%, and 14%.

141
00:16:45,000 --> 00:17:00,000
Oh, sorry about that. Thank you. There, there's your sheet. And like I said, I'll have this up for you on the web.

142
00:17:00,000 --> 00:17:24,000
If you want to key it in. So our first one, the expected return, the mean as it were. You will have to multiply negative 12% times 0.1% plus negative 3% times 0.15%

143
00:17:24,000 --> 00:17:43,000
plus 5% times 0.3% plus 10% times 0.25% plus 14% times 0.2%. However, there's a stupid pet trick in Excel.

144
00:17:43,000 --> 00:17:55,000
I don't think I've shown it to you yet, but you will be really happy to use this in a lot of situations. It's called sum product.

145
00:17:55,000 --> 00:18:08,000
I'm going to write the expected return for cell B8, I'll say equals sum product.

146
00:18:08,000 --> 00:18:21,000
Open the parentheses. Now with sum product, you tell it a first array, which would be the array negative 12%, negative 3%, 5%, 10%, 14%.

147
00:18:21,000 --> 00:18:42,000
Then comma, you give it the second array, 0.1, 0.15, 0.3, 0.25, 0.2. And what that will do is that will take each data point on the first array and multiply it by the associated data point on the second array

148
00:18:42,000 --> 00:18:55,000
and then add all those up. And there's your Uncle Bob, 5.15%. That's a lot easier than taking each one times its probability plus the next one.

149
00:18:55,000 --> 00:19:07,000
This, a little later in the course, when we get to weighted average cost of capital, that is a pain to do by hand.

150
00:19:07,000 --> 00:19:16,000
But sum product makes it a lot less painful, believe me.

151
00:19:16,000 --> 00:19:28,000
Now, however, comes the second measure of central tendency. This one was the mean.

152
00:19:28,000 --> 00:19:41,000
The next one is the median. And I could write a formula for it, but the formula is daunting, even though the idea is simple.

153
00:19:41,000 --> 00:19:53,000
The median is the halfway point in the data. Half of the data is below the median, half of the data is above the median.

154
00:19:53,000 --> 00:20:15,000
Now, that's an important thing. So I just write this as the halfway value of the data.

155
00:20:15,000 --> 00:20:25,000
This, actually, the median in many situations is more important than the average.

156
00:20:25,000 --> 00:20:33,000
Let me explain. Suppose that I give an exam.

157
00:20:33,000 --> 00:20:45,000
Where's my eraser? Suppose that I give an exam.

158
00:20:45,000 --> 00:20:56,000
And the distribution looks like this.

159
00:20:56,000 --> 00:21:06,000
This, the average, is going to be abnormally affected by those few high scores there.

160
00:21:06,000 --> 00:21:15,000
The median doesn't care what the numbers themselves are. All it cares about is how many are above the halfway point and how many are below.

161
00:21:15,000 --> 00:21:22,000
So the median would tell me that half of the scores are about here.

162
00:21:22,000 --> 00:21:31,000
The average would tell me because these high scores were pulling it, I would see a higher number.

163
00:21:31,000 --> 00:21:38,000
Just because a few people aced the exam, but most people didn't do all that well.

164
00:21:38,000 --> 00:21:50,000
The same would be true the other way. I could have a distribution where I have a few low scores, but most people did very well on the exam.

165
00:21:50,000 --> 00:21:57,000
Those low scores would draw down the average, but they would have no effect on the median.

166
00:21:57,000 --> 00:22:04,000
Because the median just counts how many are below and how many are above the middle value.

167
00:22:04,000 --> 00:22:13,000
That's why medians, especially in education statistics, they should be used and they hardly ever are.

168
00:22:13,000 --> 00:22:22,000
As a matter of fact, this abomination called Canvas, its statistics will tell me the average on an exam, but they won't tell me the median.

169
00:22:22,000 --> 00:22:29,000
I have to take it into Excel and get the median. I have to put all that data into Excel for a test and then get the median.

170
00:22:29,000 --> 00:22:36,000
Because that's the one that really matters, but Canvas doesn't give me that.

171
00:22:36,000 --> 00:22:46,000
That gives you an idea of how poorly people understand statistics, even basic ideas.

172
00:22:46,000 --> 00:22:51,000
You don't need that. Well, yeah, I do, because it means more to me.

173
00:22:51,000 --> 00:23:01,000
If I've got a test where I have a couple of you geniuses, you four geniuses right there, aced the exam, but everyone else flunks it,

174
00:23:01,000 --> 00:23:09,000
I'm going to say, well, the average was pretty good. Everyone else is going to feel like, boy, I'm a dumbass.

175
00:23:09,000 --> 00:23:19,000
No, you actually were pretty much normal for the exam. It was just these troublemakers that ruined the average.

176
00:23:19,000 --> 00:23:24,000
They busted the curve, as it were. Of course, that's something that we always love.

177
00:23:24,000 --> 00:23:31,000
Well, it could have been that bad because one person got 100, even though the rest of you got 20%.

178
00:23:31,000 --> 00:23:39,000
Of course, then they go out and slash the tires on my car. But you see what I'm saying? The median is actually useful.

179
00:23:39,000 --> 00:23:46,000
Now, fortunately, Excel, if you look at this data, now, if you're going to just visually look at the data,

180
00:23:46,000 --> 00:23:51,000
you would have to have them in order. But in Excel, it wouldn't matter whether they're in order or not.

181
00:23:51,000 --> 00:23:58,000
It'll get it for you. But if you look here, these are in order. The middle value is 5%.

182
00:23:58,000 --> 00:24:10,000
So I could just say equals median of that array, A2 through A6, and there it is.

183
00:24:10,000 --> 00:24:18,000
And that's, by the way, that's great. If you have 100 data points, finding the middle one is not that easy.

184
00:24:18,000 --> 00:24:25,000
Now, if it's an odd number of data points, then it's that middle value.

185
00:24:25,000 --> 00:24:32,000
If it's an even number of data points, you take the one right below the middle and the one right above the middle

186
00:24:32,000 --> 00:24:40,000
and take their average to get the median. But it is a really useful little thing.

187
00:24:40,000 --> 00:24:48,000
There. It's one that's worth it for us to know about.

188
00:24:48,000 --> 00:25:07,000
Now, the one that has to do with the risk, the classic one is the standard deviation.

189
00:25:07,000 --> 00:25:15,000
It measures how dispersed the data is. You see, this one is this lower one right here.

190
00:25:15,000 --> 00:25:24,000
This is a more spread out. There's less certainty in it because there are a lot of values above and a lot of values below.

191
00:25:24,000 --> 00:25:32,000
In this one, there is not nearly as much spread. So there's more certainty, less risk.

192
00:25:32,000 --> 00:25:45,000
So we use this one. Now, the formula for this one, just overall, the general formula is the sum from I equals 1 to N

193
00:25:45,000 --> 00:25:57,000
of each data point minus the average squared times the probability of that data point.

194
00:25:57,000 --> 00:26:03,000
There are a couple of homework problems where you'll do that one.

195
00:26:03,000 --> 00:26:14,000
More realistically, if you've got a ton of data, you'll just assume each probability is the same.

196
00:26:14,000 --> 00:26:42,000
And so this would reduce for a large data set to 1 over N minus 1 times the sum from I equals 1 to N of each X sub I minus the mean squared.

197
00:26:42,000 --> 00:26:53,000
A caution here. See that one right there? That one's the one you'll usually use, the one, just 1 over N.

198
00:26:53,000 --> 00:27:02,000
If it's a sample from some population, you use N minus 1 as that divisor.

199
00:27:02,000 --> 00:27:16,000
If it's the whole population, you've got every data point that could possibly exist, it would be N. That's very unusual in real life.

200
00:27:16,000 --> 00:27:28,000
You should know that because Excel will give you the chance to tell it whether you are using a population of data or a sample of data.

201
00:27:28,000 --> 00:27:34,000
And because sample is what we usually use, that's the default.

202
00:27:34,000 --> 00:27:41,000
But for this simple data set right here, the standard deviation, we'll have to do it the painful way.

203
00:27:41,000 --> 00:27:46,000
Now the first thing I'm going to do is this.

204
00:27:46,000 --> 00:28:04,000
I'm going to take each data point, for example, for the first one in cell C2, I'm going to take open parenthesis cell A2

205
00:28:04,000 --> 00:28:12,000
minus the average, the expected return, 5.15. That's cell B8.

206
00:28:12,000 --> 00:28:19,000
Now I'm going to hit F4 to make that an absolute reference on, so it's dollar B dollar 8.

207
00:28:19,000 --> 00:28:26,000
Because when I sweep it down, that will keep the 5.15 in place.

208
00:28:26,000 --> 00:28:33,000
Then close it, and then I'm going to raise, oops, close it.

209
00:28:33,000 --> 00:28:40,000
What? Close it? Is there some reason this sucks?

210
00:28:40,000 --> 00:28:50,000
Close it, and then raise it to the second power.

211
00:28:50,000 --> 00:29:03,000
Okay?

212
00:29:03,000 --> 00:29:12,000
Oh, I didn't do something here. Put in here square root.

213
00:29:12,000 --> 00:29:16,000
Forgot to take the square root of the formula.

214
00:29:16,000 --> 00:29:31,000
Okay, so now I'm going to latch on at C2, and I'm going to drag that formula down so that it does all of them.

215
00:29:31,000 --> 00:29:44,000
Expected return.

216
00:29:44,000 --> 00:29:48,000
No, I'm going to do it in the sum product this time.

217
00:29:48,000 --> 00:29:54,000
I did it the other way in the last class. I'll do it a little easier now that I've realized I could have done it easier.

218
00:29:54,000 --> 00:30:03,000
Watch this. The standard deviation will equal the SQRT, square root. You've got to do that part.

219
00:30:03,000 --> 00:30:20,000
Now watch. Sum product of this array, those XIs minus X bars.

220
00:30:20,000 --> 00:30:26,000
Huh, I added an extra one in there. I'll delete that in a second.

221
00:30:26,000 --> 00:30:33,000
Comma the probabilities.

222
00:30:33,000 --> 00:30:39,000
Close parenthesis, close parenthesis, divided by N minus 1.

223
00:30:39,000 --> 00:30:48,000
I could have done that, I can do that by count, but I'll just put 5 minus 1, 4.

224
00:30:48,000 --> 00:31:01,000
I did it again, didn't I?

225
00:31:01,000 --> 00:31:08,000
What am I doing wrong here?

226
00:31:08,000 --> 00:31:17,000
Hmm. I'll have to look at that. I may have to change that later.

227
00:31:17,000 --> 00:31:23,000
I don't like that. Let me get rid of this first of all.

228
00:31:23,000 --> 00:31:31,000
Each of these, A2 minus the data point minus the average squared.

229
00:31:31,000 --> 00:31:41,000
Data point minus the average squared. Data point minus the average squared. Data point minus the average, data point minus the average.

230
00:31:41,000 --> 00:31:54,000
And then I add those up. This, oh, I didn't, I'm using the wrong one.

231
00:31:54,000 --> 00:32:15,000
Let me delete that, try that again. Equals SQRT, open parenthesis, sum product of

232
00:32:15,000 --> 00:32:28,000
that array, probabilities, by that array.

233
00:32:28,000 --> 00:32:36,000
Close off the square root, and then divide by N minus 1, which is 4.

234
00:32:36,000 --> 00:32:43,000
Hmm. That number doesn't look right, but we'll take it for what it's worth.

235
00:32:43,000 --> 00:32:53,000
I may have to fix that later, but it looks right. Don't know why it's not what I got the last time, but anyway.

236
00:32:53,000 --> 00:33:03,000
There's one last measure of risk. It's called the coefficient of variation, the CV.

237
00:33:03,000 --> 00:33:13,000
You take the risk divided by the average. This is better than the standard deviation on its own.

238
00:33:13,000 --> 00:33:25,000
Here's why. The standard deviation is completely dependent upon the size of the numbers you put in.

239
00:33:25,000 --> 00:33:35,000
So if I'm looking at returns on a penny stock versus returns on a ginormous S&P 500 company,

240
00:33:35,000 --> 00:33:44,000
the numbers are on completely different scales, which means that their standard deviations couldn't be compared.

241
00:33:44,000 --> 00:34:05,000
The CV takes that out of the equation, takes that out of the analysis. So in this case, I would take the standard deviation divided by the average.

242
00:34:05,000 --> 00:34:17,000
And now I could compare any two companies regardless of the scale of the numbers that were involved in them.

243
00:34:17,000 --> 00:34:29,000
I don't know in your statistics class, you took that management class, if they showed you CV, but it's sort of like the median.

244
00:34:29,000 --> 00:34:42,000
Everyone looks at, expect the average and the standard deviation, when really you should look at the median and the coefficient of variation.

245
00:34:42,000 --> 00:34:53,000
Those would be much, much more explanatory.

246
00:34:53,000 --> 00:35:12,000
Now, let me do something here. I'm going to show you how to get data.

247
00:35:12,000 --> 00:35:35,000
I'm going to take you to a site called onestockone.com. I'll try to get it straight. I may have to Google to get it.

248
00:35:35,000 --> 00:35:54,000
There it is. Here's why you should know about this. Onestockone.com

249
00:35:54,000 --> 00:36:07,000
will give you a data set of returns for, since 1975, yearly returns, since 1975.

250
00:36:07,000 --> 00:36:17,000
You can get the return to the Dow, Standard Port is 500, the NASDAQ, well look at, here, let me show you one, the Dow.

251
00:36:17,000 --> 00:36:28,000
Will you quit it? This is like gold, because data sets you have to pay for data anymore.

252
00:36:28,000 --> 00:36:38,000
This site's been around for years and years and it just offers this, but it's not just like the Dow or the S&P 500 and NASDAQ.

253
00:36:38,000 --> 00:36:49,000
You get the yearly returns for any market you could imagine in the whole world. The London Exchange, the Singapore Exchange,

254
00:36:49,000 --> 00:37:02,000
places that you couldn't even think about what they're talking about. And these are all available to you.

255
00:37:02,000 --> 00:37:14,000
Nikkei, some of these places like the Swiss Bourse, just about anything you would want to imagine. New Zealand even, for heaven's sake.

256
00:37:14,000 --> 00:37:28,000
But even more than that, you could get stocks. Their yearly returns for, you go by the first letter, like I might want to find IBM.

257
00:37:28,000 --> 00:37:40,000
Just click on the i-stocks and go find IBM. You'll get its yearly returns. It's a gold mine. It's not vast, day to day or anything like that.

258
00:37:40,000 --> 00:37:54,000
But still, you can't beat it if you just want a good, decent, reliable data set. So what I do is I'm going to collect the yearly returns

259
00:37:54,000 --> 00:38:07,000
for the Dow Jones Industrials and also for the S&P 500 and for the NASDAQ. Here's how you do it in Excel.

260
00:38:07,000 --> 00:38:22,000
And I'll do this a few times. Nope, that's not the one I wanted. Okay. I'm going to take a data sheet, sheet one. Let's take sheet two.

261
00:38:22,000 --> 00:38:35,000
The first one I'm going to grab will be the Dow. I go over to stock, one stock one, and I click on the Dow Jones Industrial Average.

262
00:38:35,000 --> 00:38:50,000
This is its data set. First thing I'm going to do is grab the web address, the URL. Control C on it so that I've copied it.

263
00:38:50,000 --> 00:39:08,000
Now we're going to go back into Excel. Data. On the ribbon, top, choose data. And then over on the far left, get data.

264
00:39:08,000 --> 00:39:25,000
Go down to from other sources, and I will get from the web. And now you'll come up, it'll ask you for that web address.

265
00:39:25,000 --> 00:39:43,000
Control V, there it is. And I'll say okay. Now it's going to connect to that web page.

266
00:39:43,000 --> 00:39:55,000
Now oftentimes web pages have several tables. Many websites create their top bar, choose drop down menu. They do that as a table.

267
00:39:55,000 --> 00:40:14,000
So it's going to see a couple of tables. Table one, see that's the table we want. We say load it. And there's all that data right there for you.

268
00:40:14,000 --> 00:40:34,000
So I'll give this one the name Dow 30. Now I'm going to go to sheet two. And I'm going to back up one in one stock one dot com and click on the S&P 500.

269
00:40:34,000 --> 00:40:49,000
And there it is. And I'll click on the top bar, the address bar, Control C to copy it. I'm going to do the same thing that I just did before.

270
00:40:49,000 --> 00:41:06,000
I'm going to say data, go into Excel, in this new sheet, data, get data from other sources from the web. And I'll give it that web address and say okay.

271
00:41:06,000 --> 00:41:24,000
And it will go in there, look around, see what if it's table two. Well yes it is. Table one, I'm sorry. Load. There's all that data in your Excel sheet.

272
00:41:24,000 --> 00:41:44,000
And I'll call that worksheet S&P 500. And now I'll open up a new worksheet and we'll do the same thing for the NASDAQ.

273
00:41:44,000 --> 00:41:58,000
Go back to one stock one dot com, back up and click on NASDAQ. Go up to the address bar and Control C to copy.

274
00:41:58,000 --> 00:42:18,000
Then come back over here to Excel in this new worksheet, data, get data from other sources from the web. Control V, there's the web address of that data set.

275
00:42:18,000 --> 00:42:43,000
Say okay. Table one, there it is. Load it. And look at that. And I'll call that worksheet NASDAQ.

276
00:42:43,000 --> 00:42:50,000
And now I've got all these. The one I care about is the last column in these, the return.

277
00:42:50,000 --> 00:42:59,000
So I can go down here. Let me make these three sheets a little bigger so you can see it a little better.

278
00:42:59,000 --> 00:43:14,000
So now I'm going to go down here to the Dow. I'm going to say mean, median, standard deviation.

279
00:43:14,000 --> 00:43:25,000
Now this is the one, STDE standard deviation. I'll have some of this here. And the coefficient of variation.

280
00:43:25,000 --> 00:43:44,000
So now the mean equals average. And I just highlight that whole data set there from E2 down to E, what is it, E50?

281
00:43:44,000 --> 00:43:56,000
There's the average. I'll turn that, these first three are percentages. The median equals median.

282
00:43:56,000 --> 00:44:06,000
And I take that whole data set, drag it down.

283
00:44:06,000 --> 00:44:16,000
Standard deviation equals STDEV. Leave it like that. That will give you the sample standard deviation.

284
00:44:16,000 --> 00:44:27,000
Open parenthesis. Drag it down.

285
00:44:27,000 --> 00:44:38,000
And then the coefficient of variation is just equal to the standard deviation divided by the mean.

286
00:44:38,000 --> 00:44:42,000
There you go.

287
00:44:42,000 --> 00:44:49,000
Now these first three are percentages. And I'll set them to two decimal places.

288
00:44:49,000 --> 00:44:58,000
Now the coefficient of variation is a pure number. So you just highlight it and then hit the comma up in the ribbon.

289
00:44:58,000 --> 00:45:04,000
And it will do it as a pure number.

290
00:45:04,000 --> 00:45:10,000
And I can do that for all three of the sheets. And I'll leave you to be able to do this.

291
00:45:10,000 --> 00:45:16,000
You've got the model and you've got the data. I encourage you to give it a try to grab data.

292
00:45:16,000 --> 00:45:22,000
Once you get the hang of it, it's really easy. And you can even make it so that it's dynamic.

293
00:45:22,000 --> 00:45:27,000
So every time you open the sheet, the most recent data will show up.

294
00:45:27,000 --> 00:45:32,000
So that's kind of a nice little feature of it.

295
00:45:32,000 --> 00:45:39,000
Anyway, as far as anything else goes, we'll pick the subject back on Monday.

296
00:45:39,000 --> 00:45:47,000
You have a quiz to take. And when you're finished with that quiz, that's all I have for you today.

297
00:45:47,000 --> 00:46:10,000
I thank you.