finance

Project Pre-work

For the project, you will be analyzing 2 portfolios. The first will have SEVEN stocks and the second will have EIGHT.

I have picked four of the stocks for the first and five for the second for every student. You will find that sheet in the Project Folder.

For this pre-work, you are to pick THREE more stocks that will complete each portfolio. These three stocks will be part of BOTH portfolios.

So you only need to pick 3 stocks – BUT there are two important restriction. First - they cannot be in the “universe” of stocks from which I devised the picks for each student.

So check the tickers against the second tab of the stock pick list in the project folder.

Second – they MUST be traded throughout the period we are going to use, which is 1/6/2014 to 5/22/2017(Weekly returns, NOT Daily)

Send the tickers symbols in the body of an email with the subject Pre-Work and your name.

I just need the ticker symbols – no other project work is assigned at this time.

To see if your stocks trade throughout that period – please use Finance.Yahoo.Com (note that the spreadsheet I showed you earlier this quarter for bulk download no longer works. It appears Yahoo changed something.)

Don’t assume that just because it’s a major company, it has traded for all the period.

You are welcome to pick stocks that another student picks – I’ve assigned the others such that each portfolio will be unique.

Introduction

You will create two portfolios. Each portfolio will contain the three stocks you chose as part of the project pre-work. The first portfolio will then contain 4 additional assets chosen for you. Portfolio 2 will contain 5 other assets chosen for you. See the Stock Selections sheet in the Project folder.

Be sure none of your three stocks are in the list of stocks I for the entire class (as discussed in weeks 9 and 10). If so, you need to change it (or them) and let me know. If you don’t change it, there will be a FIVE point deduction. No exceptions.

Use the Project Template in the Project folder; it is set up similar to templates used in prior weeks’ classes.(NOTE – you MUST use this template. DO NOT create your own.) Don’t skip any parts below – especially DO NOT assume that what follows is exactly like prior work. And DO NOT assume it’s like projects from prior quarters. If you have any work in here that was assigned then but not now you will be subject to an academic integrity violation.

NOTE – Be sure the template is set for Auto-Calculation. Go to File-Options-Formula and make sure Automatic is selected under Workbook Calculation.

Also, I suggest you read through this entire document first (it will have been covered in class) before you start. I understand it’s tempting to simply get started and take it step by step – but I think it makes more sense to see where it’s going before you get started.

Finally – FOLLOW the instructions. In many cases I’m asking you answer by using a formula or function – NOT by typing in the answer.

Part 1 of 4 (7 points total):

1. Get weekly prices from 1/3/2014 to 5/19/2017. Please see the Word document “Obtaining Prices for FIN 202 Project” in the project folder. Since Yahoo Finance stopped supporting bulk price downloads, I’ve found an alternate method – though less convenient than demonstrated earlier in the quarter.

(NOTE: I checked all the assigned stocks to be sure they had prices for the time period we are using. But at times external sources have issues – if one of your stocks does not have all the necessary prices – please select another one from the S&P 500 (preferably from the same sector) and let me know via email. No point deductions for that.)

2. Sort the data into the correct order (note – if you use the downloader, they will have been sorted already. Find the prices in the far right tab called Close – near the end. )

3. Calculate the returns for each stock

(4 points total for 1 - 3)

ANOTHER ACCURACY NOTE – If you get the wrong prices, fail to sort correctly, and/or use an incorrect formula for the returns, there will be large deductions in this section. ANY error here will result in incorrect returns, which would obviously result in misleading investment advice. If you do in fact have wrong answers, I will grade the rest of the project as if those wrong answers were correct – but there will be large deductions if the returns are simply not logical.

How can you tell if it’s correct? First of all, it’s only logical that prices go UP over the three years this project represents. If any of your stocks show a decline over time, you probably forgot to sort – or sorted only the dates but not the actual prices.

Second – again logically, you won’t have a return in the first week. You just bought the stock then. Your first return is in the second week.

Next – the % return is the change in price from one week to the next in terms of the PRIOR week’s price. Once again – that’s logical. If my week 1 price is $10 and week 2 is $20, then what’s my return? Obviously 100%. But if you did it wrong, say (week2–week1) divided by week2, it would come out to be 50% - which makes no sense.

Bottom line – it is VITAL when using Excel here (and everywhere) that you THINK after you are done entering a formula: Does this result make sense?

Failure to see illogical results here and in other sections may result in 0 points for that section.

4. Add the sheet called constituents financialsfor project in the Project folder to your project template and sort this by Price / Earnings ratio (high to low). Also, make sure it is formatted to be readable (HINT – so when you scroll, you can see the titles).

(1 point)

5. Lookup the sectorsusing VLOOKUP for all yourstocks against the “constituents” list. Note that if any of your stocks are not part of the S&P, you need to find its sector from a site like Yahoo Finance- you can then simply type it in. Make sure it’s one of the eleven sectors listed in the S&P data (see Part 3). If not, use the one that seems to match best

(1 point)

6. Calculate the mean, SD, and modified Sharpe Ratio for each of the returns (the modified Sharpe Ratio is the mean divided by the SD).

(1 point)

Format the sheets for convenient reading, including freezing. Make sure you name your Excel file per the instructions above. Up to 3 points can be deducted for poor formatting. Remember also that every tab should have a short name describing its content (not Sheet 1 or Chart 1 etc.)

Part 2 of 4 (11 points total):

1.Weight theassetsin each portfolio somewhat evenly at first (make sure the weights add to 100%) and calculate the weighted average portfolio week by week using the SUMPRODUCT function.We will refer to this series of weighted average returns as “the portfolio”. (You should notice the SUMPRODUCT formula is there already. Therefore, no points, but it will be needed for the optimization step later)

