Ｅｘｃｅｌ在蒙特卡羅模擬分析中的應(yīng)用

［摘 要］ 蒙特卡羅模擬法是進(jìn)行項(xiàng)目不確定性分析的一種常用方法，Excel是具有強(qiáng)大數(shù)據(jù)分析功能的軟件，可以很好地解決手工計(jì)算煩瑣的問題。基于此，本文結(jié)合案例研究了如何運(yùn)用Excel進(jìn)行蒙特卡羅模擬分析。

［關(guān)鍵詞］ Excel；蒙特卡羅模擬；數(shù)據(jù)分析

［中圖分類號(hào)］F232；F275［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673-0194（2008）07-0044-04

一、蒙特卡羅模擬分析概述

在工程項(xiàng)目的經(jīng)濟(jì)評(píng)價(jià)中，通常假定有關(guān)數(shù)據(jù)都是不變的。但項(xiàng)目分析中采用的數(shù)據(jù)大部分來自預(yù)測(cè)或估計(jì)，它們?cè)诤艽蟪潭壬鲜艿轿磥砜勺円蛩氐挠绊懀蝗绻阉鼈冏鳛楣潭ㄖ悼创M(jìn)行分析，計(jì)算結(jié)果將與實(shí)際情況不符，從而帶來某種程度的風(fēng)險(xiǎn)。近年來的經(jīng)濟(jì)評(píng)價(jià)中越來越注意分析研究這種風(fēng)險(xiǎn)的程度和可能性，蒙特卡羅模擬法就是解決這類問題十分方便的方法。

采用蒙特卡羅模擬法可以直接從不確定性因素變化情況入手，通過建立模型直接模擬問題，從而使不確定性因素對(duì)項(xiàng)目經(jīng)濟(jì)效果的影響直接反映在對(duì)項(xiàng)目經(jīng)濟(jì)評(píng)價(jià)指標(biāo)的計(jì)算中。由于采用了隨機(jī)抽樣獲取不確定性因素的大量觀測(cè)值，將其代入模型中進(jìn)行模擬分析，不僅可以獲得評(píng)價(jià)指標(biāo)的概率分布信息，還可以從指標(biāo)的離散程度、變異系數(shù)等方面來評(píng)價(jià)項(xiàng)目的風(fēng)險(xiǎn)；同時(shí)可以通過事先設(shè)定置信度來控制模擬的準(zhǔn)確度，所得的信息完整性遠(yuǎn)遠(yuǎn)高于其他分析方法。

二、利用Excel進(jìn)行蒙特卡羅模擬分析的優(yōu)勢(shì)

蒙特卡羅模擬技術(shù)的應(yīng)用是與計(jì)算機(jī)技術(shù)的發(fā)展密不可分的，只有借助計(jì)算機(jī)高效、便捷的計(jì)算功能，蒙特卡羅模擬在實(shí)踐中的應(yīng)用才能成為可能。經(jīng)過最近十幾年計(jì)算機(jī)軟硬件技術(shù)的飛速發(fā)展，Excel軟件已成為大眾首選的決策分析工具軟件。在它的幫助下，管理人員完全可以根據(jù)實(shí)際問題，在相應(yīng)的理論基礎(chǔ)上，自己動(dòng)手建立計(jì)算模型并對(duì)這些模型進(jìn)行各種深入的分析，特別是對(duì)于蒙特卡羅模擬這類需要建立在一定模型基礎(chǔ)上進(jìn)行分析的方法來說，Excel 在建模、數(shù)據(jù)分析等方面的優(yōu)勢(shì)更是得到了充分的體現(xiàn)和發(fā)揮：

第一，Excel具有十分強(qiáng)大的數(shù)據(jù)分析功能，并且與各種數(shù)據(jù)庫具有良好的接口，這使得操作者可以通過簡(jiǎn)單的操作從具有不同結(jié)構(gòu)的數(shù)據(jù)庫中獲取所需要的數(shù)據(jù)，利用數(shù)據(jù)分析功能直接進(jìn)行一些復(fù)雜的計(jì)算、統(tǒng)計(jì)工作等。

