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

1、无利用利用 EXCEL 进行多项目最优投资组合及投资安排决策进行多项目最优投资组合及投资安排决策韩良智（北京科技大学管理学院，北京，100083）摘要：摘要：资金限额条件下投资项目的最优投资组合及投资安排是某些企业经常遇到的问题，企业对这些项目进行组合与投资安排时，不仅要考虑各项目的投资额大小，还要考虑项目投资的先后顺序。本文介绍了在 EXCEL 上进行这类投资决策问题求解的具体方法和步骤。关键词：关键词：资金限额投资优化在某些企业， 很可能面对多个具有可行性的投资项目， 但由于筹集资金数额以及筹资时间的限制，这些项目既不可能全部采用，也不可能在一年内全部投资，而是需要在这些项目中作出取舍，

2、并分散在几个投资年度进行投资，这就要求企业对这些项目进行最优组合及作出投资安排计划，使企业取得最大效益（净现值） 。笔者结合实例说明利用 EXCEL 解决这类投资决策问题的具体方法和步骤。在下述的计算中，均假设项目无论在何年投资，其初始投资、净现金流量、以及相对于该项目投资年度的净现值均不变。1 利用利用 EXCEL 进行多项目最优投资组合及投资安排方法和步骤进行多项目最优投资组合及投资安排方法和步骤1.1所有项目均在某年内一次性投资并于当年投产的情况所有项目均在某年内一次性投资并于当年投产的情况在这种情况下， 已知各个项目的初始投资及净现值， 企业需要根据制订的投资年度计划及各投资年度的资金

