Question

The employee credit union at State University is planning the allocation of funds for the coming year. The credit union makes four types of loans to its members. In addition, the credit union invests in risk-free securities to stabilize income. The various revenue-producing investments together with annual rates of return are as follows:

Type of Loan/Investment	Annual Rate of Return (%)
Automobile loans	7
Furniture loans	10
Other secured loans	11
Signature loans	12
Risk-free securities	18

The credit union will have $2.3 million available for investment during the coming year. State laws and credit union policies impose the following restrictions on the composition of the loans and investments:

• Risk-free securities may not exceed 30% of the total funds available for investment.

• Signature loans may not exceed 10% of the funds invested in all loans (automobile, furniture, other secured, and signature loans).

• Furniture loans plus other secured loans may not exceed the automobile loans.

• Other secured loans plus signature loans may not exceed the funds invested in risk-free securities.

How should the $2.3 million be allocated to each of the loan/investment alternatives to maximize total annual return?

Type of Loan/Investment	Fund Allocation
Automobile loans	$
Furniture loans	$
Other secured loans	$
Signature loans	$
Risk-free securities	$

What is the projected total annual return?

Annual Return = $

Answer 1

We are going to solve a maximization linear programming problem.

First, we will setup the problem in Excel.

Decision variables are: A = Funds invested in Automobile loans

F = Funds invested in Furniture loans and so on..

Annual Returns formula = Sum of (Funds invested in each loan * Return % on each loan)

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

The formulae are given as:

--------------------------------------------------------------------------------------------------------------------------

Constraint setup:

1) Total investment available should be less than or equal to the budget of $2,300,000

Hence, A + F + O + S + R <=2,300,000

2) For risk-free securities, they cannot exceed 30% of the total budget.

Hence, R<=0.3*2300000

That is why all other coefficients are 0

3) Signature loans:

S<=0.1*(A+F+O+S) [They cannot exceed 10% of funds invested in all other type of loans/ Note that securities are not considered loans, hence R is not included]

Simplifying this, we get:

0.9S - 0.1A - 0.1F - 0.1O <= 0

4) Furniture plus other loans:

F+O<=A

F + O - A <= 0

5) Other secured plus signature loans:

O+S<=R

O + S - R <=0

--------------------------------------------------------------------------------------------------------------------------

Now, we invoke Solver add-in to solve this linear programming model that we have built.Hope you have Solver Add-in activated in Excel. If not, you can lookup how to do that.

Our maximization variable is Annual returns which is in yellow cell and the decision variables are in the green cells.

Setting them up in Solver:

We specify the constraints together as all of them are <= constraints. We can select all Left side values which should be equal to the right side values i.e. the constraints.

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

On clicking solve: We get:

--------------------------------------------------------------------------------------------------------------------------

The values in the green cells are the answers for the apt amount to be invested in each type of investment option.

The maximum annual return we can get with those investments satisfying all the conditions is $271,975

--------------------------------------------------------------------------------------------------------------------------

Kindly comment in case of any clarifications needed. Thanks :)