Question

Use Excel Analysis Toolpak to solve.

Years   Sales
1   80
3   97
4   92
4   102
6   103
8   111
10   119
10   123
11   117
13   136
7   108
6   103
8   111
10   119
1   81
3   99
10   121
11   115
13   140
8.2   114
6   100
5   95
7   107

A sales manager collected the following data on x annual sales and y years of experience of salespersons.

Required:
a. Develop a scatter diagram with the features rating as the independent variable.
b. What does the scatter diagram developed in part (a) indicate about the relationship between the two variables?
c. Use the least squares method to develop the estimated regression equation.
d. Interpret the coefficients.
d. What is the value of the coefficient of determination? Comment on the goodness of fit.
e. What is the value of the sample coefficient of correlation? What does it means?
f. Test for the significance of the relationship (slope) at the .05 level of significance.

Answer 1

Sol:

In excel ,elect the data ,go to

Insert>scatter chart

click on+to add axis labels and chart title

click on ok

we get

b. What does the scatter diagram developed in part (a) indicate about the relationship between the two variables?

	Years	Sales
Years	1
Sales	0.956698	1

there is a strong positive relationship between years and sales

as years of experience of salespersons increases ,sales,increases and viceversa

c. Use the least squares method to develop the estimated regression equation.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.956698
R Square	0.915271
Adjusted R Square	0.911236
Standard Error	4.453382
Observations	23
ANOVA
	df	SS	MS	F	Significance F
Regression	1	4498.993	4498.993	226.8482	9.94E-13
Residual	21	416.4849	19.83261
Total	22	4915.478
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	79.16213	2.151381	36.79596	1.48E-20	74.68808	83.63617
Years	4.069438	0.270188	15.06148	9.94E-13	3.50755	4.631325

:estimated regression eq is

sales=79.16213+4.069438*years of experience

d. What is the value of the coefficient of determination? Comment on the goodness of fit.

From output:

r sq=0.91527=0.9153

91.53% variation in sales is explained by years of experience

Good fit

explained variance=91.53%

unexplained variance=100-91.51=8.49%

e. What is the value of the sample coefficient of correlation? What does it means?

from output

Multiple R

0.956698

r=0.956698

there is a strong positive relationship between years and sales

as years of experience of salespersons increases ,sales,increases and vice-versa