3、限额，优化组合及安排各个投资项目，即第 0 年先投资哪些项目，第 1年再根据第 0 年剩余的投资资金加上本年的资金限额安排哪些项目，等等。设第 t 年安排 i 项目的投资，以 xi,t表示项目 i 在第 t 年投资的决策变量，xi,t=1 表示在第 t 年对项目 i进行投资，xi,t=0 表示在第 t 年不对项目 i 进行投资，则选取的投资项目以第 t 年为投资起点的总净现值为miitiNPVx1,， 将各投资年度选取的投资项目的总净现值miitiNPVx1,看作是一个综合项目的净现金流量，则此综合项目的净现值（以第 0 年为起点）为：101,)()1 (1ptmiititNPVxkNPV（1

4、）式中：NPVi为项目 i 的的净现值（以该项目的投资年度为起点） ，m 为项目的个数，p 为企业计划安排投资的年数，k 为企业的基准收益率。则此种情况下的最优组合决策模型为：目标函数：101,)()1 (1)(ptmiititNPVxkNPVMax（2）无约束条件：、补充性等）之间的依赖性、互斥性其它约束条件（如项目或或) 1,.,2 , 1 , 0;,.,2 , 1(10),.,2 , 1(10) 1,.,2 , 1()(,10,11,max, 11max,1max, 00 ,ptmixmixptIxIIIxIIxtipttimiititmititimiii（3）式中：Ii为项目 i 的初

5、始投资，It, max为企业第 t 年的资金限额，miititIxI11,max, 1)(表示第t1 年剩余的投资资金。这里不计剩余投资资金的时间价值。例例 1某企业现有 6 个备选项目，投资分 2 期进行，两期的投资限额分别为 850 万元和600 万元，各个项目的净现值已估算完毕（见图 1） 。由于计算工艺或市场原因，项目 A、B、C 为三择一项目，项目 B 为 D 的预备项目，项目 E 和 F 为互斥项目。企业的基准收益率为15%。图 1投资项目最优组合及投资安排的 EXCEL 求解根据图 1 的有关资料，则可以列出如下的最优组合决策模型：目标函数：10, 6, 5, 4, 3, 2,

6、1)280130200260100150(15. 11ttttttttxxxxxNPVMax约束条件：ABCDEFG1项目有关资料优化计算过程及结果2项目投资额（万元）净现值（万元）项目第 0 年第 1 年变量和3A200150A0004B230100B1015C350260C0006D330200D1017E280130E0008F600280F0119 资金限制（万元）第 0 年第 1 年目标函数（净现值合计） 543.48合计使用资金10850600实际使用资金560600116011实际资金限额850890合计剩余资金12项目 A、B、C 关系129013项目 B、D 关系014项目

7、E、F 关系1无) 1 , 0; 6 , 5 , 4 , 3 , 2 , 1(100110)6 , 5 , 4 , 3 , 2 , 1(, 101011)(600280330350230200(8506006002803303502302000(850600280330350230200,1 , 10 , 11 ,0 ,1 , 61 , 50 , 60 , 51 , 41 , 20 , 40 , 21 , 31 , 21 , 10 , 30 , 20 , 10 , 60 , 50 , 40 , 30 , 20 , 11 , 61 , 51 , 41 , 31 , 21 , 10 , 60 ,

8、50 , 40 , 30 , 20 , 1tixxxAFEDCBAixxFExxxxDBxxxxCBAxxxxxxxxxxxxxxxxxxxxxxxxtiii或），年就不投资，即年投资，则在第在第例如，若项目束条件，（每个项目投资年度约、分别表示项目或为互斥项目）、（项目的预备项目）为（项目三选一）、（项目年投资额限制条件）第年投资额限制条件）第则利用 EXCEL 求解上述模型的步骤如下：（1）设计工作表格（如图 1 所示） ，其中单元格 E9 存放目标函数（净现值合计） ，计算公式为： “=SUMPRODUCT(C3:C8,E3:E8)+SUMPRODUCT(C3:C8,F3:F8)/1.1

9、5” ；单元格E3:F8 为变动单元格，存放决策变量 xi,t的值；（2）在单元格 G3 中输入项目 A 的决策变量求和公式“=E3+F3” ，项目 BF 的决策变量求和公式分别填入单元格 G4:G8，可采用复制方法，将单元格 G3 复制到单元格 G4:G8中即可而完成其他项目决策变量求和公式的输入；（ 3 ） 在 单 元 格 E10 输 入 第 0 年 的 实 际 资 金 使 用 量 计 算 公 式“=SUMPRODUCT(B3:B8,E3:E8)” ， 在单元格 F10 输入第 1 年的实际资金使用量计算公式和“=SUMPRODUCT(B3:B8,F3:F8)” ；在单元格 E11 中输入

10、第 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” ； 【等于】设置为 “最大” ； 【可变

11、单元格】 设置为 “$E$3:$F$8” ； 在 【约束】 中输入约束条件 “$E$10=$E$11，$F$10=$F$11 ， $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,

12、0= 0，x6,1=1，即第 0 年投资项目 B 和 D，第 1 年投资项目 F，可得到最大净现值 543.48 万元，共使用资金 1160 万元，剩余资金 290万元。12某些项目分年度投资的情况某些项目分年度投资的情况在这种情况下，一些项目的投资分年度进行，而不是在一年内完成全部投资。这是较复杂的一种情况，其优化决策模型表达式比较复杂，下面结合具体例子说明这类问题如何在EXCEL 上求解。【例例 2】某企业现有 6 个备选项目，各项目相互独立，每个项目均分 2 期进行投资，但不能跨期投资。企业计划在 3 年内对这些项目进行投资。图 2 为项目的有关资料。企业的基准收益率为 15%。无ABC

13、DEFGHIJ1项目有关资料优化计算过程及结果2项目投资安排（万元） 净现值（万元）项目决策变量变量和变量乘积3第 1 期第 2 期第 0 年第 1 年第 2 年4A8080150A110005B12050100B11006C110120200C110007D7090120D110008E806080E011009F15070100F0110010资金限额第 0 年第 1 年第 2 年使用资金380570130共使用资金108011500500100资金限额500620150剩余资金合计2012目标函数726.52图 2投资项目最优组合决策根据以上资料，可以建立如下的优化决策模型：目标函数：6

14、10 ,1 ,610 ,)(15. 11)(iiiiiiiNPVxxNPVxNPVMax约束条件：500611 ,0 ,iiiIx，0612,1 ,0 ,611 ,0 ,1 ,500IIxxIxxiiiiiiii）（）（，1612,2,100IIxiii02,0 ,iixx，2,3 ,0 ,iiixxx，10,或tix，)2 , 1 , 06 , 5 , 4 , 3 , 2 , 1(ti；式中：Ii,1、Ii,2分别为项目 i 在第 1 期、第 2 期的投资额，I0、I1分别为第 0 年和第 1 年剩余的投资资金，xi,t为决策变量。利用 EXCEL 求解上述模型时，可变单元格为 F4:H9；

15、目标单元格为 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 年使用资金计

16、算公式为 “=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” ； 【等于】设置为“最大” ；

17、 【可变单元格】设置为“$F$4:$H$9” ；在【约束】中输入的约束条件有： “$F$10=$F$11，$G$10=$G$11，$H$10=$H$11 ， $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

18、无开始投资、第 1 年对项目 E、F 进行投资，共可得到净现值 726.52 万元，共使用资金 1080万元，剩余资金 20 万元。作为此种情况的特例， 当选取的项目都必须同时进行投资安排时 （不管是在 1 年内完成投资，还是分期完成投资） ，则优化决策模型就可以大大简化。例如，若图 2 所有项目均安排在第0和第1年进行投资， 则优化决策模型为： 目标函数：611 ,0 ,)(iiiiNPVxxNPVMax；约束条件：titiiIIxmax,61,，xi,0-xi,1=0，xi,t=0 或 1，式中：xi,t为决策变量，xi,t=1 表示在第t 年接受项目 i，xi,t=0 表示在第 t 年拒

19、绝项目 i ；Ii,t为项目 i 在第 t 年使用的资金，Imax,t为第 t 年的资金限额，t=0，1。则此中情况下的最优结果为：对项目 A、B、C、D、E 投资，舍弃项目 F，得到净现值 650 万元，使用资金 860 万元，剩余资金 240 万元。2 结论结论对资金限额情况下的投资项目最优组合及投资安排决策， 利用线性规划方法， 建立其优化组合决策模型，并在 EXCEL 上进行计算，具有方便、迅速的优点，可以用于任何类型的投资组合优化决策问题。参参考考文文献献1 韩良智. 应用 Excel 求解受资金限制的项目投资J.冶金经济与管理，2002（3） ：2324Use EXCELLfor Determining Optimized Multi-Project InvestmentCombination andArrangementHan Liangzhi(School of Management, University of Science and Technology of Bei