Develop a regression model using the manufacturing.xlsx file. Use Number of prod

Question

Develop a regression model using the manufacturing.xlsx file. Use Number of production workers, Value Added by manufacturing, Value of industrial shipments, New capital expenditures, and End of year inventory to predict Cost of materials. The level of significance will be 0.05.

Set up and solve this problem in Excel. Use highlighting to emphasize important cells. Insert text boxes to provide answers to the following questions, based on your final output (run the regression a second, or third time, if appropriate):

1.   What proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables?

2.   Does it appear that an appropriate number of variables are used? Explain.

3.   Were any variables that were included in the initial solution not significant? If yes, list them.

4.   Are there any outliers? If yes, list them.

5.   What is the final regression equation?

Number of Value of workers manufacturing Materials shipments Number of production Value Added by Cost of industria New capital End of year Industrial itures invent 1 4 7 0 1 2 1 7 1 0 1 2360 1750 38407 29572 4300 2

Explanation / Answer

Here number of production workers, Value Added by manufacturing, Value of industrial shipments, New capital expenditures, and End of year inventory are independent variables and cost of material is dependent variable.

level of significance (alpha) = 0.05

Now we have to fit regression in excel.

steps :

ENTER data into EXCEL sheet --> Data --> Data Analysis --> Regression --> ok --> Input Y Range : select range of response variable --> Input X range : select range of all independent variables --> Click on labels --> Output Range : select one empty cell --> ok

The output will be :

  What proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables?

Here we have to calculate Rsq.

From the output :

Rsq = 0.9281 =92.81%

Proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables will be 0.9281 or 92.81%.

Does it appear that an appropriate number of variables are used? Explain.

No this is the case of multiple regression in which there is only one dependent variable and so many independent variables.

Were any variables that were included in the initial solution not significant? If yes, list them.

We can say that variable is significant if p-value of each independent variable will be less than 0.05.

Here we can say that the p-value for value added by manufacturing is 0.3418.

So this variable we will excluded from the model.

And all the remaining variables we included into the model.

5.   What is the final regression equation?

Now we have to find regression equation without excluded variable.

The regression equation will be,

y = -1811.22 + 17.58*x1 + 1344.29*x2 + 2.48*x3 + 2.42*X4

SUMMARY OUTPUT Regression Statistics Multiple R 0.963392866 R Square 0.928125814 Adjusted R Square 0.918143288 Standard Error 2162.743275 Observations 42 ANOVA df SS MS F Significance F Regression 5 2174434627 4.35E+08 92.97505 1.49725E-19 Residual 36 168388505.1 4677458 Total 41 2342823132 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -2161.190258 1116.948897 -1.93491 0.060889 -4426.467599 104.0871 -4426.47 104.0871 number of production workers 20.21908355 6.544503121 3.089476 0.003853 6.946216126 33.49195 6.946216 33.49195 value added by manufacturing -0.081656931 0.084763518 -0.96335 0.341799 -0.253565312 0.090251 -0.25357 0.090251 value of industrial shipments 1645.850927 732.6060731 2.24657 0.030895 160.0569557 3131.645 160.057 3131.645 new capital expenditure 2.793630277 0.736446278 3.793393 0.000548 1.300048008 4.287213 1.300048 4.287213 end of year inventory 2.371287439 0.488260409 4.856604 2.33E-05 1.38104944 3.361525 1.381049 3.361525

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.