Linear Programing using Solver in Excell

Fill out Values in Excell File and run solver to get the solutions

(Completed Excel document must be submitted to receive any credit) Problem: Investing for maximum total return Imagine that you work as a financial advisor. You have a client who would like to invest $850,000 in bonds. You have narrowed down your options to the following list: Company Acme Chemical DynaStar Eagle Vision Micromodeling OptiPro Sabre Systems Interest Rate Years to Maturity Rating 9.65% 11 1 - Excellent 8.50% 10 3- Good 8.25% 6 4 - Fair 9.75% 10 1 - Excellent 8.45% 7 3 - Good 10.00% 13 2 - Very Good The interest rate describes the return of the investment as the simple interest earned over the length of the bond's life. That is, if $100 is invested in Acme Chemical, the return at the end of the 11 years will be PV = = 100 0.0965 = $9.65. 1. No more than 25% of the total funds should be invested in any one investment. 2. At least half should be invested in long-term bonds that mature in ten years or more 3. No more than 35% of the total funds should be invested in the combination of DynaStar, Eagle Vision, and Optipro. 4. The last stipulation is that all $850,000 must be invested. If the goal is to maximize your client's total return on the investment, how much should they invest in each of the six bonds? What is the corresponding maximum total return they can expect to receive? 1. (6 points) Define the variables of the problem (don't forget to include a unit/quantifier for each!). 2. (7 points) Define the objective function. 1 Home Insert Draw Page Layout Formulas Data Review View Tell me X Calibri (Body 11 A A Wrap Text General Paste av IM BE Merge & Center $ % 9 v to Cond Form B * IU 14 ex fe A 1 Variables X2 2 Values D E F H 5 26 23 O 4 0 0 optimal solution U X Minimum or Maximum b) kt. 5x+ Ty.Un Optimal Value LHS. Axby. Inequality Symbol RHS Value 4 OBIECTIVE FUNCTION 5 Coefficient 5 Values 7 CONSTRAINTS 9 Coeficients 10 Constraint1 11 Constraint 2 12 Constraint 13 Constraint 4 14 Constraints 15 Constraint 16 Constraint 7 17 Constraints 10 Constraint 19 20 21 22 23 24 3. (10 points) Define the constraints (hint: this should include six non-negativity constraints). 4. (5 points) State the problem mathematically as a linear programming problem (use the standard form we typically state LP problems in). by Solver State the optimal solution and corresponding optimal value of the objective function found 7. (6 points) Use your solution to answer the original question: how much should they invest in each of the six bonds? What is the corresponding maximum total return they can expect to receive