Project 2: Historical Analysis Project This is the time to apply in practice what you learned about portfolio theory. Choose two stocks from your portfolio (see attached documents) for an historical analysis. Download into a spreadsheet the weekly closing prices for these two stocks for 11/19/2012 – 11/18/2013 (one year) from Yahoo or Google, along with the dividend amounts and dates. Do the same for the S&P 500 index. Due Date: 12/04/2013. Calculations: Calculate the following for each asset (in Excel, using the statistical functions given in parentheses): 1. Calculate the average return (AVERAGE), standard deviation of returns (STDEV), and variance of returns (VAR) for each stock and the S&P 500 index. 2. Calculate the covariance (COVAR) and correlation (CORREL) between the returns of stock 1 and stock 2. Remember to adjust the COVAR since the Excel functions divides by n rather than n-1. 3. Calculate the return and the standard deviation of a portfolio that held these two stocks in the following weights: (-30)%-130%, …0%-100%, 10%-90%, 20%-80%, 30%-70%,…, 90%-10%, 100%-0%, …, 130%-(-30)%. 4. Plot these portfolio returns – standard deviation combinations (Important: choose a “scatterplot” and not a “line”!). 5. Calculate the weights for the minimum variance portfolio, and then solve for the return and standard deviation. Mark this on your plot. 6. Calculate the optimal risky portfolio’s weights in the two stocks, its average return, and its standard deviation. Mark this on your plot as well. 7. Calculate the Sharpe ratio for each combination. 8. Assuming the weekly risk-free rate is 0.1%, on your plot, plot or draw the optimal CAL and mark the optimal risky portfolio. 9. Plot two additional investment opportunity sets – first assuming the correlation of your stocks was exactly 1, and second, assuming the correlation was –1. Written Report: Address the following questions in a one-page, single-spaced written report. Note: this should be a short essay that describes your results. Assume that you are preparing a primer on portfolio composition and analysis for the new members of your investment committee. This is an opportunity to practice writing about financial results. You will be graded on the quality of your writing as well as the quality of your analysis. Do not just answer the questions! This is an essay for your investment committee. 1. Looking at the statistics for your two stocks, does the risk-return trade-off hold? Which combinations should you avoid? Why? 2. Look at the amount you invested in stock 1 and stock 2 at the beginning of the semester. Assume, for this assignment, that these stocks are your entire portfolio. a. Which combination would deliver the least amount of risk? b. Which would deliver the maximum return per unit of risk? 3. Looking at the CAL you have drawn and your calculations for the minimum risk and optimal portfolios, what conclusions can you draw about the efficiency of the combination you chose? Deliverables: 1. A hard copy of your plot and your written analysis. 2. E-mail the Excel file you worked with to 12/04/2013. Please name your stock_analysis.xls. Make sure your Excel file is well-organized.

