Question: Durham Asset Management (please use GRG nonlinear or Evolutionary solver to solve his case) Durham Asset Management (DAM) is a small firm with 50 employees
Durham Asset Management (please use GRG nonlinear or Evolutionary solver to solve his case)
Durham Asset Management (DAM) is a small firm with 50 employees that manages the pension funds of small to medium-sized companies. Durham was founded in 1975 and has grown considerably throughout the years. Initially, DAM managed the pension funds of three small companies whose asset values totaled $30 million. By 1991 DAM's funds under management were valued at $2 billion.
James Franklin is a senior vice president at DAM, in charge of managing the equity portion of one of its largest pension funds. Franklin meets on a quarterly basis with company officials who supervise his decisions and oversee his performance. His work is measured on several levels, including both subjective and objective criteria. The subjective criteria include estimates of the quality of research reports. The objective criteria include the actual performance of Franklin's portfolio relative to a customized index of companies in DAM's in-vestment universe. Franklin attempts to "beat" the index not by trying to time market moves, but by investing more heavily in those companies he expects to outperform the customized index and less heavily in those companies he expects to underperform the index.
Franklin has several research analysts who are charged with following the performance of several companies within specific industries. The research analysts prepare reports that analyze the past performance of the companies and prepare projections of future performance. The projections include assessments of the "most likely" or average performance anticipated over the next month.
Franklin analyzes their findings and often asks for additional information or suggests modifications to the analyses. After a period of careful review, the final forecasts for the next month are assembled and summarized. Each month the analysts' forecasts are compared to the actual results. Annual bonuses for the analysts are based in part on the comparison of these numbers.
It is now late December 1991, and the projections for January 1992 are indicated in Table 1.
The projections have been made for 15 U.S. companies divided into five industry groups. The five industry groups are metals, retail, computer, automotive, and aviation.
Company
Forecasted Mean Return
Aluminum Co. of America (ALCOA)
0.6%
Reynolds Metals
0.9%
Alcan Aluminum, Ltd.
0.8%
Walmart Store, Inc.
1.5%
Sears, Roebuck & Co.
0.8%
Kmart Corporation
1.3%
International Business Machines (IBM)
0.4%
Digital Equipment Corporation (DEC)
1.1%
Hewlett Packard Co. (HP)
0.7%
General Motors Corp. (GM)
1.2%
Ford Motor Co. (FORD)
0.9%
Chrysler Corp.
1.3%
Boeing Co.
0.3%
McDonnell Douglas Corp.
0.2%
United Technologies Corp.
0.7%
Table 1: Projections for January1992 for DAM Case Study
Franklin would like to use the portfolio optimization approach to see what portfolios it would recommend. He has data containing end-of-month prices for the last two years for each of the companies. The data are contained in the spreadsheet dam.xls. Also included in the spreadsheet is information about dividends and stock splits. Using these data, James constructs a history of 24 monthly returns for each of the 15 companies.
The past data provide useful information about the volatility (standard deviation) of stock returns. They also give useful information about the degree of association (correlation) of returns between pairs of stocks. However, average returns from the past do not tend to be good predictors of future average returns. Rather than using the raw historical data directly, Franklin creates 24 future return scenarios by adjusting the 24 historical returns. The adjustments are made so that the means of the future scenario returns are consistent with the forecasts from Table 1. The adjustments are also made so that the volatilities and correlations of the future scenario returns are the same as in the historical data.
The exact procedure that Franklin uses for developing future scenario returns is described next. Let denote the historical return of security j in month i (for j = 1, ... , 15 and i = 1, ... , 24). Suppose that the average historical return of security j is . For security j, denote the forecasted mean return in Table 1 by . (For example, and , where the index 1 refers to ALCOA and 2 refers to Reynolds Metals.) Franklin creates the future scenario return for security j in scenario i using the following equation:
(i)
Franklin assumes that any of the scenarios defined by equation (i) can occur with equal probability. DAM's policy is never to invest more than 30% of the funds in any one industry group. Franklin measures the risk of a portfolio by its standard deviation of return. He then solves a portfolio optimization model for various minimum levels of mean return to see which portfolios are recommended. After analyzing the trade-off between risk and return, Franklin makes a judgment as to which portfolio to hold for the coming month.
Questions
1. Use the information in the file dam.xls to create a history of 24 monthly returns for the 15 companies. Compute the historical average return of each stock. In particular, what was the historical return of ALCOA from 12/29/89 to 1/31/90? What was the historical return of Boeing from 5/31/90 to 6/29/90? Explain how you account for dividends and stock splits in computing monthly returns. (Note: This has already been done for you in dam.xls. If you are interested, take a look and see how if statements were used to deal with the stock splits.)
2. Develop 24 future scenario returns for each of the 15 stocks using equation (i). What is the explanation underlying it? In particular, what is the return of ALCOA if scenario 1 occurs? What is the return of Reynolds Metals if scenario 3 occurs?
3. Compute and graph the mean-standard deviation efficient frontier, with no shorting allowed. Compute at least six points on the efficient frontier (including the minimum standard deviation and maximum expected return points). Create a table of results showing the following for each of your points on the efficient frontier: (1) the optimal portfolio weights, (2) mean portfolio return, and (3) standard deviation. (Briefly explain the equations and optimization model used in the spreadsheet.)[2]
Hints:
When there are no splits, the historical return for a month can be computed by the formula:
Return = (New price + Dividend - Initial price) / Initial price.
If the stock splits 3 for 2, the return is given by:
Return = (New price + Dividend - Initial price*(2/3) / (Initial price*(2/3)), which is equivalent to:
Return = ( (3/2)(New price + Dividend) - Initial price) / Initial price.
If the stock splits 2 for 1, the return is given by:
Return = (New price + Dividend - Initial price*(1/2) / (Initial price*(1/2)), which is equivalent to:
Return = ( 2(New price + Dividend) - Initial price) / Initial price.
(These return formulas with splits assume that the dividend is paid after the split, not before. However, in the data there are no cases of a split and dividend in the same month, so this issue never arises.)
Examples:
The price of ALCOA on 90/01/31 was 61.375. Its price on 89/12/29 was 75.0. A dividend of 1.7975 was paid between these dates. Hence, the return for the month ending 90/01/31 was
-0.157700 = (61.375 + 1.7975 - 75.0) / 75.0 .
The price of Boeing on 90/06/29 was 58.5. The previous month's price was 82.625. There were no intervening dividends, but the stock split 3 for 2. Hence the return was
0.062027 = ((3/2)(58.5) - 82.625) / 82.625.
Please solve the case with an excel file and upload it (or upload the link if possible). Thanks!
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help