Professor VanderWerf, Sections 6 and 7
Name : _____________________________________________________
Gringotts Bank can put money into a variety of loan and investment types, each with its own annual rate
of return, as listed in the spreadsheet below. The bank wishes to put up to $2 million in the investments
in a way that will maximize its total return for the coming year. However it must follow several
1. Risk-free securities may not exceed 30% of the total amount invested.
2. Signature loans may not exceed 10% of the funds invested in other types of loans (automobile,
furniture, and other secured).
3. Furniture loans plus other secured loans may not exceed the automobile loans.
4. Other secured loans plus signature loans may not exceed the funds i nvested in risk-free securities.
Part a: Using correct EXCEL notation, fill in all cells for therisk-free securities constraint and the
furniture and secured loans constraint in the spreadsheet below. (Thecells for decision variables and
the objective function are shaded gray.)
=C8-D8*(C4+C5+C6+C7+C8 ) 0
NOTE: Other ways to write these constraints that are mathematically equivalent (and still linear) are
also acceptable. Some of these include (see next page): 2/16/2012
Some other acceptable versions of the risk-free securities constraint:
=C8 <= =D8*(C4+C5+C6+C7+C8)
=C8 – 0.3*(C4+C5+C6+C7+C8) <= 0
=0.7*C* - 0.3*C4 – 0.3*C5- 0.3*C6 – 0.3*C7 <= 0
using SUM(C4:C8) instead of (C4+C5+C6+C7+C8) is also OK
Cannot use 2,000,000 or D9 (total funds available) for (C4+C5+C6+C7+C8) (total funds invested).
Some other acceptable versions of the furniture loan/secured loan constraint:
=C5 + C6 <= =C4
Part b: Gringotts’ Operations Research Department ran the investment model and produced the
sensitivity report below. Based on this report, answer the following questions in the space provided.
1. Management has just learned that the interest rate on furniture loans has changed to 0.07. Will
this have any impact on how much money Gringotts should put in each type of investment?