The stock information use should be from Jan 1 2015- April 30 2018. FIN 202: Quantitative Methods in Finance (Spring 2018)
Final Project

Please read these instructions carefully. You are responsible for following them and accepting point penalties that may occur from not following them. Also, even if these instructions are being made available early, I will not accept any projects until the day after your week 10 class at the earliest. It’s important that you attend class in week 10 as we walk through these instructions.

This 30 point project is due as an Excel Workbook no later than 7:00PM the week after our last class. That’s June 5 for the Tuesday section and June 6 for the Wednesday section.
One point will be deducted for every 15 minutes the project is late. So for example if it’s an hour late, the maximum points will be 26. NO EXCEPTIONS.

You must save your Excel workbook with a name that indicates both that it’s the final project and who you are. The subject on the email should be the same as the title of your project workbook. (There are points that can be lost based on formatting in general, and this is one area where that will be assessed.)

General Comments
This project incorporates many of the functions, formulas, and techniques learned throughout the quarter. There is nothing here that had not been covered as of the end of Week 10. However, some of those Excel topics might be applied in a different manner here. And it won’t always be spelled out EXACTLY what Excel technique to use.

While most of the documents you will need to help you can be found in the Project folder, don’t forget to refer to examples, HWs, and other materials from prior weeks for help.

Remember above all else that Excel is a tool for data presentation and analysis. Therefore, the end of this project asks several questions in interpreting the results of the calculations performed. Spreadsheets aren’t just to look at – they are to be used to make observations and decisions.

Therefore, as you’ll see in some of the comments in the specific sections, accuracy and logic in the results matter. To use Excel properly, you need to understand what a formula and function is doing and how it does it. Merely trying to repeat keystrokes and mouse clicks without that understanding can give you wrong answers. And wrong answers can lead to costly wrong decisions.

So as mentioned several times in week 10, at this point accuracy DOES matter. Very often small errors will be ok. What is NOT ok is if the answer does not make sense.

Also, up to 3 points can be deducted for poor formatting (see the comment at the very end of this document).

Finally – don’t forget to use the discussion board, as mentioned in Week 10.

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 – one is a low correlated fund. Portfolio 2 will contain 6 other assets chosen for you – again including a low correlated fund. See the Stock Selections sheet in the Project folder.

HOWEVER – I will not post all of your additional stocks until I receive your selections.

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 indicates you copied a prior project, 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 5 (6 points total):

1. Get weekly prices from 1/1/2015 to 4/30/2018. Use the “Multiple Stock Quote Downloader” Excel sheet as discussed in Week 10. Put in the dates exactly as I say here – your first trade date will show as 1/1/2015 even though that was a holiday; the last trade date will show as 4/26/2018. It uses Thursday dates for some reason. Also, make sure you get WEEKLY prices. The prices for the Low Correlated Funds are on a separate sheet.

