Question

In: Statistics and Probability

Using Excel generate a simple regression model with Y as the dependent variable and X1 and...

  1. Using Excel generate a simple regression model with Y as the dependent variable and X1 and X2 as the independent variables in the attached spreadsheet.
    1. Write the following from the output:

      Intercept:

      Coefficients of Independent variable:


      R-square:

      Significance F:
  1. Based on the model generated, forecast profits for a firm with X1= Based on the model generated, forecast profits for a firm with x1=250 and X2=100.



Evaluate the predictability of the model using explanatory language that someone who does not have experience with statistics or regression could understand to make informed business decisions, i.e., Interpret R2, F-test and t-tests.

X1 X2 Y
194 1.6 728.4
2256 1846 30184
32.7 1.6 179.7
37 36 1937
227 66 3472
29 14.1 782.9
-14.7 14.1 295.5
549 420 10100
512.4 200 5954.3
1086 509 17373
180 93 5550
23.1 2.6 88.1
5.4 6 174.6
8.1 8.2 172.6
43.8 17.6 1868.3
130.1 115.4 1672.7
101.8 14.2 1311.7
200 1219 21641
33 16 374.2

Solutions

Expert Solution

The regression equation is defined as,

Now, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Hours' column, Input X Range: 'Feet and Large' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

a)

Intercept:



Coefficients of Independent variable:



R-square:


Significance F:

From the ANOVA table in regression output summary,

The regression equation is,

The profit for the firm is $3292.2634

Interpretation of R2, F-test and t-test

R-square: The R-square value determine how well the model fit the data values. The R-square value for the above model is 0.9426 which mean the model explains the 94.26% of the variance of the data values.

F-test: From the ANOVA table, the significance F value is 0.000 which is less than 0.05 at 5% significance level hence be can conclude that the model is overall significant which means the independent variables significantly fit the regression model.

t-test: The t-est in regression model is used to test the significance of the independent variables in the model.

For variable X1, the p-value for the t-test is 0.1376 which is greater than 0.05 at 5% significance level hence the variable X1 is not significant in the model.

For variable X2, the p-value for the t-test is 0.0000 which is less than 0.05 at 5% significance level hence the variable X2 is significant in the model.


Related Solutions

Shown below is a portion of an Excel output for regression analysis relating Y (dependent variable)...
Shown below is a portion of an Excel output for regression analysis relating Y (dependent variable) and X (independent variable). ANOVA df SS Regression 1 39947.80 Residual (Error) 10 8280.81 Total 11 48228.61 Coefficients Standard Error t Stat P-value Intercept 69.190 26.934 2.569 0.02795 X 2.441 0.351 6.946 0.00004 1.   What is the estimated regression equation that relates Y to X? (2 Points) 2.   Is the regression relationship significant? Use the p-value approach and alpha = 0.05 to answer this question. (2...
Using Y as the dependent variable and X1, X2, X3, X4 and X5 as the explanatory...
Using Y as the dependent variable and X1, X2, X3, X4 and X5 as the explanatory variables, formulate an econometric model for data that is (i) time series data (ii) cross-sectional data and (iii) panel data – (Hint: please specify the specific model here not its general form).
Use Excel to estimate a simple linear regression model for the following data (Y is a...
Use Excel to estimate a simple linear regression model for the following data (Y is a dependent variable and X is an independent variable): Y X 0 -2 0 -1 1 0 1 1 3 2 Fill in Multiple Blanks: What is the slope of the estimated line?  In your answer, show one (1) digit to the right of the decimal point, for example, 1.0, 1.2. Apply the appropriate rounding rule if necessary. What is the Y-intercept?
Generate and provide the full regression output using x1, x2, their squared terms, and their interaction, as 'x' variables against the 'y' variable.
X1 X2 Y 10 3 2002 5 14 1747 8 4 1980 7 4 1902 6 7 1842 7 6 1883 4 21 1697 11 4 2021 5 12 1750 6 8 1832 5 18 1795 7 4 1917 8 5 1943 6 9 1830 5 12 1786 A. Generate and provide the full regression output using x1, x2, their squared terms, and their interaction, as 'x' variables against the 'y' variable.
For the table below, if Y is the dependent variable and X1 and X2 are the...
For the table below, if Y is the dependent variable and X1 and X2 are the independent variables. Using the linear regression equation Y=-0.45X1-1.34X2+15.67, which observation has the largest absolute residual? Observation number Actual Y x1 X2 1 4.5 6.8 6.1 2 3.7 8.5 5.1 3 5 9 5 4 5.1 6.9 5.4 5 7 8 4 6 5.7 8.4 5.4 The first observation The third observation The fifth observation The second observation
For the table below, if Y is the dependent variable and X1 and X2 are the...
For the table below, if Y is the dependent variable and X1 and X2 are the independent variables. Using the linear regression equation Y=-0.45X1-1.34X2+15.67, find the Sum of Squared Residuals? (choose the best answer) Observation number Actual Y x1 X2 1 4.5 6.8 6.1 2 3.7 8.5 5.1 3 5 9 5 4 5.1 6.9 5.4 5 7 8 4 6 5.7 8.4 5.4 2.57 2.97 3.2 3.5
You estimated a regression model using annual returns of ExxonMobil (as a dependent variable) and of...
You estimated a regression model using annual returns of ExxonMobil (as a dependent variable) and of the market (as an independent variable). The R-squared of this regression is 0.2, and the total variance of ExxonMobil's returns in the estimation window is 0.0625. In this case, the variance of the unsystematic (or idiosyncratic) component of ExxonMobil's returns is:
You estimated a regression model using annual returns of ExxonMobil (as a dependent variable) and of...
You estimated a regression model using annual returns of ExxonMobil (as a dependent variable) and of the market (as an independent variable). The R-squared of this regression is 0.2, and the total standard deviation of ExxonMobil's returns in the estimation window is 25%. In this case, the standard deviation of the unsystematic (or idiosyncratic) component of ExxonMobil's returns is:
Fit a multiple regression model using MPG as the dependent variable and DISP, HP, and WT...
Fit a multiple regression model using MPG as the dependent variable and DISP, HP, and WT as the independent variables. Is the overall regression model significant? Test at the α = 0.05 level of significance. State the null hypothesis, the alternative hypothesis =, the test statistic calculated and critical values and your test conclusion. mpg disp hp wt 21 160 110 2.62 21 160 110 2.875 22.8 108 93 2.32 21.4 258 110 3.215 18.7 360 175 3.44 18.1 225...
The following is part of regression output produced by Excel ( for Y vs X1 and...
The following is part of regression output produced by Excel ( for Y vs X1 and X2): Y 12.9 6.1 1.1 39.7 3.4 5.9 8.9 15 7.3 X1 0.9 0.8 1.0 0.3 0.4 0.7 0.71 0.5 0.9 X2 4.2 3.1 1.2 15.7 2.5 0.7 5.0 6.4 3.0 A) write out the estimated regression equation showing that depends on X1 and X2. b)if. X1=0.58 and X2=7.0, what is the value predicted for y c)write the number which is the standard error...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT