利用EXCEL進(jìn)行多項(xiàng)目最優(yōu)投資組合及投資安排決策VIP

1、無(wú)利用利用 EXCEL 進(jìn)行多項(xiàng)目最優(yōu)投資組合及投資安排決策進(jìn)行多項(xiàng)目最優(yōu)投資組合及投資安排決策韓良智（北京科技大學(xué)管理學(xué)院，北京，100083）摘要：摘要：資金限額條件下投資項(xiàng)目的最優(yōu)投資組合及投資安排是某些企業(yè)經(jīng)常遇到的問(wèn)題，企業(yè)對(duì)這些項(xiàng)目進(jìn)行組合與投資安排時(shí)，不僅要考慮各項(xiàng)目的投資額大小，還要考慮項(xiàng)目投資的先后順序。本文介紹了在 EXCEL 上進(jìn)行這類(lèi)投資決策問(wèn)題求解的具體方法和步驟。關(guān)鍵詞：關(guān)鍵詞：資金限額投資優(yōu)化在某些企業(yè)， 很可能面對(duì)多個(gè)具有可行性的投資項(xiàng)目， 但由于籌集資金數(shù)額以及籌資時(shí)間的限制，這些項(xiàng)目既不可能全部采用，也不可能在一年內(nèi)全部投資，而是需要在這些項(xiàng)目中作出取舍，

2、并分散在幾個(gè)投資年度進(jìn)行投資，這就要求企業(yè)對(duì)這些項(xiàng)目進(jìn)行最優(yōu)組合及作出投資安排計(jì)劃，使企業(yè)取得最大效益（凈現(xiàn)值） 。筆者結(jié)合實(shí)例說(shuō)明利用 EXCEL 解決這類(lèi)投資決策問(wèn)題的具體方法和步驟。在下述的計(jì)算中，均假設(shè)項(xiàng)目無(wú)論在何年投資，其初始投資、凈現(xiàn)金流量、以及相對(duì)于該項(xiàng)目投資年度的凈現(xiàn)值均不變。1 利用利用 EXCEL 進(jìn)行多項(xiàng)目最優(yōu)投資組合及投資安排方法和步驟進(jìn)行多項(xiàng)目最優(yōu)投資組合及投資安排方法和步驟1.1所有項(xiàng)目均在某年內(nèi)一次性投資并于當(dāng)年投產(chǎn)的情況所有項(xiàng)目均在某年內(nèi)一次性投資并于當(dāng)年投產(chǎn)的情況在這種情況下， 已知各個(gè)項(xiàng)目的初始投資及凈現(xiàn)值， 企業(yè)需要根據(jù)制訂的投資年度計(jì)劃及各投資年度的資金

