Consider the case of an investment fund manager, who currently has $500,000 for
ID: 465903 • Letter: C
Question
Consider the case of an investment fund manager, who currently has $500,000 for the purchase of securities to add to an existing portfolio. The following table gives the possibe selections and their expected rate of return (%)
GM Stock 10.3
Ford Stock 10.1
TI Stock 11.8
Xerox Stock 11.4
NMC Stock 12.7
SMS Stock 12.2
GM Bonds 9.5
Xerox Bonds 9.9
Short-Term Gov't Bonds 8.6
Long Term Gov't Bonds 9.2
I am to solve maximizing the return with Linear Programming in an excel spreadsheet while using the solver program. How do I lay out the spreadsheet and then use solver to maximize the return and solve the problem.
Explanation / Answer
Define your decision variables as the amount to be invested in type of stock and bond. For example
GM Stock X1
Ford Stock --X2
TI Stock --X3
Xerox Stock --X4
NMC Stock --X5
SMS Stock --X6
GM Bonds--X7
Xerox Bonds --X8
Short-Term Gov't Bonds--X9
Long Term Gov't Bonds---X10
As mentioned Objective is to Maximize the return so write it as a function of decision variables.In terms of above mentioned variables .103X1 + .101X2 + .118X3 + .113X4 + .127X5 + .122X6 + .095X7 + .099X8 + .086X9 + .092X10
Constraints are in the form of limitations on the investment decision variables. In the given case it is about the total amount of $500,000.
say X1+X2+X3+X4+X5+X6+X7+X8+X9+X10 = 500000
Non-negativity constraint X1,X2,X3,X4,X5,X6,X7,X8,X9 and X10 >= 0
In the absence of any other conditions/ constraints, solution is obvious to go for the stock/bond with the maximum expected rate of return. Therefore answer is to go for NMC stock with expected return of 12.7%
Excel presentation of the problem is as follows:
Solution using solver as follows:
Decision Variables(D.V.) X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 SumProduct Values of D.V. Values Objective function 0.103 0.101 0.118 0.114 0.127 0.122 0.095 0.099 0.086 0.092 0 sign RHS Constraint 1 1 1 1 1 1 1 1 1 1 0 <= 500000