Use the historical data and template provided to create a model for the analysis of 2-securities portfolio. 1. Create 'drop down' menu and use LOOKUP command so you can choose any 2 securities for the analysis 2. Calculate Summary Statistics: Average, Variance, St. Dev., Covariance, Correlation 3. Construct Opportunity Set Table 4. Find Optimal Risky Portfolio 5. Create Opportunity Set Graph, including Opportunity Set, each one of the 2 securities with labels, and Capital Asset Line 6. Complete Quiz Project Part 1 on Canvas based on the answers from your excel template Select XLU and XLK for your portfolio from your drop down menu, answer all the questions based on these two securities. 3 points each question. 1. What is the St. Dev. for XLK: a. 2.23% b. 6.18% c. 3.82% d. 6.14% 2. What is the Covariance between XLU and XLK: a. 0.00238 b. 0.00382 c. 0.00113 d. 0.00098 3. What is the correlation between XLU and XLK: a. 0.3735 b. 0.3247 c. 0.1237 d. 0.4864 4. What does correlation tell you about the relation between XLU and XLK: a. their returns tend to move very similar to each other b. their returns tend to move somewhat independently from each other c. their returns tend to move in the opposite direction relative to each other d. can not tell

5. What is the return on the portfolio that has 40% invested into XLU and 60% into XLK: a. 2.23% b. 1.31% c. 0.92% d. 1.71% e. 1.97% 6. What is the St. Dev. of the portfolio that has 40% invested into XLU and 60% into XLK: a. 6.18% b. 4.79% c. 4.46% d. 4.88% e. 5.39% 7. What are the investment proportions into XLU and XLK for the Minimum Variance Portfolio? a. 0.4 / 0.6 b. 0.2 / 0.8 c. 0.1 / 0.9 d. 0.7 / 0.3 e. 0.5 / 0.5 8. What is the maximum Sharpe Ratio? a. 0.3604 b. 0.3705 c. 0.3646 d. 0.2930 e. 0.3411 9. In order to achieve Optimal Risky Portfolio, how would you split your investment money between XLU and XLK? a. 0.3 / 0.7 b. 0.4 / 0.6 c. 0.5 / 0.5 d. 0.2 / 0.8 e. 0.1 / 0.9 10. What is the proper finance name for the line that connects Rf security and the Optimal Risky Portfolio? a. ORP line b. Capital Asset Line c. Tangency line d. Risk Free line e. Final Combination Line

Ticker

ETFs

Date

0

0

SPY

S&P 500, Large Cap

10/1/18

Average

MDY

S&P Mid Cap

11/1/18

Variance

IWM

Russell 2000, Small Cap

12/1/18

St. Dev.

LQD

Invest. Grade Corp. Bond

1/1/19

HYG

High Yield Corp. Bond

2/1/19

Variance - Covariance Matrix

VT

Total World Stock

3/1/19

0

0

VGK

FTSE Europe

4/1/19

0

VWO

FTSE Emerging Markets

5/1/19

0

XLY

Cons. Disc

6/1/19

XLP

Cons. Staples

7/1/19

XLE

Energy

8/1/19

Rf =

0.008%

XLF

Financials

9/1/19

XLU

Utilities

10/1/19

XLB

Materials

11/1/19

XLV

Healthcare

12/1/19

Optimal Risky Portfolio

XLI

Industrials

1/1/20

Sharpe Ratio

W ( 0 )

W ( 0 )

R (P)

Var (P)

St. Dev. (P)

XLK

Info Tech

2/1/20

ORP ->

XTL

Telecom

3/1/20

Rf ->

XLRE

Real Estate

4/1/20

PBW

Clean Energy

5/1/20

6/1/20

7/1/20

8/1/20

9/1/20

10/1/20

11/1/20

12/1/20

1/1/21

2/1/21

3/1/21

4/1/21

5/1/21

6/1/21

7/1/21

8/1/21

9/1/21