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%.

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

Answer 1

Go to data tab in excel --> choose data analysis -->Regression

Output:

Confidence level: n-2

consider slope of the coefficient and standard error coefficient from regression output

clients 1.127 ± t *0.148

t = df = n-2; n = 12

t = 12-2 = 10

1.127 ± 2.2281*0.148

Confidence interval ( 0.798< mu < 1.456)

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

R-squared value in the regression output is 0.8536, so 85.36 % of the variation in the company sales can be explained by the variation.

14.64 % is the unexplained variation