馃攳
Basic Excel Business Analytics #55: Summary of 4 Basic Forecasting Methods & Mean Square Error - YouTube
Channel: unknown
[0]
welcome to Highline bi 348 class video
[2]
number 55 if you want to download this
[5]
workbook the i3 before you eat chapter 5
[7]
and follow along click on the link below
[9]
the video now last video we talked about
[12]
naive average all pass values moving
[15]
average and exponential smoothing for
[17]
making forecast in this video we're
[19]
going to do a quick example of each one
[21]
of these as a summary on a different
[24]
data set in this data set has what looks
[26]
like a horizontal pattern and then a
[28]
jump because there was a new contract
[31]
sign so this data set is different than
[34]
the one we had last time so let's go
[37]
ahead and remind ourselves naive just
[39]
means hey we're always gonna look at the
[41]
actual value from the previous period
[44]
let me copy that down we can come all
[48]
the way down to the bottom because I'm
[49]
gonna forecast now watch this it
[52]
replaced the formatting so I'm going to
[53]
click on the smart tag and say fill
[56]
without formatting so then the forecast
[59]
becomes hey I'm looking at the previous
[63]
period now we want to measure the
[65]
forecast error here to gauge how
[68]
accurate this method is so we're gonna
[70]
use our array formula for calculating
[73]
mean square error sum product and we
[77]
need to take the actual values not all
[81]
the values just the same number of
[83]
values as we have forecasts and what do
[87]
we want to do we want to subtract the
[89]
forecast now this array operation one
[93]
column minus another column if I were to
[96]
highlight this and hit the f9 key that
[99]
gives me all the actual forecast air
[102]
control Z in order to get mean square
[106]
error I have to of course close
[108]
parentheses and square it the sum of it
[112]
then needs to be divided by and I'm
[115]
going to use n minus K but I'm an Iskra
[117]
an account because n minus K is really
[120]
just the number of forecasts now I'm
[123]
going to sort it sheet here I'm going to
[125]
close parentheses and I'm going to
[127]
notice that I want to calculate the same
[129]
mean square error for each one of these
[133]
this blue range the actual values I need
[136]
to lock that so I'm very carefully gonna
[139]
highlight the b-52 b27 and hit the f4
[141]
key the rest of these I'm gonna leave
[144]
relative cell reference so as I copy it
[147]
over the orange will move to the new
[149]
forecast method now when it gets to this
[152]
column we're gonna have a slight problem
[153]
but instead of recreating the whole
[156]
formula down here I'm just gonna adjust
[158]
the arranges now I'm just gonna enter it
[161]
right now and leave it there all right
[163]
that is the mean square error for our
[166]
naive or forecast method now averaging
[169]
all past values equals average and we
[173]
use an expandable range so I click in
[176]
only the first actual value I type a
[179]
colon close parentheses and then I lock
[183]
just the first cell reference so and I
[185]
hit f4 that locks down that for but not
[189]
the second for in the range so that 4 is
[192]
allowed to move to 5 6 7 as we copy it
[195]
down control enter it gives us what's
[198]
called an expandable range at any
[200]
particular cell I can hit f2 and see
[204]
that it has expanded the 4 is locked but
[207]
that number in the second B cell
[209]
reference is not locked all the way down
[212]
to the bottom that is just beautiful and
[214]
expanding range now I'm actually going
[217]
to highlight this this is super annoying
[219]
that is called an error checking and
[222]
it's not smart enough to know this this
[224]
formula is working
[226]
it says omits an adjacent cell which is
[230]
the one at the top forget it ignore
[232]
excels not smart enough most of the time
[234]
to check for errors
[235]
now I actually forgot let me copy this
[237]
one down here and point to fill without
[240]
formatting so there it is there's the
[242]
forecast now I'm gonna copy this over
[245]
hit f2 and notice we have the exact same
[249]
number of for caspere's so this formula
[252]
works perfect for our second method of
[255]
MSE so far I'm thinking I like the first
[259]
method better now we mentioned this in
[262]
last video if we do have a jump naive
[264]
forecast picks it up quick
[266]
right the averaging of the past values
[269]
is not gonna pick it up quickly because
[272]
it's still hanging on to all of it the
[274]
values if I look right here this methods
[277]
still hanging on to all of the values
[279]
from the earlier part of this time
[282]
series not the later part the most
[285]
recent catches it however there will be
[287]
a better method than most recent well at
[289]
least we hope when we do our mean square
[291]
error now moving average we're going to
[295]
do a moving average of three equals
[297]
average and this just means I'm always
[301]
going to look at only the past three
[304]
values now conceptually if you're
[307]
looking here right if we're over here
[309]
it's already looking at that value that
[312]
value that of a so it's starting to pick
[314]
up the jump more quickly by only
[318]
averaging three instead of all of the
[321]
pass values now those are relative cell
[323]
references so control-enter when I copy
[325]
it down all the way down to the bottom
[327]
and I'm going to point to the smart tag
[330]
and say fill without formatting it's got
[333]
the same problem Excel error checking is
[335]
not smart enough you want to know
[337]
something what do you really do with
[339]
error checking you say error checking
[341]
options and then down here you say
[344]
uncheck enable back row and error
[347]
checking and click ok you have to do
[349]
that not for the particular workbook but
[351]
for your particular computer whether
[353]
it's at work or at home or whatever
[355]
alright so we have an actual forecast of
[360]
691 for period January 2016 now when I
[364]
copy this formula over I am gonna have a
[366]
problem f2 but notice we know how to
[369]
deal with it it's got everything correct
[371]
it's just a couple cells off so I'm
[373]
gonna point to the corner when I see my
[375]
diagonal cursor I'm gonna click and drag
[378]
diagonal cursor click and drag and then
[381]
I come over and click and drag so I want
[385]
exactly the same boom boom they're
[387]
parallel control enter and that's so far
[390]
is definitely looking like the smallest
[392]
mean square error now we still have one
[396]
last method exponential smoothing for
[398]
our time
[400]
you we're just gonna look at the first
[401]
one enter and then from that point
[404]
forward we're gonna wait we're gonna
[406]
look at this is time three two three so
[409]
I'm looking at the actual value for time
[411]
period to relative cell reference times
[413]
the smoothing factor that's custom
[417]
number formatting there that's actually
[418]
a number in the cell point four which
[420]
will work perfect f4 to lock it so we're
[424]
waiting this value at forty percent plus
[427]
the previous forecast relative cell
[431]
reference times in parentheses 1 minus F
[435]
4 close parentheses that's times the
[439]
compliment control enter and I'm gonna
[442]
let's see what happens if I double click
[443]
and send it down and then I'm gonna go
[445]
down to the smart tag and say fill
[446]
without formatting so we have forecast
[449]
for January 2016 of 700 now I'm actually
[453]
gonna cheat I'm gonna copy this one and
[455]
then paste this one right here because
[457]
it has the right range f2 I can see
[460]
there it's working perfect and sure
[462]
enough now we can compare for this
[464]
particular data set then we have minimum
[468]
mean square error for our exponential
[471]
smoothing alright so in this video we
[475]
reviewed naive average all the past
[477]
moving average and exponential smoothing
[479]
and looked at mean square error to pick
[482]
our forecast method now when we come
[484]
back in our next video we're going to
[487]
talk about using linear regression for
[490]
forecasting all right we'll see you next
[492]
video
Most Recent Videos:
You can go back to the homepage right here: Homepage