Please show all your contraints and work in excel. Dont answer this question without the excel output.

J. D. Williams, Inc., is an investment advisory firm that manages more than $120 million

in funds for its numerous clients. The company uses an asset allocation model that recommends

the portion of each clients portfolio to be invested in a growth stock fund, an income

fund, and a money market fund. To maintain diversity in each clients portfolio, the firm

places limits on the percentage of each portfolio that may be invested in each of the three

funds. General guidelines indicate that the amount invested in the growth fund must be between

20% and 40% of the total portfolio value. Similar percentages for the other two funds

stipulate that between 20% and 50% of the total portfolio value must be in the income fund

and at least 30% of the total portfolio value must be in the money market fund.

In addition, the company attempts to assess the risk tolerance of each client and adjust

the portfolio to meet the needs of the individual investor. For example, Williams just contracted

with a new client who has $800,000 to invest. Based on an evaluation of the clients

risk tolerance, Williams assigned a maximum risk index of 0.05 for the client. The firms

risk indicators show the risk of the growth fund at 0.10, the income fund at 0.07, and the

money market fund at 0.01. An overall portfolio risk index is computed as a weighted average

of the risk rating for the three funds, where the weights are the fraction of the clients

portfolio invested in each of the funds.

Additionally, Williams is currently forecasting annual yields of 18% for the growth fund,

12.5% for the income fund, and 7.5% for the money market fund. Based on the information

provided, how should the new client be advised to allocate the $800,000 among the growth,

income, and money market funds? Develop a linear programming model that will provide

the maximum yield for the portfolio. Use your model to develop a managerial report.

Managerial Report

1. Recommend how much of the $800,000 should be invested in each of the three

funds. What is the annual yield you anticipate for the investment recommendation?

2. Assume that the clients risk index could be increased to 0.055. How much would

the yield increase, and how would the investment recommendation change?

3. Refer again to the original situation, where the clients risk index was assessed to be

0.05. How would your investment recommendation change if the annual yield for

the growth fund were revised downward to 16% or even to 14%?

4. Assume that the client expressed some concern about having too much money in the

growth fund. How would the original recommendation change if the amount invested

in the growth fund is not allowed to exceed the amount invested in the income

fund?

5. The asset allocation model you developed may be useful in modifying the portfolios

for all of the firms clients whenever the anticipated yields for the three funds

are periodically revised. What is your recommendation as to whether use of this

model is possible?