Overview
Overview
In this project, you will play the role of a personal financial advisor managing portfolios for two clients,
John and Mary, using three stocks that YOU select. You will build the optimal risky portfolio for each
client, compare their performance using the Sharpe ratio, and then form a complete portfolio by
combining the more efficient (higher-Sharpe) risky portfolio with a risk-free asset. Finally, you will
summarize and compare the results.
This project applies the core mean-variance portfolio optimization concepts from BKM Chapters 6 and 7.
Procedures
Step 1 - Select 3 Stocks (Your Choice)
Pick three publicly traded U.S. stocks from any sectors that interest you (tech, healthcare, consumer
goods, energy, ESG, value, growth - your choice). Label them Stock X, Stock Y, and Stock Z.
Tips:
- Choose stocks with at least 5 years of trading history.
- Ideally, pick from different sectors to capture diversification benefits.
- Avoid recently IPO'd companies (less than 1 year of data).
Step 2 - Download the Data
From https://www.investing.com/, download monthly returns from February 2021 through January 2026
for your three stocks (X, Y, Z) and the S&P 500 (US500) as the market index. Save each as a separate
column in your Excel workbook.
Download the 3-month T-bill rate from FRED (https://fred.stlouisfed.org/series/DTB3) or Ken French's
website (http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html). Use the average T-
bill rate over the sample period as your risk-free rate (rf).
Step 3 - Compute Summary Statistics
In Excel, for each stock and the S&P 500, compute:
- Mean monthly return: =AVERAGE(...)
- Standard deviation: =STDEV.S(...)
- Pairwise covariance: =COVARIANCE.S(...) and correlation: =CORREL(...)
Assume historical mean returns are good estimates of expected returns.
Step 4 - Build John's Optimal Risky Portfolio (Stocks X and Y)
John invests only in Stocks X and Y. Use the 2-asset optimal risky portfolio formula from Chapter 7:
Compute and report:
- Optimal weights wX, wY
- Expected return of John's optimal risky portfolio
- Standard deviation of John's optimal risky portfolio
- Sharpe ratio = (E[rp] - rf) / σp
Step 5 - Build Mary's Optimal Risky Portfolio (Stocks Y and Z)
Mary invests only in Stocks Y and Z. Repeat Step 4 using Stocks Y and Z to find Mary's optimal weights,
expected return, standard deviation, and Sharpe ratio.
Step 6 - Compare and Select the Preferred Risky Portfolio
Compute and compare the Sharpe ratios of John's and Mary's optimal risky portfolios. The portfolio with
the higher Sharpe ratio is the more efficient risky portfolio - it delivers more excess return per unit of risk.
Identify which one wins, and use it as your selected risky portfolio in the next step.
Briefly explain (1-2 sentences) why the winning portfolio has a higher Sharpe ratio (e.g., higher mean
return, lower volatility, more favorable correlation between its two stocks, etc.).
Step 7 - Complete Portfolio with Risk-Free Asset
Form a complete portfolio by combining the SELECTED risky portfolio from Step 6 (whichever of John's
or Mary's had the higher Sharpe ratio) with a risk-free asset (T-bill), using the following allocation:
- 30% in the T-bill
- 70% in the selected optimal risky portfolio
Compute and report:
- Expected return of the complete portfolio: E[rC] = 0.30 * rf + 0.70 * E[rp]
- Standard deviation of the complete portfolio: σC = 0.70 * σp
Briefly explain how adding the risk-free asset changes the portfolio's risk-return profile.
Step 8 - Summarize and Compare
In a short report (1-2 pages), discuss:
- Compare the Sharpe ratios of John's and Mary's portfolios. Which is higher? Why?
- Discuss the role of correlation between the two stocks in each portfolio - did low correlation help or
hurt?
- How does the complete portfolio (with 30% T-bill) compare with the fully risky selected portfolio in
terms of expected return and risk?
- What practical investment advice would you give John and Mary based on your analysis?
You may include charts (risk-return scatter, Sharpe ratio bar chart) to support your discussion.
Submission
Submit a single zipped folder containing the following two files:
1. Excel workbook (Portfolio_Analysis.xlsx) with the following tabs:
- Data: raw monthly returns for the 3 stocks, S&P 500, and T-bill rate
- Summary Stats: means, standard deviations, covariance/correlation matrix
- John's Portfolio: weights, return, standard deviation, Sharpe ratio
- Mary's Portfolio: weights, return, standard deviation, Sharpe ratio
- Comparison: Sharpe ratios of John's and Mary's portfolios side-by-side, with the winner clearly
identified
- Complete Portfolio: 30% T-bill + 70% selected (higher-Sharpe) risky portfolio
- All formulas should be visible and clearly labeled
2. Summary report (PDF or Word, 1-2 pages): the comparison and discussion from Step 8
Grading Rubric
Component Weight
Data quality and summary statistics 15%
John's optimal portfolio (correct calculations) 20%
Mary's optimal portfolio (correct calculations) 20%
Sharpe comparison and selection (Step 6) 10%
Complete portfolio (30% T-bill / 70% selected risky) 15%
Summary report (clarity, insight, comparison) 20%
Video Portion Overview
As part of the Portfolio Management Project, each student is required to upload a short video presentation
summarizing the work in your Excel workbook and written report. The presentation gives you a chance to
walk me through your reasoning, demonstrate your understanding of the analysis, and practice explaining
quantitative work in a clear, concise way - an essential professional skill in finance.
Length and Format
- Maximum length: 5 minutes.
- Format: Video. Kaltura Capture is recommended; Zoom or other screen-recording tools are also
acceptable.
- Submission: upload through the dedicated “Individual Project Video Presentation” link on Canvas.
Please do NOT email me your video file.
Weighting
- Written Report (Excel + summary): 15% of total course grade
- Video Presentation: 5% of total course grade
- Combined Project Weight: 20% of total course grade
Suggested Structure (5 minutes total)
Section Approximate Time
Introduction: yourself + the 3 stocks you chose and why ~30 sec
Summary statistics (mean, std dev, correlation) ~30 sec
John's optimal risky portfolio (weights, return, std dev, Sharpe) ~45 sec
Mary's optimal risky portfolio (weights, return, std dev, Sharpe) ~45 sec
Sharpe comparison and which portfolio you selected, and why ~45 sec
Complete portfolio (30% T-bill + 70% selected risky): return and risk ~45 sec
Key takeaway and what you learned ~30 sec
Tips for a Strong Presentation
- Screen-share your Excel workbook and walk through the actual numbers - do not just read off slides.
- Speak clearly and at a steady pace. Practice once before recording; aim for about 4:30 to leave a small
buffer.
- Find a quiet environment with clear audio. A headset microphone helps a lot if you have one available.
- No fancy editing required - a single clean take is great.
Grading Rubric (5 points = 5% of total course grade)
Criterion Points
Clarity and structure of explanation 1.5
Coverage of all major steps in the project 1.5
Stayed within the 5-minute limit 1.0
Professional delivery (audible audio, clear visuals, smooth pacing) 1.0
Total 5.0