Page 1 of 4

INTERNAL USE
RSM230 Financial Markets
Professor David Goldreich
Rotman School of Management

Group project #2: Portfolio analysis
Project Due: Friday, March 25, 2022 at 4:00pm


Group Project Overview

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 2 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 3 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 4 of 4

INTERNAL USE
Group Structure:
• This case is to be completed in the groups titled “Project #2 Groups” as assigned by the Rotman
Commerce 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 rotmancommerce.teamworkhelp@utoronto.ca.