Excel - Time Series Forecasting - Part 1 of 3

Part 2: http://www.youtube.com/watch?v=5C012e...
Part 3: http://www.youtube.com/watch?v=kcfiu-...

This is Part 1 of a 3 part "Time Series Forecasting in Excel" video lecture. Be sure to watch Parts 2 and 3 upon completing Part 1. The links for 2 and 3 are in the video as well as above.

---

Closed Caption:

hi guys i'm going to show you how to
deal with time series data in excel so
we're going to jump right into an
example
this is the quarterly sales data for car
sales
ok so and its quarterly because these
sales which is what we're measuring here
is recorded
once every quarter so we also have this
column here which is the year so in year
one quarter one sales were 4.8 and
that's measured in thousands so that
means 4800 units in year one quarter to
sales were 4.1 or four thousand one
hundred and finally so we understand
this data clearly in year four quarter
three
for example sales were eight thousand
units recorded as eight . oh ok
so this right here comprises the
historical time series data
ok and what we are measuring here
specifically is sales and the goal of
all this is that we want to be able to
understand how sales moves through time
and then be able to project that or
extrapolate that into the future into
perhaps year five
ok so by the end of this video we should
be able to make predictions for these
blank cells over here quarters one two
three and four
ok so let's approach this in a
step-by-step fashion so there's going to
be a lot of columns we're going to have
to create plots and little steps along
the way but the great thing is you can
always pause the video and go back and
see what I did
okay so step one is to just visualize
the data
so for this step we're just going to
create a line chart with markers
so let's do that we go to insert
line and we'll choose line with markers
ok an Excel sometimes just goes ahead
and suggest some things but we can clear
that out by going to design
select data remove everything so we
start with a blank slate
now we're going to add the name of the
series is sales
ok that's the data that we want to
understand and be able to eventually
predict
okay and here's the actual historical
data
hit OK and already you can see that
there's this really cool kind of up and
down cycling going on here
ok we're going to talk about that in a
second but before we finish this plot
let's replace this horizontal axis with
the actual years and quarters so we can
always go to exact a time . that were
interested in for example if we want to
look at year three quarter - we right
now we wouldn't we would have to do a
little bit too much thinking to find
that ok so let's fix that axis we go
edit and we can highlight
both these so if you have two columns
like this that are connected that tell
you the time you can highlight both of
them
ok it ok okay and we're done
maybe we give this a title time series
plot
of car sales
ok that's a decent name for this
ok let's make that a little smaller
ok and legends going to be useful
because as you're going to see we're
going to keep adding plots on top of
this one
ok series on top of this one alright so
let's move this to the side so that's
step one
now we take a look at this that we what
we've created here and let me make it a
little bigger actually so that we can
start understanding what we're looking
at we can clearly see that there's this
pattern that kind of repeats itself
more or less every year right
so within a year one which is between
these two lines i'm drawing
there's this kind of dip and then climax
then in year 2 there starts a tip and
then a climax
again a tip here three climax damp
climates there seems to be a cycle or
what we call seasonality because it
this cycle occurs within one year write
this down then up cycle repeats itself
once a year we call this seasonality
ok so that's one component that we can
visualize from this plot the other thing
we can visualize is that this if you
take a more zoomed out kind of
perspective
fuzzy perspective take off your glasses
if you will
you'll notice besides this up-and-down
movement that we just talked about the
overall direction of this plot is
somewhat increasing right can be
summarized maybe with this very simple
straight line
ok so that's that what we call the trend
component
ok so so far we talked about the
seasonal component which you can see the
trend component but you can also see
with a slightly different perspective
and then the final component in the
model that we are using here is going to
be the irregular component which means
the day to day or corner to corner
variation that exists
that's kind of beyond explanation and
doesn't follow any pattern the irregular
aspect they call it sometimes they call
it the random aspect ok and that's
always present in data no matter whether
it's time series data or not
ok so we have to account for variability
because nothing follows in the real
world nothing falls in the exact kind of
perfect up and down predictable pattern
even though there is a discernible
pattern
there's going to be a slight variations
from time period to time .
ok so those are three components that we
are going to have to deal with in the
time series analysis
ok so now let's put this chart aside for
now and go to our second step
ok so our next step in in this analysis
is to create a column which is just the
kind of a time code column
ok so let's insert that in front of the
time series data and we'll just call
this tea
ok
let's just Center this and all its going
to be is a timecode so the first period
that we have data on which is year one
quarter one will call one and then we'll
just increment this up holding down the
ctrl button all the way to the last .
that we have data for me so we have in
total 16 periods
ok
we're going to come back to this
variable quite a bit it's important to
create it early on
all right the next step is to smooth out
this is where we actually are getting
into the analysis and manipulation of
the time series data so as you all agree
there's this pattern going on
this pattern that I draw drew in red is
the seasonality component of the time
series data and it also includes the
irregularity
ok so in this step we want to smooth
okay and then we put this word smooth as
our shows in quotes because what we are
attempting to do in this step which is
going to be in this column is to kind of
put it
ironing ironing on this data you will
too kind of flatten out all the up and
down and try to smooth out the line
okay to do this one technique and the
technique we're going to use here is to
take a moving average
ok so and because our cycle lasts for
periods because I as we discussed it
dips and then climax is tips and climax
is the climax is once every year
so one cycle is for . four quarters
right so we can take it
moving average of four periods
ok and the way we do that is you can go
down to about the third row and you're
just going to use the average function
here and you're going to average
starting with the top tube with the top
values you're going to go down an
average the first four numbers
okay from your time series ok then you
can drag this down and you can drag it
down all the way and you'll see that
we're going to have to delete some of
these values at the end
basically we want to make sure that each
one of these calculations includes four
numbers
ok
so obviously the first one is good
the next one drops you see it dropped to
the next four numbers and this pattern
continues all the way down
and if we go to the last one will see
that the last one doesn't catch for
numbers because of this blank so so we
don't want this
ok so let's just delete this one the one
right above it does catch for numbers so
it's perfect
ok so this is the moving average of four
periods
right now the next step is connected to
this step we can because we're taking a
moving average of an even number of
periods we have this kind of problem
where this 5.4 actually represents the
center between these four numbers right
right because it averaged these four
numbers
and so it should actually be written
over here
5.4 which is between the two rows right
and then this pattern continues on and
on for the next four right
so this 5.6 should actually be written
in between these two roles because it
represents 5.6 represents the center of
the next four numbers
okay so get this
these numbers that we have here are not
centered because they fall in between
the numbers that they're averaging
that's always going to happen when you
average an even number of numbers
ok in a time series data so what we have
to do is this next step which is
centering it's called centered moving
average
ok so if you had done and on number here
you wouldn't have to do this extra step
that we're gonna we're about to do
because you would already have a center
moving average acma centered moving out
so the idea here is that if we average
this number and this number then we're
going to end up back into the centre
back into a role that we can associate
with a value
ok so let me race these lines and draw
one more time for the first one
what we did in MA for words we average
for these four numbers so the average of
these four numbers represents the center
of these four so it should be placed in
the middle of this role in other words
it doesn't fall into this cell or into
this cell and so we have to kind of put
it somewhere
excel doesn't let us go in between cells
so but really it's over here and then
the 5.6 is the average of the next four
numbers these four and it falls in
between these two rows
ok i'm doing this just for the first two
so that you can understand the concept
of century
so the idea is these to fall into no
man's land
I can't connect 5.4 - 22 or 23 because
it falls in between two and three right
and 5.6 falls in between three and four
so I have to get these back into a row
so i can connect them back to these
numbers
so the way you do that is you average
these two and the center of two off
centers falls back into the center and
we'll be back into a proper row this
number will be associated with the nut
growth year one quarter three or time
code three
ok so that's the idea behind center so
the way to do that equals average again
and this time you're just going to
average the two numbers
ok and then you could drag this down
and you'll be able to drag it down all
the way and then just test whether two
numbers were averaged this one obviously
has a blanket so we don't want this
ok so one right above
perfect so in effect we've sent we've
created a centered moving average in
this step
ok and now these numbers are useful for
us because they're connected properly to
our row
ok so now i can plot this stuff
the center moving average right on top
of this blood
ok so this step member was the point of
these two columns was to smooth the time
series data to get rid of the
seasonality and irregularity components
ok so let's plot this so in order to
plot this let's pull the plot back up
let's click design select data
and
and the name is going to be centered
moving average the values are going to
be these values right but be careful if
you just highlight these numbers excels
going to start 5.5 here
ok at you one quarter one when we know
that 5.5 we did all that work is
connected to a year one quarter three
in other words we don't have anything
center moving average numbers for year
one corner 1 & u 1 quarter - okay so you
should highlight the blanks
likewise you should highlight the last
two blanks because we want excel to plot
it
and in the right place so we don't have
values for a year for quarter 3 and year
for quarter of four
so why don't we tell Excel that ok
it ok it ok and as you can see it plots
the smooth out centered moving average
of four periods
right on top of the original time series
data which is in blue
ok so at this point I'm going to stop
this video and i'm going to call this
video a and i'm going to create video be
so that you can we can continue with
this analysis
we're about thirty three percent a third
way to to forecasting so be sure to
watch video be okay
see you next time