3、限額，優(yōu)化組合及安排各個(gè)投資項(xiàng)目，即第 0 年先投資哪些項(xiàng)目，第 1年再根據(jù)第 0 年剩余的投資資金加上本年的資金限額安排哪些項(xiàng)目，等等。設(shè)第 t 年安排 i 項(xiàng)目的投資，以 xi,t表示項(xiàng)目 i 在第 t 年投資的決策變量，xi,t=1 表示在第 t 年對(duì)項(xiàng)目 i進(jìn)行投資，xi,t=0 表示在第 t 年不對(duì)項(xiàng)目 i 進(jìn)行投資，則選取的投資項(xiàng)目以第 t 年為投資起點(diǎn)的總凈現(xiàn)值為miitiNPVx1,， 將各投資年度選取的投資項(xiàng)目的總凈現(xiàn)值miitiNPVx1,看作是一個(gè)綜合項(xiàng)目的凈現(xiàn)金流量，則此綜合項(xiàng)目的凈現(xiàn)值（以第 0 年為起點(diǎn)）為：101,)()1 (1ptmiititNPVxkNPV（1

4、）式中：NPVi為項(xiàng)目 i 的的凈現(xiàn)值（以該項(xiàng)目的投資年度為起點(diǎn)） ，m 為項(xiàng)目的個(gè)數(shù)，p 為企業(yè)計(jì)劃安排投資的年數(shù)，k 為企業(yè)的基準(zhǔn)收益率。則此種情況下的最優(yōu)組合決策模型為：目標(biāo)函數(shù)：101,)()1 (1)(ptmiititNPVxkNPVMax（2）無(wú)約束條件：、補(bǔ)充性等）之間的依賴(lài)性、互斥性其它約束條件（如項(xiàng)目或或) 1,.,2 , 1 , 0;,.,2 , 1(10),.,2 , 1(10) 1,.,2 , 1()(,10,11,max, 11max,1max, 00 ,ptmixmixptIxIIIxIIxtipttimiititmititimiii（3）式中：Ii為項(xiàng)目 i 的初

5、始投資，It, max為企業(yè)第 t 年的資金限額，miititIxI11,max, 1)(表示第t1 年剩余的投資資金。這里不計(jì)剩余投資資金的時(shí)間價(jià)值。例例 1某企業(yè)現(xiàn)有 6 個(gè)備選項(xiàng)目，投資分 2 期進(jìn)行，兩期的投資限額分別為 850 萬(wàn)元和600 萬(wàn)元，各個(gè)項(xiàng)目的凈現(xiàn)值已估算完畢（見(jiàn)圖 1） 。由于計(jì)算工藝或市場(chǎng)原因，項(xiàng)目 A、B、C 為三擇一項(xiàng)目，項(xiàng)目 B 為 D 的預(yù)備項(xiàng)目，項(xiàng)目 E 和 F 為互斥項(xiàng)目。企業(yè)的基準(zhǔn)收益率為15%。圖 1投資項(xiàng)目最優(yōu)組合及投資安排的 EXCEL 求解根據(jù)圖 1 的有關(guān)資料，則可以列出如下的最優(yōu)組合決策模型：目標(biāo)函數(shù)：10, 6, 5, 4, 3, 2,

6、1)280130200260100150(15. 11ttttttttxxxxxNPVMax約束條件：ABCDEFG1項(xiàng)目有關(guān)資料優(yōu)化計(jì)算過(guò)程及結(jié)果2項(xiàng)目投資額（萬(wàn)元）凈現(xiàn)值（萬(wàn)元）項(xiàng)目第 0 年第 1 年變量和3A200150A0004B230100B1015C350260C0006D330200D1017E280130E0008F600280F0119 資金限制（萬(wàn)元）第 0 年第 1 年目標(biāo)函數(shù)（凈現(xiàn)值合計(jì)） 543.48合計(jì)使用資金10850600實(shí)際使用資金560600116011實(shí)際資金限額850890合計(jì)剩余資金12項(xiàng)目 A、B、C 關(guān)系129013項(xiàng)目 B、D 關(guān)系014項(xiàng)目

7、E、F 關(guān)系1無(wú)) 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或），年就不投資，即年投資，則在第在第例如，若項(xiàng)目束條件，（每個(gè)項(xiàng)目投資年度約、分別表示項(xiàng)目或?yàn)榛コ忭?xiàng)目）、（項(xiàng)目的預(yù)備項(xiàng)目）為（項(xiàng)目三選一）、（項(xiàng)目年投資額限制條件）第年投資額限制條件）第則利用 EXCEL 求解上述模型的步驟如下：（1）設(shè)計(jì)工作表格（如圖 1 所示） ，其中單元格 E9 存放目標(biāo)函數(shù)（凈現(xiàn)值合計(jì)） ，計(jì)算公式為： “=SUMPRODUCT(C3:C8,E3:E8)+SUMPRODUCT(C3:C8,F3:F8)/1.1

9、5” ；單元格E3:F8 為變動(dòng)單元格，存放決策變量 xi,t的值；（2）在單元格 G3 中輸入項(xiàng)目 A 的決策變量求和公式“=E3+F3” ，項(xiàng)目 BF 的決策變量求和公式分別填入單元格 G4:G8，可采用復(fù)制方法，將單元格 G3 復(fù)制到單元格 G4:G8中即可而完成其他項(xiàng)目決策變量求和公式的輸入；（ 3 ） 在 單 元 格 E10 輸 入 第 0 年 的 實(shí) 際 資 金 使 用 量 計(jì) 算 公 式“=SUMPRODUCT(B3:B8,E3:E8)” ， 在單元格 F10 輸入第 1 年的實(shí)際資金使用量計(jì)算公式和“=SUMPRODUCT(B3:B8,F3:F8)” ；在單元格 E11 中輸入