(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 any of your stocks do 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. Find the prices in the far right tab called Adjusted Close – near the end. These will have to be sorted before being put into the template. DO NOT sort the sheet in the “Downloader” – copy that tab onto a new tab in the template and sort there. Then copy the prices onto the Portfolio template tabs.

3. Calculate the returns for each stock
(3 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, you will still get points for the subsequent sections – but there will be large deductions throughout 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 S&P 500 Stocks 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 sectors using VLOOKUP for all your stocks against the S&P list. Note that if any of your selections 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. If not, use the one that seems to match best. If it’s a fund, type “Fund”.
(1 point)

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

Format the sheets for convenient reading, including freezing and make the negative returns stand out. Make sure you have the tickers for the stocks/funds.

Part 2 of 5 (11 points total):

1. Weight the assets in 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 >=3% and the weights must sum to 100%.
(3 points)

4. Build a correlation table on another sheet for the returns of the assets of 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.35 and +0.35. Highlight the weak correlations somehow. You can use conditional formatting or just one by one.
(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.)
(3 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)

Part 3 of 5 (1 point total):

1. Take the data in the S&P tab and create a pivot 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 5 (7 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). You will use the Statistics template for many of these.

Also – recall we are using >=80% to be considered significant in the probability questions.
______________________________________________________________________________

1a. What is the probability portfolio 1 outperforming the S&P?
Would we conclude the difference in returns is significant (yes or no)?

1b. What is the probability portfolio 2 outperforming the S&P?
Would we conclude the difference in returns is significant (yes or no)?

1c. What is the probability of the portfolio with the higher mean outperforming the other portfolio?
Would we conclude the difference in returns is significant (yes or no)?

Note that you will need to calculate the correlation between whichever two assets you are comparing – using the CORREL function.

(0.5 points each, 1.5 points total)

2a. Which is riskier (based on the SD): Portfolio 1 or the S&P?
Based on the F-statistic for the Portfolio 1 and the S&P (ratio of the variances), what is the probability that the ratio will continue to be greater than 1?
Based on that probability, would you say the difference in risk is significant (yes or no)?

2b. Which is riskier (based on the SD): Portfolio 2 or the S&P?
Based on the F-statistic for the Portfolio 2 and the S&P (ratio of the variances), what is the probability that the ratio will continue to be greater than 1?
Based on that probability, would you say the difference in risk is significant (yes or no)?

2c. Which is riskier (based on the SD): Portfolio 1 or Portfolio 2?
Based on the F-statistic for the Portfolio 1 and Portfolio 2 (ratio of the variances), what is the probability that the ratio will continue to be greater than 1?
Based on that probability, would you say the difference in risk is significant (yes or no)?

(0.5 points each, 1.5 points total)

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

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

3c. Considering portfolio 1, is there a return unrelated to the market, as measured by Alpha?

3d. Considering portfolio 2, is there a return unrelated to the market, as measured by Alpha?

The regression results will show you Alpha, Beta, and their standard Errors. Plug in the values in the appropriate place in the statistics template. We will use 80% as our (arbitrary) cutoff. If the Prob > value is 80% or more, we say the statistic is significantly greater than the hypothesized value. If the Prob < value is 80% or more, we say the statistic is significantly less than hypothesized value. If neither are true, we say the value is essentially the same as the hypothesized value.

THEN – interpret those results accordingly to answer the question.
(0.5 points each, 2 points total)

4. 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.35 and +0.35 in order to say there are diversification benefits due to correlations.

And we want the SD of the portfolio to be less than that of MORE than half of the SDs of the individual assets in the portfolio to say there are diversification benefits due to SD.
(2 points)
______________________________________________________________________________

5. Which stock is the weakest in each portfolio (based on the Sharpe Ratio) – and would you say it is sector based or stock based?
First – this has to be based on a stock where you were able to determine the sector. Then, the sector of that stock is in the last five of the eleven 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.)
(1 point)
Part 5 of 5 (2 points total):
Present your findings on a new tab titled Recommendation.

Make this VERY simple – you are telling a client why you recommend one portfolio over the other. Include the relevant statistics but don’t have any Excel formulas on this page. You can type in the value (or I’d suggest doing copy – paste special – value).

I am giving no suggestions on this other than it must make sense. The types of things you’d want to consider are risk, return, diversification, comparison to the S&P etc.

Don’t stress over this – it’s highly unlikely I’d deduct any points unless it’s illogical. If you want to experiment with some formatting, that’s fine. But it doesn’t need to be fancy – readability and making sense are important.

END OF PROJECT
Remember that the pre-work was worth 2 points – and the rest of the project (that detailed above) is worth 28, or 30 total.

General formatting notes: Remember also that every tab should have a short name describing its content. Make sure you name your Excel file per the instructions above. Up to 3 points can be deducted for poor formatting.

When completed, send your Excel file (named appropriately) to my email address. Make sure the subject of the email is correct per the instructions above. Failure to do that will result in points off under formatting.