RSM230 Financial Markets Professor David School of Management
Group project #2: Portfolio analysis
Project Due: Friday, March 25, 2022 at 4:00pm
Group Project Overview
Copyright By PowCoder代写 加微信 powcoder
In this project, your group will analyze returns from selected stocks that are part of the Dow Jones Industrial Average. This project requires substantial use of Microsoft Excel. You may want to review the recording of the March 9 lecture to see how I downloaded data and manipulated data within Excel. You also may want to watch my “Stock data analysis in Excel (recorded 2020)” video.
I expect all members of each group to participate fully in this group assignment. To ensure that I all students learn, I strongly encourage each student to work through the data analysis on his/her own computer while discussing it in the group, but only one submission per group should be handed in. I expect each group member to help the others with Excel as necessary.
The following steps (1) to (8) are the data analysis that you must do in Excel. Your Excel file must be submitted as a backup to your analysis, but your main submission will be a Word or PDF file. The Excel file will not be explicitly marked. The instructions for the submission (in Word or PDF) follow in steps (9) to (15). Note that these instructions assume a PC and may be different for a Mac.
Data analysis steps
1) Choose a stock. Go to Yahoo Finance (ca.finance.yahoo.com) and look up a stock that is part of the Dow Jones Industrial Average (^DJI). The list of DJIA components can also be found at: https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average#Components.
Choose one of those stocks to see information about that stock. Make sure to click the “Profile” tab to see basic information about the company. (Do not choose AAPL since we have looked at that frequently in class. Do not choose DOW since that is a 2019 spinoff from DowDuPont and does not have a long independent history.) We will refer to your chosen stock as STOCK1.
2) Download data. On Yahoo finance, look up the quote for STOCK1 by its ticker symbol. Click the tab “Historical Data” to see past stock prices. Choose a five-year time period and a monthly frequency. Click “Apply”. Click “Download Data” and copy the data into your Excel spreadsheet. You should have about 60 monthly data points. (Note that the dates refer to the entire month. The open is the open on the first day of the month and the close refers to the final price on the last day of the month. There may be an extra unnecessary row with just one day’s prices. If so, that can be discarded.) You will mostly be using the data from the columns “Date” and “Adjusted Close”.
Page 1 of 4 INTERNAL USE
3) Download additional data. At the same time, download the equivalent data for a different stock (also drawn from the Dow Jones Industrial Average, but not AAPL or DOW). We will refer to this second stock as STOCK2. Furthermore, download the equivalent data for the S&P500 index (^GSPC). To be clear, this should be for the index – not for a stock in the index – so that it can be a benchmark for your stock. We will refer to this as S&P500. Combine them all in one spreadsheet and make sure that the dates align. (If Yahoo Finance does not allow you to download S&P500 data, you can get it from the course Quercus page here: S&P500 monthly data. Be careful to ensure that the dates match with the rest of your data. You will likely have to fix the March 2021 closing price in the data I posted, since it is for the day I download the data. You should match it to the day you download the rest of your data.)
4) Calculate returns. For each of STOCK1 and STOCK2, and the S&P500, calculate monthly returns for each of the 60 (or 59) months. Base the returns on the adjusted close prices.
5) Summary statistics. For each set of returns for STOCK1, STOCK2, and S&P500, calculate the average monthly return, the median return, the maximum return, the minimum return, the standard deviation of returns, the correlation between returns of STOCK1 and STOCK2, the correlation between STOCK1 and S&P500, and the correlation between STOCK2 and S&P500. [The Excel functions with a range of data within the parentheses are: =average(.), =median(.), =max(.), =min(.), =stdev.s(.), =correl(.,.). The correlation function will have two ranges of data within the parentheses separated by a comma.]
6) Create a portfolio. Imagine putting half your money in STOCK1 and half in STOCK2. Each month, the portfolio will be rebalanced so that the proportions remain 50:50. Calculate the monthly returns on this portfolio for each of the 60 months. (This is simply averaging the returns of STOCK1 and STOCK2 each month.) For this portfolio, calculate the average monthly return, the median return, the maximum return, the minimum return, and the standard deviation of returns.
7) Price graphs. Plot a price graph that shows both your STOCK1 and the S&P500 over the past 5 years. The x-axis should be the date. You probably want a secondary axis to be able to usefully view both graphs. (After plotting both, right click on one of the graphs, select “Format Data Series”, and then tick secondary axis. Note that this graph should be based on price data and not returns.)
8) Scatter plot. Make a scatter plot comparing returns of STOCK1 and the S&P500. Make sure that you have the S&P500 returns on the x-axis and the returns on STOCK1 on the y-axis. Include a trendline. (Right click on one data point, select “Add Trendline”, tick “Linear” and “Display Equation on Chart”.
Report instructions (submit in Word or PDF)
9) What stock did you choose as STOCK1? Over the past five years, how well did the stock do? Paste the graph of your stock price and the S&P500 (from (7) above) into the Word file. Compare the graph of your stock price to the graph of the S&P500. Did your stock outperform or underperform the S&P500? How well do the two charts track each other? Interpret as necessary.
Page 2 of 4 INTERNAL USE
10) Report the average and median returns from (5) above. How do the average and median returns of STOCK1 compare to the average and median returns of the S&P500? Does this coincide with your finding in (9)? Is the average return of STOCK1 higher or lower than its median return? What do you infer from this? How do the average and median returns of STOCK1 compare to the average and median returns of STOCK2? Interpret as necessary.
11) Suppose you used the monthly “close” price instead of “adjusted close” price. How would the average monthly returns change? What would this depend on?
12) Report the maximum, minimum, and standard deviations of returns from (5) above for STOCK1, STOCK2, and S&P500. Which investment is riskiest and which is safest? Why is it this way? Do the max and min give the same interpretation as the standard deviation?
13) Report the average monthly return, the median return, the maximum return, the minimum return, and the standard deviation of returns for the portfolio in (6). How do these compare to the same statistics for STOCK1 and STOCK2? How do they compare to the same statistics for the S&P500? Explain why you see what you do. What is the correlation between returns of STOCK1 and STOCK2? How might this affect the statistics on the portfolio?
14) Paste in the scatter plot from (8) comparing the S&P500 and STOCK1. What does a visual inspection suggest about the relationship? Why might there be such a relationship? Is the relationship consistent with the correlation between STOCK1 and S&P500 from (5)? What is the equation of the line of best fit? What is the slop of the line? Is the equation for the line consistent with the visual inspection and the correlation?
15) Suppose instead of Stock STOCK2, you chose a different stock STOCK3 to create a portfolio with STOCK1. What characteristics of STOCK3 would lead to a portfolio with more/less risk the portfolio comprised of STOCK1 and STOCK2? Suppose you made a portfolio of equal weights on STOCK1, STOCK2, and STOCK3 (1/3 each), what can you say about the likely average return and standard deviation compared to the portfolio of STOCK1 and STOCK2. If you could create any portfolio using any or all of the 30 stocks of the Dow Jones Industrial Average, what sort of portfolio would give the least risk? Approximately how much risk would such a portfolio likely have?
Note that all of the analysis relies on the intuitive descriptions of standard deviation, correlation, etc., that we discussed in class and the estimates from Excel. The exact mathematical definitions are not necessary (and you will learn about them in later courses) and should not be used for this assignment.
Page 3 of 4 INTERNAL USE
Group Structure:
• This case is to be completed in the groups titled “Project #2 Groups” as assigned by the office. With few exceptions, these are the same groups as for the first group assignment.
• All members of the team are expected to participate and contribute fully. Submission Instructions:
• One member of your group upload ALL THREE OF your Word/PDF file, your Excel file, and your peer evaluation form to Quercus no later than Friday, March 25 at 4:00 pm.
• The cover page of your assignment should include the names and student numbers of all members of the group who actively participated in this assignment.
Peer evaluation:
All Peer Evaluation Instructions will be communicated via Quercus Announcement, like Group Project #1.
As described in the course outline, if necessary to resolve a conflict, you can book a meeting with the Teamwork Mentor, by sending an email to
Page 4 of 4 INTERNAL USE
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com