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