(upbeat music)
- Hello, I'm Professor Rick Jerz
and I welcome you back to my week nine
reinforcement session of business analytics.
In this session I would like to explore a problem
to illustrate the many kinds of approaches
to using multiple regression analysis
using my Excel model.
I want to then explore some of the assumptions
behind multiple regression analysis
and see how we might use our models to make sure
that we are not violating any of these assumptions.
I also plan to explain some of my Excel modeling techniques
that I have used.
Let's get started.
The goals for this session
are to review a few administrative items,
to explain the multiple regression model
and its development,
to do another problem using this model,
to explore multiple regression assumptions,
and to say a few words about our next session.
Relative to administrative items
I really don't have anything new for you.
We are approaching the end of the semester
and we will have our second exam in a couple weeks.
I'd like to share with you how I developed this current
multiple regression model in Excel.
The problem with multiple regression analysis
is that we have a lot of data feeding our analysis
and a number of equations to have to deal with.
We have a number of these sum of square
intermediate calculations that have to be performed.
By themselves they'd look rather mild,
but the summation symbol
means that you have to do this calculation
for every individual data set
and you have to have previously calculated some averages.
Then these intermediate sum of square calculations
feed other equations that look a little complex.
Computationally this can become exhausting
if you were doing these calculations by hand
or even with a calculator.
My HP-41C calculator
that I purchased in about 1980 was programmable
and could be programmed to do multiple regression analysis.
However, HP did sell these utility chips
to do special functions
and I had purchased this Stat Pac chip to do statistics,
including multiple regression analysis.
Remember that in 1980 the personal computer
had just been introduced
and we did not have products like Microsoft Excel.
I did love this HP-41 calculator
and today I even have an app on my iPhone for it.
I haven't figured out a way to get this Stat Pac chip
inserted into my iPhone however.
Most statisticians will argue that you need to use
statistical software for multiple regression analysis.
For those who want to use Microsoft Excel
you're kind of stuck with the Data Analysis tool pack,
which does provide a utility
for multiple regression analysis
or add-on utilities, like MegaStat.
In my own multiple regression model
I do show you the output
of these two utilities over to the right.
You have heard me mention before
that I do not like these add-in utilities
for a number of reasons.
These add-ins do not update results when data changes,
you must rerun the add-in.
This ability for results to immediately update
when input data changes was the fundamental design
behind spreadsheets when the spreadsheet was developed
back in the early 1980s.
So these add-ins violate this basic design principle
and remove the power of spreadsheets being interactive.
These add-ins do not help you interpret the results.
This is still up to you.
They are what they are and you cannot modify them.
In the models that I have been providing to you,
my approach is to allow you
to make any kinds of changes that you desire.
My models are completely open.
It is difficult to use the results of these add-ins
in bigger business models that you might want to develop.
Excel's analysis tool pack is free,
so you will find many instructors using it.
I have even been told by others that I must use it.
But it is probably the weakest of all add-ins,
because it was developed back in the early 1990s
and it has never been improved.
Some instructors have said to me, just use it,
it is good enough for students, but I disagree.
MegaStat from McGraw-Hill is an example of an add-in
provided by a textbook publisher.
Many textbook publishers have provided these add-ins,
usually at a cost.
Recently I came across a free add-in developed by Bob Nau
called RegressIt.
Its focus is on regression analysis,
whereas Excel's Data Analysis tool pack
provides more breadth.
But Bob has put a lot of cool features into his add-in.
It runs on both PCs and Macs
and if you like the add-in approach
I would strongly recommend
that you give Bob's RegressIt add-in a try.
It addresses and fix many of the deficiencies
of Microsoft's Data Analysis tool pack.
In my early years of teaching statistics
I was stuck with the Data Analysis tool pack,
but then I discovered an Excel function called LINEST.
It had promise for building
an interactive multiple regression model,
so I began using it.
LINEST is an array function.
I tried using it, but it was difficult to use
and it was difficult to interpret the output.
As I thought about improving my own model
I decided to explore what is behind this LINEST function.
Maybe there were different ways that it could be used.
I stumbled upon a webpage
where the author was trying to explain
how to deconstruct the LINEST function.
I'm sharing this information with you
on my reinforcement webpage.
In it you will notice that this particular author
describes how to use matrix manipulations
to get to some of these intermediate and final values.
This looked interesting to me.
I pulled out my old textbook from 1972 titled
"Probability and Statistics for Engineers and Scientists"
by Walpole,
and I saw Walpole using this technique
of matrix manipulations to solve linear regression problems.
You have to appreciate that when I was a college student
in 1972 we did not have calculators, personal computers,
or spreadsheet programs,
so the only way to do multiple regression analysis
was to somehow simplify the mathematics.
And using matrices to do this
became an efficient manual method of doing the calculations.
The only problem with this approach
is that only engineers and scientists
know how to manipulation matrices.
To my advantage was that Microsoft Excel
does have matrix manipulation functions.
So I decided to develop an Excel model using this approach.
I do most of this heavy duty work in Excel
a little off to the right.
Some of the intermediate calculations I show in orange.
And then I use the intermediate calculations
to feed my final model to the left.
The last problem that I encountered
was how to have students add and remove data
to this particular model.
Simply inserting or deleting rows
as we have done in other models would not work for this one,
because I do a bunch of intermediate calculations
off to the right
and I would have to rely on the student
duplicating all of these equations downwards.
This seemed to be a little bit
of an awkward requirement for students,
so I had to come up with another approach.
My solution was to use an inserted table.
Many people do not know about this technique.
Let me take a moment to show it to you.
I've given you a link on my resources webpage
that will download this Grades file
that we have seen earlier that we used for pivot table.
I can convert this to a data table
by clicking on some value,
then going to Insert, Table, and choosing the defaults.
A default color theme is applied,
but I will change this.
The only visual clue that you have
that this is a data table is this little tick mark
at the bottom right of the table.
You might be wondering, big deal, what has this done?
Well, by having an inserted table in Excel
if you happen to add a Column
where you're going to do some calculations,
like maybe doubling the score,
when you enter this equation in the first cell
it gets immediately replicated
all the way down to the bottom of this table.
This is pretty nice.
You do not need to manually duplicate it downwards.
The other thing that a data table will do
is if you do happen to insert some rows
internal equations get replicated.
So if I happen to enter a few new values
I do not have to duplicate the equations downwards,
it just works.
And this became the solution that I decided to apply.
The only requirement of using this model,
as I mentioned to you in my Excel lecture video,
is that the number of rows within this internal table
has to match the number of rows
of data values that you have.
We haven't seen this requirement in my previous models,
so this is a new requirement.
Let's try this problem.
The North Valley Real Estate
data reports information on homes on the market.
Use the selling price of the home as the dependent variable
and determine the regression equation
using the size of the home, number of bedrooms,
days on the market, and number of bathrooms
as independent variables.
We are then given some questions.
I encourage you to pause this video
and try to solve this problem on your own,
before seeing how I solve this problem.
I have provided you the data file for this
on the resources webpage.
When you open this data file you will notice
that it contains many variables that might be of interest.
We are interested in the Price, Size, Bedrooms, Baths,
and number of days on the market columns.
I am going to cut the Days column
and insert it next to the Baths,
just so that I have all of our independent variables
of interest side by side.
This will make it easier for me to copy and paste the data
into my Excel model.
Recall that one of the requirements of using my model
is that it must have the exact same number of green rows
as our data set.
So I go to the bottom of this Real Estate data file
and note that there are 105 data sets.
I return to my model and I insert some blank rows.
I use the keystroke Control + Y
to repeat what I just did several times.
I scroll down and note how many rows I have.
In this case, 141, which is too many,
but this is easy, I just need to delete some rows
between 105 and 141.
Now my model has 105 rows
and the data set has 105 rows of data.
My model is now ready to receive the Real Estate data.
I go to the Real Estate data file
and swipe through all the data of interest,
then I do a Copy, I return to my model,
and do a Paste Special, Values,
and I have copied all the Real Estate data of interest
into my model.
Using my model's Yes No switch at the very top
I make sure that all of our data of interest
is switched to Yes.
Well, the good news is that all of the calculations
have been performed.
All that we need to do is sit back and interpret the results
and see if we need to fine-tune our model.
Then when we are satisfied with our model
we can use it to make some predictions.
The first thing that I like to do
is to see if any of the predictors should be eliminated
either due to multicollinearity or a high p-value.
I do see that two variables have multicollinearity
and I would eliminate the one with the higher p-value.
In this case, Bedrooms.
But I also notice that both Baths and days on the market
also have high p-values,
so these should also be eliminated from our model.
This is pretty simple.
I just scroll up to the top
and switch Bedrooms, Baths, and Days from Yes to No.
I scroll back down into the regression analysis area
and notice that I now have a good model.
It may be surprising that we have only one
predictor variable that remained in our model, Size.
Sometimes this happens.
Returning to the questions,
we see that we have already answered questions A and B.
Yes, we saw some problems with multicollinearity
and we also saw some independent variables
with weak correlations.
We easily removed these from the model.
Our Excel model shows the regressed equation
and we didn't have to use statistical software,
as suggested in the question.
Our coefficients of correlation
and determination are quite high,
about 90% of the variation in price is attributed to size.
With my Excel model it takes us,
oh, maybe two minutes or less
to get to these kinds of results.
In question C the author wants us
to evaluation the edition of a pool or a garage.
Whether a home has a pool or garage
are yes, no kinds of questions,
and we will represent these as dummy variables in our model,
taking on the values of zero or one.
It is quite simple for us to include these predictors.
I return to the data set,
I swipe through the Pool and Garage data,
copy it, return to my model,
and paste this into the upper data entry area.
I make sure to turn both of these predictors on.
Then I scroll down and I see that Pool
should be kept in the model, but the Garage should not.
Once again, I scroll up to the top,
turn off the Garage predictor, scroll back down,
and I have the results of my analysis.
We have our regressed equation that we can use to predict
and we do see that the coefficients of correlation
and determination have improved.
This is good news.
We can see from the regressed equation
that if a home has a pool
that adds about $28,000 onto the price of a home.
Why hasn't the author asked us to use our model
to make a prediction?
My guess is that since the author
is using the Data Analysis tool pack,
which does not have prediction built in,
the author decided not to ask us to do this.
I find this a little bit strange.
The whole reason we do regression analysis
is to create a model that we can use to predict,
yet the author forgets to ask us to do this.
Oh well.
Questions D and E have something to do
with the assumptions behind multiple regression analysis,
so let's spend some time on the assumptions.
The first assumption is that the relationships
between the independent variables
and the dependent variable are indeed linear.
One of the best ways to test this assumption
is to use a scatter diagram to plot the dependent variable
against each independent variable.
I have provided you this Excel worksheet
titled Multiple Regression Assumptions.
In it I have areas
relating to each of these five assumptions.
For this first assumption
that there is a linear relationship
I have created this nice summary table
by duplicating the analysis that I did
on my simple linear regression model,
but applying it to all six variables at one time.
The very bottom line of this table
provides a Yes or a No
for whether or not there is a relationship.
To the right I also provide plots
for each of these six independent variables.
You can ignore the data or the plots
for any of the variables that we have turned off.
When looking at these results
I would say yes, there is a linear relationship
for Size and for Pool.
We can go onto the next assumption.
The second assumption is labeled homoscedasticity.
Wow, how is that for a statistical word?
It says that the variation of the residuals
are the same, or constant.
Take a moment and look to the right of our data entry area.
There are two orange columns.
The first one is labeled Predicted,
the second one is labeled Residuals.
The Residual is calculated by taking the difference
between the actual observed value and the predicted value.
In other words, it is the error between the actual value
and what our regressed linear model equation calculates.
What the second assumption says is that these errors
should not have any trend to them.
More specifically, the residuals should not
have any relationship to the predicted value.
They should be random, as illustrated in this graphic.
So how do we explore this?
For us, it is actually pretty easy.
In my Multiple Regression Assumptions worksheet
I have this second assumption shown right here.
I have taken the predicted values
and plotted them on the horizontal axis,
versus the residuals on the vertical axis.
I am treating this data as two separate variables
and I want to know if they are related.
So all that I am doing is simple correlation analysis
for these two variables.
We can look at the scatter diagram
to see if there is any trend,
but what we are really seeking
is a small coefficient of correlation.
When I look at what my model calculates
the coefficient of correlation is zero.
So we have not violated the second assumption.
Let's move onto the third assumption.
The third assumption says that these residuals
should be normally distributed.
What I have done in this next area
of my Multiple Regression Assumptions worksheet
is to create a frequency distribution for these residuals.
I am using the same technique that I used
on my weighted data model to do this for the residuals.
To the right I do all the calculations to create the bins
and I tally the residual values into these bins.
Next to this I do another set of calculations
answering the question, what would this distribution be
if this data were perfectly normally distributed?
I then create this graph,
which shows the distribution of the residuals
versus this perfectly normally distributed distribution.
Looking at the graph it does appear
that the residuals are normally distributed,
but below this I do do this Chi-Square Test
with the null hypothesis
being that these two frequencies are the same.
Our Chi-Square Test concludes
that these distributions are the same.
Well, not bad.
Let's go onto the fourth assumption.
The fourth assumption says that the independent variables
should not be correlated.
This is multicollinearity
and I do calculate this within our model.
So we have already addressed this fourth assumption
and we are in good shape.
Let's go onto the fifth assumption.
The fifth assumption says that successive residuals
should be independent.
This is sometimes referred to as autocorrelation.
We can test this by first creating a scatter diagram
and look to see if there appears to be any positive
or negative slope.
Meaning the slope should be equal to zero.
I have already checked for the slope equaling zero
up above in assumption two,
when I checked for homoscedasticity.
The null hypothesis is that the slope is equal to zero
and our data suggests that we do not reject this null,
meaning the slope is equal to zero.
In the chart to the right we can see
that the slope is equal to zero,
so it looks like we have not violated this fifth assumption.
The last item on my assumptions PowerPoint
is to be careful predicting outside the range of data.
Remember that in my multiple regression model
I do show you the confidence interval
and the prediction interval.
These become wider the further you get
away from the data that you used
to create this regression equation.
So there you have it, my multiple regression model
and the testing of multiple regression assumptions.
Remember, with multiple regression
the statisticians love doing all kinds
of additional analyzes with this kind of data,
but we have covered a majority of the analyzes
and with my model we can do all of these analyzes
and test the assumptions in probably less than five minutes.
Give it a try.
One last thing I should mention
is if you like this particular model
you can use it for simple regression analysis too.
At the bottom of my reinforcement webpage
I give you a few more problems to try,
along with the answers.
If you think about all of the things
that we are able to do with this model
you might agree with my statement earlier
that we are able to cover this topic
at a more advanced level
than the average course in business analytics
or business statistics.
This is the power of modeling with Microsoft Excel.
Our topic for next week is optimization and simulation.
In my Excel models you will see me applying
some of the techniques
that I've used in this multiple regression model.
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help
please let me know.