Suppose you are a CFO of a certain company that holds a lot of cash and wants to invest it. Using portfolio theory, optimize your companies investment strategy in four selected stocks.

1. Please go to the Yahoo! Finance Web site. Select the following four companies: Pfizer (symbol: PFE), Sysco (symbol: SYY), Ford (symbol: F) and Apple (symbol: AAPL). Get the adjusted monthly closing prices for each month starting September 2013 and ending September 2023. For each stock find the:

a. Get the one year close to close return for each year of the ten years from 2013-2023.

b. Mean Annual Return over the ten years

c. Variance and Standard Deviation of return.

d. Find the correlation coefficient and the covariance of returns between the stocks over this period.

2. Find the optimal weights you should invest in each company if your objective is to minimize the risk of the portfolio. Remember that you have stocks, therefore the number of covariance terms is determined by , and a. Follow the following steps: Data Solver

i. Set target cell (refer to the cell with the Standard Deviation of portfolio formula calculated in c) to Min

ii. By Changing Cells (refer to cells with weights (w1, w2, w3, w4)

iii. You will find that the weights will change the resulting weights are those that will give you the minimum risk

iv. What is your portfolios expected return and risk?

3. Assume that the risk free rate is 1%. Using solver, find the value the weights such that the slope of the CAL is maximized (Maximum Sharpe Ratio). Using this get the and Std. Deviation of the tangency portfolio.

4. Using the E(Ri) on each stock (the mean of historical returns), Std. Deviations and covariance between the four stocks that you derived and for a range of target expected returns on the portfolio E(Rp) from 5% to 50% in steps of 5% use excel solver to minimize the standard deviation of the portfolio for each target E(Rp). Plot the resulting minimum-variance frontier.