第二，Excel具有大量?jī)?nèi)建函數(shù)，操作者可以利用Excel提供的各種函數(shù)公式，通過確定參數(shù)的數(shù)值，按特定的順序或結(jié)構(gòu)自動(dòng)執(zhí)行復(fù)雜計(jì)算，大大簡(jiǎn)化了計(jì)算模型建立的過程，并有助于提高模型的準(zhǔn)確性，特別是Excel的財(cái)務(wù)函數(shù)、統(tǒng)計(jì)函數(shù)等對(duì)于財(cái)務(wù)分析相關(guān)模型的建立有很大的幫助。

第三，Excel還具有良好的圖形顯示能力，將這種功能與其他功能相結(jié)合，決策者可以在決策模型的基礎(chǔ)上經(jīng)過簡(jiǎn)單的操作制作高質(zhì)量的動(dòng)態(tài)圖形，將抽象的決策模型與直觀生動(dòng)的圖形完美結(jié)合，從而以最有效的方法幫助決策者進(jìn)行各種必要的決策分析。

三、利用Excel進(jìn)行蒙特卡羅模擬分析的實(shí)施

利用Excel進(jìn)行蒙特卡羅模擬分析首先應(yīng)根據(jù)待解決問題的具體情況，分析影響項(xiàng)目經(jīng)濟(jì)效果的不確定性因素，確定與決策相關(guān)的目標(biāo)變量與隨機(jī)變量；然后根據(jù)變量分析結(jié)果在電子表格中建立經(jīng)濟(jì)評(píng)價(jià)指標(biāo)的計(jì)算模型，同時(shí)根據(jù)項(xiàng)目情況預(yù)測(cè)隨機(jī)變量概率分布；通過計(jì)算機(jī)程序生成符合特定分布隨機(jī)變量的觀測(cè)值，并將其代入模型中進(jìn)行模擬計(jì)算，從而獲得目標(biāo)變量大量的觀測(cè)值；計(jì)算目標(biāo)變量各種統(tǒng)計(jì)指標(biāo)并收集頻數(shù)分布，生成直方圖，對(duì)模擬結(jié)果進(jìn)行解釋。利用Excel進(jìn)行蒙特卡羅模擬的具體步驟，如圖1所示。

在利用Excel進(jìn)行蒙特卡羅模擬的過程中，應(yīng)注意以下幾個(gè)方面：

1. 隨機(jī)變量概率分布的選擇

對(duì)既定分布的隨機(jī)變量進(jìn)行隨機(jī)抽樣產(chǎn)生隨機(jī)變量觀測(cè)值在蒙特卡羅模擬法中占有非常重要的地位，選擇確定隨機(jī)變量的概率分布類型，才能利用計(jì)算機(jī)產(chǎn)生符合特定概率分布的隨機(jī)數(shù)，進(jìn)而才能利用隨機(jī)數(shù)運(yùn)行模型進(jìn)行模擬分析。

對(duì)于進(jìn)行模擬分析的許多項(xiàng)目來說，大都可以利用經(jīng)驗(yàn)數(shù)據(jù)來選擇模型中隨機(jī)變量概率分布的類型。無論是歷史數(shù)據(jù)，還是努力收集的其他數(shù)據(jù)，都可以通過這些數(shù)據(jù)建立直方圖，從考察直方圖入手，尋求特定分布的特有形狀，從而確定一個(gè)經(jīng)驗(yàn)分布。

2. 特定分布隨機(jī)數(shù)的產(chǎn)生

從概率分布中得到樣本的基礎(chǔ)在于隨機(jī)數(shù)這一概念，生成符合概率分布的隨機(jī)數(shù)是蒙特卡羅模擬的關(guān)鍵步驟之一。

