Basic Excel Business Analytics #56: Forecasting with Linear Regression: Trend & Seasonal Pattern

Download file from “Highline BI 348 Class” section: https://people.highline.edu/mgirvin/excelisfun.htm
Learn:
1) (00:11) Forecasting using Regression when we see a trend and belief the trend will extend into the future. Will will predict outside the Experimental Region with the Assumption is that trend continues into future.
2) (00:53) Forecast a Trend using Simple Liner Regression. We use the Data Analysis Regression Feature.
3) (03:22) Learn how to use FORECAST function.
4) (08:57) Forecast a Seasonal Pattern using Multiple Regression and three Categorical Variables for quarter using Multiple Linear Regression. We use the Data Analysis Regression Feature.
5) (12:12) VLOOKUP & MATCH functions with Mixed Cell References to populate new categorical variable columns with the Boolean ones and zeroes.
6) (19:53) Forecast a Trend with a Seasonal Pattern using Multiple Regression and three Categorical Variables for quarter and one quantitative variable using Multiple Linear Regression. We use the Data Analysis Regression Feature.
7)

Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.

---

Closed Caption:


Welcome to Highline BI348
class video number 56.
OK, if you want to download
this workbook BI348-Chapter05
and follow along, click on
the link below the video.
Hey, we're talking
chapter five, we're
talking about forecasting.
Last couple of videos, we've
seen some different methods
for forecasting.
But if we plot a time series,
time variable and some values,
and we see what
looks like a trend,
and we believe that this trend
will extend into the future,
we can use regression
to make a forecast.
Now our last chapter, chapter
4, when we studied regression,
we said, make sure when
you make a prediction
to keep within the min
and max of the x values.
But this is different.
We have a time series
and our assumption
is that this trend will
continue into the future.
Now here's our data set.
We have months and sales.
Now we have learned
various methods
to calculate slope and
intercept, to create
our linear regression equation.
But I think this time we will
use Data and Data Analysis
feature, and then, Regression.
Click OK.
Now our y input, there's
going to be our sales.
Our x, that's our time variable.
I call it x, the
textbook calls it t.
That's fine.
It's an independent variable.
We definitely have
labels at the top.
Confidence interval,
we can change it.
We're not going to do
any hypothesis testing
like we did last chapter.
But we'll spit out the
confidence interval, anyway.
I'm going to say
Residuals, Residual Plots.
We're not going to
use those either.
But you can look at
them, if you want.
And our Output Range, we'll
say something like M1.
Click OK.
And so there are our
regression statistics.
If we were in
hypothesis testing,
we'd see our residual plot,
which looks pretty good.
We have our F-test
statistic, our P-value.
But all we're interested
in is this-- the slope
and the intercept.
So what this means, since--
and if we look over here--
our variable is month
with increments of 1.
So they have equal time
periods for every one month.
Units increase by about 5.
Now we can come over and
make our forecast-- period
25, 26, 27, and 28.
It's just extending beyond
the bottom of our data set.
Hey, equals in our slope.
That's going to be
time t-- and I'm
going to lock it with a F4
key-- times our x variable.
Remember that slope means about
five units for every month.
We started at 1, went
to 24, now we're at 25.
You can think of the slope
here as an average increase
per month.
And that'll be a relative
cell reference, plus,
and then we'll go
get our y-intercept.
And I'm going to lock
it with the F4 key.
Control-Enter and Copy it down.
So that's pretty convenient.
F2, we're checking it.
It looks good.
As long as our assumption
that this trend will continue,
then that will be
a good forecast.
Now, there's a great function
Excel that does exactly this.
It forecasts using
linear regression.
And guess what, they named
it smartly-- Equals FORECAST.
It needs to know the
particular x, comma,
the known y's-- we highlight all
of them and we're going to have
to hit the F4 key--
comma, and the known x's.

