EXCEL进行最优投资组合及投资安排决策.docVIP

利用EXCEL进行多项目最优投资组合及投资安排决策 韩良智 （北京科技大学管理学院，0年先投资哪些项目，第1年再根据第0年剩余的投资资金加上本年的资金限额安排哪些项目，……，等等。设第t年安排i项目的投资，以xi,t表示项目i在第t年投资的决策变量，xi,t =1表示在第t年对项目i进行投资，xi,t=0表示在第t年不对项目i进行投资，则选取的投资项目以第t年为投资起点的总净现值为，将各投资年度选取的投资项目的总净现值看作是一个综合项目的净现金流量，则此综合项目的净现值（以第0年为起点）为： （1） 式中：NPVi为项目i的的净现值（以该项目的投资年度为起点），m为项目的个数，p为企业计划安排投资的年数，k为企业的基准收益率。则此种情况下的最优组合决策模型为： 目标函数： 约束条件： （3） 式中：Ii为项目i的初始投资，It, max为企业第t年的资金限额，表示第t–1年剩余的投资资金。这里不计剩余投资资金的时间价值。 例1 某企业现有6个备选项目，投资分2期进行，两期的投资限额分别为850万元和600万元，各个项目的净现值已估算完毕（见图1）。由于计算工艺或市场原因，项目A、B、C为三择一项目，项目B为D的预备项目，项目E和F为互斥项目。企业的基准收益率为15%。 A B C D E F G 1 项目有关资料 优化计算过程及结果 2 项目 投资额（万元） 净现值（万元） 项目 第0年 第1年 变量和 3 A 200 150 A 0 0 0 4 B 230 100 B 1 0 1 5 C 350 260 C 0 0 0 6 D 330 200 D 1 0 1 7 E 280 130 E 0 0 0 8 F 600 280 F 0 1 1 9 资金限制 （万元） 第0年 第1年 目标函数（净现值合计） 543.48 合计使用资金 10 850 600 实际使用资金 560 600 1160 11 实际资金限额 850 890 合计剩余资金 12 项目A、B、C关系 1 290 13 项目B、D关系 0 14 项目E、F关系 1 图1 投资项目最优组合及投资安排的EXCEL求解 根据图1的有关资料，则可以列出如下的最优组合决策模型： 目标函数： 约束条件： 则利用EXCEL求解上述模型的步骤如下： （1）设计工作表格（如图1所示），其中单元格E9存放目标函数（净现值合计），计算公式为：“=SUMPRODUCT(C3:C8,E3:E8)+SUMPRODUCT(C3:C8,F3:F8)/1.15”；单元格E3:F8为变动单元格，存放决策变量xi,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所示），并出现【规划求解结果】对话框，然后按确定键，保存规划求解结果。最终优化结果为：x1, 0=0，x1, 1=0；x2, 0=1，x2, 1=0；x3, 0=0，x3, 1=0；x4, 0=1，x4, 1=0；x5,0= 0，x5,1= 0；x6,0= 0，x6,1=