For this project you will assume the role of a portfolio manager. As part of your new assignment, assume that you have six stocks under management. For example, you may use the 6 largest long positions in your Stock-Trak portfolio for this purpose. Your task is to determine how your six stocks are performing applying a Markowitz mean-variance framework.

Portfolio Theory Analysis

Perform a portfolio analysis for your six firms that comprise your portfolio using the spreadsheet I presented in class as a template. Stock price data can be downloaded from Yahoo Finance or Bloomberg terminals. If using Yahoo finance data, go to the historical price link and download Adj. Closing Price for the stocks. For the six stocks that you select, download 61 monthly stock prices ending December 2022. Please keep to my Excel template because I find it easier to read after you hand it in when the format is just as I have specified (using a spreadsheet that is worse or different than what I did in class is not a good idea). Some notes/hints/reminders on the spreadsheet and on this valuation assignment:

First off, be sure to reproduce my results in my spreadsheet. To reproduce results you will have to fill in formulas into relevant cells to get the same numerical answers as shown in cell. I have separate tabs for the separate analysis that I have performed so you should be able to reproduce my results before your forge ahead with your analysis.

You will run all analyses on your stocks using monthly prices ending December 2022. You will have 60 monthly returns from January 2018 to December 2022.

You will need to be able to manipulate SOLVER for this analysis. I have not coded any macros, but if you do and it improves the analysis then this will be very impressive.

I was forced to abbreviate the tabs so the required ones are mean and SD, variance covariance matrix, global minimum variance portfolio, optimal risky portfolio, efficient frontier inputs, graph of efficient frontier (CAL) with individual assets plotted.

I want each group to determine the optimal risky portfolio and the slope of the CAL for two situations. Your first set of analysis will focus on the assumption that there are no short sale constraints on your holdings and one smaller set of analysis that will focus on the effect of a short sale constraint on your performance.

Finally, analyze how a portfolio of six ETFs would perform. Put these into a SEPARATE spreadsheet and perform an efficient frontier and Sharpe Ratio analysis for these six ETFs. ETF data can be downloaded from Bloomberg terminals or Yahoo Finance. For this purpose, use the following ETFs.

Use the following six sector SPDR ETFs for your analysis. Details on sector ETFs are available under U.S. equities tab at https://us.spdrs.com/en/product/.

XLK: Technology Select Sector SPDR Fund

XLY: Consumer Discretionary Select Sector SPDR Fund

XLI: Industrial Select Sector SPDR Fund

XLF: Financial Select Sector SPDR Fund

XLV: Health Care Select Sector SPDR Fund

XLP: Consumer Staples Select Sector SPDR Fund

What you turn in

Q1 What was your criterion (criteria) for each stock that was selected? Does your optimal risky portfolio perform better in terms of risk and return than each individual stock?

Q2. You should include a brief description of the effect of short-sale constraints on your results. Do short sales affect your optimal portfolio results? Should they? Why or why not?

To do this, all you have to do is manipulate the Make unconstrained variables non-negative checkbox of Solver. If short sale constraints exist, then you need to check the box which will force excel to give only positive weights. If you uncheck the box, short sales are allowed and Excel will allow negative weights in solving the optimization.