Question;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 Transposefeature 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 X4b;are 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.
Paper#62164 | Written in 18-Jul-2015Price : $47