可以利用Excel 內(nèi)建函數(shù)生成特定分布隨機(jī)數(shù)，如可以利用Excel的 RAND（）函數(shù)生成一個(gè)在（0，1）區(qū)間中均勻分布的隨機(jī)數(shù)，也可以利用RANDBETWEEN（a，b）函數(shù)生成一個(gè)在[a，b]區(qū)間中取整數(shù)值的均勻分布隨機(jī)數(shù)。

3. 模擬次數(shù)的選擇

模擬的重復(fù)次數(shù)直接影響模擬結(jié)果的質(zhì)量，通常，重復(fù)的次數(shù)越多，對(duì)輸出目標(biāo)變量各項(xiàng)統(tǒng)計(jì)特征的描述就越精確，對(duì)于模擬次數(shù)的確定，需要運(yùn)用統(tǒng)計(jì)中有關(guān)樣本容量的知識(shí)。有時(shí)也會(huì)根據(jù)歷史經(jīng)驗(yàn)取一個(gè)數(shù)值。

4. 模擬運(yùn)行結(jié)果的記錄

在進(jìn)行蒙特卡羅模擬分析時(shí)有一個(gè)關(guān)鍵問題必須解決，即將在各個(gè)變量或參數(shù)不同隨機(jī)值條件下多次運(yùn)行模型所產(chǎn)生的目標(biāo)變量的相應(yīng)取值記錄下來，這些目標(biāo)變量值正好就是進(jìn)行模擬結(jié)果分析的數(shù)據(jù)來源。

5. 模擬結(jié)果的分析

實(shí)現(xiàn)蒙特卡羅模擬分析還有一個(gè)非常重要的問題就是對(duì)模擬運(yùn)行結(jié)果進(jìn)行統(tǒng)計(jì)分析，內(nèi)容包括樣本均值、樣本標(biāo)準(zhǔn)差等在內(nèi)的各種描述性統(tǒng)計(jì)指標(biāo)的計(jì)算，此外，還應(yīng)當(dāng)分析樣本數(shù)據(jù)的頻數(shù)分布，根據(jù)樣本數(shù)據(jù)繪制直方圖，以及求出數(shù)據(jù)的某些具有特殊意義的概率值，如投資項(xiàng)目中凈現(xiàn)值大于零、內(nèi)部收益率大于基準(zhǔn)收益率的概率值等。

（1）主要統(tǒng)計(jì)指標(biāo)計(jì)算方法。

①利用Excel內(nèi)建函數(shù)計(jì)算統(tǒng)計(jì)指標(biāo)。Excel內(nèi)建的統(tǒng)計(jì)函數(shù)提供了數(shù)據(jù)統(tǒng)計(jì)指標(biāo)計(jì)算的功能，如使用VAR（data range）計(jì)算樣本方差、STDEV（data range） 計(jì)算樣本均值、CORREL （array1，array2） 計(jì)算相關(guān)系數(shù)等。

②利用Excel數(shù)據(jù)分析功能計(jì)算統(tǒng)計(jì)指標(biāo)。首先需要選擇Excel“工具”菜單下的“數(shù)據(jù)分析”項(xiàng)，在其對(duì)話框中選中“描述統(tǒng)計(jì)”項(xiàng)，然后屏幕將出現(xiàn)一個(gè)描述統(tǒng)計(jì)對(duì)話框，選中對(duì)話框中的相關(guān)輸入輸出信息，就可以得到相應(yīng)的統(tǒng)計(jì)結(jié)果。

利用數(shù)據(jù)分析功能可以生成一個(gè)完整的描述統(tǒng)計(jì)量列表，但是利用內(nèi)建函數(shù)計(jì)算統(tǒng)計(jì)量較為靈活方便，更值得一提的是Excel的自動(dòng)重算功能，只要數(shù)據(jù)發(fā)生變化，所返回的統(tǒng)計(jì)值量也會(huì)發(fā)生相應(yīng)的變化，從而使數(shù)據(jù)與統(tǒng)計(jì)量之間保持著動(dòng)態(tài)的聯(lián)系。

