Welcome to Highline BI348 class video number 55.
If you want to download this workbook, BI348 Chapter 5
and follow along, click on the link below the video.
Now, last video, we talked about naive average
all past values, moving average, and exponential smoothing
for making forecasts.
In this video, we're going to do a quick example
of each one of these as a summary
on a different data set.
And this data set has what looks like a horizontal pattern
and then a jump, because there was a new contract signed.
So this data set is different than the one
we had one last time.
So let's go ahead and reminder ourselves-- naive just means,
hey, we're always going to look at the actual value
from the previous period.
Let me copy that down.
We can come all the way down to the bottom,
because I'm going to forecast.
Now watch this.
It replaced the formatting, so I'm
going to click on the smart tag and say
Fill Without Formatting.
So then the forecast becomes, hey, I'm
looking at the previous period.
Now we want to measure the forecast error here
to gauge how accurate this method is,
so we're going to use our array formula for calculating
mean square error sum product.
And we need to take the actual value--
not all the values, just the same number of values
as we have forecasts.
Now what do we want to do?
We want to subtract the forecast.
Now, this array operation, one column minus another column-
if I were to highlight this and hit the F9 key,
that gives me all the actual forecast error.
Control Z. In order to get mean squared error, I have to,
of course, close parentheses and square it.
The sum of it then needs to be divided by--
and I'm going to use n minus k, but I'm just going to count,
because n minus k is really just the number of forecasts.
Now I'm going to sort of cheat here.
I'm going to close parentheses, and I'm
going to notice that I want to calculate
the same mean square error for each one of these.
But this blue range, the actual values, I need to lock that.
So I'm very carefully going to highlight
the B5 to B27 hit the F4 key.
The rest of these, I'm going to leave relative cell reference
so as I copy it over, the orange will move to the new forecast
method.
Now, when it gets to this column,
we're going to have a slight problem.
But instead of recreating the whole formula down here,
I'm just going to adjust the ranges.
Now I'm just going to enter it right now and leave it there.
That is the mean square error for our naive or forecast
method.
Now averaging, all past value equals average,
and we use an expandable range.
So I click in only the first actual value.
I type a colon, close parentheses, and then
I lock just the first cell reference.
So when I hit F4, that locks down that four, but not
the second four in the range.
So that 4 is allowed to move to 5, 6, 7 as we copy it down.
Control Enter.
It gives us what's called an expandable range.
At any particular cell, I can hit F2
and see that it has expanded.
The 4 is locked, but that number in the second B cell reference
is not locked.
All the way down to the bottom-- that is just beautiful--
an expanding range.
Now I'm actually going to highlight this.
This is a super annoying.
That is called an error checking,
and it's not smart enough to know this formula is working.
It says omits and an adjacent cell,
which is the one at the top.
Forget it.
Ignore.
Excel is not smart enough, most of the time,
to check for errors.
Now, I actually forgot.
Let me copy this one down here and point to Fill
Without Formatting.
So there it is.
There's the forecast.
Now I'm going to copy this over.
Hit F2, and notice we have the exact same number of forecasts.
So this formula works perfect for our second method of MSE.
So far, I'm thinking I like the first method better.
Now, we mentioned this in the last video--
if we do have a jump, Naive Forecast picks it up quickly.
The averaging of the past values is not
going to pick it up quickly, because it's still hanging
on to all of the values.
If I look right here, this method
is still hanging on to all of the values
from the earlier part of this time series, not
the later part.
The most recent catches it.
However, there will be a better method than most recent-- well,
at least we hope when we do our mean square error.
Now, moving average-- we're going
to do a moving average of 3 equals average.
And this just means I'm always going to look
at only the past three values.
Now, conceptually, if you're looking here--
if we're over here, it's already looking at that value,
that value, and that value.
So it's starting to pick up the jump
more quickly by only averaging 3 instead
of all of the past values.
Now, those are relative cell references, so Control Enter.
When I copy it down all the way down to the bottom,
then I'm going to point to the Smart tag
and say fill without formatting.
It's got the same problem, Excel error checking
is not smart enough.
You want to know something?
What do you really do with error checking?
You say error checking options, and then
down here, you say uncheck Enable Background Error
Checking and click OK.
You have to do that not for the particular workbook,
but for your particular computer,
whether it's at work or at home or whatever.
So we have an actual forecast of 691 for period January, 2016.
Now when I copy this formula over,
I am going to have a problem-- F2.
But notice we know how to deal with it.
It's got everything correct.
It's just a couple cells off.
So I'm going to point to the corner.
When I see my diagonal cursor, I'm going to click and drag.
Diagonal cursor click and drag, and then
I come over and click and drag.
So I want exactly the same-- boom, boom,
they're parallel Control Enter.
And that, so far, is definitely looking like the smallest
mean square error.
Now, we still have one last method-- exponential
smoothing for our time 2.
We're just going to look at the first one, Enter.
And then from that point forward, we're going to wait.
This is time period 3, so I'm looking at the actual value
for time period 2, relative cell reference times the smoothing
factor.
That's custom number formatting there.
That's actually a number in the cell point
4, which will work perfect.
F4 to lock it.
So we're weighting this value at 40%
plus the previous forecast relative cell reference times,
in parentheses, 1 minus F4 close parentheses.
That's times the complement.
Control Enter.
And let's see what happens if I double click and send it down.
And then I'm going to go down to the smart tag
and say fill without formatting.
So we have forecast for January, 2016 of 700.
Now I'm actually going to cheat.
I'm going to copy this one and then paste this one right here,
because it has the right range.
F2.
I can see there it's working perfect.
And sure enough, now we can compare
for this particular data set.
Then we have minimum mean square error
for our exponential smoothing.
So in this video, we reviewed naive,
average all the past moving average and exponential
smoothing and looked at mean squared error
to pick our forecast method.
Now, when we come back in our next video,
we're going to talk about using linear regression
for forecasting.
All right.
We'll see you next video.