Question

we have data of 10 stores: Column A contains years in business and column B contains inventory volume in thousands of dollars. Do problems a-e with the use of Excel. In addition to the answers, show all your solution, including which Excel functions you used, with all the parameters.

a. Develop the estimated regression equation that could be used to estimate the inventory volume given the years in business.

b. Interpret the coefficients of the regression equation.

c. Predict the inventory volume of a store with 9 years in business.

d. Find the coefficient of determination. Briefly explain what it means.

e. Find the coefficient of correlation. Briefly explain what it means.

Years	inventory
1	85
2	97
4	92
4	100
5	103
8	111
10	120
10	125
11	117
13	140

Answer 1

a. Develop the estimated regression equation that could be used to estimate the inventory volume given the years in business.

The following data are passed:

X	Y
1	85
2	97
4	92
4	100
5	103
8	111
10	120
10	125
11	117
13	140

The independent variable is X, and the dependent variable is Y. In order to compute the regression coefficients, the following table needs to be used:

	X	Y	X*Y	X2	Y2
	1	85	85	1	7225
	2	97	194	4	9409
	4	92	368	16	8464
	4	100	400	16	10000
	5	103	515	25	10609
	8	111	888	64	12321
	10	120	1200	100	14400
	10	125	1250	100	15625
	11	117	1287	121	13689
	13	140	1820	169	19600
Sum =	68	1090	8007	616	121342

Based on the above table, the following is calculated:

$\bar X = \frac{1}{n} \sum_{i=1}^{n} X_i = \frac{ 68}{ 10} = 6.8$

$\bar Y = \frac{1}{n} \sum_{i=1}^{n} Y_i = \frac{ 1090}{ 10} = 109$

$SS_{XX} = \sum_{i=1}^{n} X_i^2 - \displaystyle\frac{1}{n}\left(\sum_{i=1}^{n} X_i\right)^2 = 616 - 68^2/10 = 153.6$

$SS_{YY} = \sum_{i=1}^{n} Y_i^2 - \displaystyle\frac{1}{n}\left(\sum_{i=1}^{n} Y_i\right)^2 = 121342 - 1090^2/10 = 2532$

$SS_{XY} = \sum_{i=1}^{n} X_i Y_i - \displaystyle\frac{1}{n}\left(\sum_{i=1}^{n} X_i\right) \left(\sum_{i=1}^{n} Y_i\right) = 8007 - 68 \times 1090/10 = 595$

Therefore, based on the above calculations, the regression coefficients (the slope m, and the y-intercept n) are obtained as follows:

$m = \frac{SS_{XY}}{SS_{XX}} = \frac{ 595}{ 153.6} = 3.8737$

$n = \bar Y - \bar X \cdot m = 109 - 6.8 \times 3.8737 = 82.6589$

Therefore, we find that the regression equation is:

Y = 82.6589 + 3.8737 X

Graphically:

Scatter Plot and Regression Line -1.40 74'0.60 2.60 4.60 6.60 8.60 10.60 12.60 14.60 - Regression equation: Y = 82.6589 + 3.8737 * X

b. Interpret the coefficients of the regression equation.

With 1 unit increases in X - Year the variable Y - Inventory will increase 2.8737 times.

Also, when the year is 0, the variable Inventory will be 82.6589

c. Predict the inventory volume of a store with 9 years in business.

At X = 9

Y = 82.6589 + 3.8737 *9

Y = 82.6589 + 34.8633

Y = 117.5222

d. Find the coefficient of determination. Briefly explain what it means.

the correlation coefficient is computed using the following expression::

$r= \displaystyle \frac{SS_{XY}}{\sqrt{SS_{XX}SS_{YY}}} \\ \\=\displaystyle \frac{595}{\sqrt{153.6 \times 2532}} \displaystyle= 0.9541$

Then, the coefficient of determination, or R-Squared coefficient (R^2) , is computed by simply squaring the correlation coefficient that was found above. So we get:

$R^2 =0.9541^2 = \displaystyle 0.9103$

Therefore, based on the sample data provided, it is found that the coefficient of determination is R^2 = 0.9103 . This implies that approximately 91.03% of variation in the dependent variable is explained by the independent variable.

e. Find the coefficient of correlation. Briefly explain what it means.

the correlation coefficient is computed using the following expression::

$r= \displaystyle \frac{SS_{XY}}{\sqrt{SS_{XX}SS_{YY}}} \\ \\=\displaystyle \frac{595}{\sqrt{153.6 \times 2532}} \displaystyle= 0.9541$

The variables are positively correlated,if X increase the y also increases and the correlation i really high.