（2）利用Excel的Frequency函數(shù)建立直方圖。在進(jìn)行操作之前，需要先確定待分析數(shù)據(jù)的極大值與極小值，以此為參照來規(guī)定待分析數(shù)據(jù)區(qū)間的劃分寬度，然后在工作表的一列范圍內(nèi)，按升序鍵入作為劃分區(qū)間分界點(diǎn)的分格數(shù)據(jù)，也就是接收數(shù)據(jù)。

接收數(shù)據(jù)確定后，就將一個(gè)與接收數(shù)據(jù)范圍具有相同高度的一列單元格選黑，并鍵入公式Frequency（Data-array，Bins-array），將被分析的樣本數(shù)據(jù)所在范圍與接收數(shù)據(jù)所在范圍的單元格引用分別作為該函數(shù)的第一和第二個(gè)參數(shù)，然后按“Ctrl+Shift+Enter”組合鍵，這樣在選黑的單元格范圍內(nèi)便可得到樣本出現(xiàn)在各個(gè)子區(qū)間的頻數(shù)了。

創(chuàng)建了頻數(shù)分布以后，就可以利用Excel柱形圖的圖表向?qū)?chuàng)建直方圖了。這樣，頻數(shù)分布和數(shù)據(jù)建立了聯(lián)系，由此生成的樣本數(shù)據(jù)直方圖就是可以隨樣本數(shù)據(jù)變化而自動(dòng)更新的“活”的直方圖了。

四、案例分析

1. NPV（凈現(xiàn)值）計(jì)算函數(shù)簡(jiǎn)介

Excel的內(nèi)建函數(shù)具有強(qiáng)大的數(shù)據(jù)計(jì)算和數(shù)據(jù)分析功能，特別是NPV（凈現(xiàn)值）計(jì)算函數(shù)對(duì)于研究利用 Excel實(shí)現(xiàn)蒙特卡羅模擬在財(cái)務(wù)評(píng)價(jià)上的應(yīng)用有很大幫助。

NPV函數(shù)表達(dá)式為：NPV（Rate，Value1，Value2，…）

式中，Rate表示各期現(xiàn)金流折算成當(dāng)前值的貼現(xiàn)率，它的取值在各期中應(yīng)是固定不變的，Valuel，Value2，…代表支出或收入的1~29個(gè)參數(shù)，它們?cè)跁r(shí)間上必須具有相等間隔，并且都發(fā)生在期末。NPV函數(shù)中Value指定的是每一周期期末發(fā)生的現(xiàn)金流，如果第一個(gè)周期的期初有另外的現(xiàn)金流，那么在計(jì)算時(shí)，需要將這一現(xiàn)金流加到NPV函數(shù)返回值中，這樣才能計(jì)算出正確的凈現(xiàn)值。

2. 模擬變量構(gòu)成分析

不管是對(duì)凈現(xiàn)值的預(yù)測(cè)，還是對(duì)內(nèi)部收益率以及投資回收期的預(yù)測(cè)，都與項(xiàng)目計(jì)算期內(nèi)各年凈現(xiàn)金流量有密不可分的關(guān)系，所以應(yīng)當(dāng)從項(xiàng)目的凈現(xiàn)金流量從手，分析凈現(xiàn)金流量的構(gòu)成要素及其計(jì)算模型。各年的現(xiàn)金流量Yt的計(jì)算公式為：Y0 =投資（負(fù)值），Yt =（R-C-Dt）（1-Te）+Dt （t =1，2，…，n）。其中，R表示年收入，C表示年支出，Te表示稅率，采用直線折舊法進(jìn)行，即折舊Dt =（投資-殘值）/壽命期（t =1，2，…，n）。

3. 模擬變量構(gòu)成及概率分布

