Explore BrainMass

Linear Program For an Investment Using Excel Solver

Please solve the following problem in Excel 97-2003 using solver. Please show detailed breakdown of the problem to answer the following questions.

A credit union wants to make investments in the following:

Type of Investment Annual Rate of Return (%)
Vehicle Loans 7
Consumer Loans 9
Other secured loans 10
Signature Loans 11
Risk-Free securities 8

The firm will have $2,500,000 available for investment during the coming year. The following restrictions apply:

- Risk free securities may not exceed 30% of the total funds, but must comprise at least 5% of the total.
- Signature loans may not exceed 12% of the funds invested in all loans (vehicle, consumer, other secured loans and signature loans)
- Consumer loans plus other secured loans may not exceed the vehicle loans
- Other secured loans plus signature loans may not exceed the funds invested in risk free securities.

How should the $2,500,000 be allocated to each alternative to maximize annual return? What is the annual return?

Solution Summary

A complete, neat and step-by-step solution is provided in the attached Excel file.