10、第 0 年資金限額“=B10” ；在單元格 F11 中輸入第 1 年資金限額計(jì)算公式“=C10+(E11-E10)” ；（4）在單元格 E12 中輸入項(xiàng)目 A、B、C 關(guān)系的約束條件計(jì)算公式“=SUM(E3:F5)” ；在單元格 E13 中輸入項(xiàng)目 B、D 關(guān)系的約束條件計(jì)算公式“=E4-E6+F4-F6” ；在單元格 E14中輸入項(xiàng)目 E、F 關(guān)系的約束條件計(jì)算公式“=SUM(E7:F8)” ；（5）單擊 EXCEL【工具】菜單，選擇【規(guī)劃求解】項(xiàng)，出現(xiàn)【規(guī)劃求解參數(shù)】對(duì)話(huà)框；在【規(guī)劃求解參數(shù)】 對(duì)話(huà)框中， 【設(shè)置目標(biāo)單元格】設(shè)置為單元格“$E$9” ； 【等于】設(shè)置為 “最大” ； 【可變

11、單元格】 設(shè)置為 “$E$3:$F$8” ； 在 【約束】 中輸入約束條件 “$E$10=$E$11，$F$10=$F$11 ， $E$3:$F$8=0 ， $E$3:$F$8= 整 數(shù) ， $G$3:$G$8=0 ，$G$3:$G$8=1，$E$12=1，$E$13=0，$E$14=1” ；（6）單擊【求解】 ，即可得到優(yōu)化的結(jié)果（如圖 1 所示） ，并出現(xiàn)【規(guī)劃求解結(jié)果】對(duì)話(huà)框，然后按確定鍵，保存規(guī)劃求解結(jié)果。最終優(yōu)化結(jié)果為：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 年投資項(xiàng)目 B 和 D，第 1 年投資項(xiàng)目 F，可得到最大凈現(xiàn)值 543.48 萬(wàn)元，共使用資金 1160 萬(wàn)元，剩余資金 290萬(wàn)元。12某些項(xiàng)目分年度投資的情況某些項(xiàng)目分年度投資的情況在這種情況下，一些項(xiàng)目的投資分年度進(jìn)行，而不是在一年內(nèi)完成全部投資。這是較復(fù)雜的一種情況，其優(yōu)化決策模型表達(dá)式比較復(fù)雜，下面結(jié)合具體例子說(shuō)明這類(lèi)問(wèn)題如何在EXCEL 上求解。【例例 2】某企業(yè)現(xiàn)有 6 個(gè)備選項(xiàng)目，各項(xiàng)目相互獨(dú)立，每個(gè)項(xiàng)目均分 2 期進(jìn)行投資，但不能跨期投資。企業(yè)計(jì)劃在 3 年內(nèi)對(duì)這些項(xiàng)目進(jìn)行投資。圖 2 為項(xiàng)目的有關(guān)資料。企業(yè)的基準(zhǔn)收益率為 15%。無(wú)ABC

13、DEFGHIJ1項(xiàng)目有關(guān)資料優(yōu)化計(jì)算過(guò)程及結(jié)果2項(xiàng)目投資安排（萬(wàn)元） 凈現(xiàn)值（萬(wàn)元）項(xiàng)目決策變量變量和變量乘積3第 1 期第 2 期第 0 年第 1 年第 2 年4A8080150A110005B12050100B11006C110120200C110007D7090120D110008E806080E011009F15070100F0110010資金限額第 0 年第 1 年第 2 年使用資金380570130共使用資金108011500500100資金限額500620150剩余資金合計(jì)2012目標(biāo)函數(shù)726.52圖 2投資項(xiàng)目最優(yōu)組合決策根據(jù)以上資料，可以建立如下的優(yōu)化決策模型：目標(biāo)函數(shù)：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分別為項(xiàng)目 i 在第 1 期、第 2 期的投資額，I0、I1分別為第 0 年和第 1 年剩余的投資資金，xi,t為決策變量。利用 EXCEL 求解上述模型時(shí)，可變單元格為 F4:H9；

15、目標(biāo)單元格為 F12，計(jì)算公式為“=SUMPRODUCT(D4:D9,F4:F9)+SUMPRODUCT(D4:D9,G4:G9-F4:F9)/1.15” （注意為數(shù)組輸入，需同時(shí)按“Shift+Ctrl+Enter”鍵） ；單元格 I4:I9 存放各項(xiàng)目決策變量和公式（如 I4中為“=F4+H4-G4，其他各行可以此復(fù)制） ；單元格 J4:J9 中存放各項(xiàng)目的變量乘積（如 J4中為“=F4*G4” ，其它以此類(lèi)推） ；第 0、1、2 年使用資金在單元格 F10、G10、H10 中，其中第 0 年使用資金計(jì)算公式分別為“=SUMPRODUCT(B4:B9,F4:F9)” 、第 1 年使用資金計(jì)

