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

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)

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)

The individual value prediction for the amount of sales (in $1,000s)

y = 1.127x + 17.175

x = 1000

y = 1.127*1000 + 17.175

y = 1144.18

prediction for the amount of sales (in $1,000s) = 1144.18