Forecasting Trend and Seasonality

Using dummy variables and multiple linear regression to forecast trend and seasonality

---

Closed Caption:

so the final models that we're going to
look at within time series forecasting
are seasonality and trend i'm going to
go ahead and just jump straight to one
that has both trend and seasonality in
it
here we have terry's tire shop we've
already determined that they have three
seasons a Christmas season like in
november december father's day I guess
that's what into me
june and in all other times so
everything else is coming together
notice the seasons don't have to be the
same length and time average weekly
sales and dollars during each of the
three seasons during the past four years
are shown below so year one in during
christmas is he sold 1856 during
father's day 2012 all their x 95 so on
the surface at least it looks like
season two is the heavy s season one
comes in close behind and then see the
rest of the year relatively slow our job
is to determine forecast for the average
weekly sales in year five for each of
the three seasons so we need three
numbers so what we're going to do this
is we're going to create dummy variables
also called indicator variables there
are one or zero they represent the
seasons we need s minus one dummy
variables where s is the number of
seasons we have three seasons so we will
need to dummy variables will also need
to capture the trend we also need a time
. index so the first thing I need to do
with the data shown in this format
doesn't help me much I need each record
on a row right setting 1856 on a robot
itself 2012 on a robot itself 95 etc so
let me do the following my hope up
another sheet and let's call this year
season sales time . index and then
whether we have season 1 season 2
you write or season 3 i'll put that in
there but we'll talk about that a little
bit so what I need to do I have won 11
seasons I have one two three
I got this for years right and then to
22 33 44 and then I need to copy over
the data so sales are going to copy this
guy right click paste special where I do
transpose and then you need to do that
every time for x so not particularly fun
but not particularly difficult guys are
fighters and typing I think and then one
more time for a year for and then it
wouldn't hurt to double-check to make
sure they're in the right spot time .
index or just 12 all the way down to 12
so then in order to do these dummy
variables
there's a one if it's in the first
season 0 if it's not so in season two is
can't be ensnared they have to be
mutually exclusive and collectively
exhaustive so you get a 1000 1000 not
9001 so you should see a pattern
emerging immediately
alright alright so now we're set up we
can run the multiple linear regression
using the data analysis toolpak in excel
so this is tys data rearranged right
where this is our dependent variable and
these are independent variables so the
first thing i said we only needed to i
put the third one in here trying to show
you what happens when you mess up if you
put all three and it doesn't like it
unless you do something special so let
me show you what the mistake first so
first I need to go to data over to data
analysis and in regression because
that's what we had picked out before for
one of the other ones in ahead and
restart excel yet so click OK and it
thinks I'm still back doing the we're
checked for stationary one so my why
range what I'm trying to predict this
sales so see input range i'm going to
all of these D through G alright have
labels output range only pick something
higher so we can just kind of see what
happens and then when i click ok i
should get an error message I don't get
an error message but I do get some funny
stuff down here it says not not a number
00 so that's not good if you get this
this is bad
the reason is i can't put all the dummy
variables and at the same time unless i
sent to intercept 0
so let's go back and do it properly
going to come down here and just delete
those guys and then i'm going to do data
analysis regression and here i just want
time . index s1 s2 that means that my
bass season is everything else that's
Milo season because it's my bases and
it's my lowest value i would expect s1
and s2 have coefficients that are
positive numbers so i click ok and i see
wow I get a really high R squared
adjusted r-square like that and then I
come down here and I see that the
intercept 277 ties each time . it i'm
adding 36 and a half more almost season
1 i'm adding a thousand ninety five more
season to 1189 more we notice that we do
have all small p values so that's a good
thing
so overall significance of the model is
very small is also very important very
small p-value so that makes me think I
have a good model
alright so now i need to do the
forecasting for the next time . so 13 14
15 and that's it right three three
seasons so this will be a copy this
right so my forecast for this guy will
be the intercept which was the 797 plus
the time . let me go back here and then
we anchor that plus the time .
coefficient thirty six and two-thirds
for not quite two thirds times time . x
plus the s-1 coefficient anchor that
multiplied times that's one time the
indicator variable there plus thats to
anchor that times the indicator variable
for that
NSE 1st season one the christmas season
for your five I got twenty-three
sixty-six for Father's Day 2497 and for
all other times 1344 so those are my
forecast for the fifth year now there's
another way we could do it but I'm going
out to talk about that in class instead
of putting it on the video so that is
both trend and seasonality I can see the
trend because it's small p-value so it
tells me that there is a trend both of
these have small p-value that tells me
that there is seasonality they are
statistically significant lead different
from the third and season there so that
covers pretty much everything we're
going to do in forecasting