Forecasting in Excel Using Simple Linear Regression

Get you Master of Science in Supply Chain Management online in as little as one year. Please visit: business.rutgers.edu/scmonline.

---

Closed Caption:

ok in this video I'm going to show you
how to run a regret forecast based on a
linear regression a simple linear
regression and the idea is basically
that we have data with some kind of a
growth component in it and it's moving
upwards so we're going to guesstimate an
intercept and slope that will then be
used to create the forecast
we're only going to use the first year
of data which is the first 12 months
that we have here to create our
regression coefficients to estimate the
intercept and the slope and then we're
going to apply the regression to to the
entire set of data now let's get started
so first of all we have to calculate our
intercept and are slow
there are several ways to do to run a
regression in an excel but the easiest
one for this case is to just use the
intercept and the slope function to use
the indifferent function we talking
intercept and then we opened the
parentheses and then it asks for two
things are known wise and are known XS
are known wise are basically the output
that we want the forecast to achieve so
in this case that would be our demand we
are going to highlight the first 12
months of demand then-unknown exes are
going to tell us where in the time
series we are so for that created this
column here that's called . then I had
that there again the first time . close
the parentheses and then we'll give us a
intercept of a 2.67 now to calculate the
slope it works very similarly we're
going to type slow of parentheses and
then the function use the same logic
highlight our first months of the men
and then we hired a four-stroke . the
numbers sequential close-up disease and
enter and here we go our slope is 7.5 77
alright so now in order to create the
forecast we have to you basically type
in the record regression equation in to
our forecast column so before we type in
the actual regression equation we want
to make sure that we don't forecast half
unit we're going to use the round
function and we're gonna see around then
the general we are regression is set up
is the forecast equals the intercept
plus the slope times how many periods we
are there so we are going to say
intercept we want to make sure that when
we copy this formula down States puts
i'm going to hit f4 is going to place
two dollar signs in there and that will
ensure that when you copy the formula
for state part class slow kinds the .
number again for slope we have to go
back and also have the four and make
sure that it stays put when we copy the
formula down and then I'm just finishing
up this formula here and making sure
that the round function is complete and
close the parentheses and there we go
that's our regression equation now all i
need to do is I need to copy this down
and here we go we see on our graph we
just created a straight time for
last 42 years worth of data if it's a
little better for the first year then
for the second year because for some
reason the slow changes slightly in the
second year however that is basically
how a simple linear regression would
work
ok now just to ensure that we created a
good forecast going to leave out the
first period for because in order to
calculate the you statistic we need one
. before so I'm going to just
consistently calculate all my forecast
accuracy measures for a starting in
$YEAR . to you really wanted to you
could even started year too because that
when you when it really matters but when
we're going to start . to in this case
so our focus areas basically demand
forecast absolute error is the absolute
value of that forecast on our percentage
era we're going to do the absolute
percentage error in this case equals are
absolutely / demand r squared error is
basically our error term that we raised
to the second power and then finally the
denominator for ru statistic which you
statistic basically compares how good
this forecast is the naive forecast so
we the the numerator off that is the
squared error we just need the
denominator which would basically be the
squared error would have obtained if we
would have used the night method so that
is open disease demand computer- and
one . here that's what the typically
used as a forecast and raised to the
second power
ok so now all we need to do is we need
to copy all of these formulas down here
and I set up the error the absolute
there the percentage and the squared
error columns with conditional for
formatting so we can see a little bit
where most of the eris occur and it just
visually makes it a lot easier to to see
how good our forecast is ok not to
calculate overall accuracy measures to
get a calculator which means we take the
average of $OPERAND column E and just we
take the average of our error then we
have to calculate them absolute error on
here come our mean absolute percentage
error again average of ecology are MSC
of college-age which is all squares and
then finding the you statistic is the
square root of the sum of the squared
error is divided by the sum of squared
errors we would have obtained with a
3-wood performed you statistic is Q key
policies careful to actually some of
them are not average and look for the
other ones sum of squared errors and
then divide that by some
of this statistic two parentheses and
their way up so this was a forecast
using simple linear regression in itself