And I'm going to have to
lock it with an F4 key.
And here's how cool the
FORECAST function is.
Because I put x's and y's
in and a particular x,
FORECAST will actually calculate
the slope and the intercept
for us and use that x in the
estimated regression equation
and calculate the
exact same answer.
So I can copy it down
either way you go.
That's pretty cool to
have a FORECAST function.
Now I want to calculate mean
square error for forecasting.
So I'm going to come over here.
Remember, we need the actual
values, your actual forecast--
and we're going to
use regression--
and then the forecast
error, et cetera, et cetera.
So we're going to say
equals-- and we're
using the FORECAST function.
There's time period
1, that's our x comma.
There's the y's.
F4, comma, and the known x's.

F4, closed parenthesis,
Control-Enter.
I'm going to calculate
Forecast Error right off
the bat-- equals the particular
value minus the forecast.
Now again, this
says Forecast Error.
But we've seen error like this
throughout our whole class.
We've seen deviation, which is
a particular value minus x bar.
We've seen residual, which is
the x minus the value predicted
by our equation, which is
exactly what we're doing here.
Last chapter, we called
this residual here,
we're calling it Forecast Error.
So I'm going to Control-Enter
and then highlight both of them
and double-click and
send both of them down.
I can go and check and see.
It looks like it's working.
Now we're going to
calculate mean square error.
I'm not going to do the
extra column with the squared
and then add it up.
I'm just going to come over
here and, of course, there's
a function-- just like we saw
in chapter 4-- Sum of Squares.
And what it does
is it will first--
we're taking our error,
just like last chapter,
we took our residual--
take all of those.
It will square it
and then sum it.
Now that's not quite enough.
I'm going to Control-Enter
because I forgot something.
Last chapter, that
would be called
Sum of the Square of
Residuals or Errors.
But check this
out-- last chapter,
whereas we took that amount and
divided by Degrees of Freedom
in forecasting, we're
going to take it
and divide by the
number of forecasts.
That's our n minus
k we've been talking
about in the last
couple of videos.
So I'm going to count all
of our forecasts, which
happens to be exactly the same
as our number of observations,
right.
Now I can take F2 and
divide it by our 24.
And that will give us our Mean
Square Error for Forecasting.
Now we want to be careful
because Mean Square
Error for Forecasting is going
to be calculated slightly
differently than our
last chapter, chapter 4.
And the only difference
is the denominator.
Here, we're actually in the
denominator using the count
of all of the forecasts.
Now remember, we saw exponential
smoothing and moving averages
and we had different
numbers of forecasts
that we had in those
examples compared
to the actual sample
size, or n count,
of all the actual values.
So when we get to Mean
Square for Forecasts,
I'm going to divide by the total
count of all of our forecasts.
Now I want to show
you a different way
to calculate this.
If you do this output, you
have the two pieces you need.
There is the total observations.
And remember, Sum of the Square
of Residual is the same as SSE,
or Sum of the Squares of Error.
And there it is.
If we come over here
from this output,
we can take Sum of the
Squares of the Error divided
by our total count.
And there it is.
We get that.
Now I want to remind you,
we had a slightly different
calculation.
And guess what, back
in chapter 4 and 5, all
they call both of
these numbers that
come out with
different answer, they
call them Mean Square Error.
They don't differentiate.
So here I said, Mean Square
Error for Forecasting
and Mean Square for Regression.
The difference is
we're going to take
our Sum of the Squares
of Error and we divide
by our Degrees of Freedom.
We take into account that
we had some parameters
that we estimated.
So there would be, for
regression, the Mean Square
Error.
And you can see, that's actually
calculated in the output Mean
Square of Residual or Errors.
But this is what we're
going to use-- Mean Square
Error for Forecasting
Now here we had simple linear
regression, a single variable.
And it was a quantitative
variable on number.
Now we're going to come look
at our sheet, Regression
Seasonality No Trend.
Here's our data set--
year, quarter, and sales.
And here's the line chart
for our time series.
And it looks like
we definitely have
a seasonal or periodic
pattern-- Quarter 4, Quarter 4.
Quarter 4 always seems
to be the lowest.
And so we want to see how to
use linear regression to create
a forecast.
Now what we're going to have to
do is transform this data set.
If you remember last
chapter, chapter 4,
when we had a
categorical variable,
we had to break a
particular column apart
into multiple
columns and then use
0's and 1's to represent whether
it was that particular variable
or not.
Now I'm going to copy just
the sales, Control-C, 1, 2, 3.
And I'm going to put it right
here, Control-V, so in K1.
Now the way this works, and
we saw this last chapter
with only two categories.
Here we have four.
What this means is we're
going to have to add one,
two, three extra columns
to our data set, Y3.
Because if you have
whatever number
of categorical variables,
you have one less column
than there are variables.
So since we have four
categories-- Quarter 1,
Quarter 2, Quarter
3, Quarter 4-- that's
four categorical variables,
4 minus 1 is 3 columns.
So we're going to call this
Quarter 1, Control-Enter,
and fill handle it over.
I'm going to Control-B. And
you might be asking, well,
wait a second, what
happened to four.
Well, here's what's
going to happen.
When we run linear
regression, we're
going to get one,
two, three slopes.
Now if you look at this data
set, right here, if we were
predicting based
on Quarter 1, we
would not want the Quarter 2,
Quarter 3, or Quarter 4 slope.
However, when we got
down to Quarter 2,
we wouldn't want Quarter
1, Quarter 3 slope.
And we wouldn't want
Quarter 4, which will
be part of the y-intercept.
We'd want just
Quarter 2, and so on.
Quarter 3, down here, we'd only
want the slope from Quarter 3.
So here's how it works.
Once you have four
categorical variables,
you list one, two,
three of them.
When we get to a
record in our data set
or a forecast for
Quarter 1, we'll
multiply our x value, 1,
times the slope for Quarter 1.
And then 0 times
2 and 0 times 3.
When we get to Quarter 2, 0
times the slope for Quarter 1.
But 1 times the
slope for Quarter 2.
0 for Quarter 3.
For Quarter 3, only
a 1 for Quarter 3.
But when we get to
Quarter 4, we have 0, 0, 0
because the slope for Quarter
4 will already be incorporated
into the y-intercept.
Ah, but what does that mean?
How are we going to add
these extra three columns?
Well, you have to create a table
like this-- Quarter 1, Quarter
2-- and describe
the logic of how
you're going to apply the
slopes in your equation.
Once you have that, I want
to do it manually over here.
So we're going to apply
some of our Excel skills.
We definitely know
how to use VLOOKUP.
We definitely know how to
use the MATCH function.
And we definitely know how
to use Mixed Cell References.
And we're going to
put a formula here.
And I actually put
this new data set
right next to the old
data set so I can look up
each one of the proper quarters.
So I'm going to
use equals VLOOKUP.
And I'm looking up
this quarter here.
Now I'm going to have VLOOKUP
find the quarter here,
and then look up the appropriate
1 or 0 for each quarter.
Now think about this,
that cell reference,
when I copy over to
this side, I need
to be locked on that quarter.
But when I copy it down, it
needs to move to the Quarter
2 and then Quarter 3.
So that means I need to hit the
F4 key one, two, three times.
C is locked.
So when I go this way,
it's locked on C4.
But the 4 doesn't
have a dollar sign.
So when I move down, it goes
to C5, C6, et cetera, comma,
the table.
First column has the thing
we're going to match.
Subsequent columns are the
things we're going to look up.
I need to lock it with the F4
key, comma, the column index
number.
Well, for Quarter 1, I
need the second column
in the VLOOKUP table.
That has the thing I want
to get and bring back.
Quarter 2 has one, two,
it's in the third column.
And Quarter 3, the thing I
want to go get from this table
and bring back is in the
one, two, three, four column.
I do not want to type
this in an edit it,
especially when you get
categorical variables that
are many columns long.
Actually, some of the homework
problems in the textbook
have six, seven, eight
columns like this.
So I'm actually going
to look up the quarter
within that range, those field
names, with the MATCH function.
MATCH function will
tell me, are you
the first item in this range,
second, third, or fourth.
Notice Quarter 1 is in the
second relative position, which
will be perfect for
column number 4, VLOOKUP.
MATCH is going to sit right
in that VLOOKUP column index
number.
Now watch, when I hit Tab,
the Screen Tip changes.
Now the lookup value,
I want the quarter.
When I copy this one
down, I need it locked.
But when I move to the side,
I want it to move relatively.
So I hit the F4 key once and
twice, comma, look up array.
That's this range of
field names at the top.
And I'm going to
lock it, F4, comma.
The lookup type for MATCH is
exact because I'm doing text 0.
And watch this, when
I close parentheses,
that Screen Tip will disappear.
Close parentheses.
It jumps back to VLOOKUP.
Column index has MATCH.
That's just delivering
the relative position,
which will tell VLOOKUP
which column to go and get
the number from.
Comma, and the final argument
in VLOOKUP is exact match.
I'm putting a 0, close
parentheses, Control-Enter.
Whoa, an NA, that
means not available.
That means either that or
one of these two lookups
are not correct, or actually
one of these over here.
So I'm going to hit F2 and
sure enough, look at that.
QRT, QTR.
I made a typing error.
I'm going to edit this and
change the middle part to TR.
Control-Enter.
Copy this over.
Spelling is my downfall.
Now I'm not a copy
the formula over,
double-click, and send it down.
Go to the last cell
and hit F2 to check.
Sure enough, the VLOOKUP
is in the correct place.
The range for both MATCH and
VLOOKUP are in the right place.
The MATCH lookup
value is correct.
That is amazing.
Now I'm going to
add some formatting.
Now I have my proper
new table with one,
two, three x variables to
pick up a seasonal trend
in linear regression and my y.
I'm going to use the built-in
feature Data Analysis.
Actually, this is an add-in.
You have to go to
Tools, Add-Ins,
and add in Data Analysis.
Click OK.
The input range, I
better make sure,
and I definitely want to
include the label at the top,
particularly if you're
doing multiple regression
because we're going to need
to pick out the right slope.
So these field names at
the top are very important.
So when I go to x input,
I'm going to delete that.
Highlight the field
lines and then
all of the records
for those x variables.
Labels, confidence interval,
residuals-- we're not
going to use any of that.
That's for hypothesis testing.
But I definitely
want to change this.
Scroll over and put it
in something like T1.
Click OK.
And now we can see over
here, our intercept-- Quarter
1, Quarter 2, Quarter
3 slope values.
Now in this example,
if we looked over here,
Quarter 4 is always
the smallest.
So that means this y-intercept
includes that value in it.
We don't know
exactly what it is.
But that's why, since
Quarter 4 is smallest,
Quarter 1 will add some.
Quarter 2 will add some more.
On average, that's
the biggest quarter.
And then Quarter
3 will add some.
Actually, in our next example,
we'll have a reversed example.
Quarter 4, I think,
is the smallest.
So some of these slopes
will come out negative.
We can build our
formula for estimating.
Now what in the world
are we going to do?
If we have slopes, what
is the actual x value?
They are up here.
Equals sign, I'm
going to go down
to get my slope for Quarter 1.
And I'm going to hit
the F4 key, times my x.
Now you could build a separate
formula for each one of these.
But I'm not.
I'm just going to
build it as if there
is an x value and
a slope every time.
Plus, the slope for
Quarter 2, and I'm
going to hit the F4 key,
times-- and there's my x value.
Notice it's a relative
cell reference.
So when I copy down, it's
going to be in the same order
as Quarter 2, Quarter 2, 3,
4, here, plus-- by the way,
I could have put
these x variables
right next to this
data set, if I wanted.
The next slope is for Quarter 3.
And I'm going to hit F4,
times-- and there's our x value,
plus our y-intercept.
And I'm going to hit F4.
Now I can Control-Enter.
And there, if I copy this
down, all our estimates
for Quarter 1, 2, 3, and 4.
So we could see that the
estimate for Quarter 4
is the smallest because in
our original source time
series, that was the
pattern that was displayed.
Now as we discussed earlier in
this video and in past videos,
we definitely want to
calculate Mean Square Error.
And I'm going to
put for regression.
And I'm actually going to move
this over here, format it.
And I'm going to calculate
it from my output.
Remember, Sum of the
Squares of Residuals
is the same as Sum of
the Squares of Error.
And I'm going to divide it by 9
degrees of freedom observation.
And there is the
Mean Square Error.
All right, so here in this
second example in this video,
we had just to season.
Now we want to go over
to the next sheet tab.
All right, we're going to
click on this sheet, Regression
Seasonality with Trend 3.
Here's our data set.
Different data center.
Look at this.
It definitely looks like
Quarter 2, Quarter 2, Quarter 2.
That's the low point.
And it looks like
Quarter 4, 4, 4.
4 is the high point, but
there's also an upward trend.
So when we see that, we
can use linear regression.
Now in our last example,
we saw just season.
So that meant we had to break
quarter apart into 1, 2, 3.
And we built our
little lookup table.
The patterns of 0's and 1's
indicate when we use the slope
and when we don't use the slope.
And we created our formula.
But look at this, we also
have now a fourth variable.
This is time, straight
from over here.
This t variable
represents quarter.
And so now we're
going to include that.
This new variable here
will also get a slope.
And it means the slope
of that will indicate,
for each quarter, it will
be an increase, right.
For each quarter, there'll be
an increase-- one, two, three,
four x variables, four
slopes, and a y-intercept.
All right, we're
going to use Data,
over to Data
Analysis, Regression.
And now we want to make sure we
put our y, so it's highlighted.
Here is the y.
And I want to make sure
and have the actual label
at the top in all the values.
And then the x's--
one, two, three,
four labels and all of the
records for each one of those
variables.
Labels, confidence, residuals--
that's all that's all OK.
We have T1.
So I'm going to click OK.
And down here, look at that.
Now we have Quarter 1,
Quarter 2, Quarter 3.
The slopes are negative because
Quarter 4 is the highest.
So any time we
get a 1 from here,
it'll be subtracting because
the amount for Quarter 4
will be in the y-intercept.
And we also have
one, two, three,
and a fourth independent
variable and slope.
That means for each
quarter over here,
since our data was
in thousands, this
means like a $1,400 increase.
All right, let's build our
estimated regression formula.
Equals, and let's
try Quarter 1 slope.
And I'm going to hit the
F4 to lock it, times--
and there's my x for Quarter 1.
Plus, now I get my
slope for Quarter 2.
And I'm going to
hit F4 to lock it,
times the x for slope
2, plus slope number
3, F4, times x,
plus-- and now we're
going to get slope number 4.
And I'm going to hit F4.
That's for the time
variable-- times.
And there it is--
17, 18, 19, 20.
Those are the next
four quarters.
Notice our last quarter
over here was 16.
So 17-- boom-- that
will be our x or t.
And then we have to
add our y-intercept.
F4, Control-Enter,
and copy it down.
You've got to kidding.
We have a way to forecast using
multiple linear regression
and a categorical variable when
we see seasonality and a trend.
Now here we used all of the x's.
You actually could use an
individual formula for each one
because you only need one of
these slopes for each one--
1, 2, 3, or 4.
Here is an alternative way.
Now watch this.
I'm going to click on
the slope for Quarter 1.
And I'm not going
to even multiply
because the slope
times 1 is the slope.
And I'm noticing Quarter
1, 2, 3, 1, 2, 3.
So I need to just
copy this down.
It will automatically
get the right slope
for each one of those quarters.
But I have to add the slope
for our time variable.
F4, times the actual
relative cell reference.
That's the time period.
And now I simply have
to add the y-intercept.
F4, Control-Enter, and
I can copy it down.
Then I have to create
a separate one here.
Equals simply slope, F4 times
20 plus our y-intercept.
And actually, I didn't need to
even lock that one-- and boom.
All right, In this
video, we saw how
to use linear regression to
take a seasonal and trend
pattern for a time series and
create an estimated regression
formula for forecasting.
Over on the sheet-- Reg
Seasonality No Trend.
We saw how to use linear
regression to forecast
when only always seasonality.
And then back on the first sheet
over here, we had just a trend.
And we use linear regression
and we use the cool FORECAST
function.
All right, that's
it for chapter 5.
We'll see you next
chapter and next video.