Yahoo Finance
Total Value Of Portfolio
Minimum Investment
Comission on each trade
Trading Permitted
Number of stocks in portfolio
Maximum number of traders permitted
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Cash Available
Total Value Of Portfolio
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
Portfolio As On
Name Of Stock
Apple Inc
The Boeing Company
The Walt Disney Company
General Motors Company
Johnson & Johnson
Total
Total Portfolio Value Including Cash
Overall Absolute Portfolio Gain
Overall Portfolio Percentage Gain
=
=
=
=
=
=
$100,000
$90,000
$10
11/09/2013 to 11/18/2013
5
5
Times
11/7/2013
Ticker Symbol
Closing Price
11/06/2013
Number of
Stocks
Commission
(Purchase)
AAPL
BA
DIS
GM
JNJ
$520.92
$133.09
$69.00
$36.59
$93.04
50
100
250
300
300
$10.00
$10.00
$10.00
$10.00
$10.00
Ticker Symbol
Closing Price
11/07/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$512.49
$131.51
$67.15
$35.92
$92.69
50
100
250
300
300
$$$$$-
Ticker Symbol
Closing Price
11/08/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$520.56
$133.49
$68.58
$36.66
$94.05
50
100
250
300
300
$$$$$-
Dividend
Total Initial Closing Price Received
During
Cost
11/07/2013
Period
$26,056.00
$512.49
3.05
$13,319.00
$131.51
0.485
$17,260.00
$67.15
0
$10,987.00
$35.92
0
$27,922.00
$92.69
0
$95,544
% of
Return Total Value of
Portfolio
-1.03%
$25,624.50
-0.82%
$13,151.00
-2.68%
$16,787.50
-1.83%
$10,776.00
-0.38%
$27,807.00
$94,146
Dividend
Total Initial Closing Price Received
During
Cost
11/08/2013
Period
$25,624.50
$520.56
0
$13,151.00
$133.49
0
$16,787.50
$68.58
0
$10,776.00
$36.66
0
$27,807.00
$94.05
0
$94,146
% of
Return Total Value of
Portfolio
1.57%
$26,028.00
1.51%
$13,349.00
2.13%
$17,145.00
2.06%
$10,998.00
1.47%
$28,215.00
$95,735
Dividend
Total Initial Closing Price Received
During
Cost
11/11/2013
Period
$26,028.00
$519.05
0
$13,349.00
$132.53
0
$17,145.00
$68.34
0
$10,998.00
$36.68
0
$28,215.00
$94.29
0
$95,735
% of
Return Total Value of
Portfolio
-0.29%
$25,952.50
-0.72%
$13,253.00
-0.35%
$17,085.00
0.05%
$11,004.00
0.26%
$28,287.00
$95,582
Dividend
Total Initial Closing Price Received
During
Cost
11/12/2013
Period
$25,952.50
$520.01
0
$13,253.00
$132.33
0
$17,085.00
$67.77
0
$11,004.00
$36.66
0
$28,287.00
$93.56
0
$95,582
% of
Return Total Value of
Portfolio
0.18%
$26,000.50
-0.15%
$13,233.00
-0.83%
$16,942.50
-0.05%
$10,998.00
-0.77%
$28,068.00
$95,242
Dividend
Total Initial Closing Price Received
During
Cost
11/13/2013
Period
$26,000.50
$520.63
0
$13,233.00
$133.17
0
$16,942.50
$68.97
0
$10,998.00
$38.44
0
$28,068.00
$93.34
0
$95,242
% of
Return Total Value of
Portfolio
0.12%
$26,031.50
0.63%
$13,317.00
1.77%
$17,242.50
4.86%
$11,532.00
-0.24%
$28,002.00
$96,125
Dividend
Total Initial Closing Price Received
During
Cost
11/14/2013
Period
$26,031.50
$528.16
0
$13,317.00
$135.09
0
$17,242.50
$70.02
0
$11,532.00
$38.50
0
$28,002.00
$93.92
0
$96,125
% of
Return Total Value of
Portfolio
1.45%
$26,408.00
1.44%
$13,509.00
1.52%
$17,505.00
0.16%
$11,550.00
0.62%
$28,176.00
$97,148
Dividend
Total Initial Closing Price Received
During
Cost
11/15/2013
Period
$26,408.00
$524.99
0
$13,509.00
$136.08
0
$17,505.00
$70.00
0
$11,550.00
$38.77
0
$28,176.00
$94.39
0
$97,148
% of
Return Total Value of
Portfolio
-0.60%
$26,249.50
0.73%
$13,608.00
-0.03%
$17,500.00
0.70%
$11,631.00
0.50%
$28,317.00
$97,306
Dividend
Total Initial Closing Price Received
During
Cost
11/18/2013
Period
$26,249.50
$518.63
0
$13,608.00
$138.36
0
$17,500.00
$69.50
0
$11,631.00
$38.57
0
$28,317.00
$94.30
0
$97,306
% of
Return Total Value of
Portfolio
-1.21%
$25,921.50
1.68%
$13,826.00
-0.71%
$17,365.00
-0.52%
$11,561.00
-0.10%
$28,280.00
$96,954
$4,456
$98,602
$(1,398)
-1.46%
11/8/2013
$100,191
$1,589
1.69%
11/11/2013
$100,038
$(154)
-0.16%
11/12/2013
Ticker Symbol
Closing Price
11/11/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$519.05
$132.53
$68.34
$36.68
$94.29
50
100
250
300
300
$$$$$-
$99,698
$(340)
-0.36%
11/13/2013
Ticker Symbol
Closing Price
11/12/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$520.01
$132.33
$67.77
$36.66
$93.56
50
100
250
300
300
$$$$$-
$100,581
$883
0.93%
11/14/2013
Ticker Symbol
Closing Price
11/13/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$520.63
$133.17
$68.97
$38.44
$93.34
50
100
250
300
300
$$$$$-
$101,604
$1,023
1.06%
11/15/2013
Ticker Symbol
Closing Price
11/14/2013
Number of
Stocks
Commission
AAPL
BA
DIS
GM
JNJ
$528.16
$135.09
$70.02
$38.50
$93.92
50
100
250
300
300
$$$$$-
$101,762
$158
0.16%
11/18/2013
Ticker Symbol
Closing Price
11/15/2013
Number of
Stocks
Commission
(sales)
AAPL
BA
DIS
GM
JNJ
$524.99
$136.08
$70.00
$38.77
$94.39
50
100
250
300
300
$10.00
$10.00
$10.00
$10.00
$10.00
$101,410
$(352)
-0.36%
Total units of index purchased using
Cash balance
Date
November 6, 2013
November 7, 2013
November 8, 2013
November 11,2013
November 12,2013
November 13,2013
November 14,2013
November 15,2013
November 18,2013
53.66
4456
Value of index (including cash)
$99,463.38
$98,210.92
$99,469.82
$99,538.51
$99,313.13
$100,081.03
$100,543.59
$100,949.27
$100,592.42
Return
-1.26%
1.28%
0.07%
-0.23%
0.77%
0.46%
0.40%
-0.35%
Name Of Company / Date
November 6, 2013
November 7, 2013
November 8, 2013
November 11,2013
November 12,2013
November 13,2013
November 14,2013
November 15,2013
November 18,2013
AAPL
520.92
512.49
520.56
519.05
520.01
520.63
528.16
524.99
518.63
Closing Prices
BA
DIS
GM
133.09
69
36.59
131.51
67.15
35.92
133.49
68.58
36.66
132.53
68.34
36.68
132.33
67.77
36.66
133.17
68.97
38.44
135.09
70.02
38.5
136.08
70
38.77
138.36
69.5
38.57
Closing prices
S&P index/Date
November 6, 2013
November 7, 2013
November 8, 2013
November 11,2013
November 12,2013
November 13,2013
November 14,2013
November 15,2013
November 18,2013
1770.49
1747.15
1770.61
1771.89
1767.69
1782
1790.62
1798.18
1791.53
JNJ
93.04
92.69
94.05
94.29
93.56
93.34
93.92
94.39
94.3
AAPL
3.05
BA
0.485
Dividend
DIS
0
GM
JNJ
0
0
Cash balance
4456
Date
November 7, 2013
November 8, 2013
November 11,2013
November 12,2013
November 13,2013
November 14,2013
November 15,2013
November 18,2013
Value of index (including cash)
$98,602.00
$100,191.00
$100,037.50
$99,698.00
$100,581.00
$101,604.00
$101,761.50
$101,409.50
Return
-1.46%
1.69%
-0.16%
-0.36%
0.93%
1.06%
0.16%
-0.36%
Average return of portfolio and S&P 500 index
Date
Portfolio return
Index return
7-Nov
8-Nov
11-Nov
12-Nov
13-Nov
14-Nov
15-Nov
18-Nov
-1.46%
1.69%
-0.16%
-0.36%
0.93%
1.06%
0.16%
-0.36%
-1.26%
1.28%
0.07%
-0.23%
0.77%
0.46%
0.40%
-0.35%
Average return
Standard deviation
Variance
Covariance
Beta of portfolio
0.19%
1.00%
0.010%
0.000%
0.00
0.14%
0.78%
0.006%
2.00%
1.50%
1.00%
0.50%
Column D
0.00%
6-Nov
-0.50%
-1.00%
-1.50%
-2.00%
8-Nov
10-Nov
12-Nov
14-Nov
16-Nov
18-Nov
20-Nov
Column C
Apple Inc (AAPL)
Fundamental Analysis
Technical Analysis
Boeing Company
Fundamental Analysis
Technical Analysis
Apple Inc (AAPL)
Fundamental Analysis
Technical Analysis
General Motors Company
Fundamental Analysis
Technical Analysis
Johnson & Johnson
Fundamental Analysis
Technical Analysis