Question

A bank manager is trying to determine how much of $350,000 to invest in stocks, bonds, auto loans and personal loans. The annual rates of return on each type of investment is: 10% on stocks, 15% on bonds, 13% on auto loans and 20% on personal loans. However, there are three investment restrictions:

i. Amount invested in auto loans cannot exceed the amount invested in bonds

ii. Amount invested in bonds cannot exceed the amount invested in stocks

iii. No more than 20% of total amount invested can be in personal loans

Using solver in excel, how can the bank maximize the annual return on its investment portfolio?

Answer 1

Le the decision variables,

x1 = amount invested in stocks,

x2 = amount invested in bonds,

x3 = amount invested in auto loans and

x4 = amount invested in personal loans.

The objective of the problem is to maximize revenue. The objective function is defined as,

The constraints of the problem are,

i. The total amount is 350,000

ii. Amount invested in auto loans cannot exceed the amount invested in bonds

iii. Amount invested in bonds cannot exceed the amount invested in stocks

iv. No more than 20% of the total amount invested can be in personal loans

and the nonnegativity constraints are,

Now, the LP is solved using the excel solver by following these steps,

Step 1: Write the decision variable with value zero. The screenshot is shown below

Step 2: Write the objective function equation while taking the decision variable value. The screenshot is shown below,

Step 3: Write the constraints equation while taking the decision variable value and write the right-side value of the constraint The screenshot is shown below,

(constraint 5, 6, 7, 8 are non-negativity constraint)

Step 4: DATA > Solver > OK. The screenshot is shown below,

Step 5:

Set Objective: Select objective value,

To: select Max

Subject to the Constraints > Add > in Cell Reference select constraint value and in Constraint: select right-hand side value of constraint and select the <= inequality.

Tick Make Unconstrained Variables Non-Negative

Select a Solving Method: Simplex LP

then click Solve. The screenshot is shown below,

Step 6: Select Reports > Answer then Ok

The result is obtained. The screenshots are shown below,

The Answer Report

Max Z = 47600

x1 = 140000

x2 = 70000

x3 = 70000

x4 = 70000