Question

The managers of a brokerage firm are interested in finding out if the number of new clients a broker brings into the firm affects the sales generated by the broker. They sample 12 brokers and determine the number of new clients they have enrolled in the last year and their sales amounts in thousands of dollars. These data are presented in the table that follows.

Broker Clients Sales

1 48 72

2 11 37

3 42 64

4 33 55

5 15 29

6 15 34

7 25 58

8 36 59

9 28 44

10 30 48

11 17 31

12 22 38

Please use Excel to conduct the Simple Linear Regression Analysis on the data set above and show the summary output. Please attach the output. (3 points)

Please use the regression analysis results you get from the information above to answer the following questions. Confidence level is 95%.

5. Please write down the regression equation. (3 points)

6. The individual value prediction for the amount of sales (in $1,000s) for a person who brings 25 new clients into the firm is ________.Please show your calculation. (3 points)

7. What is the total variation of “Sales”? (3 points)

8. What is the variation of “Sales” that can be explained by the regression equation? (3 points)

9. What is the variation of “Sales” that cannot be explained by the regression equation? (3 points)

10. What percentage of the variation in “Sales” is explained by its regression on “number of new clients”? (3 points)

11. What is the coefficient of determination? (3 points)

12. What is the standard error of estimate? (3 points)

13. Is there a significant relationship between the variable “Sales” and the variable “number of new clients” at the 5% significance level? Please explain it. (3 points)

14. What is the calculated value of the t statistic to test the hypothesis for the significance of the coefficient on number of new clients? (3 points)

15. Is the coefficient on number of new clients significantly different from zero? Please explain it. (3 points)

16. What does the 95% confidence interval for the slope of the regression line mean? (3 points)

17. What is the sum of squares of X (SSx) for these data? Please show your calculation, and without calculation you will get zero point. (3 points)

18. Suppose the managers of the brokerage firm want to obtain a 95% confidence interval estimate for the mean sales made by brokers who have brought into the firm 24 new clients. What is the confidence interval estimate? (4 points, please show your calculation, and without calculation you will get zero point.)

19. Suppose the managers of the brokerage firm want to obtain a 95% prediction interval for the sales made by a broker who has brought into the firm 18 new clients, what is the prediction estimate? (4 points, please show your calculation, and without calculation you will get zero point.)

Answer 1

Following is the output obtained using the regression analysis in MS Excel:

Based on output, regression equation can be written as:

Sales ($1,000s) = 17.1750 + 1.1270*New Clients

Substituting New Client = 25 in regression equation, we can determine predicted Sales ($1,000s) as follows:

Sales ($1,000s) = 17.1750 + 1.1270*25

Sales ($1,000s) = 45.35

The individual value prediction for the amount of sales (in $1,000s) for a person who brings 25 new clients into the firm is 45.35

Total Varaition in sales = 2180.9167 (Refer to ANOVA table in output)

The variation of “Sales” that can be explained by the regression equation = 1861.6466 (Refer to ANOVA table in output)

The variation of “Sales” that cannot be explained by the regression equation = 319.2701 (Refer to ANOVA table in output)

The variation in “Sales” explained by its regression on “number of new clients” = (1861.6466/2180.9167)*100

The variation in “Sales” explained by its regression on “number of new clients” = 85.36%

The coefficient of determination = R Square = 0.8536 (Refer to regression statistics table)

The standard error of estimate = 5.6504 (Refer to regression statistics table)

***Dear Student, We can answer 4 sub-parts of a question per post. Please post remaining sub-parts separately***