利用EXCEL进行多项目最优投资组合及投资安排决策

利用EXCEL进行多项目最优投资组合及投资安排决策：图1 投资项目最优组合及投资安排的EXCEL求解

根据图1的有关资料，则可以列出如下的最优组合决策模型：

约束条件：

则利用EXCEL求解上述模型的步骤如下：
（1）设计工作表格（如图1所示），其中单元格E9存放目标函数（净现值合计），计算公式为：“=SUMPRODUCT(C3:C8,E3:E8)+SUMPRODUCT(C3:C8,F3:F8)/1.15”；单元格E3:F8为变动单元格，存放决策变量x_i,t的值；
（2）在单元格G3中输入项目A的决策变量求和公式“=E3+F3”，项目B～F的决策变量求和公式分别填入单元格G4:G8，可采用复制方法，将单元格G3复制到单元格G4:G8中即可而完成其他项目决策变量求和公式的输入；
（3）在单元格E10输入第0年的实际资金使用量计算公式“=SUMPRODUCT(B3:B8,E3:E8)”，在单元格F10输入第1年的实际资金使用量计算公式和“=SUMPRODUCT(B3:B8,F3:F8)”；在单元格E11中输入第0年资金限额“=B10”；在单元格F11中输入第1年资金限额计算公式“=C10+(E11-E10)”；
（4）在单元格E12中输入项目A、B、C关系的约束条件计算公式“=SUM(E3:F5)”；在单元格E13中输入项目B、D关系的约束条件计算公式“=E4-E6+F4-F6”；在单元格E14中输入项目E、F关系的约束条件计算公式“=SUM(E7:F8)”；
（5）单击EXCEL【工具】菜单，选择【规划求解】项，出现【规划求解参数】对话框；在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$E$9”；【等于】设置为“最大”；【可变单元格】设置为“$E$3:$F$8”；在【约束】中输入约束条件“$E$10<=$E$11，$F$10<=$F$11，$E$3:$F$8<=1，$E$3:$F$8>=0，$E$3:$F$8=整数，$G$3:$G$8>=0，$G$3:$G$8<=1，$E$12=1，$E$13=0，$E$14=1”；
（6）单击【求解】，即可得到优化的结果（如图1所示），并出现【规划求解结果】对话框，然后按确定键，保存规划求解结果。最终优化结果为：x_1,0=0，x_1,1=0；x_2,0=1，x_2,1=0；x_3,0=0，x_3,1=0；x_4,0=1，x_4,1=0；x_5,0=0，x_5,1=0；x_6,0=0，x_6,1=1，即第0年投资项目B和D，第1年投资项目F，可得到最大净现值543.48万元，共使用资金1160万元，剩余资金290万元。

1．2 某些项目分年度投资的情况

在这种情况下，一些项目的投资分年度进行，而不是在一年内完成全部投资。这是较复杂的一种情况，其优化决策模型表达式比较复杂，下面结合具体例子说明这类问题如何在EXCEL上求解。

【例2】某企业现有6个备选项目，各项目相互独立，每个项目均分2期进行投资，但不能跨期投资。企业计划在3年内对这些项目进行投资。图2为项目的有关资料。企业的基准收益率为15%。

利用EXCEL进行多项目最优投资组合及投资安排决策：图2 投资项目最优组合决策

根据以上资料，可以建立如下的优化决策模型：

式中：I_i,1、I_i,2分别为项目i在第1期、第2期的投资额，DI0、DI1分别为第0年和第1年剩余的投资资金，xi,t为决策变量。

利用EXCEL求解上述模型时，可变单元格为F4:H9；目标单元格为F12，计算公式为“=SUMPRODUCT(D4:D9,F4:F9)+SUMPRODUCT(D4:D9,G4:G9-F4:F9)/1.15”（注意为数组输入，需同时按“Shift+Ctrl+Enter”键）；单元格I4:I9存放各项目决策变量和公式（如I4中为“=F4+H4-G4”，其他各行可以此复制）；单元格J4:J9中存放各项目的变量乘积（如J4中为“=F4*G4”，其它以此类推）；第0、1、2年使用资金在单元格F10、G10、H10中，其中第0年使用资金计算公式分别为“=SUMPRODUCT(B4:B9,F4:F9)”、第1年使用资金计算公式为“=SUMPRODUCT(B4:B9,G4:G9-F4:F9)+SUMPRODUCT(C4:C9,F4:F9,G4:G9)”（也为数组输入，需同时按“Shift+Ctrl+Enter”键）、第2年资金使用量计算公式“=SUMPRODUCT(C4:C9,H4:H9)”；各年的资金限量存放在单元格F11、G11、H11中，计算公式分别为：“=B11”、“=C11+F11-F10”、“=D11+G11-G10”。

在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$F$12”；【等于】设置为“最大”；【可变单元格】设置为“$F$4:$H$9”；在【约束】中输入的约束条件有：“$F$10<=$F$11，$G$10<=$G$11，$H$10<=$H$11，$F$4:$H$9<=1，$F$4:$H$9>=0，$F$4:$H$9=整数，$I$4:$I$9=0，$J$4:$J$9=0”。需要注意的是，如果决策变量的初始值设置的不合适的话，就可能得不到最优解。经过计算实践，一般对净现值大的项目的第0年和第1年的决策变量设置为1（即单元格G4:G9中设置为零）、而净现值最小的项目的决策变量可以设置为零比较合适。

则优化计算结果如图2所示，最优投资组合及安排如下：第0年对项目A、B、C、D开始投资、第1年对项目E、F进行投资，共可得到净现值726.52万元，共使用资金1080万元，剩余资金20万元。

作为此种情况的特例，当选取的项目都必须同时进行投资安排时（不管是在1年内完成投资，还是分期完成投资），则优化决策模型就可以大大简化。例如，若图2所有项目均安排在第0和第1年进行投资，则优化决策模型为：目标函数：；约束条件；xi,0-xi,1 =0，xi,t =0或1，式中：xi,t为决策变量，xi,t =1表示在第t年接受项目i，xi,t =0表示在第t年拒绝项目i ；I i,t为项目i在第t年使用的资金，I max,t为第t年的资金限额，t=0，1。则此中情况下的最优结果为：对项目A、B、C、D、E投资，舍弃项目F，得到净现值650万元，使用资金860万元，剩余资金240万元。

2 结论

对资金限额情况下的投资项目最优组合及投资安排决策，利用线性规划方法，建立其优化组合决策模型，并在EXCEL上进行计算，具有方便、迅速的优点，可以用于任何类型的投资组合优化决策问题。