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

利用EXCEL进行多项目最优投资组合及投资安排决策韩良智（北京科技大学管理学院，北京，100083）摘要：资金限额条件下投资项目的最优投资组合及投资安排是某些企业经常遇到的问题，企业对这些项目进行组合与投资安排时，不仅要考虑各项目的投资额大小，还要考虑项目投资的先后顺序。本文介绍了在EXCEL上进行这类投资决策问题求解的具体方法和步骤。关键词：资金限额 投资 优化在某些企业，很可能面对多个具有可行性的投资项目，但由于筹集资金数额以及筹资时间的限制，这些项目既不可能全部采用，也不可能在一年内全部投资，而是需要在这些项目中作出取舍，并分散在几个投资年度进行投资，这就要求企业对这些项目进行最优组合及作出投资安排计划，使企业取得最大效益（净现值）。笔者结合实例说明利用EXCEL解决这类投资决策问题的具体方法和步骤。在下述的计算中，均假设项目无论在何年投资，其初始投资、净现金流量、以及相对于该项目投资年度的净现值均不变。1 利用EXCEL进行多项目最优投资组合及投资安排方法和步骤1.1 所有项目均在某年内一次性投资并于当年投产的情况在这种情况下，已知各个项目的初始投资及净现值，企业需要根据制订的投资年度计划及各投资年度的资金限额，优化组合及安排各个投资项目，即第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为企业的基准收益率。则此种情况下的最优组合决策模型为：（2）目标函数： 约束条件： （3）式中：Ii为项目i的初始投资，It, max为企业第t年的资金限额，表示第t1年剩余的投资资金。这里不计剩余投资资金的时间价值。例1 某企业现有6个备选项目，投资分2期进行，两期的投资限额分别为850万元和600万元，各个项目的净现值已估算完毕（见图1）。由于计算工艺或市场原因，项目A、B、C为三择一项目，项目B为D的预备项目，项目E和F为互斥项目。企业的基准收益率为15%。ABCDEFG1项目有关资料优化计算过程及结果2项目投资额（万元）净现值（万元）项目第0年第1年变量和3A200150A0 0 0 4B230100B1 0 1 5C350260C0 0 06D330200D1 0 17E280130E0 0 08F600280F0 1 19资金限制（万元）第0年第1年目标函数（净现值合计）543.48合计使用资金10850600实际使用资金560600116011实际资金限额850890合计剩余资金12项目A、B、C关系1 29013项目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”，项目BF的决策变量求和公式分别填入单元格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=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=1，即第0年投资项目B和D，第1年投资项目F，可得到最大净现值543.48万元，共使用资金1160万元，剩余资金290万元。12 某些项目分年度投资的情况在这种情况下，一些项目的投资分年度进行，而不是在一年内完成全部投资。这是较复杂的一种情况，其优化决策模型表达式比较复杂，下面结合具体例子说明这类问题如何在EXCEL上求解。【例2】某企业现有6个备选项目，各项目相互独立，每个项目均分2期进行投资，但不能跨期投资。企业计划在3年内对这些项目进行投资。图2为项目的有关资料。企业的基准收益率为15%。ABCDEFGHIJ1项目有关资料优化计算过程及结果2项目投资安排（万元）净现值（万元）项目决策变量变量和变量乘积3第1期第2期第0年第1年第2年4A8080150A1 1 0 00 5B12050100B1 1 0 0 6C110120200C1 1 0 00 7D7090120D1 1 0 00 8E806080E0 1 1 00 9F15070100F0 1 1 00 10资金限额第0年第1年第2年使用资金380570130共使用资金108011500500100资金限额500620150剩余资金合计2012目标函数726.52图2 投资项目最优组合决策根据以上资料，可以建立如下的优化决策模型：目标函数：约束条件：， ， ， ， ， 式中：Ii,1、Ii,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=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上进行计算，具有方便、迅速的优点，可以用于任何类型的投资组合优化决策问题。参 考 文 献1 韩良智. 应用Excel求解受资金限制的项目投资J.冶金经济与管理，2002（3）：2324Use EXCELL for Determining Optimized Multi-Project Investment Combination and ArrangementHan Liangzhi(School of Management, University of Science and Technology of Beijing, 100083)Abstract How to optimize investment combination and arrangement for an investment project with restricted fund is a problem some enterprises often encounter. To determine investment combination and arrangement for such projects, it is necessary to take account not only of the investment amount, but also the sequence of investment. This paper describes in detail methods of and proc