excel投资决策

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

2、投资年度进行投资，这就要求企业对这些项目进行最优纽合及作出投资安排 计划，使企业取得最人效益（净现值）。笔者结合实例说明利jij excel解决这类投资决策问题 的具体方法和步骤。在下述的计算屮，均假设项目无论在何年投资，其初始投资、净现金流量、 以及相对于该项目投资年度的净现值均不变。1利用excel进行多项目最优投资组合及投资安排方法和步骤1.1所有项目均在某年内一次性投资并于当年投产的情况在这种情况卜，已知各个项0的初始投资及净现值，企业需要根据制订的投资年度计划及各投资年度的资金限额，优化组合及安排各个投资项h,即第0年先投资哪些项h,第1年再根据第0年剩余的投资资金加上本年的资金限额

3、安排哪些项口，等等。设第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)£

4、;札二0 或 i(/af j.2, % = q 或 12 严廨"«,! ,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%。abc°ef i i1顼目有关资料优化计算过程及结果2項

5、目投賢（万元）妙现值（万元）項冃第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约束条件:pn0x9f

6、i 23oa +35&口 +330 +280 +600850< 第 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

7、.3.4 $&>$# =<则利用excel求解上述模烈的步骤如下:（1）设计丁作表格（如图1所示），其中单元格e9存放h标函数（净现值合计），计算公式为："=sumproduct(c3:c&e3:e8)+sumproduct(c3:c&f3:f8)/15s单元格e3:f8为变动单元格，存放决策变量xi,t的值；（2）在单元格g3中输入项目a的决策变量求和公式je3+f3”，项口 bf的决 策变量求和公式分别填入单元格g4:g8,可采用复制方法，将单元格g3复制到单 元格g4:g8中即可而完成其他项日决策变量求和公式的输入；（3）在单元格e10输入第

8、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关系的约束条件计算公式“=e4e6+f4f6s在单元 格e14中输入项目e、f关系的约束条件计算公式t4=sum（e7:f8f；（5）单击ex

9、cel【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所示），并出现【规划求解结 果】对话框,

10、然后按确定键,保存规划求解结果。最终优化结果为：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个备选项

11、h,各项f1相互独立，每个项h均分2期进行 投资，但不能跨期投资。企业计划在3年内对这些项目进行投资。图2为项目的有 关资料。企业的革准收益率为15%。abcof：f i °1 h1 «1项目有关资料优化计算过程及结果2项目投贵安排（万元）冷现值决徹变交*和3«1 m第2期（万元）推0年第丨年第2年4a8080isoa1100sb12050100b1106c110120200c11007d7090120d11008e806080e01109f15070!00f011010一一 -资金柬韓第0年如2年使用资金380570130如使用费金11$00500100资金限籲

12、500620150剧余资金合计12目标商k726. 52图2投资项目最优组合决策根据以上资料，可以建立如下的优化决策模烈:目血數:max i i - wx 托勒牝宀."约柬条件，j石 500，右g -*«.> a.i 看g £) /.：<500* a/t.号 /.4<ioo* a/jl兀屯-x. at 珀 «o s® 1 (i «l2t3.4t5a6s< =0 j .2)式中：h,l、ii,2分别为项目i在第1期、第2期的投资额，dio、di1分别为第0年和第1年剩余的投资资金，xi,t为决策变量。利用exce

13、l求解上述模型时，可变单元格为f4:h9；目标单元格为f12,计算 公式为 “=sumproduct(d4:d9,f4:f9)+sumproduct(d4:d9,g4:g9f4:f9)/1.15”(注意为数纽输入，需同时按“shift+ctrl+enter"键)；单元格14:19存放各项目决策 变量和公式(如14中为“=f4+h4g4s其他各行可以此复制)；单元格j4:j9中存 放各项目的变量乘积(如j4中为jf4*g4”，其它以此类推)；第0、1、2年使用 资金在单元格fl()、gl()、h10中，其中第()年使用资金计算公式分别为 “=sumproduct(b4:b9,f4:f9

14、八 第1年使用资金讣算公式为 “=sumproduct(b4:b9,g4:g9f4:f9)+sumproduct(c4:c9,f4:f9,g4:g9)"(也为 数组输入，需同时按“shift+51+ente键)、第2年资金使用量计算公式 “=sumproduct(c4:c9,h4:h9)s各年的资金限量存放在单元格fl 1、gil、h11 屮，计算公式分别为：“二bll”、“=c11+f11f1(t、dll+gll-gloo在【规划求 解参数】对话框中，【设置目标单元格】设置为单元格“$f$12=【等丁】设置为“最 人，；【可变单元格】设置为“$f$4:$h$9s在【约束】中输入的约

15、束条件冇： “$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中设置为零)、而净 现值最小的项目的决策变量可以设置为零比较合适。则优化计算结果如图2所示，最优投资组合及安排如下：第0年对项目a、b、 c、d开始投资、第1年对项目e、f进行

16、投资，共可得到净现值726.52万元，共使 用资金1080万元，剩余资金20万元。作为此种情况的特例，当选取的项口都必须同时进行投资安排时(不管是在1 年内完成投资，还是分期完成投资)，则优化决策模型就可以大大简化。例如，若 图2所有项日均安排在第0和第1年进行投资，则优化决策模型为：目标函数:66恥沿刊=工（和和）心s 忑'珀nwx, tt；约束条件；说xi,o-xi,l =0, xi,t =0 或 1,式中：xi,t为决策变量，xi,t=l表示在第t年接受项目i, xi,t =0表示在第t年拒绝项hi ； ii,t为项目i在第（年使用的资金，imax,t为第t年的资金限额，匸0,

17、1。则 此中情况下的最优结果为：对项目a、b、c、d、e投资，舍弃项目f,得到净现 值650万元，使用资金860万元，剩余资金240万元。2结论对资金限额情况下的投资项目最优组合及投资安排决策，利用线性规划方法，建立 其优化组合决策模型，并在excel ±进行计算，具有方便、迅速的优点，可以用 于任何类型的投资组合优化决策问题。参考文献1韩良智.应用excel求解受资金限制的项口投资j.冶金经济与管理，2002 （3） : 2324收稿日期:2003-09-19o作者简介：韩良智，女，1963年出生，北京科技大学管理学院财会系副教授。研究方向：财务管理，证券投资。己出版的著作主要有西

18、方会计理论与实务、财务管理学教程、上市 公司财务报告阅读与分析等，在各种学术期刊上发农论文30多篇。use excell for determining optimized multi-project investment combination andarrangementhan liangzhi(school of management, university of science and technology of beijing, 1()(m)83)abstracthow to optimize investment combination and arrangement for an investment project with restricted fund is a problem some entei-prises often encounter. to determine investment combination and arrangement for su