Question

A sales manager collected data on annual sales for new customer accounts and the number of years of experience for a sample of 10 salespersons. In the Microsoft Excel Online file below you will find a sample of data on years of experience of the salesperson and annual sales. Conduct a regression analysis to explore the relationship between these two variables and then answer the following questions.

Salesperson	Years of Experience	Annual Sales ($1000s)
1	3	79
2	4	92
3	5	87
4	3	102
5	5	105
6	7	111
7	9	119
8	9	124
9	12	119
10	13	131

1. Compute b₁ and b₀ (to 1 decimal).

   b₁ =

   b₀ =

   Complete the estimated regression equation (to 1 decimal).

   =  + x

2. According to this model, what is the change in annual sales ($1000s) for every year of experience (to 1 decimal)?

3. Compute the coefficient of determination (to 3 decimals). Note: report r² between 0 and 1.

   r² =

   What percentage of the variation in annual sales ($1000s) can be explained by the years of experience of the salesperson (to 1 decimal)?

   %

4. A new salesperson joins the team with 6 years of experience. What is the estimated annual sales ($1000s) for the new salesperson (to the nearest whole number)?

   $

Answer 1

I have used Excel , And the command is

Data > Data Analysis > Regression > select the data > ok

========================================

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.870851173
R Square	0.758
Adjusted R Square	0.728179486
Standard Error	8.873236277
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1977.025424	1977.025424	25.11008379	0.001038471
Residual	8	629.8745763	78.73432203
Total	9	2606.9
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	78.2	6.3693	12.2851	0.0000	63.5598	92.9351
Years of Experience(x)	4.1	0.8168	5.0110	0.0010	2.2096	5.9769

========================

b₀ = 78.2

b₁ = 4.1

y = 78.2 + (4.1) x

b) 4.1

c) r² = 0.758

    In percentage = 75.8%

d) y = 78.2 + (4.1) *6 = 102.8