Simple linear regression analysis using excels.
A brokerage house wants to predict the number of trade executions per day, using the number of incoming phone calls as a predictor variable. Data were collected over a period of 35 days and are stored in the file trades.xls attached.
a. make use of the least-squares method to compute the regression of coefficients b0 and b1.
b. Interpret the meaning of b0 and b1 in this problem.
c. calculate the number of trades executed for a day in which the number of incoming calls is 2,000.
d. Should you use the model to predict the number of trades executed for a day in which the number of incoming calls is 5,000? Why or why not?
e. resolve the coefficient of determination, r2, and explain its meaning in this problem.
f. plan the residuals against the number of incoming calls and also against the days. Is there any evidence of a pattern in the residuals with either of these variables? Explain.
g. resolve the Durbin-Watson statistic for these data.
h. Based on the results of (6) and (7), is there reason to question the validity of the model? Explain.
i. At 0.05 level of significance, is there evidence of a linear relationship between the volume of trade executions and the number of incoming calls?
j. Construct a 95 percent confidence interval estimate of the mean number of trades executed for days in which the number of incoming calls is 2,000.
k. Construct a 95 percent prediction interval of the number of trades executed for a particular day in which the number of incoming calls is 2,000.
l. Construct a 95 percent confidence interval estimate of the population slope.
m. Based on the results of (1) through (9), do you think the brokerage house should focus on a strategy of increasing the total number of incoming calls or on a strategy that relies on trading by a small number of heavy trades? Explain.
|
Day
|
Calls
|
Trade Executions
|
X
|
Y
|
X2
|
XY
|
|
1
|
2591
|
417
|
2591
|
417
|
6713281
|
1080447
|
|
2
|
2146
|
321
|
2146
|
321
|
4605316
|
688866
|
|
3
|
2185
|
362
|
2185
|
362
|
4774225
|
790970
|
|
4
|
2245
|
364
|
2245
|
364
|
5040025
|
817180
|
|
5
|
2600
|
442
|
2600
|
442
|
6760000
|
1149200
|
|
6
|
2510
|
386
|
2510
|
386
|
6300100
|
968860
|
|
7
|
2394
|
370
|
2394
|
370
|
5731236
|
885780
|
|
8
|
2486
|
376
|
2486
|
376
|
6180196
|
934736
|
|
9
|
2483
|
463
|
2483
|
463
|
6165289
|
1149629
|
|
10
|
2297
|
389
|
2297
|
389
|
5276209
|
893533
|
|
11
|
2106
|
302
|
2106
|
302
|
4435236
|
636012
|
|
12
|
2035
|
266
|
2035
|
266
|
4141225
|
541310
|
|
13
|
1936
|
339
|
1936
|
339
|
3748096
|
656304
|
|
14
|
1951
|
369
|
1951
|
369
|
3806401
|
719919
|
|
15
|
2292
|
403
|
2292
|
403
|
5253264
|
923676
|
|
16
|
2094
|
319
|
2094
|
319
|
4384836
|
667986
|
|
17
|
1897
|
306
|
1897
|
306
|
3598609
|
580482
|
|
18
|
2237
|
397
|
2237
|
397
|
5004169
|
888089
|
|
19
|
2328
|
365
|
2328
|
365
|
5419584
|
849720
|
|
20
|
2078
|
330
|
2078
|
330
|
4318084
|
685740
|
|
21
|
2134
|
312
|
2134
|
312
|
4553956
|
665808
|
|
22
|
2192
|
340
|
2192
|
340
|
4804864
|
745280
|
|
23
|
1965
|
339
|
1965
|
339
|
3861225
|
666135
|
|
24
|
2147
|
364
|
2147
|
364
|
4609609
|
781508
|
|
25
|
2015
|
295
|
2015
|
295
|
4060225
|
594425
|
|
26
|
2046
|
292
|
2046
|
292
|
4186116
|
597432
|
|
27
|
2073
|
379
|
2073
|
379
|
4297329
|
785667
|
|
28
|
2032
|
294
|
2032
|
294
|
4129024
|
597408
|
|
29
|
2108
|
329
|
2108
|
329
|
4443664
|
693532
|
|
30
|
1923
|
274
|
1923
|
274
|
3697929
|
526902
|
|
31
|
2069
|
326
|
2069
|
326
|
4280761
|
674494
|
|
32
|
2061
|
306
|
2061
|
306
|
4247721
|
630666
|
|
33
|
2010
|
352
|
2010
|
352
|
4040100
|
707520
|
|
34
|
1913
|
290
|
1913
|
290
|
3659569
|
554770
|
|
35
|
1904
|
283
|
1904
|
283
|
3625216
|
538832
|
|
|
|
|
75483
|
12061
|
164152689
|
26268818
|
|
Average
|
2156.657
|
344.6
|
|
|
200.1277
|
47.65266
|