A company invests in short-term trade credits (TC), corporate bonds (CB), gold stocks (GS) and construction loans (CL). To encourage a diversified portfolio, the board of directors has placed limits on the amount that can be committed to any one type of investment. The company has $5,000,000 available for immediate investment and wishes to do two things:
1. Maximise the interest earned on the investments made over the next six months, and
2. Satisfy the diversification requirements as set out by the board of directors.
The specifics of the investment possibilities are:-
Investment Interest Earned (%)
Trade Credit (TC) 7
Corporate Bonds (CB) 11
Gold Stocks (GS) 19
Construction Loans (CL) 15
Investment Maximum Investment ($ Millions)
Trade Credit (TC) 1.0
Corporate Bonds (CB) 2.5
Gold Stocks (GS) 1.5
Construction Loans (CL) 1.8
In addition the board specifies that at least 55% of the funds invested must be in gold stocks and construction loans, and that no less than 15% be invested in trade credit.
I came out with the following....
Decision Variables
X1= Amount of dollars in millions invested in Trade Credit
X2= Amount of dollars in millions invested in Corporate Bonds
X3= Amount of dollars in millions invested in Gold Stocks
X4= Amount of dollars in millions invested in Construction Loans
Objective Function
0.07(X1) + 0.11(X2) + 0.19(X3) + 0.15(X4) = Z (maximize interest earned on investment)
Constraints
X1, X2, X3, X4 > 0 (Non-negativity constraint)
X1 + X2 + X3 + X4 < 5 (Investment Budget constraint)
X1 < 1 (Maximum investment constraint)
X2 < 2.5 (Maximum investment constraint)
X3 < 1.5 (Maximum investment constraint)
X4 < 1.8 (Maximum investment constraint)
-0.55X1-0.55X2+0.45X3+0.45X4
0.85X1-0.15X2-0.15X3-0.15X4
I am required to solve this using excel solver....And that's what i got below....
Decision Variables
x1 0.75
x2 0.95
x3 1.5
x4 1.8
objective fuction
0.07 0.11 0.19 0.15
max investement
0.712
Constraints
LHS RHS
5.00 1 1 1 1 5
0.75 1 0 0 0 1
0.95 0 1 0 0 2.5
1.50 0 0 1 0 1.5
1.80 0 0 0 1 1.8
0.55 -0.55 -0.55 0.45 0.45 0
0.00 0.85 -0.15 -0.15 -0.15 0
Now i am not sure whether the LHS(highlight portion) of the excel should match the data in X3 + X4, should it be 3.3 instead of the 0.55 but the formula i entered is according to the constraints i developed which is -0.55X1-0.55X2+0.45X3+0.45X4 and for the last row should the highlight portion be 0.75, as the formula on the Left hand side is
0.85X1-0.15X2-0.15X3-0.15X4
Can pls tell me whether my constraint is correct in this sense? Is the whole ans correct? Pls help...tks...