A problem of interest to health officials (and others) is to determine the effects of smoking during pregnancy on infant health. One measure of infant health is birth weight; a birth weight that is too low can put an infant at risk for contracting various illnesses. Since factors other than cigarette smoking that affect birth weight are likely to be correlated with smoking, we should take those factors into account.

1. Open the Excel file from Moodle. You should see a worksheet that prompts you for your name and CSUN ID number. Fill these items in before proceeding. On the second worksheet named Raw Data you will find the data for the assignment. Here are the variable names and their descriptions:

2. The data you see on the Raw Data worksheet is locked and can't be modified. Copy all of the data and paste it into the worksheet named Modified Data. If you mess up your data at some point, you can retrieve it from the Raw Data worksheet.

3. In the Modified Data worksheet create four dummy variables.

(a) Create a dummy variable based on the variable gender. The dummy should equal 1 for male children and 0 otherwise. Name this variable gender dum.

(b) Create a dummy variable based on the variable white. The dummy should equal 1 for white children, 0 otherwise. Name this variable white dum.

(c) Create a dummy variable based on the variable nutrition. The dummy should equal 1 for mother's who took a nutrition class, 0 otherwise. Name this variable nut dum.

(d) Create a dummy variable based on the variable married. The dummy should equal 1 for mother's who are married, 0 otherwise. Name this variable married dum.

4. The values for family income are missing from the main data set but can be found on the Family Income worksheet. Match the family income data to the other data using the social security numbers on both worksheets. There are almost 1,400 observations, so you obviously can't match them one-by-one. But you can do the matching easily in Excel using techniques we learned in the computer lab.

5. Perform a simple regression using the following model:

bweight = α + β.cigs + ε

Name the worksheet with the regression output regression 1. Expand the columns as needed to make the results look nice.

6. Fill in the values for the estimated coefficients and other statistics on the Answers worksheet.

You will need to copy and paste your results from regression 1 into the appropriate cells. Do not round your answers.

7. Fill in the answers to the following questions on the Answers worksheet.

(a) What is the meaning of the slope coefficient and the intercept?

(b) Explain the estimated effect of cigarette smoking on birth weight.

(c) Do the coefficients have the signs you would expect?

(d) Are the coefficients statistically significant at the 95% confidence level?

(e) What does the R2 value tell you?

Be sure to put your explanations of slope coefficients in terms of the original units of measure.

8. Now examine the relationship between cigarette smoking and birth weight visually. Create a new worksheet tab named chart. On the new tab, create a scatter plot with trend line showing the linear relationship. The birthweight variable should be on the y-axis and the number of cigarettes smoked should be on the x-axis. Make the chart look pretty by removing the gridlines and labeling each axis.

9. Now perform a multiple regression, using the explanatory variables

-cigs

-faminc

-parity

-motheduc

-gender dum

-white dum

-married dum

-nut dum

-moth hgt

-gest age

Expand the columns to make the results look nice. Name the worksheet with the new regression output regression 2.

(a) On the Answers worksheet, fill in the results from the regression 2 worksheet and respond to the questions below as in item 7 above.

(b) Compare your results from this regression to the previous one. Has the coefficient for cigs changed? If so, explain why. What can you say about the goodness-of-fit for regressions 1 and 2? Write your responses on the Answers worksheet

(c) Does the second regression model violate the basic assumption that the explanatory variables must be uncorrelated with the error term? Explain. If the assumption was violated, provide a potential solution.

Attachment:- excel.xlsx