NAME______________ Major:_____________________
PART A). Excel Project Instruction
To answer these questions, you first need to look at the
attached Excel File and, after some required manipulations, which will entail using
Excelâs Formula and other functions, you are to run a model using Excelâs
Multiple Regression feature.
You are to type your answers here in MS Word and, wherever
needed, copy & paste the information (Numbers or graphs) form Excel print
outs of the Regression Results to this Word File.
The First part has to do with you manipulate the data and
run the regression and saved that as an Excel File and turn it in; you will see
below what operations you should perform in Excel.
Save this file as:
âExcel
Output-Your Nameâ Example: Excel
Project- Bart Simpson
and
deliver them via Blackboard. Do not
forget to Include, on the top of Excel
output, YOUR NAME and YOUR MAJOR.
The second part, Part B (A MS
Word file) contains the regression Questions).
It should be typed, saved and submitted via Bb.
Save This File as:
âExcel
Project Answers- Your Nameâ
Each Part (Parts A & B), have 100 pints for a
total of 200 points for this Excel Project.
The attached file (Excel Project data) is a sample
data where Y is modeled as a function of X1, X2,X3,and
X4. That is:
y = Æ(X1, X2, X3,X4).
The linear model specified is
as follows:
y = ?o + ?1X1 + ?2X2 + ?3X3 +?3X4 +
?
Based on the sample data, you
are to Run a Multiple Regression model in Excel to estimate the values of the
parameters (b0 , b1 ,b2 ,b3 and b4) for
the following Estimator equation:
?=b? +b1X1 +b2X2
+b3X3 +b3X4
PART A)–MANIPULATION OF THE EXCEL DATA
SET (100 Points)
A1.First of all, you see that,
instead of the familiar COLUMNS of the independent variables, here, you have
ROWS of them. You are to use Excelâs Transpose
feature to create a data set with
columns, instead of rows (You probably
know that Excel really does not need the transpose to run Regression Model, but I AM ASKING YOU
to do it!)
A2. As
you can see in the attached Excel file, you do not have a column for the fourth
independent variable in the model above (X4).
YOU are to create that column next to column
for column X3. Now, Variable X4
, is the Natural Logarithm (Base e) of the Mean of columns X2 and X3,. Then get the Natural Log of that Mean You are
to create this column, using appropriate Excel functions.
A3. After creating the column for X4,
you run the Multiple Regression where y is your dependent variable
and X1, X2, X3,and X4bare
your Dependent variables. Ask for
Residuals, and residual plots and Normal . Probability Plot.
A5.
YOU should also create the
Correlation Matrix of the independent variables in this NEW data set using Excelâs relevant statistical function. You have questions below that will require
use of that matrix.
A6. You should know how to
calculate the Durbin-Watson Statistic (Formula below as well as in Formula
sheet) and you should also know for what purpose Durbin-Watson Statistic and
Correlation Matrix is used. You are
asked to calculate the Durbin-Watson statistics. Here is the formula for D-W
Test Statistics (Also included in the Formula Sheet 2):
Durbin-Watson Test Statistics
A7.
You also need to create the Histogram of the
residuals so that you answer the related question(s) in Part B. You may have to extend the height of your
Histogram Chart to make it more visible.
PART B). This part is also included in Bb as a
separate part called Final, you should
use THAT file to answer the questions based on your Excel work and do not use
that below.
Now, based on your Excel
output, answer the following questions about Multiple Regression. As you know, for some of these questions, you
have to copy & paste here, from Excel output, the relevant numbers or
charts.
Since you have already created
the Excel Output, copy and paste any part that you need t use to give our
answer, from Excel to this Document.
Here are the questions:
Note: In all
Questions, where-ever needed, use the 5% level of significance to test the
hypotheses (?=0.05).
B1.What is the Sample Regression Equation?
B2. Which of the Independent
variables are significant? Why? Explain.
Use? = 0.05 in ALL questions.
Use only the p-value
to explain your answers.
B3. Test the overall significance of the model by
conduction an F test. Demonstrate the
Hypothesis you are testing.
B4. What is the value of adjusted r-square.
Verify its value, using the formula for adjusted r-square and using the values
in your Excel Printout.
B5. Comment on the Normality assumption for the
residuals for this model. In other word,
has the normality assumption been satisfied?
Explain your answer (Hint: you need to run Excelâs Histogram feature for
Column of the Residuals).
B6. Do you see any indication of
Multicollinearity? Explain why.
(Hint: Run the Correlation Matrix of the
Independent Variables). Can you find any evidence of Multicollinearity without
referring to the Correlation Matrix? Explain.
B8. Do you see any indication of
Heteroscedasticity for the Variable X4? Copy and paste the residuals for this
variable only. Demonstrate and Explain
why.
