Hello, I have seen other answers to this question with other numbers, but could

Question

Hello, I have seen other answers to this question with other numbers, but could you please explain how to use the numbers to answer the questions? If you could, can you explain how to use excel for this question. Thank you.

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 revenueproducing investments together with annual rates of return are as follows: Type of Loan/Investment Annual Rate of Return (%) Automobile loans Furniture loans Other secured loans Signature loans Risk-free securities 8 12 14 13 The credit union will have $1.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: o 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) o Furniture loans plus other secured loans may not exceed the automobile loans o Other secured loans plus signature loans may not exceed the funds invested in risk-free securities How should the $1.3 million be allocated to each of the loan/investment alternatives to maximize total annual return? Round your answers to the nearest dollar Automobile Loans Furniture Loans Other Secured Loans$ Signature Loans Risk Free Loans What is the projected total annual return? Round your answer to the nearest dollar

Explanation / Answer

Let us first define various given loan amount in variables to solve the given problem with the help of linear programming.

let's say, x1 = $ automobile loans

x2 = $ furniture loans

x3= $ other secured loans

x4 = $ signature loans

x5 = $ risk-free securities

Formulation:

Max ( total annual return ) = 0.08x1 + 0.12x2 + 0.14 x3 + 0.13 x4 + 0.09 x5

s.t. ,

x5 3,90,000 ( 30 % of 1,30,00,00)......................(1)

x4 0.10 ( x1+x2+x3+x4)

-0.10x1-0.10x2-0.10x3+0.90x4 0.......................(2)

x2+x3 x1

-x1+x2+x3 0 ............................................(3)

x3+x4 x5

-x5+x3+x4 0.................................(4)

x1+x2+x3+x4+x5 = 13,00,000............(5)

x1,x2,x3,x4,x5 0 .........(non-negativity)

Excel solution in using excel solver:

Answer : Maximum projected total return value Z = $1,34,810

for your reference , please find attached link of excel sheet.

https://www.dropbox.com/s/xhhb2g3l4i0v99o/excel%20solver%20for%20investment.xlsx?dl=0

investment function x1 x2 x3 x4 x5 409500 110500 299000 91000 390000 Z 0.08 0.12 0.14 0.13 0.09 134810 total RHS Constraints 0 0 0 0 1 390000 3,90,000 -0.1 -0.1 -0.1 0.9 -1.45519E-11 0 -1 1 1 0 0 1 1 -1 0 0 1 1 1 1 1 1300000 = 13,00,000

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.