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

2、投資年度進(jìn)行投資，這就要求企業(yè)對這些項目進(jìn)行最優(yōu)紐合及作出投資安排 計劃，使企業(yè)取得最人效益（凈現(xiàn)值）。筆者結(jié)合實例說明利jij excel解決這類投資決策問題 的具體方法和步驟。在下述的計算屮，均假設(shè)項目無論在何年投資，其初始投資、凈現(xiàn)金流量、 以及相對于該項目投資年度的凈現(xiàn)值均不變。1利用excel進(jìn)行多項目最優(yōu)投資組合及投資安排方法和步驟1.1所有項目均在某年內(nèi)一次性投資并于當(dāng)年投產(chǎn)的情況在這種情況卜，已知各個項0的初始投資及凈現(xiàn)值，企業(yè)需要根據(jù)制訂的投資年度計劃及各投資年度的資金限額，優(yōu)化組合及安排各個投資項h,即第0年先投資哪些項h,第1年再根據(jù)第0年剩余的投資資金加上本年的資金限額

3、安排哪些項口，等等。設(shè)第t年安排i項目的投資， 以xi,t表示項目i在第t年投資的決策變量,xi,21表示在第t年對項目i進(jìn)行投資,xi,t=o表示在第i年不對項忖i進(jìn)行投資，則選取的投資項h以第i年為投資起點的總凈現(xiàn)值為2-1，將各投資年度選取的投資項冃的總凈現(xiàn)值2-1看作是一個綜合項冃的凈現(xiàn)金流最，則此綜合項目的凈現(xiàn)值（以第0年為起點）為:丫柳“:自為自和護(hù)嶺h式中：npvj為項目i的的凈現(xiàn)值（以該項目的投資年度為起點），m為項目的個數(shù)，p為企業(yè)計劃安排投資的年數(shù)，k為企業(yè)的基準(zhǔn)收益率。則此種悄況下的最優(yōu)組合決策模型為：3工吹八煎馬軒" （2）(21,2, ， j(j)£

