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

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

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

4、）式中：NPVi為項目 i 的的凈現(xiàn)值（以該項目的投資年度為起點） ，m 為項目的個數(shù)，p 為企業(yè)計劃安排投資的年數(shù)，k 為企業(yè)的基準收益率。則此種情況下的最優(yōu)組合決策模型為：目標函數(shù)：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為企業(yè)第 t 年的資金限額，miititIxI11,max, 1)(表示第t1 年剩余的投資資金。這里不計剩余投資資金的時間價值。例例 1某企業(yè)現(xiàn)有 6 個備選項目，投資分 2 期進行，兩期的投資限額分別為 850 萬元和600 萬元，各個項目的凈現(xiàn)值已估算完畢（見圖 1） 。由于計算工藝或市場原因，項目 A、B、C 為三擇一項目，項目 B 為 D 的預備項目，項目 E 和 F 為互斥項目。企業(yè)的基準收益率為15%。圖 1投資項目最優(yōu)組合及投資安排的 EXCEL 求解根據(jù)圖 1 的有關資料，則可以列出如下的最優(yōu)組合決策模型：目標函數(shù)：10, 6, 5, 4, 3, 2,

6、1)280130200260100150(15. 11ttttttttxxxxxNPVMax約束條件：ABCDEFG1項目有關資料優(yōu)化計算過程及結果2項目投資額（萬元）凈現(xiàn)值（萬元）項目第 0 年第 1 年變量和3A200150A0004B230100B1015C350260C0006D330200D1017E280130E0008F600280F0119 資金限制（萬元）第 0 年第 1 年目標函數(shù)（凈現(xiàn)值合計） 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 存放目標函數(shù)（凈現(xiàn)值合計） ，計算公式為： “=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【工具】菜單，選擇【規(guī)劃求解】項，出現(xiàn)【規(guī)劃求解參數(shù)】對話框；在【規(guī)劃求解參數(shù)】 對話框中， 【設置目標單元格】設置為單元格“$E$9” ； 【等于】設置為 “最大” ； 【可變

11、單元格】 設置為 “$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)化的結果（如圖 1 所示） ，并出現(xiàn)【規(guī)劃求解結果】對話框，然后按確定鍵，保存規(guī)劃求解結果。最終優(yōu)化結果為：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，可得到最大凈現(xiàn)值 543.48 萬元，共使用資金 1160 萬元，剩余資金 290萬元。12某些項目分年度投資的情況某些項目分年度投資的情況在這種情況下，一些項目的投資分年度進行，而不是在一年內(nèi)完成全部投資。這是較復雜的一種情況，其優(yōu)化決策模型表達式比較復雜，下面結合具體例子說明這類問題如何在EXCEL 上求解?！纠?2】某企業(yè)現(xiàn)有 6 個備選項目，各項目相互獨立，每個項目均分 2 期進行投資，但不能跨期投資。企業(yè)計劃在 3 年內(nèi)對這些項目進行投資。圖 2 為項目的有關資料。企業(yè)的基準收益率為 15%。無ABC

13、DEFGHIJ1項目有關資料優(yōu)化計算過程及結果2項目投資安排（萬元） 凈現(xiàn)值（萬元）項目決策變量變量和變量乘積3第 1 期第 2 期第 0 年第 1 年第 2 年4A8080150A110005B12050100B11006C110120200C110007D7090120D110008E806080E011009F15070100F0110010資金限額第 0 年第 1 年第 2 年使用資金380570130共使用資金108011500500100資金限額500620150剩余資金合計2012目標函數(shù)726.52圖 2投資項目最優(yōu)組合決策根據(jù)以上資料，可以建立如下的優(yōu)化決策模型：目標函數(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分別為項目 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” （注意為數(shù)組輸入，需同時按“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)”（也為 數(shù) 組 輸 入 ， 需 同 時 按 “ Shift+Ctrl+Enter ” 鍵 ） 、 第 2 年 資 金 使 用 量 計 算 公 式“=SUMPRODUCT(C4:C9,H4:H9)” ；各年的資金限量存放在單元格 F11、G11、H11 中，計算公式分別為： “=B11” 、 “=C11+F11-F10” 、 “=D11+G11-G10” 。在【規(guī)劃求解參數(shù)】 對話框中， 【設置目標單元格】設置為單元格“$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= 整 數(shù) ， $I$4:$I$9=0 ，$J$4:$J$9=0” 。需要注意的是，如果決策變量的初始值設置的不合適的話，就可能得不到最優(yōu)解。經(jīng)過計算實踐，一般對凈現(xiàn)值大的項目的第 0 年和第 1 年的決策變量設置為 1（即單元格 G4:G9 中設置為零） 、而凈現(xiàn)值最小的項目的決策變量可以設置為零比較合適。則優(yōu)化計算結果如圖 2 所示，最優(yōu)投資組合及安排如下：第 0 年對項目 A、B、C、D

18、無開始投資、第 1 年對項目 E、F 進行投資，共可得到凈現(xiàn)值 726.52 萬元，共使用資金 1080萬元，剩余資金 20 萬元。作為此種情況的特例， 當選取的項目都必須同時進行投資安排時 （不管是在 1 年內(nèi)完成投資，還是分期完成投資） ，則優(yōu)化決策模型就可以大大簡化。例如，若圖 2 所有項目均安排在第0和第1年進行投資， 則優(yōu)化決策模型為： 目標函數(shù)：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。則此中情況下的最優(yōu)結果為：對項目 A、B、C、D、E 投資，舍棄項目 F，得到凈現(xiàn)值 650 萬元，使用資金 860 萬元，剩余資金 240 萬元。2 結論結論對資金限額情況下的投資項目最優(yōu)組合及投資安排決策， 利用線性規(guī)劃方法， 建立其優(yōu)化組合決策模型，并在 EXCEL 上進行計算，具有方便、迅速的優(yōu)點，可以用于任何類型的投資組合優(yōu)化決策問題。參參考考文文獻獻1 韓良智. 應用 Excel 求解受資金限制的項目投資J.冶金經(jīng)濟與管理，2002（3） ：2324Use EXCELLfor Determining Optimized Multi-Project InvestmentCombination andArrangementHan Liangzhi(School of Management, University of Science and Technology of Bei