Question

The planning committee of a bank makes monthly decisions on the amount of funds to allocate to loans and to government securities. Some of the loans are secured (backed by collateral such as a home or automobile), and some are unsecured. A list of the various types of loans and their annual rates of return as shown below:

Type of Investment	Annual Rate of Return
Secured loans:
Residential morgage (x1)	11
Commercial morgage (x2)	12
Automoble (x3)	15
Home improvement (x4)	13
Unsecured loans:
Vacation (x5)	17
Student (x6)	10
Government securities (x7)	9

The bank has £5 million available for loans and investments in the next month. In making its decision, the planning committee must satisfy certain legal requirements and bank policies. These can be summarized by the following set of conditions:

1)        The amount allocated to secured loans must be at least four times the amount allocated to unsecured loans.

2)         Auto and home loans should not be more than 20% of all secured loans.

3)         Student loans should be no more than 30% of unsecured loans.

4)        The amount allocated to government securities should be at least 10%, but no more than 20%, of available funds

5)         The amount allocated to vacation loans must not exceed 10% of all loans.

Required:

Formulate a linear programming model that will enable planning committee to determine the optimal allocation of funds if the objective is to maximize the annual, given the preceding list of conditions.

Formulate this problem as an LP problem

Answer 1

Type of Investment	Annual Rate of Return
Secured
Residential morgage (x1)	0.11
Commercial morgage (x2)	0.12
Automoble (x3)	0.15
Home improvement (x4)	0.13
Unsecured
Vacation (x5)	0.17
Student (x6)	0.1
Government securities (x7)	0.09

Rate of returns are in percentage, converting these to fractions gives the above table

Decision Variables: Let the x₁,x₂,x₃,x₄,x₅,x₆,x₇ be the decision variables representing the amount invested in each type of investment in millions as mentioned in the above table.

Where  x₁,x₂,x₃,x₄ are secured loans and x₅,x₆,x₇ are unsecured loans

Objective Function: To maximize the annual return.

Zmax =  0.11x₁+0.12x₂+0.15x₃+0.13x₄+0.17x₅+0.1x₆+0.09x₇

Constraints:

x₁+x₂+x₃+x₄+x₅+x₆+x₇ <= 5

x₁+x₂+x₃+x₄ <= 4*(x₅+x₆+x₇ )

x₃+x₄ <= 0.2*(x₁+x₂+x₃+x₄)

x₆ <= 0.2*(x₅+x₆+x₇ )

x₅ <= 0.1*(x₁+x₂+x₃+x₄+x₅+x₆+x₇)

x₇ <= 0.2*5

x₇ >= 0.1*5

Solving the LP in solver:

The solver is an excel plug in which can be installed form excel options. After installation, it is available in the data segment of the excel sheet. Once installed and launched, the parameters can be added

Spreadsheet Model along with formula:

Adding Parameters to Solver:

1st: Enter Green highlighted cell (objective function) in the set objective field

2nd: Select Max

3rd: Enter the yellow cells (decision variables) in the by changing variable cells field

4th: In constraints, click on add, enter the blue cells in the dialogue box which appears.

On the left area (cell reference), enter the left side values, select relationships in the middle, and in the right enter the right side values of the inequality signs. Similarly, repeat for the next constraints by clicking on add button. Then click ok to go back to the parameters part.

5th; Select Simplex Lp in solving method

6th: Click solve

Solution:

The optimal allocation of funds as mentioned in the below table: (All values are in millions)

Residential mortgage (x1)	0
Commercial mortgage (x2)	3.2
Automobile (x3)	0.8
Home improvement (x4)	0
Vacation (x5)	0.5
Student (x6)	0
Government securities (x7)	0.5