excel投资决策.doc

利用EXCEL进行多项目最优投资组合及投资安排决策2004-3-22 15:12:09【作者】畅享网韩良智（北京科技大学管理学院，北京，100083）摘要：资金限额条件下投资项口的最优投资组合及投资安排是某些企业经常遇到的问题， 企业对这些项H进行组合与投资安排时，不仅要考虑各项H的投资额大小，还要考虑项H投资的 先后顺序木文介绍了在EXCEL上进行这类投资决策问题求解的具体方法和步骤关键词：资金限额投资优化在某些金业，很可能而对多个具有可行性的投资项H,但由于筹集资金数额以及筹资时间 的限制，这些项目既不可能全部采用，也不可能在一年内全部投资，而是需要在这些项目中作出 取舍，并分散在儿个投资年度进行投资，这就要求企业对这些项目进行最优纽•合及作出投资安排 计划，使企业取得最人效益（净现值）笔者结合实例说明利JIJ EXCEL解决这类投资决策问题 的具体方法和步骤在下述的计算中，均假设项目无论在何年投资，其初始投资、净现金流量、 以及相对于该项目投资年度的净现值均不变1利用EXCEL进行多项目最优投资组合及投资安排方法和步骤1.1所有项目均在某年内一次性投资并于当年投产的情况在这种情况卜，已知各个项0的初始投资及净现值，企业需要根据制订的投资年度计划及各投资年度的资金限额，优化组合及安排各个投资项H,即第0年先投资哪些项H,第1年再根据第0年剩余的投资资金加上本年的资金限额安排哪些项口，……，等等。

设第t年安排i项目的投资， 以Xi,t表示项目i在第t年投资的决策变量,Xi,21表示在第t年对项目i进行投资,Xi,t=O表示在第i年不对项忖i进行投资，则选取的投资项H以第i年为投资起点的总净现值为2-1，将各投资年度选取的投资项冃的总净现值2-1看作是一个综合项冃的净现金流最，则此综合项目的净现值（以第0年为起点）为:丫柳“:自為自和•护岭H⑴式中：NPVj为项目i的的净现值（以该项目的投资年度为起点），m为项目的个数，p为企业计划安排投资的年数，k为企业的基准收益率则此种悄况下的最优组合决策模型为：3工吹八煎馬軒"}] （2）(21,2, ・，』■ J (J)札二0 或 I (/af J.2, —% = Q 或 1 2 严■廨"•,! ,2,…中-1 >I其瞬東和 晰且那咖I性环ttd湘娜r式中：Ii为项目i的初始投资，It, max为企业第t年的资金限额,表示第t-1年剩余的投资资金这里不计剩余投资资金的时间价值例1某企业现有6个备选项冃，投资分2期进行，两期的投资限额分别为850力元和600 万元，各个项目的净现值已估算完毕（见图1）由于计算工艺或帀场原因，项目A、B、C为 三择一项目，项目B为D的预备项目，项目E和F为互斥项目。

企业的基准收益率为15%A」BCEF I I1顼目有关资料优化计算过程及结果2項目投賢■（万元）妙现值（万元）項冃第0年1年43A200150A004B230100B105C350260C006D330200D107E280130E008F600280F019资金限明第0年第丨年忖标断数（净现儼合计）543.48合tH10（万无）&50600实标使用资金560600I11实际资金限畝&50890合计*12頊H A.B.C关系1<13项目B、D关系014项目E、F关系图1投资项目最优组合及投资安排的EXCEL求解根据图1的有关资料，则可以列出如下的最优组合决策模型:目杯■ ■八 1001 冲 *200^ + 130^ t28O^.JJ约束条件:pn0x9fi ♦23O^a +35&口 +330^ +280^ +600^^850 < 第 0 嶽资限制条200% 十23Qr和230和"W%. +600 壬<+850・（20垢』+23Q需“★355“ +• 斑心十280^*600%） （第1年投资限制杀知> 卡曲』+玖Q于1iu +xKi ■习j Fjj怜-和= F十咤,q十驹」十宛J室Q十升」=0或X（與目A、B、C三述 （锁目B为D游预岱项 （项目E芒为互斥项 （4 = 1.2,33,5^分别表示项目仏B&D、E”每个碘目铁资年度紳束条 例如》若顼目A在第0年投陡朋在第1年就不投见即飢严!內・1 :（i =》b2.3.4 •$■&>$# =<则利用EXCEL求解上述模烈的步骤如下:（1）设计丁作表格（如图1所示），其中单元格E9存放H标函数（净现值合计），计算公式为："=SUMPRODUCT(C3:C&E3:E8)+SUMPRODUCT(C3:C&F3:F8)/1」5S单元格E3:F8为变动单元格，存放决策变量Xi,t的值；（2） 在单元格G3中输入项目A的决策变量求和公式JE3+F3”，项口 B〜F的决 策变量求和公式分别填入单元格G4:G8,可采用复制方法，将单元格G3复制到单 元格G4:G8中即可而完成其他项日决策变量求和公式的输入；（3） 在单元格E10输入第0年的实际资金使用量计算公式 “=SUMPRODUCT（B3:B&E3:E8八在单元格F10输入第1年的实际资金使用量计算 公式和“=SUMPRODUCT（B3:B&F3:F8）S在单元格Ell中输入第（）年资金限额“二B10"；在单元格F11中输入第1年资金限额计算公式"=C10+（Ell-E10r；（4） 在单元格E12中输入项目A、B、C关系的约束条件计算公式“=SUM（E3:F5八 在单元格E13中输入项目B、D关系的约束条件计算公式“=E4・E6+F4・F6S在单元 格E14中输入项目E、F关系的约束条件计算公式t4=SUM（E7:F8f；（5） 单击EXCEL【T具】菜单，选择【规划求解】项，出现【规划求解参数】对话框；在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$E$9S【等于】设置为“最人【可变单元格】设置为“$E$3:$F$8S在【约束】中输入约 束条件U$E$1O<=$E$11, $F$1O<=$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所示），并出现【规划求解结 果】对话框,然后按确定键,保存规划求解结果。