16、算公式為 “=SUMPRODUCT(B4:B9,G4:G9-F4:F9)+SUMPRODUCT(C4:C9,F4:F9,G4:G9)”（也為 數(shù) 組 輸 入 ， 需 同 時(shí) 按 “ Shift+Ctrl+Enter ” 鍵 ） 、 第 2 年 資 金 使 用 量 計(jì) 算 公 式“=SUMPRODUCT(C4:C9,H4:H9)” ；各年的資金限量存放在單元格 F11、G11、H11 中，計(jì)算公式分別為： “=B11” 、 “=C11+F11-F10” 、 “=D11+G11-G10” 。在【規(guī)劃求解參數(shù)】 對(duì)話(huà)框中， 【設(shè)置目標(biāo)單元格】設(shè)置為單元格“$F$12” ； 【等于】設(shè)置為“最大” ；

17、 【可變單元格】設(shè)置為“$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= 整 數(shù) ， $I$4:$I$9=0 ，$J$4:$J$9=0” 。需要注意的是，如果決策變量的初始值設(shè)置的不合適的話(huà)，就可能得不到最優(yōu)解。經(jīng)過(guò)計(jì)算實(shí)踐，一般對(duì)凈現(xiàn)值大的項(xiàng)目的第 0 年和第 1 年的決策變量設(shè)置為 1（即單元格 G4:G9 中設(shè)置為零） 、而凈現(xiàn)值最小的項(xiàng)目的決策變量可以設(shè)置為零比較合適。則優(yōu)化計(jì)算結(jié)果如圖 2 所示，最優(yōu)投資組合及安排如下：第 0 年對(duì)項(xiàng)目 A、B、C、D

18、無(wú)開(kāi)始投資、第 1 年對(duì)項(xiàng)目 E、F 進(jìn)行投資，共可得到凈現(xiàn)值 726.52 萬(wàn)元，共使用資金 1080萬(wàn)元，剩余資金 20 萬(wàn)元。作為此種情況的特例， 當(dāng)選取的項(xiàng)目都必須同時(shí)進(jìn)行投資安排時(shí) （不管是在 1 年內(nèi)完成投資，還是分期完成投資） ，則優(yōu)化決策模型就可以大大簡(jiǎn)化。例如，若圖 2 所有項(xiàng)目均安排在第0和第1年進(jìn)行投資， 則優(yōu)化決策模型為： 目標(biāo)函數(shù)：611 ,0 ,)(iiiiNPVxxNPVMax；約束條件：titiiIIxmax,61,，xi,0-xi,1=0，xi,t=0 或 1，式中：xi,t為決策變量，xi,t=1 表示在第t 年接受項(xiàng)目 i，xi,t=0 表示在第 t 年拒

19、絕項(xiàng)目 i ；Ii,t為項(xiàng)目 i 在第 t 年使用的資金，Imax,t為第 t 年的資金限額，t=0，1。則此中情況下的最優(yōu)結(jié)果為：對(duì)項(xiàng)目 A、B、C、D、E 投資，舍棄項(xiàng)目 F，得到凈現(xiàn)值 650 萬(wàn)元，使用資金 860 萬(wàn)元，剩余資金 240 萬(wàn)元。2 結(jié)論結(jié)論對(duì)資金限額情況下的投資項(xiàng)目最優(yōu)組合及投資安排決策， 利用線(xiàn)性規(guī)劃方法， 建立其優(yōu)化組合決策模型，并在 EXCEL 上進(jìn)行計(jì)算，具有方便、迅速的優(yōu)點(diǎn)，可以用于任何類(lèi)型的投資組合優(yōu)化決策問(wèn)題。參參考考文文獻(xiàn)獻(xiàn)1 韓良智. 應(yīng)用 Excel 求解受資金限制的項(xiàng)目投資J.冶金經(jīng)濟(jì)與管理，2002（3） ：2324Use EXCELLfor Determining Optimized Multi-Project InvestmentCombination andArrangementHan Liangzhi(School of Management, University of Science and Technology of Bei