4、;札二0 或 i(/af j.2, % = q 或 12 嚴(yán)廨"«,! ,2,屮-1 >i其瞬東和 晰且那咖i性環(huán)ttd湘娜r式中：ii為項目i的初始投資，it, max為企業(yè)第t年的資金限額,表示第t-1年剩余的投資資金。這里不計剩余投資資金的時間價值。例1某企業(yè)現(xiàn)有6個備選項冃，投資分2期進(jìn)行，兩期的投資限額分別為850力元和600 萬元，各個項目的凈現(xiàn)值已估算完畢（見圖1）。由于計算工藝或帀場原因，項目a、b、c為 三擇一項目，項目b為d的預(yù)備項目，項目e和f為互斥項目。企業(yè)的基準(zhǔn)收益率為15%。abc°ef i i1頊目有關(guān)資料優(yōu)化計算過程及結(jié)果2項

5、目投賢（萬元）妙現(xiàn)值（萬元）項冃第0年«1年43a200150a004b230100b105c350260c006d330200d107e280130e008f600280f019資金限明第0年第丨年忖標(biāo)斷數(shù)（凈現(xiàn)儼合計）543.48合th10（萬無）&50600實標(biāo)使用資金560600i11實際資金限畝&50890合計*12頊h a.b.c關(guān)系1<13項目b、d關(guān)系014項目e、f關(guān)系圖1投資項目最優(yōu)組合及投資安排的excel求解根據(jù)圖1的有關(guān)資料，則可以列出如下的最優(yōu)組合決策模型:目杯八 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 習(xí)j fjj憐-和=° f十咤,q十駒十宛j室q十升=0或x（與目a、b、c三述 （鎖目b為d游預(yù)岱項 （項目e芒為互斥項 （4 = 1.2,33,5分別表示項目仏b&d、e”每個碘目鐵資年度紳束條 例如若頊目a在第0年投陡朋在第1年就不投見即飢嚴(yán)!內(nèi)1 :（i =b2

7、.3.4 $&>$# =<則利用excel求解上述模烈的步驟如下:（1）設(shè)計丁作表格（如圖1所示），其中單元格e9存放h標(biāo)函數(shù)（凈現(xiàn)值合計），計算公式為："=sumproduct(c3:c&e3:e8)+sumproduct(c3:c&f3:f8)/15s單元格e3:f8為變動單元格，存放決策變量xi,t的值；（2）在單元格g3中輸入項目a的決策變量求和公式j(luò)e3+f3”，項口 bf的決 策變量求和公式分別填入單元格g4:g8,可采用復(fù)制方法，將單元格g3復(fù)制到單 元格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關(guān)系的約束條件計算公式“=sum（e3:f5八 在單元格e13屮輸入項目b、d關(guān)系的約束條件計算公式“=e4e6+f4f6s在單元 格e14中輸入項目e、f關(guān)系的約束條件計算公式t4=sum（e7:f8f；（5）單擊ex

9、cel【t具】菜單，選擇【規(guī)劃求解】項，出現(xiàn)【規(guī)劃求解參數(shù)】對話框；在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標(biāo)單元格】設(shè)置為單元格“$e$9s【等于】設(shè)置為“最人【可變單元格】設(shè)置為“$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= 整數(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é) 果】對話框,

10、然后按確定鍵,保存規(guī)劃求解結(jié)果。最終優(yōu)化結(jié)果為：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,可得到最大凈現(xiàn)值543.48萬元，共使用資 金1160萬元，剩余資金290萬元。1. 2某些項目分年度投資的情況在這種情況下，一些項fi的投資分年度進(jìn)行，而不是在一年內(nèi)完成全部投資。這是 較復(fù)雜的一種情況，其優(yōu)化決策模世表達(dá)式比較復(fù)雜，下面結(jié)合具體例子說明這類 問題如何在excel上求解。【例2】某金業(yè)現(xiàn)有6個備選項

11、h,各項f1相互獨立，每個項h均分2期進(jìn)行 投資，但不能跨期投資。企業(yè)計劃在3年內(nèi)對這些項目進(jìn)行投資。圖2為項目的有 關(guān)資料。企業(yè)的革準(zhǔn)收益率為15%。abcof：f i °1 h1 «1項目有關(guān)資料優(yōu)化計算過程及結(jié)果2項目投貴安排（萬元）冷現(xiàn)值決徹變交*和3«1 m第2期（萬元）推0年第丨年第2年4a8080isoa1100sb12050100b1106c110120200c11007d7090120d11008e806080e01109f15070!00f011010一一 -資金柬韓第0年如2年使用資金380570130如使用費金11$00500100資金限籲

12、500620150劇余資金合計12目標(biāo)商k726. 52圖2投資項目最優(yōu)組合決策根據(jù)以上資料，可以建立如下的優(yōu)化決策模烈:目血數(shù):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；目標(biāo)單元格為f12,計算 公式為 “=sumproduct(d4:d9,f4:f9)+sumproduct(d4:d9,g4:g9f4:f9)/1.15”(注意為數(shù)紐輸入，需同時按“shift+ctrl+enter"鍵)；單元格14:19存放各項目決策 變量和公式(如14中為“=f4+h4g4s其他各行可以此復(fù)制)；單元格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)"(也為 數(shù)組輸入，需同時按“shift+51+ente鍵)、第2年資金使用量計算公式 “=sumproduct(c4:c9,h4:h9)s各年的資金限量存放在單元格fl 1、gil、h11 屮，計算公式分別為：“二bll”、“=c11+f11f1(t、dll+gll-gloo在【規(guī)劃求 解參數(shù)】對話框中，【設(shè)置目標(biāo)單元格】設(shè)置為單元格“$f$12=【等丁】設(shè)置為“最 人，；【可變單元格】設(shè)置為“$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=整數(shù)，$i$4:$i$9=0, $j$4:$j$9=0”。需要注意的是，如杲?jīng)Q策變量的初 始值設(shè)置的不合適的話，就可能得不到最優(yōu)解。經(jīng)過計算實踐，一般對凈現(xiàn)值人的 項fi的第0年和第1年的決策變量設(shè)置為1 (即單元格g4:g9中設(shè)置為零)、而凈 現(xiàn)值最小的項目的決策變量可以設(shè)置為零比較合適。則優(yōu)化計算結(jié)果如圖2所示，最優(yōu)投資組合及安排如下：第0年對項目a、b、 c、d開始投資、第1年對項目e、f進(jìn)行

16、投資，共可得到凈現(xiàn)值726.52萬元，共使 用資金1080萬元，剩余資金20萬元。作為此種情況的特例，當(dāng)選取的項口都必須同時進(jìn)行投資安排時(不管是在1 年內(nèi)完成投資，還是分期完成投資)，則優(yōu)化決策模型就可以大大簡化。例如，若 圖2所有項日均安排在第0和第1年進(jìn)行投資，則優(yōu)化決策模型為：目標(biāo)函數(shù):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。則 此中情況下的最優(yōu)結(jié)果為：對項目a、b、c、d、e投資，舍棄項目f,得到凈現(xiàn) 值650萬元，使用資金860萬元，剩余資金240萬元。2結(jié)論對資金限額情況下的投資項目最優(yōu)組合及投資安排決策，利用線性規(guī)劃方法，建立 其優(yōu)化組合決策模型，并在excel ±進(jìn)行計算，具有方便、迅速的優(yōu)點，可以用 于任何類型的投資組合優(yōu)化決策問題。參考文獻(xiàn)1韓良智.應(yīng)用excel求解受資金限制的項口投資j.冶金經(jīng)濟(jì)與管理，2002 （3） : 2324收稿日期:2003-09-19o作者簡介：韓良智，女，1963年出生，北京科技大學(xué)管理學(xué)院財會系副教授。研究方向：財務(wù)管理，證券投資。己出版的著作主要有西

18、方會計理論與實務(wù)、財務(wù)管理學(xué)教程、上市 公司財務(wù)報告閱讀與分析等，在各種學(xué)術(shù)期刊上發(fā)農(nóng)論文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