最终优化结果为：xL0=0, xu=0； X20=l，X2,]=0； X3,o=O, X3,[=0； X4,（）=l , X4,]=0； X5,o=O, X5j=0； X6,0=，X6,] = l ,即弟 0 年投资项目B和D,第1年投资项目F,可得到最大净现值543.48万元，共使用资 金1160万元，剩余资金290万元1. 2某些项目分年度投资的情况在这种情况下，一些项FI的投资分年度进行，而不是在一年内完成全部投资这是 较复杂的一种情况，其优化决策模世表达式比较复杂，下面结合具体例子说明这类 问题如何在EXCEL上求解例2】某金业现有6个备选项H,各项F1相互独立，每个项H均分2期进行 投资，但不能跨期投资企业计划在3年内对这些项目进行投资图2为项目的有 关资料企业的革准收益率为15%ABCoF：F I 1 H1 1项目有关资料优化计算过程及结果2项目投贵安排（万元）冷现值决徹变■交*和31 M第2期（万元）推0年第丨年第2年4A8080ISOA1100SB12050100B1106C110120200C11007D7090120D11008E806080E01109F15070!00F011010一一—— -资金柬韓第0年如2年使用资金380570130如使用费金11$00500100资金限籲500620150剧余资金合计12目标商k726. 52图2投资项目最优组合决策根据以上资料，可以建立如下的优化决策模烈:目血數:Max i I - • WX ♦托勒牝宀."•约柬条件，J石％ • ^500・，右g -*.•> • A.I •看g ・•■)・ /..：<500* A/t.号％ • /.4

利用EXCEL求解上述模型时，可变单元格为F4:H9；目标单元格为F12,计算 公式为 “=SUMPRODUCT(D4:D9,F4:F9)+SUMPRODUCT(D4:D9,G4:G9・F4:F9)/1.15”(注意为数纽•输入，需同时按“Shift+Ctrl+Enter"键)；单元格14:19存放各项目决策 变量和公式(如14中为“=F4+H4・G4S其他各行可以此复制)；单元格J4:J9中存 放各项目的变量乘积(如J4中为JF4*G4”，其它以此类推)；第0、1、2年使用 资金在单元格Fl()、Gl()、H10中，其中第()年使用资金计算公式分别为 “=SUMPRODUCT(B4:B9,F4:F9八 第1年使用资金讣算公式为 “=SUMPRODUCT(B4:B9,G4:G9・F4:F9)+SUMPRODUCT(C4:C9,F4:F9,G4:G9)"(也为 数组输入，需同时按“Shift+51+EntE键)、第2年资金使用量计算公式 “=SUMPRODUCT(C4:C9,H4:H9)S各年的资金限量存放在单元格Fl 1、Gil、H11 中，计算公式分别为：“二Bll”、“=C11+F11・F1(T、^Dll+Gll-GlO^o在【规划求 解参数】对话框中，【设置目标单元格】设置为单元格“$F$12=【等丁】设置为“最 人，，；【可变单元格】设置为“$F$4:$H$9S在【约束】中输入的约束条件冇： “$F$10v=$F$ll, $G$10<=$G$ll, $H$10<=$H$ll, $F$4:$H$9<=1, $F$4:$H$9>=0, $F$4:$H$9=整数，$I$4:$I$9=0, $J$4:$J$9=0”。

需要注意的是，如杲决策变量的初 始值设置的不合适的话，就可能得不到最优解经过计算实践，一般对净现值人的 项FI的第0年和第1年的决策变量设置为1 (即单元格G4:G9中设置为零)、而净 现值最小的项目。