Question

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.

JONES INVESTMENT SERVICE Concepts Minimization Charles Jones is a financial advisor who specializes in making recommendations to tances, lottery winnings, and the like. He discusses investment goals with his who have recently come unexpected sums of money from inheri clients, taking into account each client's attitude toward risk and liquidity. After an initial consultation with a client, Charles selects a group of stocks, bonds, murual funds, savings plans, and other investments that he feels may be ap- propriate for consideration in the portfolio. He then secures information on each investment 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 liquid ity 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 S100,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 Expected Jones's Liquidity Risk Return Raing Analysis Factor 4.0% A Immediate 0 A -year Savings account Certificate of deposit Atlantic Lighting Arkansas REIT Bedrock insurance annuity Nocal Mining bond Minicomp Systems Antony Hotels 5.2% 71% 10.0% 8.2% B+ Immediate 25 B Immediate 30 A B+ 1-year 1-year 20 6. 5% 20.0% A Immediate 65 12.5% C Immediate 40 Based on their second meeting, Charles has been able to help Frank develop the following portfolio goals. I. 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 . No more than $30,000 in savings accounts and certificates of deposit Given that Frank is risk-averse, Charles would like to make a final recommenda- tion that will minimize total risk while meeting these goals.

Answer 1

Answer to question# 1)

The following is the manner how we set up the objective and constraints in excel

E19 Saving AtlanticL ArkansasR BedrocMinicomp Antony 3 risk o Objective 15 Formula used B3 B4+C3 C4+D3 D4+E3 E4+F3 F4+G3 G4+H3 H4+13*14 7 Constraints 0 greater than 0 greater than 0 greater than O less than 0 less than Formula used for return constraint 4 B3+5.2 C3+7.1 D3+10 E3+8.2 F3+6.5 G3+20 H3+12.5313 Formula use for A rated investments 4 B3+5.2 C3+7.1 D3+10 E3+8.2 F3+6.5 G3+20 H3+12.5*13 Formula used for immediately liquid investments -B3+D3+E3+H3+13 Formula used for sum of all investments to be 100000-B3 C3 D3+E3+F3+G3+H3+13 7.5 15 16

.

Now click on solver and enter the following details int he solver window:

.

ccess Web Text SourcesConnections Alldt Lunks Advanced Columns Duplicates V Solver Parameters Get External Data Connections Sort &Filter Set Objective: o O Max 則Changing variable Cells: ⓔMn Ovalue of: Saving AtlanticL ArkansasR Bedroc Minicomp Antony risk 0 Objective 25 15 65 Subject to the Constraints: A$1050000 AS11-40000 SAS12 <-100000 SA$13-30000 Constraints 0 greater than 0 greater than 0 greaterthan 0 less than 0 less than 7.5 Formula used for return constraint B3+5.2 C3+7.1 D3+10 Formula use for A rated investments 4 B3 5.2 C3+7.1 D3+ Formula used for immediately liquid investments B3+D3+ Formula used for sum of all investments to be 100000 -B3+ Beset Al Select a Solving Method: GRG Nonlinear Solving Method Select the GRG Noninear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non smooth.

.

Enter the objective cell

Enter the variables cells

Enter the constraint cells

Check the box for make unconstrained variables as non-negative

Click solve

.

We get the following output:

.

.

Ahaly Get External Data Connections Sort & Filter Solver Results Solver found a solution. All Constraints and optimality conditions are satisfied. Saving CD Atlantic L ArkansasR Bedroc NMinicomp Antony 0 3 risk 4. o Objective 0550000 O keep Solver Solution O Restore Original values Sensitivity Limits 0 10000 20000 3.64E-12 15 6 Return to Solver Parameters Dialog Oytline Reports 7 Constraints 7.5 Cancel Save Scenario... 9 321000 greater than 10 50000 greater than 11 40000 greater than 12 60000 less than 13 30000 less than Formula used for return constraint B3+5.2 C3+7.1 D3+ Formula use for A rated investments 4*B3+5.2 C3+7.1 D Formula used for immediately liquid investments -B3+D Solver found a solution. All Constraints and optimality conditions are Formula used for sum of all investments to be 100000-83 100000 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 15

Thus the investment in Saving must be: 30000

In Certifacte of deposit (CD) = 0

Atlantic investment = 10000

Arkansas investment = 0

Bedrock investment = 0

Nocal investment = 20000

Minicomp investment = 0

Antony investment = 0

Minimum risk = 550000