- 相關(guān)推薦
基于Ｅｘｃｅｌ的投資項(xiàng)目風(fēng)險(xiǎn)模擬分析
［摘 要］ 借助蒙特卡洛模擬分析方法，在考察投資決策變量（如銷售量、銷售價(jià)格、單位變動(dòng)成本等）概率分布規(guī)律的基礎(chǔ)上，對目標(biāo)變量投資項(xiàng)目凈現(xiàn)值的取值情況進(jìn)行大量隨機(jī)試驗(yàn)，獲取相關(guān)風(fēng)險(xiǎn)分析的統(tǒng)計(jì)信息，為投資決策提供有力支持。而Excel的運(yùn)用，使得快速取得隨機(jī)試驗(yàn)結(jié)果成為可能。［關(guān)鍵詞］Excel；投資項(xiàng)目凈現(xiàn)值；風(fēng)險(xiǎn)分析；蒙特卡洛模擬
一、引 言
對投資項(xiàng)目凈現(xiàn)值進(jìn)行風(fēng)險(xiǎn)分析，是資本預(yù)算中的一個(gè)重要環(huán)節(jié)。源自于卡西諾賭博計(jì)算方法的蒙特卡洛模擬分析（Monte Carlo Simulation），將敏感性和輸入變量的概率分布緊密聯(lián)系，與常見的分析方法（如敏感性分析、情景分析）相比，充分考慮各變量取值的隨機(jī)性，通過隨機(jī)模擬技術(shù)，給出了投資項(xiàng)目凈現(xiàn)值可能取值的范圍和不小于某一特定值的概率，為投資決策提供了更為科學(xué)的決策依據(jù)。運(yùn)用Excel所提供的數(shù)學(xué)、財(cái)務(wù)及其他函數(shù)，以及分析工具和圖表功能，可以很好地解決該問題。
二、項(xiàng)目投資決策分析方法
1． 確定性條件下的投資決策
基于貼現(xiàn)現(xiàn)金流技術(shù)的凈現(xiàn)值法，是投資項(xiàng)目評估最為常見的方法。該法按照項(xiàng)目的資本成本計(jì)算每一年的現(xiàn)金流量（包括現(xiàn)金流入量和現(xiàn)金流出量）現(xiàn)值，并將貼現(xiàn)的現(xiàn)金流量匯總，得到項(xiàng)目的凈現(xiàn)值（Net Present Value，NPV）。如果項(xiàng)目的凈現(xiàn)值大于零，則接受該項(xiàng)目；反之，則放棄該項(xiàng)目。
2．不確定性條件下的投資決策——蒙特卡洛風(fēng)險(xiǎn)模擬分析方法
凈現(xiàn)值法的計(jì)算和分析基礎(chǔ)是每年的現(xiàn)金流量，這是一個(gè)同時(shí)受到多個(gè)隨機(jī)輸入變量影響的隨機(jī)變量。其中，輸入變量包括具有不同概率分布規(guī)律的銷售數(shù)量、銷售價(jià)格、單位變動(dòng)成本等。利用蒙特卡洛模擬分析模型，計(jì)算機(jī)根據(jù)已知的各輸入變量概率分布規(guī)律，隨機(jī)選擇每一個(gè)輸入變量的數(shù)值，然后將這些數(shù)值加以綜合，計(jì)算出項(xiàng)目的凈現(xiàn)值并儲(chǔ)存到計(jì)算機(jī)的記憶中。接著，隨機(jī)選取第2組輸入值，計(jì)算出第2個(gè)凈現(xiàn)值。重復(fù)該過程100次或1 000次，產(chǎn)生相應(yīng)的100個(gè)或1 000個(gè)凈現(xiàn)值，就可以確定凈現(xiàn)值的有關(guān)數(shù)字特征（如均值、標(biāo)準(zhǔn)差等）。其中，均值可以作為項(xiàng)目預(yù)期盈利能力的衡量指標(biāo)，而標(biāo)準(zhǔn)差作為項(xiàng)目風(fēng)險(xiǎn)的評價(jià)指標(biāo)。同時(shí)利用Excel的作圖功能，還可得到凈現(xiàn)值隨機(jī)變量的概率密度柱形圖和累計(jì)概率分布圖，進(jìn)一步為投資決策提供相關(guān)信息。
三、運(yùn)用Excel進(jìn)行投資項(xiàng)目風(fēng)險(xiǎn)模擬分析
為了說明Excel在投資項(xiàng)目風(fēng)險(xiǎn)模擬分析中的應(yīng)用過程，現(xiàn)舉例說明如下：
[例]某公司準(zhǔn)備開發(fā)一種新產(chǎn)品。有如下預(yù)測：初始投資額為400萬元（新機(jī)器），使用期為5年，采用直線折舊政策，期末殘值為0。運(yùn)營后，銷售部門預(yù)測：第1年產(chǎn)品的銷量是一個(gè)服從均值為150萬件而標(biāo)準(zhǔn)差為40萬件的正態(tài)分布，以后每年增長10%，而銷售價(jià)格是一個(gè)服從均值為6元/件、標(biāo)準(zhǔn)差為2元/件的正態(tài)分布。生產(chǎn)部門預(yù)測：為了維持正常的運(yùn)營，需要在期初投入營運(yùn)資本50萬元。每年的固定經(jīng)營成本為150萬元，新產(chǎn)品的單位變動(dòng)成本是一個(gè)服從從2元/件到4元/件均勻分布的隨機(jī)變量。如果該投資項(xiàng)目的貼現(xiàn)率為10%，所得稅稅率為35%，試分析此投資項(xiàng)目的風(fēng)險(xiǎn)。
1． 輸入、輸出隨機(jī)變量分析
項(xiàng)目凈現(xiàn)值的大小為輸出結(jié)果，是每期凈現(xiàn)金流量現(xiàn)值之和。根據(jù)每期凈現(xiàn)金流量的構(gòu)成與特征不同，計(jì)算公式如下：
期初凈現(xiàn)金流量（投資支出）=投資金額（設(shè)備的購置費(fèi)與安裝運(yùn)輸費(fèi)） 增加的營運(yùn)資本
經(jīng)營期期間凈現(xiàn)金流量=（銷售收入-經(jīng)營成本-折舊）×（1-稅率） 折舊
=（銷售量×銷售價(jià)格–固定經(jīng)營成本–單位可變成本 ×銷售量–折舊）×（1-稅率） 折舊
期末凈現(xiàn)金流量 = 殘值的稅后收入 期末回收的營運(yùn)資本
項(xiàng)目凈現(xiàn)值為各期凈現(xiàn)金流量的現(xiàn)值之和（包括投資支出與收入）。
在經(jīng)營期期間，由于期間凈現(xiàn)金流量的高低受到銷售量、銷售價(jià)格、成本（包括固定成本、變動(dòng)成本）的共同作用，而作為輸入變量的銷售量、銷售價(jià)格和變動(dòng)成本，是服從一定概率分布的隨機(jī)變量，因此，項(xiàng)目凈現(xiàn)值也是一個(gè)由以上各隨機(jī)變量共同決定的隨機(jī)變量，對此投資項(xiàng)目的風(fēng)險(xiǎn)分析即為對項(xiàng)目凈現(xiàn)值的不確定性分析。采用蒙特卡洛模擬，輸出變量就是各期凈現(xiàn)金流量的凈現(xiàn)值。
2． 在Excel中建立原始數(shù)據(jù)和輸入相關(guān)參數(shù)（如圖1所示）
3． 生成符合分布規(guī)律的隨機(jī)輸入變量（包括銷售量、銷售價(jià)格和單位變動(dòng)成本）
本例中的隨機(jī)輸入變量有3個(gè)：服從正態(tài)分布的銷售量（單元格B14）和銷售價(jià)格（單元格B15）、均勻分布的單位變動(dòng)成本（單元格B16），其各自的分布參數(shù)來自圖1相應(yīng)單元格中的數(shù)值，生成隨機(jī)數(shù)的公式如圖2所示。
其中，單元格B14和單元格B15調(diào)用了Excel內(nèi)置的生成正態(tài)分布隨機(jī)數(shù)函數(shù)NORMINV( )和生成大于0小于1的均勻分布隨機(jī)數(shù)函數(shù)RAND( )，分別生成了均值為150（單元格B4）、標(biāo)準(zhǔn)差為40（單元格B5）的正態(tài)分布隨機(jī)數(shù)和均值為6（單元格B6）、標(biāo)準(zhǔn)差為2（單元格B7）的正態(tài)分布隨機(jī)數(shù)。單元格B16中公式生成的是2（單元格B10）至4（單元格B9）的均勻分布隨機(jī)數(shù)。
4． 建立項(xiàng)目每期凈現(xiàn)金流量相關(guān)數(shù)據(jù)計(jì)算區(qū)，并計(jì)算項(xiàng)目投資凈現(xiàn)值
首先求出投資期期初的凈現(xiàn)金流量（流出）（單元格D15），期初投資等于設(shè)備的購置費(fèi)用（單元格D2）與投入的營運(yùn)資本（單元格D3）之和。
在經(jīng)營期期間，第1年的銷量（單元格E4）和銷售價(jià)格（單元格E5）以及可變成本（單元格E8）分別引用了在第3個(gè)步驟中所計(jì)算出的隨機(jī)數(shù)。其他各年的相關(guān)數(shù)據(jù)可由公式復(fù)制得到。根據(jù)每年經(jīng)營凈現(xiàn)金流量的計(jì)算公式，可得到每年的凈現(xiàn)金流量。在項(xiàng)目結(jié)束期，還需在經(jīng)營現(xiàn)金流的基礎(chǔ)上，加回期初投入的營運(yùn)資本。
由于每期凈現(xiàn)金流量不等，所以采用Excel內(nèi)置財(cái)務(wù)函數(shù)NPV( )函數(shù)進(jìn)行計(jì)算。本例在單元格E17中輸入項(xiàng)目凈現(xiàn)值的計(jì)算公式為：=NPV(B11,E15:I15) D15。
5． 對步驟3中的隨機(jī)計(jì)算結(jié)果進(jìn)行模擬試驗(yàn)，并記錄試驗(yàn)結(jié)果進(jìn)行統(tǒng)計(jì)分析 在Excel中，如果直接按F9鍵，單元格E17中的數(shù)值就會(huì)發(fā)生變化，這時(shí)可將該試驗(yàn)結(jié)果記錄到工作表的一個(gè)空白表格區(qū)域。重復(fù)該手工操作多次，可以獲得所需要的試驗(yàn)結(jié)果樣本。此種方法盡管可行，但是對于大樣本試驗(yàn)結(jié)果的生成，是不可取的。利用Excel中所提供的模擬運(yùn)算表對虛自變量進(jìn)行分析技術(shù)，可有效地解決該問題。本例題中選擇完成1 000次試驗(yàn)，生成一個(gè)統(tǒng)計(jì)上可稱之為大樣本的試驗(yàn)結(jié)果，基本可以滿足大多數(shù)統(tǒng)計(jì)假設(shè)和推論。
試驗(yàn)結(jié)果區(qū)的位置在單元格區(qū)域E21至E1020中。具體操作如下：
在單元格E20中輸入計(jì)算公式：=E17，單元格區(qū)域D21至D1020中輸入模擬次數(shù)（1~1 000）。選定單元格區(qū)域D20至E1020，選擇“數(shù)據(jù)/模擬運(yùn)算表”命令，在出現(xiàn)的“模擬運(yùn)算表”對話框中，單擊“輸入引用列的單元格”的輸入框后，單擊工作表中的任意空白單元格（如本例中的D17）。單擊“確定”按鈕后，即可在該區(qū)域內(nèi)獲得指定目標(biāo)變量（凈現(xiàn)值）和試驗(yàn)次數(shù)（1 000次）的模擬試驗(yàn)結(jié)果（如圖4所示）。
6． 生成統(tǒng)計(jì)分析數(shù)據(jù)
在獲得1 000次試驗(yàn)結(jié)果基礎(chǔ)上，利用Excel內(nèi)置的統(tǒng)計(jì)分析函數(shù)均值函數(shù)AVERAGE（ ）、標(biāo)準(zhǔn)差函數(shù)STDEV（ ）、最大值函數(shù)MAX（ ）、最小值函數(shù)MIN（ ），計(jì)算有關(guān)的統(tǒng)計(jì)量。計(jì)算公式如圖5所示。
7． 生成投資項(xiàng)目凈現(xiàn)值各可能取值的概率、累積概率有關(guān)數(shù)據(jù)
為了繪制凈現(xiàn)值的概率分布圖、累積概率分布圖以及投資項(xiàng)目大于某一凈現(xiàn)值的概率圖，需要計(jì)算出凈現(xiàn)值在各個(gè)取值范圍內(nèi)的概率，累積概率等數(shù)據(jù)，本例中（單元格區(qū)域G20至K50）將凈現(xiàn)值的取值范圍（最大值與最小值之差）均等的分成30個(gè)小區(qū)域，分別計(jì)算在各取值區(qū)域中凈現(xiàn)值出現(xiàn)次數(shù)、頻次、累積頻次。具體計(jì)算公式如圖6所示。
相鄰的兩個(gè)NPV值之間的距離為取值范圍總長度的1/30，因此，在單元格G20中為1 000次隨機(jī)試驗(yàn)結(jié)果中的最小值，與之相鄰的單元格G21的計(jì)算公式是在單元格G20基礎(chǔ)上加上一個(gè)固定的步長($B$20-$B$21)/30。同樣，其他的刻度分別在前一刻度計(jì)算結(jié)果的基礎(chǔ)上加上相同的步長即可。
1 000次隨機(jī)試驗(yàn)結(jié)果，隨機(jī)分布在所劃分的30個(gè)區(qū)域之中，需要計(jì)算在每個(gè)凈現(xiàn)值取值區(qū)域中試驗(yàn)結(jié)果出現(xiàn)的次數(shù)（在大樣本下可近似看作是頻次）。頻次的計(jì)算采用了Excel的統(tǒng)計(jì)函數(shù)FREQUENCY( )。具體的操作為：選中單元格區(qū)域H20至H50，利用函數(shù)向?qū)�，對該區(qū)域輸入計(jì)算公式：=FREQUENCY(E14:E1013,H20:H50)，同時(shí)按ctrl-shift-enter三鍵，在該區(qū)域中會(huì)自動(dòng)出現(xiàn)所有凈現(xiàn)值取值區(qū)域中凈現(xiàn)值出現(xiàn)的頻次。
頻率的計(jì)算可在各取值區(qū)域出現(xiàn)頻次的基礎(chǔ)上，直接除以隨機(jī)試驗(yàn)的總次數(shù)1 000，即在單元格I20中輸入計(jì)算公式：=H20/COUNT($E$14:$E$1013)，并將該公式往下拖動(dòng)復(fù)制到單元格區(qū)域I21至I50中，得到與頻次相應(yīng)的頻率。
累計(jì)頻率的計(jì)算比較簡單。首先在單元格J20中輸入計(jì)算公式：=I20，在單元格J21中輸入計(jì)算公式：=J20 I21，然后直接將單元格J21中的計(jì)算公式復(fù)制到單元格區(qū)域J21至J50，即可得到相應(yīng)凈現(xiàn)值取值區(qū)域的累積概率。小于某一NPV數(shù)值的概率直接等于1減去相應(yīng)區(qū)域的累積概率。
8． 利用Excel的繪圖功能，分別繪制模擬試驗(yàn)凈現(xiàn)值的概率分布圖（如圖7所示）、累積概率分布圖（如圖8所示）和大于某凈現(xiàn)值的概率分布圖（如圖9所示），從而為投資決策提供依據(jù)。
其中，投資項(xiàng)目凈現(xiàn)值概率分布圖的X軸取值區(qū)域?yàn)閱卧駞^(qū)域G20至G50，Y軸取值區(qū)域?yàn)閱卧駞^(qū)域I20至I50；累計(jì)概率分布圖X軸取值區(qū)域?yàn)閱卧駞^(qū)域G20至G50，Y軸取值區(qū)域?yàn)閱卧駞^(qū)域J20至J50；大于某一凈現(xiàn)值概率圖X軸取值區(qū)域?yàn)閱卧駞^(qū)域G20至G50，Y軸取值區(qū)域?yàn)閱卧駞^(qū)域K20至K50。
四、模型分析總結(jié)
利用Excel的各種函數(shù)、分析工具和作圖功能，設(shè)計(jì)蒙特卡洛風(fēng)險(xiǎn)模擬分析模型，通過大量的隨機(jī)模擬試驗(yàn)，得到隨機(jī)目標(biāo)變量凈現(xiàn)值的分布規(guī)律，能夠?yàn)橥顿Y決策提供必要的依據(jù)。相對于常見的概率分析、敏感性分析方法，更加深入考察了決策變量的可能取值，從而決策信息更加全面和客觀。Excel的應(yīng)用，使得快速獲取大量隨機(jī)試驗(yàn)結(jié)果成為可能，是風(fēng)險(xiǎn)分析中的有效工具。
【基于Ｅｘｃｅｌ的投資項(xiàng)目風(fēng)險(xiǎn)模擬分析】相關(guān)文章：
Ｅｘｃｅｌ在投資決策中的應(yīng)用12-08
基于ＥＶＡ的投資決策分析03-20
基于模糊理論的對外資源型投資項(xiàng)目風(fēng)險(xiǎn)評價(jià)模型03-23
基于粗集的神經(jīng)網(wǎng)絡(luò)的項(xiàng)目風(fēng)險(xiǎn)評估02-27
基于實(shí)物期權(quán)理論的企業(yè)產(chǎn)品研發(fā)投資評估分析03-19