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 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.
Paper#57270 | Written in 18-Jul-2015Price : $27