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.

Open spreadsheet
Compute b1 and b0 (to 1 decimal).
b1 =____ fill in the blank
b0 =____ fill in the blank
Complete the estimated regression equation (to 1 decimal).
y= ____ + ____x fill in the blank 5x
According to this model, what is the change in annual sales ($1000s) for every year of experience (to 1 decimal)?
____fill in the blank
Compute the coefficient of determination (to 3 decimals). Note: report r2 between 0 and 1.
r2 =____ fill in the blank
What percentage of the variation in annual sales ($1000s) can be explained by the years of experience of the salesperson (to 1 decimal)?
fill in the blank ____ %
A new salesperson joins the team with 8 years of experience. What is the estimated annual sales ($1000s) for the new salesperson (to the nearest whole number)?
$____ fill in the blank
Salesperson Years of Experience Annual Sales ($1000s)
1 2 78
2 4 94
3 4 87
4 4 104
5 5 107
6 9 109
7 9 124
8 10 121
9 10 122
10 14 131

Answer 1

(a)
`b_1 =3.4` and
`b_0 = 83.7`

(b)
`y=83.7+3.4 x`

(c)
`r^2 = 0.790`

(d)
`\% Variation = 79.0\%`

(e) The expected earnings is: $110,900

Step-by-step explanation:

The data for the 10 sales person is as follows:

`\begin{array}{ccc}{Years\ of Experience} & {Annual\ Sales(\$ 1000)} & {Salesperson} & {1} & {1} & {85} & {2} & {3} & {97} & {3} & {3} & {95}& {4} & {5} & {97}& {5} & {7} & {105}& {6} & {8} & {106}& {7} & {10} & {122} & {8} & {10} & {120} & {9} & {12} & {113} & {10} & {12} & {134}\ \end{array}`

Required

Perform a regression analysis using Microsoft Excel

Note

• I added two attachments to this solution

• In the first attachment, I highlighted the step to follow to perform regression analysis.
• At step 4, ensure that you fill in the input y range and the input x range.
• The input y range (in this question) is the column for sales person while the input x range is the annual sales
• Click ok, then Microsoft Excel will perform the analysis for you.

The question will be answered based on the result of the analysis performed by the application (See attachment 2)

See attachment 2 for the result of the analysis (I've highlighted the solution to each question on the attached file)

(a) b1 and b0

`b_1 = 3.372767857`

`b_1 \approx 3.4`

`b_0 = 83.65625`

`b_0 \approx 83.7`

(b) The estimated regression equation

The equation is of the form:

`y =b_0 + b_1x`

Where:

`b_1 =3.4`

`b_0 =83.7`

So:

`y=83.7+3.4 x`

(c) The coefficient of determination (r^2)

`r^2 = 0.790361699`

`r^2 \approx 0.790`

(d) Percentage of variation

To do this, we simply convert r^2 to percentage

`\% Variation = r^2 * 100\%`

`\% Variation = 0.790 * 100\%`

`\% Variation = 79.0\%`

(e) Expected annual sales of a sales person with 8 years of experience.

Using the regression equation

`y=83.7+3.4 x`

Where

`x = 8` --- the years of experience.

So;

`y = 83.7 + 3.4 * 8`

`y = 83.7 + 27.2`

`y = 110.9`

The expected earnings is: $110,900