某投資項(xiàng)目期投資、壽命期、殘值以及各年的收入、支出，以及應(yīng)付稅金的稅率、項(xiàng)目的資本成本等都是獨(dú)立的隨機(jī)變量，概率密度函數(shù)如表1所示。

表1 模擬變量構(gòu)成及概率分布

4. 各獨(dú)立變量的模擬過程

（1）將概率對(duì)應(yīng)的可能值標(biāo)準(zhǔn)化為0~99之間的數(shù)，投資概率及對(duì)應(yīng)的隨機(jī)數(shù)如表2所示，其他變量的轉(zhuǎn)化方法與之相同。

表2 投資概率及對(duì)應(yīng)的隨機(jī)數(shù)

（2）得到各獨(dú)立變量的模擬值，下面仍以投資為例對(duì)模擬的過程進(jìn)行說明。

首先使用RANDBETWEEN（0，99）函數(shù)在A31:A5030區(qū)域產(chǎn)生5 000個(gè)隨機(jī)數(shù)，然后使用VLOOKUP（）函數(shù)搜索表格首列滿足條件的元素，確定待檢查單元格在區(qū)域中的行序號(hào)，再進(jìn)一步返回選定單元格的值。例如，在A31產(chǎn)生一個(gè)隨機(jī)數(shù)61，然后使用VLOOKUP（A31，$C$3:$D$5，2）就可以找到隨機(jī)數(shù)對(duì)應(yīng)的投資額550。

將各獨(dú)立變量進(jìn)行模擬后，運(yùn)用凈現(xiàn)金流量的計(jì)算公式，得到其模擬值，然后運(yùn)用凈現(xiàn)值計(jì)算公式NPV，在Q31:Q5030區(qū)域得到凈現(xiàn)值的模擬值。

5. 運(yùn)用Excel函數(shù)計(jì)算凈現(xiàn)值模擬運(yùn)算結(jié)果（見表3）

表3 凈現(xiàn)值模擬計(jì)算結(jié)果

6. 利用Excel的Frequency函數(shù)建立直方圖（見圖2）

五、結(jié) 論

利用 Excel 程序可以完成對(duì)項(xiàng)目經(jīng)濟(jì)效果的蒙特卡羅模擬分析，獲得經(jīng)濟(jì)評(píng)價(jià)指標(biāo)的各項(xiàng)統(tǒng)計(jì)信息，不僅可以從經(jīng)濟(jì)評(píng)價(jià)指標(biāo)高于或低于基準(zhǔn)指標(biāo)的概率上來分析項(xiàng)目的可行性，還可以從指標(biāo)的變異系數(shù)、離散程度等方面來分析項(xiàng)目的風(fēng)險(xiǎn)情況。因此利用Excel對(duì)項(xiàng)目經(jīng)濟(jì)效果進(jìn)行蒙特卡羅模擬分析所得的信息比較全面、科學(xué)，根據(jù)這些信息投資者可以更加科學(xué)、合理地判斷項(xiàng)目投資的風(fēng)險(xiǎn)性。

主要參考文獻(xiàn)

［1］ 劉清志. 石油技術(shù)經(jīng)濟(jì)學(xué)［M］. 東營(yíng)：中國(guó)石油大學(xué)出版社，1998.

［2］ ［美］ 戴維·R ·安德森，丹尼斯·J ·斯威尼.數(shù)據(jù)模型與決策［M］. 于淼譯. 北京：機(jī)械工業(yè)出版社，2003.

［3］ ［美］肯·布萊克等. 以Excel為決策工具的商務(wù)與經(jīng)濟(jì)統(tǒng)計(jì)［M］.北京：機(jī)械工業(yè)出版社，2003.

［4］ 張瑞君. 計(jì)算機(jī)財(cái)務(wù)管理［M］. 北京：中國(guó)人民大學(xué)出版社，2005.

［5］ 韓良智. Excel在財(cái)務(wù)管理與分析中的應(yīng)用［M］. 北京：中國(guó)水利水電出版社，2004.