The Problem

One of the primary responsibilities of financial advisors is to help their clientele in developing a stock portfolio in which the returns are maximized and risk is minimized. You will be gathering historical data about stock prices and constructing portfolios based on the companies you choose. You will then develop an algorithm that will provide good estimates for stock return and use these estimates to build an optimal portfolio.

Background Information

Harold M. Markowitz developed the subject known as Modern Portfolio Theory in the 1950�s. Markowitz built an objective function that combines maximum return and minimum risk. He defined risk as standard deviation in return and defined as efficient a portfolio that maximized return for a given level of risk. One of the reasons that this type of information is important to stock brokerage houses is that they can use this information to assist in purchasing.

This project makes extensive use of Excel, as there are many data points that must be analyzed on a large scale. While Excel has the ability to calculate Means, Covariance, Correlation, and Standard Deviation easily, it is good to understand the process that goes into these calculations. For an introduction to Excel, see the Excel tutorial.

Materials Included

• Problem statements and questions for students
• Sample solutions
• Excel spreadsheet containing the daily stock price data for the five companies below (See Excel file FiveStocks2005.xls). The spreadsheet also includes sample calculations.
  • OO Oakley, Inc.
  • AAPL Apple Computer
  • GE General Electric
  • KO The Coca-Cola Company
  • GIS General Mills