Problem: This problem should be solved using a spreadsheet. In Excel you can use the fx function to estimate the coefficients of a line.
Within the STATISTICAL functions are separate programs for estimating SLOPE and INTERCEPT. There also is a program called LINEST which does everything you need all at once.
You are given the following data and asked to determine the beta coefficient and the alpha coefficient for IBM and General Electric stock. If you assume that past history is indicative of future expectations and covariances, were IBM and GE good buys at the end of this data set (I.e. in January 2007)? Why or why not? The one thing you cannot use in your answer is what has happened to these stocks since December 2006. That is, you cannot peak ahead. The numbers are monthly returns, not annual returns (in %). The alpha coefficient you get should also refer to monthly returns.
|
|
IBM
|
GE
|
Market
|
T-Bills
|
|
|
|
|
|
|
|
|
|
Jan-05
|
3.548
|
4.678
|
6.21
|
0.421
|
|
|
Feb-05
|
-8.367
|
-0.604
|
0.81
|
0.417
|
|
|
Mar-05
|
-4.522
|
-3.524
|
-4.16
|
0.428
|
|
|
Apr-05
|
16.94
|
11.839
|
5.97
|
0.431
|
|
|
May-05
|
7.788
|
8.784
|
6.14
|
0.428
|
|
|
Jun-05
|
4.335
|
7.66
|
4.46
|
0.410
|
|
|
Jul-05
|
17.175
|
7.885
|
7.94
|
0.422
|
|
|
Aug-05
|
-4.137
|
-10.784
|
-5.56
|
0.428
|
|
|
Sep-05
|
4.562
|
8.791
|
5.48
|
0.414
|
|
|
Oct-05
|
-7.075
|
-5.051
|
-3.34
|
0.413
|
|
|
Nov-05
|
11.168
|
14.313
|
4.63
|
0.412
|
|
|
Dec-05
|
-4.452
|
-0.677
|
1.72
|
0.429
|
|
|
Jan-06
|
-5.615
|
5.622
|
1.11
|
0.424
|
|
|
Feb-06
|
5.759
|
0.323
|
7.21
|
0.424
|
|
|
Mar-06
|
-0.539
|
10.852
|
5.12
|
0.419
|
|
|
Apr-06
|
11.552
|
-1.16
|
1.01
|
0.417
|
|
|
May-06
|
1.402
|
-2.128
|
-1.72
|
0.417
|
|
|
Jun-06
|
-2.287
|
8.996
|
4.06
|
0.417
|
|
|
Jul-06
|
15.406
|
-1.582
|
-1.07
|
0.413
|
|
|
Aug-06
|
-15
|
-10.552
|
-14.46
|
0.412
|
|
|
Sep-06
|
14.095
|
-0.547
|
6.41
|
0.395
|
|
|
Oct-06
|
15.564
|
9.976
|
8.13
|
0.340
|
|
|
Nov-06
|
11.195
|
3.286
|
6.06
|
0.370
|
|
|
Dec-06
|
11.658
|
12.863
|
5.76
|
0.368
|
|