How to Make Predictions from a Multiple Regression Analysis

From an existing multiple regression output produced with Excel 2007, I show you how to make point predictions and approximate 95% prediction intervals. The basic package of Excel does not have a routine for making predictions intervals, so I suggest a method of inflating the residual standard deviation statistic by 10% to get an approximate standard error of prediction.

---

Closed Caption:

hello and welcome back to a video series
on doing multiple regression in this
video I'll show you how to make a
prediction and estimate a prediction
interval using Microsoft Excel 2007 so
we are using the same data as before
some pulse rate data in the first video
I showed you how to make a scatter plot
matrix and we saw that there was some
kind of relationship between inclined
and pulse and speed and pulse but no
relationship between inclined and speed
as desired
we in the second video ran this multiple
regression and got this output and we
saw that indeed both X variables are
statistically important for
understanding the response pulse rate so
they're helping they're both helping out
and in this video i want to show you
some predictions so i'm going to use
Excel to make the prediction the .
prediction and estimated prediction
interval i'm going to insert another
column right here and then let's go down
a few cells and let's say this is
inclined and this is speed and I want to
do it . prediction right here and let's
do that first
so let's do a hypothetical here let's
say I want an incline of four want to
predict my pulse rate an incline of 4
and a speed of 4.2 that's format these
to one decimal place
ok and now actually I did have a data
point in the pad in when I collected
this data where I had an incline of 4
and a speed of 4.2 actually I had
repeated operation of that but very
pulse rates so we'll see what we get for
a prediction using this model up here I
could punch this into a calculator but i
like to use the coefficients from the
output right there
and that way i don't end up with any
rounding error ok so I'm going to say in
the cell i say equal and then I grab the
intercept plus the slope for inclined
times the incline value that I want to
predict at plus this coefficient first
speed times the speed value they want to
predict that and that's an h 34 which
i'll just type
there we go there's my . prediction
technically my pulse rate was only
measured to the nearest whole number so
why don't we get rid of that extra
decimal place there that's the point
prediction what if i wanted to make
another prediction let some try an
incline of five and a speed of 50
technically this is a little bit of an
extrapolation since the highest speed
that was going before when I collected
the data was 4.8 so a bit of an
extrapolation the incline is not an
extrapolation even though it wasn't one
of the values that I had collected in
the past it's in the range from 0 to 8
so not that portion is not an
extrapolation but overall it is it's
outside a little bit of the x space but
not too bad
ok I'd like to copy this formula down
that doesn't seem right but ok so it's
not right and that's because as I copied
the formula down it was also copying my
cell references down which I wanted to
do for here but not up here so i need to
lock in the cell references I 25 click
next to the eye in the i-25 up here at
the f4 key next to the i-26 hit the f4
key and the I 27 hit the f4 key now the
cell references are locked into place
and I can copy this down and that seems
much more reasonable given i'm walking
fairly given i would be walking fast and
a fairly high implying pulse rate should
go up
ok those are . predictions let's also
to try to estimate a ninety-five percent
prediction interval the basic package of
Excel does not have a routine for
calculating prediction intervals we know
that if we were doing a simple
regression we could type in a formula to
get a prediction interval standard error
of prediction find a margin of error etc
and find the lower bound upper bound of
the prediction interval that formula for
simple regression doesn't work for
multiple regression it would require
matrix algebra to do in multiple
regression and that's kind of difficult
to manipulate in a spreadsheet so we all
will estimate what I'm going to do next
is a copy i want a t-value and I want a
standard error
well i'm going to call this approximate
standard error of prediction
this is the part that we're
approximating and then this will be my
margin of error and obviously the
approximate to $OPERAND and this will be
my lower bound
this will be my upper bound and this
will be my interval with ok so now let's
say I want an approximate 95% prediction
interval CI ok so I'm ready to get some
numbers here now the t value if we're
doing a ninety-five percent interval we
could use 24 RT value but i'll get a
more precise t value even though it's
kind of irrelevant here so i type equal
T inv
parentheses . o 5 comma and then the
degrees of freedom from my model degrees
of freedom error from my model is 27
I'll just grab that cell i guess we go
there's mighty value let's format that
three decimal places and we're going to
approximate next the standard error of
prediction
so this is the complicated part of
finding a prediction interval in
multiple regression so i'm going to
estimate it what I know about standard
errors of prediction is there always a
little bit ever a little bit bigger than
s the standard error or residual
standard deviation so what I'm going to
do is just inflate is by ten percent so
i'm going to multiply it by 1.1 there we
go that's format this 2 3 decimals so
this is s inflated by 1.1 part but x
factor 1.1 ten percent larger because I
know standard errors are always a little
bit larger
now that's an oversimplification but
that's where the approximation comes
from ok margin of error is the t value
times the standard error of prediction
format that to three decimal places
actually that's good enough ok then the
lower bound equals the . prediction
minus the margin of error and gives me
way too many decimal places will clean
that up though
then the upper bound is the point
prediction plus the margin of error and
the interval width is the upper bound
minus the lower bound
ok let's for match these numbers let's
get rid of all the decimal places since
we know this is an approximation anyway
in format this and that looks much
better
so here's what we have for this row
anyway given that i'm going to walk and
an incline of 4 and a speed of 4.2 i
predict my pulse rate would be one
30 after that treatment combination but
there's a ninety-five percent
probability it will actually be between
somewhere between 119 and 140 that
interval with his 21
ok I can also do let's see what our
prediction interval for this would be
now I know that this is a slight
extrapolation it's a bit farther from
the center of the X base so technically
the standard error of prediction should
be bigger but we're not we can estimate
much more accurately so where I'm just
going to leave this is the standard
error of prediction let's lock in cell I
21 here with the f4 key and lock in
I 15 here and now i can copy all of this
down and there's my new . prediction and
there's my new prediction interval but
notice the standard error of prediction
is the same even though technically it
should be a little bit larger
ok that's it for . predictions and
estimating prediction intervals in a
multiple regression context if you
wanted to be more precise with your
prediction interval you could use a more
sophisticated software package like our
SAS or minitab in video for i'll show
you how to make residual plots in a
multiple regression context