Question

1. Suppose we have the following values for a dependent variable, Y, and three independent variables, X1, X2, and X3. The variable X3 is a dummy variable where 1 = male and 2 = female:

X1       X2       X3       Y

0          40        1          30

0          50        0          10

2          20        0          40

2          50        1          50

4          90        0          60

4          60        0          70

4          70        1          80

4          40        1          90

6          40        0          70

6          50        1          90

8          80        1          100

9          90        0          80

9          20        1          150

1. Run the multiple regression in Excel and provide the resulting multiple regression equation.
2. Provide the R-Square measure. Is this a good regression model? Explain. Use a level of significance of 0.05 in any tests you consider.
3. Which variables are important in explaining Y when the level of significance is 0.05? Is the dummy variable important at this level of significance? Discuss what the coefficients mean regarding the effect of each variable on Y.
4. Suppose a female with X1 = 5 and X2 = 80 is selecte What would be her predicted value of Y?
5. What types of problems might exist in this multiple regression?

Answer 1

STEP1 - GO TP DATA ANALYSIS

File Home View Tableau Help E Data Analysis 2005 SEPTEMBER Clear Y 21 T Insert Page Layout Formulas Data Review Queries & Connections TIT Properties Refresh Stocks Geography – All Edit Links Q ueries & Connections Data Types Get E EX Text to ZI Sort Outline Data• 留 Reapply Advanced Sort & Filter Columns Es Data Tools What If Forecast Analysis Sheet Forecast Get & Transform Data Analysis A1 x fc x1 ДА B C D G H I J K L M N O P X3 _ click Data 70 90 100 80 0 1 9 150

STEP2 - CLICK ON REGRESSION

Data Analysis Гок Cancer Analysis Tools Histogram Moving Average Random Number Generation Ronk and percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances Z-Test: Two Sample for Means Hell 90 100 90 80 9 1 150 Click

STEP3 SELECT THE DATA FROM EXCEL AND RUN REGRESSION

Regression kaput Input Y Range: OK $D$2:$D$14 SAS1:SCS14 1 1 Input X Range: Help Labels Confidence Level: Constant is Zero 95 % SGS2 1 Output options O Output Range: O New Worksheet Ply: O New Workbook Residuals Residuals Standardized Residuals 150 Residual Plots Line Fit Plots

OUTPUT OF THE EXCEL WILL COME IN THE SAME SHEET

E M N SUMMARY OUTPUT Regression Statistics Multiple R 0.947717304 R Square Adjusted R Square 0.864224117 Standard Error 12.98080706 Observations 0.898168088 —> R, S qua 2 R-Square ANOVA SS Significance F 8.49868E-05 Regression Residual Total 3 9 12 MS F 13375.79553 4458.598508 26.46031297 1516.512167 168.5013519 14892.30769 126.9544 Intercept insignificant Coefficients 30.22987255 10.22529386 -0.305747377 21.13825379 Standard Error Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 11.20305209 2.698360439 0.024459285 4.886808026 55.57293707 4.886808026 55.57293707 1.291849429 7.915236582 2.40986E-05 7.302927422 13.1476603 7.302927422 13.1476603 0.171131357 -1.786623929 0.107643916 -0.692873401 0.081378647 -0.692873401 0.081378647 7.434731859 2.84317635 0.019302866 4.319721865 37.95678572 4.319721865 37.95678572 P-values

Using the table from excel I was able to anser the following questions.

1.Run the multiple regression in Excel and provide the resulting multiple regression equation.

THE EQUATION IS  

Y~30.23 + 10.225 * X1-0.306 X2 + 21.38 * X3

2.Provide the R-Square measure. Is this a good regression model? Explain. Use a level of significance of 0.05 in any tests you consider.

R2 is 0.8981. R-square value is an acceptable but as a variable X2 is not significat at 0.05 level we might need to tune the model for better accuracy.

3.Which variables are important in explaining Y when the level of significance is 0.05? Is the dummy variable important at this level of significance? Discuss what the coefficients mean regarding the effect of each variable on Y.

Variables X1 and X3 are important(significant) explaining the change in Y while X2 is insignificant at 0.05 level.

from the equation we can say regarding coefficient of the respective variables as follows

• X1 - for every 1 unit chnge of X1 there would be a change of 10.225 unit change in Y for all X2 and X3 remaining constant
• X3- for X3=1 i.e female there would be a change of 21.38 units in Y keeping X1 and X2 constant
• as X2's coefficients are not significant at 0.05 level same is not true in change in X2

4.Suppose a female with X1 = 5 and X2 = 80 is selecte What would be her predicted value of Y?

Putting the values in the equation - we get predicted value as 126.9544

5.What types of problems might exist in this multiple regression?

As X2 is not significant there could be errors in predicting values of Y hence its recommended that we should exclude the variable X2 from the multiple linear regreession we might get better results for predicting values of Y

I also created the alternate model with only X1 and X3 in it - output is as below the R2 is similer and the variables X1 and X3 are significant at 0.05 level

SUMMARY OUTPUT Regression Statistics Multiple R 0.928467242 R Square 0.86205142 Adjusted R Square 0.834461703 Standard Error 14.33308308 Observations Asquare similar 13 ANOVA SS Significance F 4.99558E-05 Prolur. Cicant Regression Residual Total 2 10 12 MS 12837.93499 6418.967493 31.24538929 2054.372706 205.4372706 14892.30769 are sloniti at o.o5 Intercept X1 X3 Coefficients Standard Error Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 15.18678471 8.160045377 1.861115228 0.092345788 -2.994929427 33.36849885 -2.994929427 33.36849885 9.555171669 1.364981838 7.000218905 3.71461E-05 6.513802603 12.59654074 6.513802603 12.59654074 24.05312028 8.009149018 3.003205487 0.013270769 6.207624179 41.89861637 6.207624179 41.89861637