View the step-by-step solution to:

# Y X1 X2 X3 M N 1128 23.5 552.25 110.25 246.75 1005 17.2 309.76 51.84 126.72 1212 26.6 691.69 57.76 199.88 893 16.9 272.25 34.81 97.35 1073 22.6...

I have Microsoft Office 2007 Excel but I am not very good with manipulating it. I have attached the data for the following homework assignment which I have saved as a word document that shows a formula and table that did not copy correctly. Please help, as you can see computer software is not my strong suite !?!

This Excel project, as you will see below, has two parts. The first part will be turned in as an Excel file; the second part must be turned in as a MS Word File.

Part A: 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.

Part B: You are to type your answers in MS Word and, wherever needed, copy & paste the information (numbers or graphs) from Excel print outs of the Regression Results to this Word File.

PART A)--MANIPULATION OF THE EXCEL DATA SET (100 Points)

A1. First of all, you see that, instead of COLUMNS of the Independent variables, you have ROWS of them. (5 Points).

A2. As you can see in the attached Excel file, you do not have a column for X4, and X5. YOU are to create (Insert) those columns next to column for column X3.
Column X4 , is (or, WILL be after you created) square of the MEAN of columns M and N. That is, you are to first calculate the Mean of M & N, then square that value. Column X5 and then get the Natural Log of that squared value for all the cells in that column (Use Excel’s Formula for Math and Trig). (20Points).
Attention: When you return your assignment, it must show inside the cells that you have in fact used the formulas function. It is not enough to have the correct number.

A3. After creating the column for X4, you ignore (Not delete) columns M and N and then the left-over columns are your NEW data set using which you run the Multiple Regression model and do the rest of your Excel Project. You will have the Excel Output in your Excel file of the solution including the Residuals, Residual Plots, Normal Probability Plot. You should demonstrate all the charts distinctly (To do this, you have to drag the charts that have been super-imposed over other charts so they will all be visible at first glance). (40 points).

A4. Create Charts and Graphs- Use the columns M and N to draw two distinct charts. In both of these charts, create the Scatter Plot. In one you should show the Linear Trend Line, in the other, you should show the exponential Trend Line. Be sure these two charts appear separately in your Excel Output (That is you may have to drag the charts). (15 Points).

A5. YOU should also create the Correlation Matrix of the independent variables in this NEW data set using Excel’s Correlation Function. You will have questions below that will require use of that matrix.(10 Points).

A6. I have included a value for Durbin-Watson Test Statistic under the related question below (Part2), because you may not know how to get it in Excel. If you do not know how to calculate D-W in Excel, using formula, you must still answer the question regarding the related statistical topic in Part 2. In any case, regardless of Excel matters, you should know for what purpose Durbin-Watson Statistic and Correlation Matrix is used. You are asked to calculate the Durbin-Watson statistics using Excel. 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). You may have to extend the height of your Histogram Chart to make it more visible. (15 Points).

PART B) You are to type your answers 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.

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, from Excel output, here in MS Word, the relevant numbers or charts.

Now that you have created the Excel Output, copy and paste the Output You will save it and turn it in as an Excel File as well as copying and Pasting the needed relevant parts here in MS Word.

No need to reiterate the fact that, to answer some of these questions, you need to have done the function in Excel first, so that you copy and paste them here. Here are the questions:

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 adjusted r-square? Verify its value, using the formula for adjusted r-square from your Formula Sheet. Hint: Copy and paste the Formula and place the numbers and SHOW your calculations.

B5. Comment on the Normality assumption for the residuals for this model. In other word, has the normality assumption been satisfied? Explain your answer (Hints 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.

B7. Do you see any Indication of Autocorrelation? The value of the Durbin-Watson test statistics is, of course, equal to 2.06 (d =2.06) Demonstrate and Explain Why. (Hint: Use the chart for Durbin Watson test provided in the Formula Sheet).

The Durbin–Watson Test for Autocorrelation
Autocorrelation Test is inconclusive No evidence of autocorrelation Test is inconclusive Autocorrelation
0

2 4-
4-
4
***The above table did not copy correctly***

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.

Y 1128 1005 1212 893 1073 1179 1109 1019 1228 812 1193 983 1281 1156 1032 856 978 1017 1091 1048 1192 1256 1215 1233 950 X1 23.5 17.6 26.3 16.5 22.3 26.1 24.3 20.9 27.1 15.6 25.7 30.5 26.5 25.7 21.8 33.6 17.9 18.3 30.1 29.8 28.5 27.5 26.8 24.3 17.8 X2 10.5 7.2 7.6 5.9 6.6 6.3 12.1 14.9 8.9 3.4 10.5 6 8.6 11.6 13.7 5.8 10.3 5.3 6.3 5.3 10.4 8.7 9.5 8.3 6.1 X3 552.25 309.76 691.69 272.25 497.29 681.21 590.49 436.81 734.41 243.36 660.49 930.25 702.25 660.49 475.24 1128.96 320.41 334.89 906.01 888.04 812.25 756.25 718.24 590.49 316.84 M 110.25 51.84 57.76 34.81 43.56 39.69 146.41 222.01 79.21 11.56 110.25 36 73.96 134.56 187.69 33.64 106.09 28.09 39.69 28.09 108.16 75.69 90.25 68.89 37.21 N 246.75 126.72 199.88 97.35 147.18 164.43 294.03 311.41 241.19 53.04 269.85 183 227.9 298.12 298.66 194.88 184.37 96.99 189.63 157.94 296.4 239.25 254.6 201.69 108.58
Sign up to view the entire interaction

Dear Student, I have reviewed your assignment thoroughly, based on your assignment details and current... View the full answer

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents