Question

PLEASE ATTACH THE SPREAD SHEET 19. A trust officer at the Blacksburg National Bank needs to determine how to invest $100,000 in the following collection of bonds to maximine the annual retum. Annual Bulu Maturity Risk TaxFree 9.9% Long High Yes B 8.0% Short Low с 9.0% Long Low No D 9.0% Long High Yes No Short The officer wants to invest at least 50% of the money in short-term issues and no more than 50% in high-risk issues. At least 30% of the funds should go into tax-free investments and at least 40% of the total annual return should be tax-free. Hint: the formula for the last condition/constraint is: 0.095A+ 0.08B +0.09D 0.4* (0.095A +0.08B +0.09C +0.09D +0.09E) 2-How many uecision variavies are there. 3 what is the value of the objective function 3a Ignoring the constraints, the value of the objective function would have to be between and 4 how many S of A bonds 5How many $ of B bonds 6 How many dollars of C bonds 7 How many dollar of D bond 8How many dollar of E bonds

Answer 1

Solution:

Part A: LP Formulation:

Decision Variables:

Let,

A = # $ Amount to be invested in Bond A

B = # $ Amount to be invested in Bond B

C = # $ Amount to be invested in Bond C

D = # $ Amount to be invested in Bond D

E = # $ Amount to be invested in Bond E

Thus, there are total 5 decision variables to be found for the given sum. (Answer 2)

Objective Function:

Max Z = 0.095 A + 0.08 B + 0.09 C + 0.09 D + 0.09 E

Subject to Constraints:

C1 = A + B + C + D + E ≤ 100000 (Total Amount to be invested)

C2 = B + E ≥ 0.50 (A + B + C + D + E) (At least 50% of the total investment should be done in short term bonds)

C3 = A + D + E ≤ 0.50 (A + B + C + D + E) (No more than 50% of the total investment to be done in high risk bonds)

C4 = A + B + D ≥ 0.30 (A + B + C + D + E) (At least 30% of the total investment to be done in the tax free bonds)

C5 = 0.095 A + 0.08 B + 0.09 D ≥ 0.40 ( 0.095 A + 0.08 B + 0.09 C + 0.09 D + 0.09 E ) (At least 40% of the total annual return should come from the tax free investments)

Non-Negativity Condition:

A, B, C, D, E ≥ 0

Part B: LP Solution:

As it is specifically mentioned in the question, we will solve the given LP model by using MS-Excel Solver data tool as mentioned in the below steps:

Step 1: Prepare the following table in the excel

(Note: Make Sure to prepare this table in the exact Rows and Columns as mentioned in the below screenshot)

А B F G H - D E $ Amount to be Invested in Bonds 1 Particulars Total Sign Min or Max 2 A B D E 3 Decision Variable ($ Amount) <= Optimal Solution 4 Max Z 0.095 0.08 0.09 0.09 0.09 < Max Annual Return 5 C1 1 1 1 1 1 6 C2 0 1 0 0 1 7 C3 1 0 0 1 1 8 C4 1 1 0 1 0 9 C5 0.095 0.08 0 0.09 0

Step 2: Now, enter the exact formulas as mentioned in the below screenshot and then press enter:

G Н. Total Sign Min or Max <Max Annual Return < < = Optimal Solution =SUMPRODUCT($B$3:$F$3,B4:F4) =SUMPRODUCT($B$3:$F$3,B5:F5) =SUMPRODUCT($B$3:$F$3,B6:F6) =SUMPRODUCT($B$3:$F$3,17:F7) =SUMPRODUCT($B$3:$F$3,B8:F8) =SUMPRODUCT($B$3:$F$3,B9:F9) V VI 100000 =0.5*SUM(B3:F3) =0.5*SUM(B3:F3) =0.3*SUM(B3:F3) =0.4*SUMPRODUCT($B$3:$F$3,B4:F4) IV IV

Hence, we get the following table:

A B F G H с D E $ Amount to be Invested in Bonds 1 Particulars Total Sign Min or Max 2 A B C D E 3 Decision Variable ($ Amount) < = Optimal Solution 4 Max Z 0.095 0.08 0.09 0.09 0.09 0.00 < Max Annual Return c1 1 1 1 1 1 0 < 100000 5 6 C2 0 1 0 0 1 0 0 7 C3 1 0 0 1 1 0 0 8 C4 1 1 0 1 0 0 > 0 9 C5 0.095 0.08 0 0.09 0 0 > 0

Step 3: Now, open Solver (Data > Analyze > Solver), and fill the exact data in Solver Pop-Up Window as shown in the below screenshot:

Solver Parameters х Set Objective: $G$4 1 To: Max Min Value Of: 6925 By Changing Variable Cells: $B$3:$F$3 Add Subject to the Constraints: $G$5 <= $1$5 $G$6 >= $1$6 $G$7 <= $1$7 $G$8 >= $1$8 $G$9 >= $1$9 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear 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. Help Solve Close

Step 4: Once, you press the solve button as mentioned in the above step, you will get the following table. Here, the highlighted values are the optimal values for the decision variables:

А B F G H - С D E $ Amount to be Invested in Bonds 1 Particulars Total Sign Min or Max 2 A B C D E 3 0.00 29661.02 < = Optimal Solution Decision Variable ($ Amount) 20338.98 20338.98 29661.02 Max Z 0.095 0.08 0.09 4 0.09 0.09 8898.31 < Max Annual Return 5 C1 1 1 1 1 100000 100000 6 C2 0 1 0 0 1 50000 50000 7 C3 1 0 0 1 1 50000 A Villa 50000 30000 8 C4 1 1 0 1 0 40678 9 C5 0.095 0.08 0 0.09 0 3559 3559

Thus,

Answer 3 = 8898.31 $ (Rounded to 2 decimal places)

Answer 3-a: = Ignoring all the constraints, we need to invest a total of $ 100000, thus, the lowest return = 0.08 x 100000 = $ 8000 and the Highest Return = 0.095 x 100000 = $ 9500, thus, the value of objective function would have to be between 8000 $ and 9500 $

Answer 4 = 20338.98 $ (Rounded to 2 decimal places)

Answer 5= 20338.98 $ (Rounded to 2 decimal places)

Answer 6 = 29661.02 $ (Rounded to 2 decimal places)

Answer 7 = 0 $

Answer 8 = 29661.02 $ (Rounded to 2 decimal places)

(Kindly raise an upvote for this answer, if you found it useful)