2. Calculate the mean, SD and (modified) Sharpe Ratio for each portfolio. Make sure this makes sense. The mean will be between that of the lowest and highest for the stocks. The SD should be lower than that of most of the assets. If you have some strange portfolio mean like 32847%, the rest of the project will receive very few points.

(1 point)

3. Using Solver, find weights that maximize each portfolio’s (modified) Sharpe Ratio, subject to the constraints that each weight must be >=5% and the weights must sum to 100%.

(2 points)

4. Build a correlation table on another sheet for the returns of the assetsof each portfolio (using the correlation function in the Data Analysis ToolPak).That is, you will have two correlation tables. Name this sheet Correlations. Make sure the tickers are used for titles rather than column 1, column 2, etc. and label each table by the portfolio number. We will consider a “weak” correlation to be between -0.3 and +0.3. Use conditional formatting to highlight the weak correlations.

(2 points)

5. Perform a simple linear regression for each optimized portfolio – use the portfolio returns as the Y variable and the S&P returns as the X variable (using the regression function in the Data Analysis ToolPak). Name those sheets CAPM 1 and CAPM 2.(No need to include the charts of the line fit and residuals, though that is always a good idea.)

(2 points)

6. To help in your analysis, the template is automatically calculating a 12 week moving average of each portfolio, and the template contains the 12 week MA for the S&P. Create a simple line chart with THREE lines: The 12 week S&P Moving Average, and the 12 week Moving Average series for each of the two portfolios. Make sure this chart is on its own tab, named logically, and has a legend.

(2 points)

7. There is a tab called Asset summaries. You need to populate this – use the titles as a guide. Make sure you have the tickers and not “Asset 1, Asset 2 etc.) SORT each series of columns for Port 1 and Port 2 by the Sharpe Ratio. Make sure you set this up such that if you changed one or more asset in portfolio, this page automatically changes. Then – using a simple IF statement, determine which of the two portfolios has the better Sharpe Ratio.

(2 points)

Part 3 of 4 (1 point total):

1. Take the data in the Constituents tab and create apivot table on another page that displays – by sector – the AVERAGE for Price/Earnings. Sort it by high to low. This table will be used in a question below.

(1 point)

2. Name this sheet something that tells the reader what you are seeing. I suggest Average Financials by Sector. No points, but a format deduction if you fail to do this.

Part 4 of 4 (11 points total):

Look at the Answer tab on the Project Template. The answers to these questions must go there –and the may be typed in (that is, the formulas used to find the answer do not need to be here).

1. What is the probability of the portfolio with the higher mean outperforming the other portfolio?

2. What is the probability portfolio 1 outperforming the S&P?

3. What is the probability portfolio 2 outperforming the S&P?

Probability questions are answered using the Difference between Two Means (paired) tab in the Statistics Template.

(3 points total)

4. Considering portfolio 1, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same?

5. Considering portfolio 2, is the risk measured by Beta less than the S&P, greater than the S&P, or essentially the same?

First – use the Normal Distribution tab in the Statistics Template. The regression results will show you the SE of Beta. Plug in the value for Beta as the mean, the SE in the “SD or SE” cell, and 1 as the critical value. We will use 80% as our (arbitrary) cutoff. If the Prob > CV is 80% or more, we say Beta is significantly greater than 1, and therefore the portfolio is riskier than the S&P. If the Prob < CV is 80% or more, we say Beta is significantly less than 1, and therefore the portfolio is less risky than the S&P. If neither are true, we say the portfolio has the same risk as the S&P.

(2 points total)

6. Considering portfolio 1, is the excess return measured by Alpha less than the 0, greater than 0, or essentially 0?

7. Considering portfolio 2, is the excess return measured by Alpha less than the 0, greater than 0, or essentially 0?

Again – use the Normal Distribution tab in the Statistics Template. The regression results will show you the SE of Alpha. Plug in the value for Alpha as the mean, the SE in the “SD or SE” cell, and 0 as the critical value. We will use 80% as our (arbitrary) cutoff. If the Prob > CV is 80% or more, we say Alpha is significantly greater than 0, and therefore has an excess return not related to the S&P. If the Prob < CV is 80% or more, we say Alpha is significantly less than 0, and therefore has a loss not related to the S&P. (NOTE – this is very unlikely to happen). If neither is true, we say the portfolio does not have a return or loss unrelated to the S&P.

(2 points total)

8. What do you conclude about the diversification of portfolios 1 and 2 based on Correlations and SD?

We want more than half the correlations to be between -0.3 and +0.3 in order to say there are diversification benefits due to correlations.

And we want the SD of the portfolio to be less than 4 or more stocks’ SD for the seven stock portfolio and less than 5 or more stocks’ SD for the eight stock portfolio to say there are diversification benefits due to SD.

(2 points)

9. Considering everything above, which portfolio would you choose? (Only 1 or 2 - you can't choose the S&P.) List ONE reason based on the above

The types of things you’d want to consider are risk, return, diversification, comparison to the S&P etc. No particular correct answers here, just make sure they are based only on the statistics you see, and your interpretation of those statistics makes sense.

(1 point)

10. Which stock is the weakest in that portfolio (based on the Sharpe Ratio) – and would you say it is sector based or stock based?

If the sector of that stock is in the last five of theeleven sectors for the P/E then it is likely sector based. If not – then assume its stock based. (DON’T GUESS - You’ll have to use the pivot table create in Part 3.Without that, you’ll receive no points for this question.)