Reference no: EM132808317

Question:

Using the information below. I've been instructed to follow the directions below. I would like help with talking through the steps for both #1 and #2. I am responsible for uploading in Excel. Any explanation or education you can share will be helpful.

1. Assuming that Frank will invest all $100,000, develop a linear programming model to determine the amount to be invested in each products so that the total risk will be minimized while meeting the four portfolio goals, by a) first write out the complete formulation, b) then use Excel Solver to find the solution.

(Hints: Risk is calculated by amount invested on a product multiplied by its associated risk factor; for example, if $100 is invested in Atlantic Lighting, then its associated risk is 100 x 25=2500.)

2. Reformulate the problem so that the objective is to maximize the expected return while keeping total risk less than or equal to 1,500,000 and the three remaining portfolio goals, and use Solver to find the solution.

Submit both problems in one single excel file in separate worksheets. Name your worksheets accordingly for legibility.

PROBLEM:

Charles Jones is a financial advisor who specializes in making recommendations to investors who have recently come into unexpected sums of money from inheritances, lottery winnings, and the like. He discusses investment goals with his clients, considering each client's attitude toward risk and liquidity. After an initial consultation with a client, Charles selects a group of stocks, bonds, mutual funds, savings plans, and other investments that he feels may be appropriate for consideration in the portfolio. He then secures information on each investments and determines his own rating. With this information he develops a chart giving the risk factors (numbers between 0 and 100, based on his evaluation) expected returns based on current and projected company operations, and liquidity information. At the second meeting Charles defines the client's goals more specifically. The responses are entered into a linear programming model, and a recommendation is made to the client based on the results of the model.

Frank Baklarz has just inherited $100,000. Based on their initial meeting, Charles has found Frank to be quite risk averse. Charles, therefore, suggests the following potential investments that can offer good returns with small risk:

Potential Investment Expected Return Jones's Rating Liquidity Analysis Risk Factor

Potential Investment Expected Return Jones Rating Liquid Analysis Risk Factor

Savings account 4.0% A Immediate 0

Certificate of Deposit 5.2% A 5-year 0

Atlantic Lighting 7.1% B+ Immediate 25

Arkansas REIT 10.0% B Immediate 30

Bedrock Insurance annuity 8.2% A 1-year 20

Nocal Mining bond 6.5% B+ 1-year 15

Minicomp Systems 20.0% A Immediate 65

Antony Hotel 12.5% C Immediate 40

Based on their second meeting, Charles has been able to help Frank develop the following portfolio goals.

1. An expected annual return of at least 7.5%

2. At least 50% of the inheritance in A-rated investments

3. At least 40% of the inheritance in immediately liquid investments

4. No more than $30,000 in savings accounts and certificates of deposi

Given that Frank is risk averse. Charles would like to make a final recommendation that will minimize total risk while meeting these goals.