Question

Adirondack Savings Bank (ASB) has $1 million in new funds that must be allocated to home loans, personal loans, and automobile loans. The annual rates of return for the three types of loans are 5% for home loans, 11% for personal loans, and 11% for automobile loans. The bank’s planning committee has decided that at least 40% of the new funds must be allocated to home loans. In addition, the planning committee has specified that the amount allocated to personal loans cannot exceed 60% of the amount allocated to automobile loans. (a) Formulate a linear programming model that can be used to determine the amount of funds ASB should allocate to each type of loan to maximize the total annual return for the new funds. If the constant is "1" it must be entered in the box. If your answer is zero enter “0”. Let H = amount allocated to home loans P = amount allocated to personal loans A = amount allocated to automobile loans Max H + P + A s.t. H + P + A ≥ Minimum Home Loans H + P + A ≤ Personal Loan Requirement H + P + A = Amount of New Funds (b) How much should be allocated to each type of loan? Loan type Allocation Home $ Personal $ Automobile $ What is the total annual return? If required, round your answer to nearest whole dollar amount. $ What is the annual percentage return? If required, round your answer to two decimal places. % (c) If the interest rate on home loans increases to 9%, would the amount allocated to each type of loan change? - Select your answer - Explain. The input in the box below will not be graded, but may be reviewed and considered by your instructor. (d) Suppose the total amount of new funds available is increased by $10,000. What effect would this have on the total annual return? Explain. If required, round your answer to nearest whole dollar amount. An increase of $10,000 to the total amount of funds available would increase the total annual return by $ . (e) Assume that ASB has the original $1 million in new funds available and that the planning committee has agreed to relax the requirement that at least 40% of the new funds must be allocated to home loans by 1%. How much would the annual return change? If required, round your answer to nearest whole dollar amount. $ How much would the annual percentage return change? If required, round your answer to two decimal places. %

Answer 1

A)

A linear programme model is a mathematical mode with a linear objective function a set of constraints and variables. Let H be the amount allocated to home loans, P be the amount allocated to personal loan and A be the amount allocated to automobile loans.

max	0.07H+0.12P+0.09A
st
	H+P+A=1.000,000	amount of new fund
	0.6H-0.4P-0.4A0	minimum home loans
	P.0.6A0	personal loan requirement
	H.P.A

B)

Use excel to solve for the optimal solution and sensitivity report using the following steps.

1. select the data tab from the ribbon

2. select solver from the analysis group

3. Where the solver parameters dialog appears

enter the objective function

select the To: max option

enter the decision variable into the by changing variables calls box

select add

4. Where the add constraint dialog box appears:

enter the constraints in the cell reference box

select<=

enter the right hand side in teh constrain box click ok

5. When the solver parameters dialog box appears , click the check box make unconstrained variables non- negative

6. select the select solving method drop down button

select simpler LPA

7. CLICK SOLVE

8. When the solver results dialog box appears

select keep solver solution

select sensitivity in the report box

the optimal solution is H=400,000, P=225,000 and A=375,000 substitutes these values into the objective function to find its value

0.07H+0.12P+0.09A=0.07 (400,000) +0.12(225,000)+0.09(375,000)

  = 28.000+27.000+33.750

=88.750

annual percentage return is the ratio of the total income divided by the amount of funds expressed by a percentage.

88.750 / 1,000,000 x 100

=8.875%

C

From the sensitivity report created by EXCEL , the objective cefficient range for H is no lower limit 0.101. Since 0.09 is within the range the optimal solution remains H=400,000 P=225,000 A= 375,000

D

From the sensitivity report created by EXCEL , The shadow price for new funds is 0.069 . The right hand side range for new funds is 0 to no upper limit, then if new funds available increased to 810,000 the shadow price is applicable.

E

The new linear model is a follows:

max	0.07H+0.12P+0.09A
st
	H+P+A=1.000,000	amount of new fund
	0.61H-0.39P-0.39A>0	minimum home loans
	P.0.6A0	personal loan requirement
	H.P.A 0

use excel to solve for the optimal solution and sensitivity report using the following steps.

1. select the data lab from the ribbon

2. select solve from the analysis group

3. when the solver parameters dialog appears

enter the objective function

select the To:max option

enter the decision variable into the by changing variable cell box

select add

4. when the add constrain dialog box appears

enter the constrain in the cell reference  box select<=

enter the right side in the constrain box click ok

5. when the solver parameter dialog box appears click the checkbox for making unconstrained variable non-negative

6. select the select solving method drop down button

select simpler LPA

7. CLICK SOLVE

8. When the solver results dialog box appears

select keep solver solution

select sensitivity in the report box

the optimal solution is H=390,000, P=228,750 and A=381,250 substitutes these values into the objective function to find its value

  0.07H+0.12P+0.09A=0.07 (390,000) +0.12(228,750)+0.09(381,250)

  = 27,300+27,450+34,312.5

=89.0625

the change of annual return is the new value minus the old value

89.0625-88.750=312.5

annual return would increase by 5312.50

annual percentage return is the ratio of the total annual return divided by the amount of funds expressed by a percentage.

89.0625 / 1,00,000 x 100 = 8.90625%