I have been trying to do this for some time now, and I have set it up, but it doesn't work when I solve it using Excel Solver. Can someone please set it up (objective fxn and contraints)?

Problem:

Winston saving has $20 million available for investment. It wishes to invest over the next four months in such way that will maximize the total interest as well as have at least $10,000,000 available at the start of month 5 for a high rise building.

For the time being, Winston wishes to invest only in 2-month government bonds, (earning 2% over the 2-month period), and 3-month construction loans (earning 6% over the 3-month period). Each of these is available each month for investment. Funds not invested in these two investments are liquid and earn 0.75% per month when invested locally.

Also, no more than $8,000,000 should be invested in government bonds at any time. And, no more than $8,000,000 should be invested in construction loans at any time.


This is my setup:

Define Variables
Gj: Amount invested in Government Bonds (GB) in month j, where j = 1,2,3,4
Cj: Amount invested in Construction Loans (CL) in month j, where j = 1,2,3,4
Lj: Amount invested in Local Investments (LI) in month j, where j = 1,2,3,4


Objective function

Maximize 0.02G1 + 0.02G2 + 0.02G3 + 0.02G4 + 0.06C1 + 0.06C2 + 0.06C3 + 0.06C4 + 0.0075L1 + 0.0075L2 + 0.0075L3 + 0.0075L4

Month 1: G1 + C1 + L1               <=  20,000,000
Month 2: G2 + C2 + L2               <=  1.0075L1
Month 3: G3 + C3 + L3               <=  1.02G1 + 1.0075L2
Month 4: G4 + C4 + L4               <=  1.06C1 + 1.02G2 + 1.0075L3
Month 5: 1.06C2 + 1.02G3 + 1.0075L4 >=  10,000,000

Month 1: G1       <= 8,000,000
Month 2: G1 + G2  <= 8,000,000
Month 3: G2 + G3  <= 8,000,000
Month 4: G3 + G4  <= 8,000,000

Month 1: C1            <= 8,000,000
Month 2: C1 + C2       <= 8,000,000
Month 3: C1 + C2 + C3  <= 8,000,000
Month 4: C2 + C3 + C4  <= 8,000,000

12/28/2008 3:05:31 PM

OK, I did it. I had a silly mistake in how I entered it in Excel (negative signs where they shouldn't have been).

Now, just help me decide between the 2 models I have made, as I explained above. (one with G4, C3, and C4, and one without them)

12/28/2008 4:47:27 PM

Simplex method, its always the simplex method.

I hated this class...

12/28/2008 6:55:43 PM

0-1 knapsack

[Edited on December 29, 2008 at 12:05 AM. Reason : there might be a one-liner to do this in matlab optimization toolbox]

12/29/2008 12:04:04 AM

I am not interested in any advanced methods for solving it.

I am talking about setting up the problem, i.e., making the contraints.

And even if you use Matlab or some powerful program to solve this, you STILL need to set it up first.

So, which model is correct? With G4, C3, and C4, or without?

12/30/2008 6:37:49 AM

I think that you should include them. The problem setup sounds like they want to invest over a 4 month period, ie. invest monthly for 4 months.

12/31/2008 1:56:50 AM