excel在投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析中的應(yīng)用

1、第8章 Excel在投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析中的應(yīng)用8.1 盈虧平衡分析(1)盈虧平衡分析的原理就是根據(jù)量本利之間的關(guān)系，計(jì)算項(xiàng)目的盈虧平衡點(diǎn)的銷(xiāo)售量，從而分析項(xiàng)目對(duì)市場(chǎng)需求變化的適應(yīng)能力。一般來(lái)說(shuō)，盈虧平衡點(diǎn)是指企業(yè)既不虧又不盈或營(yíng)業(yè)利潤(rùn)為零時(shí)的銷(xiāo)售量。根據(jù)是否考慮資金的時(shí)間價(jià)值，盈虧平衡分析又可分為靜態(tài)盈虧平衡分析和動(dòng)態(tài)盈虧平衡分析。8.1.11 靜靜態(tài)盈虧虧平衡分分析靜態(tài)盈虧平平衡分析析是在不不考慮資資金的時(shí)時(shí)間價(jià)值值情況下下，對(duì)投投資項(xiàng)目目的盈虧虧平衡進(jìn)進(jìn)行分析析。當(dāng)某某年的營(yíng)營(yíng)業(yè)利潤(rùn)潤(rùn)為零時(shí)時(shí)，可以以得到該該年盈虧虧平衡點(diǎn)點(diǎn)的銷(xiāo)售售量為（這這里假設(shè)設(shè)只有一一種產(chǎn)品品）：式中，Qtt為

2、第t年的盈盈虧平衡衡點(diǎn)銷(xiāo)售售量（又又稱保本本銷(xiāo)售量量）；FFt為第t年的固固定成本本，這里里假設(shè)非非付現(xiàn)固固定成本本只有折折舊，即即Ft = Dt + Fc，Dt為第t年的折折舊；FFc為付現(xiàn)現(xiàn)固定成成本；pp為產(chǎn)品品單價(jià)；v為產(chǎn)品品的單位位變動(dòng)成成本，并并假設(shè)各各年的付付現(xiàn)固定定成本、產(chǎn)產(chǎn)品單價(jià)價(jià)和產(chǎn)品品的單位位變動(dòng)成成本均不不變。當(dāng)產(chǎn)銷(xiāo)量低低于盈虧虧平衡點(diǎn)點(diǎn)銷(xiāo)售量量時(shí)，投投資項(xiàng)目目處于虧虧損狀態(tài)態(tài)，反之之，當(dāng)產(chǎn)產(chǎn)銷(xiāo)量超超過(guò)盈虧虧平衡點(diǎn)點(diǎn)銷(xiāo)售量量時(shí)，項(xiàng)項(xiàng)目就有有了盈利利。當(dāng)企企業(yè)在盈盈虧平衡衡點(diǎn)附近近經(jīng)營(yíng)，即即銷(xiāo)售量量接近于于Qt時(shí)，投投資項(xiàng)目目的經(jīng)營(yíng)營(yíng)風(fēng)險(xiǎn)很很大，或或經(jīng)營(yíng)上上的安全全程度很

3、很低，銷(xiāo)銷(xiāo)售量微微小的下下降都可可能使企企業(yè)發(fā)生生虧損。單一產(chǎn)品的的盈虧平平衡分析析比較簡(jiǎn)簡(jiǎn)單。根根據(jù)給定定的各年年的付現(xiàn)現(xiàn)固定成成本、折折舊、產(chǎn)產(chǎn)品單價(jià)價(jià)和單位位變動(dòng)成成本，即即可由上上述公式式計(jì)算出出各年的的靜態(tài)保保本銷(xiāo)售售量。當(dāng)一個(gè)投資資項(xiàng)目同同時(shí)生產(chǎn)產(chǎn)多種不不同的產(chǎn)產(chǎn)品，或或?qū)σ粋€(gè)個(gè)生產(chǎn)多多種產(chǎn)品品的整個(gè)個(gè)企業(yè)進(jìn)進(jìn)行盈虧虧平衡分分析時(shí)，則則需要考考慮多品品種產(chǎn)品品的情況況。在進(jìn)進(jìn)行多品品種盈虧虧平衡分分析時(shí)，加加權(quán)平均均法是較較常用的的一種方方法?！纠?-11】某企業(yè)業(yè)生產(chǎn)AA、B、C三種產(chǎn)產(chǎn)品，AA產(chǎn)品年年銷(xiāo)售量量10000000件，單單價(jià)100元/件，單單位變動(dòng)動(dòng)成本88.5元元/

4、件；B產(chǎn)品年年銷(xiāo)售量量250000臺(tái)臺(tái)，單價(jià)價(jià)20元/臺(tái)，單單位變動(dòng)動(dòng)成本116元/臺(tái)；C產(chǎn)品年年銷(xiāo)售量量100000套套，單價(jià)價(jià)50元/套，單單位變動(dòng)動(dòng)成本225元/套；全全廠固定定成本33000000元元。根據(jù)據(jù)以上資資料，可可以建立立分析表表格如圖圖8-11所示。有關(guān)計(jì)算分分析公式式如下：銷(xiāo)售收入=銷(xiāo)售量量單價(jià)邊際貢獻(xiàn)=銷(xiāo)售量量（單價(jià)價(jià)單位位變動(dòng)成成本）邊際貢獻(xiàn)率率=邊際貢貢獻(xiàn)銷(xiāo)售收收入銷(xiāo)售比重=某產(chǎn)品品銷(xiāo)售收收入全廠各各產(chǎn)品銷(xiāo)銷(xiāo)售收入入合計(jì)全廠綜合邊邊際貢獻(xiàn)獻(xiàn)率=某產(chǎn)品品邊際貢貢獻(xiàn)率該產(chǎn)品品銷(xiāo)售比比重全廠綜合保保本額=全廠固固定成本本全廠綜綜合邊際際貢獻(xiàn)率率某產(chǎn)品保本本額=全廠綜綜合保

5、本本額該產(chǎn)品品銷(xiāo)售比比重某產(chǎn)品保本本量=某產(chǎn)品品保本額額該產(chǎn)品品單價(jià)圖8-1 某企業(yè)業(yè)的多品品種盈虧虧平衡分分析輸入已知數(shù)數(shù)據(jù)及定定義完公公式后，即即可馬上上計(jì)算出出各個(gè)可可變單元元格的數(shù)數(shù)值來(lái)，即即全廠綜綜合保本本額122000000元元，產(chǎn)品品A、B、C的保本本額分別別為60000000元、30000000元和和30000000元，保保本量分分別為6600000件、1550000臺(tái)和60000套套。各單元格的的計(jì)算公公式為單元格E33:E5 ：“= BB3:B5*C3:C5”（數(shù)組組公式輸輸入）。單元格F33:F5 ：“=B33:B5*(C33:C5-D3:D5)”（數(shù)組組公式輸輸入）。單

6、元格G33:G6 ：“=F33:F6/E3:E6”（數(shù)組組公式輸輸入）。單元格E66：“=SUUM(EE3:E5)”。單元格F66：“=SUMM(F33:F5)”。單元格B88:B100 ：“=E33:E5/E6”（數(shù)組組公式輸輸入）。單元格D88:D100 ：“=B88:B100*D111”（數(shù)組組公式輸輸入）。單元格E88:E100 ：“=D88:D100/C33:C5”（數(shù)組組公式輸輸入）。單元格D111 ：“=H66/G66”。圖8-1建建立了各各產(chǎn)品的的單價(jià)、單單位變動(dòng)動(dòng)成本和和固定成成本與保保本額或或保本量量之間的的關(guān)系，利利用圖88-1就就可分析析它們對(duì)對(duì)盈虧平平衡點(diǎn)的的影響。8

7、.1.22 動(dòng)動(dòng)態(tài)盈虧虧平衡分分析8.1.22.1 獨(dú)立立項(xiàng)目的的動(dòng)態(tài)盈盈虧平衡衡分析靜態(tài)盈虧平平衡分析析沒(méi)有考考慮資金金的時(shí)間間價(jià)值、所所得稅、利利率，以以及通貨貨膨脹等等因素的的影響，由由此計(jì)算算出的盈盈虧平衡衡點(diǎn)銷(xiāo)售售量?jī)H僅僅能使項(xiàng)項(xiàng)目的當(dāng)當(dāng)期達(dá)到到盈虧平平衡，卻卻并不能能保證項(xiàng)項(xiàng)目的凈凈現(xiàn)值恰恰好為零零。在考考慮資金金的時(shí)間間價(jià)值和和所得稅稅等因素素的條件件下，項(xiàng)項(xiàng)目的動(dòng)動(dòng)態(tài)盈虧虧平衡點(diǎn)點(diǎn)就是項(xiàng)項(xiàng)目?jī)衄F(xiàn)現(xiàn)值為零零的那一一點(diǎn)，即即動(dòng)態(tài)保保本銷(xiāo)售售量就是是使項(xiàng)目目?jī)衄F(xiàn)值值為零的的銷(xiāo)售量量。考慮慮單一產(chǎn)產(chǎn)品的情情況，令令NPVV=0，則則可得到到項(xiàng)目各各年的動(dòng)動(dòng)態(tài)保本本銷(xiāo)售量量的計(jì)算算公式為

8、為式中，T為為所得稅稅稅率；Qt為各年年的保本本銷(xiāo)售量量；pt、vt和Fct分別別為各年年的產(chǎn)品品單價(jià)、單單位變動(dòng)動(dòng)成本、付付現(xiàn)固定定成本；i為項(xiàng)目目的基準(zhǔn)準(zhǔn)收益率率；I為初始始投資（并并假設(shè)在在第0年一次次性投入入項(xiàng)目）。這樣，利用用上述公公式，即即可分析析各種情情況下項(xiàng)項(xiàng)目各年年保本銷(xiāo)銷(xiāo)售量的的變化情情況?！纠?-22】某企業(yè)業(yè)準(zhǔn)備投投資生產(chǎn)產(chǎn)一種新新產(chǎn)品，項(xiàng)項(xiàng)目總投投資3550萬(wàn)元元，項(xiàng)目目壽命期期5年，期期末無(wú)殘殘值，采采用直線線法計(jì)提提折舊。經(jīng)經(jīng)預(yù)測(cè)，項(xiàng)項(xiàng)目投產(chǎn)產(chǎn)后每年年可銷(xiāo)售售產(chǎn)品8850000臺(tái)，產(chǎn)產(chǎn)品單價(jià)價(jià)40元/臺(tái)，單單位變動(dòng)動(dòng)成本220元/臺(tái)，年年付現(xiàn)固固定成本本50萬(wàn)元元

9、，企業(yè)業(yè)的基準(zhǔn)準(zhǔn)收益率率為100%，所所得稅稅稅率333%。此時(shí)，各年年的折舊舊額相同同，假設(shè)設(shè)各年的的銷(xiāo)售量量、產(chǎn)品品單價(jià)、單單位變動(dòng)動(dòng)成本、付付現(xiàn)固定定成本以以及折舊舊均相同同，可以以利用上上述公式式直接導(dǎo)導(dǎo)出動(dòng)態(tài)態(tài)盈虧平平衡銷(xiāo)售售量（保保本銷(xiāo)售售量）的的計(jì)算公公式如下下：根據(jù)此公式式計(jì)算出出保本銷(xiāo)銷(xiāo)售量為為766664件件，如圖圖8-22所示，單單元格EE4中的的保本銷(xiāo)銷(xiāo)售量計(jì)計(jì)算公式式為：“=(EE3+(B3/PV(F3,B3,-1)-G33)*AA3/BB3/(1-GG3)/(CC3-DD3)*100000”。圖8-2 投資資項(xiàng)目的的動(dòng)態(tài)盈盈虧平衡衡分析但實(shí)際上，各各年的銷(xiāo)銷(xiāo)售量受受

10、到企業(yè)業(yè)內(nèi)部及及市場(chǎng)條條件的影影響，它它們是不不同的，故故可以利利用規(guī)劃劃求解工工具來(lái)計(jì)計(jì)算各年年的保本本銷(xiāo)售量量，步驟驟如下：（1）如圖圖8-22所示，將將單元格格C100:G100作為可可變單元元格，存存放各年年的保本本銷(xiāo)售量量。（2）在單單元格BB7中輸輸入公式式“= -A3”，在單單元格CC7:G7中輸輸入凈現(xiàn)現(xiàn)金流量量計(jì)算公公式“=(C99:G9/100000*(C33-D33)-EE3)*(1-G3)+SLLN(AA3,HH3,BB3)*G3”（數(shù)組公式輸入）。（3）在單單元格HH7中輸輸入凈現(xiàn)現(xiàn)值計(jì)算算公式“=NPVV(F33,C77:G7)+B77”。（4）單擊擊工具菜菜單中的

11、的【規(guī)劃劃求解】項(xiàng)項(xiàng)，出現(xiàn)現(xiàn)【規(guī)劃劃求解參參數(shù)】對(duì)對(duì)話框，其其中【設(shè)設(shè)置目標(biāo)標(biāo)單元格格】選“$H$7”，【等等于】選選“0”，【可可變單元元格】選選“$C$9:$G$9”；單擊擊【求解解】按鈕鈕，即得得各年的的保本銷(xiāo)銷(xiāo)售量，如如圖8-2所示示。當(dāng)可變單元元格C99:G9中賦賦予不同同的初始始值時(shí)，會(huì)會(huì)得到不不同的保保本銷(xiāo)售售量,如圖8-2所示示），各各年的銷(xiāo)銷(xiāo)售量變變化范圍圍很大，這這正反映映了動(dòng)態(tài)態(tài)盈虧平平衡的特特點(diǎn)。實(shí)實(shí)際上，各各年保本本銷(xiāo)售量量組合有有無(wú)數(shù)個(gè)個(gè)，故為為求得符符合實(shí)際際情況的的保本銷(xiāo)銷(xiāo)售量組組合，還還要考慮慮一些約約束條件件，比如如各年的的會(huì)計(jì)利利潤(rùn)應(yīng)大大于零，各各年取得得

12、的現(xiàn)金金收入應(yīng)應(yīng)能夠償償還各年年到期的的債務(wù)以以及支付付股利，等等等。8.1.22.2 互斥斥項(xiàng)目的的動(dòng)態(tài)盈盈虧平衡衡分析在需要對(duì)若若干個(gè)方方案進(jìn)行行比較的的情況下下，若是是某一個(gè)個(gè)共有的的不確定定性因素素（比如如銷(xiāo)售量量、產(chǎn)量量、壽命命、產(chǎn)品品價(jià)格、單單位變動(dòng)動(dòng)成本等等）影響響這些方方案的取取舍，則則可以利利用盈虧虧平衡分分析幫助助決策?！纠?-33】某企業(yè)業(yè)投資生生產(chǎn)某種種產(chǎn)品，現(xiàn)現(xiàn)有兩個(gè)個(gè)方案，有有關(guān)資料料如圖88-3所所示，該該產(chǎn)品的的市場(chǎng)壽壽命具有有較大的的不確定定性，如如果基準(zhǔn)準(zhǔn)收益率率為155%，不不考慮期期末資產(chǎn)產(chǎn)殘值，那那么，企企業(yè)應(yīng)如如何根據(jù)據(jù)項(xiàng)目的的壽命期期來(lái)選擇擇方案？

13、圖8-3 根據(jù)據(jù)方案壽壽命期選選擇項(xiàng)目目如圖8-33所示，進(jìn)進(jìn)行決策策分析的的步驟如如下：（1）首先先在單元元格F33中輸入入“5”，在單單元格HH3中輸輸入“11”，單元元格G33可先空空置。（2）選取取單元格格區(qū)域FF4:H5，輸輸入不同同壽命期期下兩個(gè)個(gè)方案的的凈現(xiàn)值值計(jì)算公公式“=PVV(D44:D5,F3:H3,-C44:C5)-B44:B5”（數(shù)組組公式輸輸入）。（3）在單單元格GG6中輸輸入公式式“=G44-G55”，將單單元格GG6作為為目標(biāo)單單元格，將將單元格格G3作為為可變單單元格，利利用【規(guī)規(guī)劃求解解】工具具或【單單變量求求解】工工具，可可以求得得兩個(gè)方方案凈現(xiàn)現(xiàn)值相等等

14、時(shí)的項(xiàng)項(xiàng)目壽命命期。由計(jì)算結(jié)果果可以看看出，兩兩個(gè)方案案凈現(xiàn)值值相等時(shí)時(shí)的壽命命期為99.922年，這這就是以以項(xiàng)目壽壽命期為為共有變變量時(shí)方方案1與方案案2的凈現(xiàn)現(xiàn)值無(wú)差差異點(diǎn)。因因此，當(dāng)當(dāng)壽命期期小于99.922年時(shí)，應(yīng)應(yīng)采用方方案1；而當(dāng)當(dāng)壽命期期大于99.922年時(shí)，應(yīng)應(yīng)采用方方案2。8.1.33 投投資項(xiàng)目目盈虧平平衡分析析模型除了前面介介紹的直直接利用用公式計(jì)計(jì)算盈虧虧平衡點(diǎn)點(diǎn)保本銷(xiāo)銷(xiāo)售量或或設(shè)計(jì)工工作表格格進(jìn)行分分析外，我我們還可可以設(shè)計(jì)計(jì)盈虧平平衡分析析模型來(lái)來(lái)分析每每個(gè)因素素各種可可能的變變動(dòng)情況況下對(duì)保保本銷(xiāo)售售量的影影響?！纠?-44】投資項(xiàng)項(xiàng)目的盈盈虧平衡衡分析模模型如

15、圖圖8-44所示，具具體設(shè)計(jì)計(jì)步驟如如下：圖8-4 投資資項(xiàng)目盈盈虧平衡衡分析模模型（1）首先先設(shè)計(jì)好好分析模模型結(jié)構(gòu)構(gòu)，如圖圖8-44所示（表表中數(shù)據(jù)據(jù)以例88-2為為例）。（2）單擊擊【視圖圖】，選選擇【工工具欄】，再再單擊【窗窗體】，出出現(xiàn)【窗窗體】工工具欄，如如圖8-5所示示，單擊擊【滾動(dòng)動(dòng)條】按按鈕，然然后在工工作表的的合適位位置（這這里為EE4F4單元元格）拖拖曳出一一個(gè)矩形形【組合合框】控控件，并并調(diào)整其其大小。圖8-5 【窗體體】工具具欄（3）將鼠鼠標(biāo)移到到新建立立的【滾滾動(dòng)條】控控件上，單單擊鼠標(biāo)標(biāo)右鍵，出出現(xiàn)快捷捷菜單，選選擇【設(shè)設(shè)置控件件格式】，出出現(xiàn)【設(shè)設(shè)置控件件格式】

16、對(duì)對(duì)話框，選選擇【控控制】項(xiàng)項(xiàng)，如圖圖8-66所示。圖8-6 【設(shè)設(shè)置控件件格式】對(duì)對(duì)話框（4）在【當(dāng)當(dāng)前解】欄欄輸入225，【最最小值】輸輸入0，【最最大值】輸輸入500，【步步長(zhǎng)】輸輸入1，【頁(yè)頁(yè)步長(zhǎng)】輸輸入100，在【單單元格鏈鏈接】填填入“E4”，然后后單擊【確確定】按按鈕，這這就建立立了初始始投資的的【滾動(dòng)動(dòng)條】控控件。（5）其他他項(xiàng)目的的【滾動(dòng)動(dòng)條】控控件可按按照上述述方法進(jìn)進(jìn)行。（6）在單單元格CC4:C100中建立立變動(dòng)百百分比與與【滾動(dòng)動(dòng)條】控控件的聯(lián)聯(lián)系，即即：在單單元格CC4中輸輸入公式式“=E44/1000-225%”，并將將單元格格C4分別別復(fù)制到到單元格格C5:C1

17、00中。說(shuō)明：本例例中各因因素的變變動(dòng)范圍圍為-225%+255%，而而滾動(dòng)條條控制按按鈕的值值的變化化范圍為為050，為為了使?jié)L滾動(dòng)條控控制按鈕鈕的變化化表示為為百分?jǐn)?shù)數(shù)的變化化，這里里將控制制按鈕的的值除以以1000后再減減去255%，則則每次單單擊滾動(dòng)動(dòng)條兩端端的箭頭頭，單元元格C44:C100中的變變動(dòng)百分分比就變變化1%，而當(dāng)當(dāng)滾動(dòng)條條在中間間位置，變變動(dòng)百分分比恰好好為零。（7）選取取單元格格D4:D100區(qū)域并并輸入變變化后數(shù)數(shù)值的計(jì)計(jì)算公式式“=B44:B100*(11+C44:C100)”（數(shù)組組公式輸輸入）。（8）在單單元格CC15中中輸入靜靜態(tài)盈虧虧平衡銷(xiāo)銷(xiāo)售量計(jì)計(jì)算公式

18、式“=(SSLN(D4,D5,B111)+DD6)/(D88-D99)*1100000”。(9）在單單元格CC16中中輸入動(dòng)動(dòng)態(tài)盈虧虧平衡銷(xiāo)銷(xiāo)售量計(jì)計(jì)算公式式：“=(D66+(DD4-PPV(DD10,B111,-11)*SSLN(D4,D5,B111)*BB13-D5/(1+D100)BB11)/PVV(D110,BB11,-1)/(11-B113)/(DD8-DD9)*100000”。這里，當(dāng)項(xiàng)項(xiàng)目各年年的銷(xiāo)售售量、付付現(xiàn)成本本、產(chǎn)品品單價(jià)、單單位變動(dòng)動(dòng)成本、折折舊（直直線法）相相等、且且考慮固固定資產(chǎn)產(chǎn)殘值回回收時(shí)，動(dòng)動(dòng)態(tài)盈虧虧平衡銷(xiāo)銷(xiāo)售量計(jì)計(jì)算公式式為式中，S為為期末固固定資產(chǎn)產(chǎn)殘值。

19、這樣，就建建立了投投資項(xiàng)目目盈虧平平衡分析析模型。每每次單擊擊滾動(dòng)欄欄兩端的的箭頭或或用鼠標(biāo)標(biāo)拖曳滑滑塊，變變動(dòng)百分分比就變變化1%，當(dāng)在在滾動(dòng)框框與滾動(dòng)動(dòng)條之間間單擊滾滾動(dòng)條時(shí)時(shí)，變動(dòng)動(dòng)百分比比就以10%變化。則則通過(guò)不不同的因因素變化化可以了了解投資資項(xiàng)目盈盈虧平衡衡銷(xiāo)售量量的變化化情況，有有利于企企業(yè)管理理者進(jìn)行行決策。第8章 EExceel在投投資項(xiàng)目目不確定定性風(fēng)險(xiǎn)險(xiǎn)分析中中的應(yīng)用用8.2 敏敏感性分分析(11) 敏感性分析析是投資資決策中中常用的的一種重重要的分分析方法法。它是是用來(lái)衡衡量當(dāng)投投資方案案中某個(gè)個(gè)因素發(fā)發(fā)生了變變動(dòng)時(shí)，對(duì)對(duì)該方案案預(yù)期結(jié)結(jié)果的影影響程度度。如果果某因素

20、素在較小小范圍內(nèi)內(nèi)發(fā)生了了變動(dòng)就就會(huì)影響響原定方方案的經(jīng)經(jīng)濟(jì)效果果，即表表明該因因素的敏敏感性強(qiáng)強(qiáng)；如果果某因素素在較大大范圍內(nèi)內(nèi)變動(dòng)時(shí)時(shí)才會(huì)影影響原定定方案的的經(jīng)濟(jì)效效果，即即表明該該因素的的敏感性性弱。通常要作敏敏感性分分析的因因素有：（1）投資資額，包包括固定定資產(chǎn)投投資和追追加的流流動(dòng)資產(chǎn)產(chǎn)投資。（2）項(xiàng)目目壽命期期。（3）產(chǎn)品品的產(chǎn)銷(xiāo)銷(xiāo)量。（4）產(chǎn)品品價(jià)格。（5）經(jīng)營(yíng)營(yíng)成本，特特別是其其中的變變動(dòng)成本本。（6）項(xiàng)目目壽命期期末的設(shè)設(shè)備殘值值。（7）折現(xiàn)現(xiàn)率。在長(zhǎng)期投資資決策中中，敏感感性分析析通常用用來(lái)研究究有關(guān)投投資方案案的現(xiàn)金金凈流量量或固定定資產(chǎn)壽壽命發(fā)生生變動(dòng)時(shí)時(shí)，對(duì)該該方案

21、的的凈現(xiàn)值值和內(nèi)部部收益率率的影響響程度。同同時(shí)，它它也可以以用來(lái)研研究有關(guān)關(guān)投資項(xiàng)項(xiàng)目的內(nèi)內(nèi)部收益益率變動(dòng)動(dòng)時(shí)，對(duì)對(duì)該方案案的現(xiàn)金金凈流量量或使用用年限的的影響程程度。敏敏感性分分析有助助于企業(yè)業(yè)領(lǐng)導(dǎo)了了解在執(zhí)執(zhí)行決策策方案時(shí)時(shí)應(yīng)注意意的問(wèn)題題，從而而可以預(yù)預(yù)先考慮慮措施與與對(duì)策，避避免決策策上的失失誤。8.2.11 一一般的敏敏感性分分析方法法【例8-55】圖8-77為某一一投資方方案的有有關(guān)資料料，所采采用的數(shù)數(shù)據(jù)是根根據(jù)對(duì)未未來(lái)可能能出現(xiàn)的的情況預(yù)預(yù)測(cè)的，未未來(lái)的投投資額、付付現(xiàn)成本本和銷(xiāo)售售收入都都有可能能在300%的范范圍內(nèi)變變動(dòng)。試試對(duì)這三三個(gè)因素素做敏感感性分析析。企業(yè)業(yè)采用直

22、直線法計(jì)計(jì)提折舊舊，基準(zhǔn)準(zhǔn)收益率率為155%。圖8-7 不確確定性因因素對(duì)凈凈現(xiàn)值的的影響一般性的敏敏感性分分析方法法和步驟驟如下：（1）設(shè)計(jì)計(jì)如圖88-7所所示的分分析表格格。（2）在單單元格BB10:H100中輸入入投資額額變動(dòng)對(duì)對(duì)凈現(xiàn)值值的影響響計(jì)算公公式：“=PV(E3,10,-(C4-C5)*(11-F33)+SSLN(B3*(1+B9:H9),D66,100)*FF3)+D66/(11+E33)110-BB3*(1+BB9:H9)”。（3）在單單元格BB11:H111中輸入入銷(xiāo)售收收入變動(dòng)動(dòng)對(duì)凈現(xiàn)現(xiàn)值的影影響計(jì)算算公式：“=PV(E3,10,-(C4*(1+B9:H9)-C55)*

23、(1-FF3)+SLNN(B33,D66,100)*FF3)+D66/(11+E33)110-BB3”。（4）在單單元格BB12:H122中輸入入付現(xiàn)成成本變動(dòng)動(dòng)對(duì)凈現(xiàn)現(xiàn)值的影影響計(jì)算算公式：“=PV(E3,10,-(C4-C5*(1+B9:H9)*(1-FF3)+SLNN(B33,D66,100)*FF3)+D66/(11+E33)110-BB3”。以上各單元元格區(qū)域域的公式式輸入均均為數(shù)組組公式輸輸入，則則計(jì)算結(jié)結(jié)果如圖圖8-77所示。對(duì)計(jì)算結(jié)果果繪制分分析圖如如圖8-8所示示，步驟驟如下：圖8-8 敏感感性分析析圖（1）選取取單元格格區(qū)域AA9:H122，單擊擊工具欄欄上的【圖圖表向?qū)?dǎo)

24、】按鈕鈕，在【圖圖表向?qū)?dǎo)4步驟之之1圖表類(lèi)類(lèi)型】對(duì)對(duì)話框中中，【圖圖表類(lèi)型型】選“XY散點(diǎn)點(diǎn)圖”，【子子圖表類(lèi)類(lèi)型】選選“平滑線線散點(diǎn)圖圖”，單擊擊【下一一步】按按鈕。（2）在【圖圖表向?qū)?dǎo)4步驟之之2圖表源源數(shù)據(jù)】對(duì)對(duì)話框中中，不做做任何輸輸入，單單擊【下下一步】按按鈕。（3）在【圖圖表向?qū)?dǎo)4步驟之之3圖表選選項(xiàng)】對(duì)對(duì)話框中中，在【圖圖表標(biāo)題題】欄中中輸入“敏感性性分析圖圖”，在【數(shù)數(shù)值(XX)軸】欄欄中輸入入“不確定定性因素素變動(dòng)幅幅度”，在【數(shù)數(shù)值(YY)軸】欄欄中輸入入“凈現(xiàn)值值”，單擊擊【下一一步】按按鈕。（4）在【圖圖表向?qū)?dǎo)4步驟之之4圖表位位置】對(duì)對(duì)話框中中，不做做任何輸輸

25、入，單單擊【確確定】按按鈕。（5）對(duì)圖圖表的大大小、坐坐標(biāo)數(shù)值值、標(biāo)題題等格式式進(jìn)行調(diào)調(diào)整，使使圖表賞賞心悅目目，則圖圖表制作作即告完完成?？梢?jiàn)，銷(xiāo)售售收入對(duì)對(duì)凈現(xiàn)值值的影響響最大，付付現(xiàn)成本本其次，而而投資額額的影響響最小。然后可以利利用單變變量求解解工具求求出當(dāng)凈凈現(xiàn)值為為零時(shí)每每個(gè)不確確定性因因素的變變動(dòng)數(shù)值值，方法法是：在在J100中輸入入公式“=PVV(E33,100,-(C44-C55)*(1-FF3)+SLNN (BB3*(1+II10),D66,100)*FF3)+D66/(11+E33)110-BB3*(1+II10)”，并將將J100作為目目標(biāo)單元元格，II10作作為可變變

26、單元格格，即可可利用單單變量求求解工具具計(jì)算出出凈現(xiàn)值值為零時(shí)時(shí)的投資資額最大大變動(dòng)率率。用同同樣的方方法可以以求出凈凈現(xiàn)值為為零時(shí)的的銷(xiāo)售收收入和付付現(xiàn)成本本最大變變動(dòng)率?？煽梢?jiàn)，當(dāng)當(dāng)銷(xiāo)售收收入和付付現(xiàn)成本本不變時(shí)時(shí)，投資資額增加加到411.644%以上上時(shí)會(huì)使使方案變變得不應(yīng)應(yīng)被接受受；當(dāng)投投資額和和付現(xiàn)成成本不變變時(shí)，銷(xiāo)銷(xiāo)售收入入低于預(yù)預(yù)期值的的11.48%以上時(shí)時(shí)會(huì)使方方案變得得不應(yīng)被被接受；而當(dāng)投投資額和和銷(xiāo)售收收入不變變時(shí)，付付現(xiàn)成本本高于預(yù)預(yù)期值118.779%以以上時(shí)會(huì)會(huì)使方案案變得不不應(yīng)被接接受。因因此，三三個(gè)因素素的敏感感性由強(qiáng)強(qiáng)到弱的的排序依依次為：銷(xiāo)售收收入、付付現(xiàn)成本

27、本和投資資額。8.2.22 投投資項(xiàng)目目?jī)衄F(xiàn)值值敏感性性分析模模型我們也可以以參照前前面所述述的投資資項(xiàng)目盈盈虧平衡衡分析模模型，來(lái)來(lái)建立投投資項(xiàng)目目?jī)衄F(xiàn)值值敏感性性分析模模型?！纠?-66】建立投投資項(xiàng)目目?jī)衄F(xiàn)值值敏感性性分析模模型。如如圖8-9所示示，這里里最大變變化區(qū)間間取50%，在【設(shè)設(shè)置控件件格式】對(duì)對(duì)話框的的【當(dāng)前前解】欄欄中輸入入“50”，【最最大值】欄欄中輸入入“1000”，【頁(yè)頁(yè)步長(zhǎng)】欄欄中輸入入“5”。其他他項(xiàng)目的的計(jì)算方方法同投投資項(xiàng)目目盈虧平平衡分析析模型。定義一個(gè)名名為“凈現(xiàn)值值”的自定定義函數(shù)數(shù)，其語(yǔ)語(yǔ)法為：凈現(xiàn)值值(初始投投資, 期末殘殘值, 壽命期期, 年付付現(xiàn)

28、固定定成本, 年銷(xiāo)銷(xiāo)售量, 產(chǎn)品品價(jià)格, 單位位變動(dòng)成成本, 基準(zhǔn)收收益率, 所得得稅稅率率)。自定定義函數(shù)數(shù)可以通通過(guò)一小小段程序序?qū)ζ鋮?shù)及參參數(shù)之間間的關(guān)系系進(jìn)行描描述，這這種程序序又稱過(guò)過(guò)程代碼碼?！皟衄F(xiàn)值值”自定義義函數(shù)的的過(guò)程代代碼如下下：Publiic FFuncctioon 凈凈現(xiàn)值(初始投投資, 期末殘殘值, 壽命期期, 年付付現(xiàn)固定定成本, 年銷(xiāo)銷(xiāo)售量, 產(chǎn)品品價(jià)格, 單位位變動(dòng)成成本, 基準(zhǔn)收收益率, 所得得稅稅率率)凈現(xiàn)金流量量 = (年銷(xiāo)銷(xiāo)售量 * (產(chǎn)品價(jià)價(jià)格 - 單位位變動(dòng)成成本) / 1100000 - 年付付現(xiàn)固定定成本) * (1 - 所所得稅稅稅率) +

29、 (初始投投資 - 期末末殘值) / 壽命期期 * 所得稅稅稅率圖8-9 投資資項(xiàng)目?jī)魞衄F(xiàn)值敏敏感性分分析模型型凈現(xiàn)值 = 凈現(xiàn)現(xiàn)金流量量 * (1 - (1 + 基準(zhǔn)準(zhǔn)收益率率) -壽壽命期) / 基準(zhǔn)收收益率 - 初初始投資資 + 期末殘殘值 / (11 + 基準(zhǔn)收收益率) 壽命期期End FFuncctioon在單元格AA16中中輸入預(yù)預(yù)計(jì)凈現(xiàn)現(xiàn)值計(jì)算算公式“=凈現(xiàn)值值(B44,B55,B66,B77,B88,B99,B110,BB11,$B$12)”，（步步驟為：?jiǎn)螕艄すぞ邫诘牡摹菊迟N貼函數(shù)】按按鈕，選選擇“用戶定定義”，選中中“凈現(xiàn)值值”函數(shù)，出出現(xiàn)該函函數(shù)對(duì)話話框，輸輸入相應(yīng)應(yīng)的內(nèi)

30、容容即可），并并復(fù)制到到單元格格B166中；在在單元格格C166中輸入入公式“=B116-AA16”，在單單元格DD16輸輸入公式式“=C116/AA16”。這樣樣，就得得到了多多因素變變動(dòng)對(duì)凈凈現(xiàn)值的的綜合影影響結(jié)果果。設(shè)計(jì)單因素素變動(dòng)影影響分析析表格，如如圖8-9所示示，在單單元格BB19:B266中輸入入公式“=D44:D111”（數(shù)組組公式輸輸入），在在單元格格C199:C266中分別別粘貼各各個(gè)因素素單獨(dú)變變動(dòng)時(shí)的的凈現(xiàn)值值計(jì)算函函數(shù)如下下： 單元格C119：凈凈現(xiàn)值(C4,B5,B6,B7,B8,B9,B100,B111,BB12)單元格C220：凈凈現(xiàn)值(B4,C5,B6,B7,

31、B8,B9,B100,B111,BB12)單元格C221：凈凈現(xiàn)值(B4,B5,C6,B7,B8,B9,B100,B111,BB12)單元格C222：凈凈現(xiàn)值(B4,B5,B6,C7,B8,B9,B100,B111,BB12)單元格C223：凈凈現(xiàn)值(B4,B5,B6,B7,C8,B9,B100,B111,BB12)單元格C224：凈凈現(xiàn)值(B4,B5,B6,B7,B8,C9,B100,B111,BB12)單元格C225：凈凈現(xiàn)值(B4,B5,B6,B7,B8,B9,C100,B111,BB12)單元格C226：凈凈現(xiàn)值(B4,B5,B6,B7,B8,B9,B100,C111,BB12)在單元

32、格DD19:D266中輸入入公式“=C119:C266-A116”（數(shù)組組公式輸輸入），在在單元格格E199:E266中輸入入公式“=D119:D266/A116”（數(shù)組組公式輸輸入）。則投資項(xiàng)目目敏感性性分析模模型就建建立起來(lái)來(lái)了。單單擊各個(gè)個(gè)影響因因素滾動(dòng)動(dòng)條的箭箭頭，改改變其變變動(dòng)幅度度，就可可以很方方便地了了解各個(gè)個(gè)因素對(duì)對(duì)投資項(xiàng)項(xiàng)目?jī)衄F(xiàn)現(xiàn)值的單單獨(dú)影響響程度以以及綜合合影響程程度。這樣，通過(guò)過(guò)單擊滾滾動(dòng)欄兩兩端的箭箭頭或用用鼠標(biāo)拖拖曳滑塊塊，即可可改變各各種因素素的變動(dòng)動(dòng)率，并并分析其其對(duì)項(xiàng)目目?jī)衄F(xiàn)值值的影響響。8.2.33 投投資項(xiàng)目目?jī)?nèi)部收收益率敏敏感性分分析模型型【例8-77】

33、建立投投資項(xiàng)目目?jī)?nèi)部收收益率敏敏感性分分析模型型。我們們也可以以對(duì)投資資項(xiàng)目的的內(nèi)部收收益率的的敏感性性進(jìn)行分分析，方方法與投投資項(xiàng)目目?jī)衄F(xiàn)值值敏感性性分析模模型是一一樣的。但但需要注注意的是是，當(dāng)要要分析單單因素變變動(dòng)對(duì)內(nèi)內(nèi)部收益益率的影影響時(shí)，內(nèi)內(nèi)部收益益率的計(jì)計(jì)算是一一件很麻麻煩的事事，因?yàn)闉楫?dāng)投資資項(xiàng)目壽壽命期內(nèi)內(nèi)各年的的凈現(xiàn)金金流量不不相等時(shí)時(shí)，不能能使用RRATEE函數(shù)來(lái)來(lái)計(jì)算內(nèi)內(nèi)部收益益率，不不過(guò)可以以通過(guò)自自定義內(nèi)內(nèi)部收益益率函數(shù)數(shù)來(lái)解決決這個(gè)問(wèn)問(wèn)題。作作者研究究了一種種內(nèi)部收收益率的的穩(wěn)定迭迭代計(jì)算算方法，具具有穩(wěn)定定、快速速、收斂斂性好的的優(yōu)點(diǎn)，計(jì)計(jì)算原理理及步驟驟如下：（

34、1）首先先假定一一個(gè)內(nèi)部部收益率率的初始始值，并并以此內(nèi)內(nèi)部收益益率作為為貼現(xiàn)率率i，計(jì)算算項(xiàng)目的的凈現(xiàn)值值NPVV；（2）根據(jù)據(jù)計(jì)算出出的凈現(xiàn)現(xiàn)值數(shù)據(jù)據(jù)，利用用下面的的公式計(jì)計(jì)算第11次迭代代后的內(nèi)內(nèi)部收益益率IRRR：式中I初始投投資現(xiàn)值值。若相鄰兩次次計(jì)算的的內(nèi)部收收益率相相差不大大，或計(jì)計(jì)算出的的凈現(xiàn)值值接近于于零，則則停止計(jì)計(jì)算，就就得到了了內(nèi)部收收益率的的近似值值，否則則重復(fù)上上述迭代代步驟。內(nèi)部收益率率函數(shù)的的過(guò)程代代碼如下下：Publiic FFuncctioon 內(nèi)內(nèi)部收益益率(初始投投資, 期末殘殘值, 壽命期期, 年付付現(xiàn)成本本, 年銷(xiāo)銷(xiāo)售量, 產(chǎn)品品價(jià)格, 單位位變動(dòng)成

35、成本, 所得稅稅稅率) 凈凈現(xiàn)金流流量 = (年年銷(xiāo)售量量 * (產(chǎn)品品價(jià)格 - 單位變變動(dòng)成本本) / 1000000 - 年付現(xiàn)現(xiàn)成本) * (1 - 所得稅稅稅率) + (初始始投資 - 期末殘殘值) / 壽壽命期 * 所所得稅稅稅率 xx1 = 0.110 jjxz = 凈凈現(xiàn)金流流量 * (11 - (11 + x1) -壽命期期) / x11 - 初始投投資 + 期末末殘值 / (1 + x11) 壽命命期 xx2 = (11 + x1) * (1 + jjxz / 初初始投資資) (11 / 壽命期期) - 1 IIf AAbs(x2 - x11) = 00.0000000000

36、001 Theen 內(nèi)內(nèi)部收益益率 = x22 Ellse x1 = xx2: GooTo 10End FFuncctioon這樣，就可可以分析析不同因因素變動(dòng)動(dòng)對(duì)內(nèi)部部收益率率的影響響。圖88-100為投資資項(xiàng)目?jī)?nèi)內(nèi)部收益益率敏感感性分析析模型。其其中單元元格B114中的的計(jì)算公公式為“=內(nèi)部收收益率(B4,B5,B6,B7,B8,B9,B100,B111)”；單元元格D114中的的計(jì)算公公式為“=內(nèi)部收收益率(C4,C5,C6,C7,C8,C9,C100,B111)”，單元元格C117:C233中的計(jì)計(jì)算公式式分別如如下：圖8-100 投投資項(xiàng)目目?jī)?nèi)部收收益率敏敏感性分分析模型型單元格C1

37、17：“=內(nèi)部收收益率(C4,B5,B6,B7,B8,B9,B100,B111)” 單元格C118：“=內(nèi)部收收益率(B4,C5,B6,B7,B8,B9,B100,B111)”單元格C119：“=內(nèi)部收收益率(B4,B5,C6,B7,B8,B9,B100,B111)” 單元格C220：“=內(nèi)部收收益率(B4,B5,B6,C7,B8,B9,B100,B111)”單元格C221：“=內(nèi)部收收益率(B4,B5,B6,B7,C8,B9,B100,B111)”單元格C222：“=內(nèi)部收收益率(B4,B5,B6,B7,B8,C9,B100,B111)”單元格C223：“=內(nèi)部收收益率(B4,B5,B6,B

38、7,B8,B9,C100,B111)”其他各單元元格的計(jì)計(jì)算公式式可參閱閱例8-6。第8章 EExceel在投投資項(xiàng)目目不確定定性風(fēng)險(xiǎn)險(xiǎn)分析中中的應(yīng)用用8.3 概概率分析析 概率分析是是通過(guò)研研究各種種不確定定性因素素發(fā)生不不同幅度度變動(dòng)的的概率分分布及其其對(duì)投資資方案經(jīng)經(jīng)濟(jì)效果果的影響響，對(duì)方方案的凈凈現(xiàn)金流流量及其其經(jīng)濟(jì)效效果指標(biāo)標(biāo)作出某某種概率率描述，從從而對(duì)方方案的風(fēng)風(fēng)險(xiǎn)情況況作出比比較準(zhǔn)確確的判斷斷。在實(shí)際經(jīng)濟(jì)濟(jì)活動(dòng)中中，影響響投資方方案經(jīng)濟(jì)濟(jì)效果的的大多數(shù)數(shù)因素（如如投資額額、成本本、銷(xiāo)售售量、產(chǎn)產(chǎn)品價(jià)格格、項(xiàng)目目壽命期期等）都都是隨機(jī)機(jī)變量，我我們可以以預(yù)測(cè)其其未來(lái)可可能的取取值

39、范圍圍，估計(jì)計(jì)各種取取值或值值域發(fā)生生的概率率，但不不能肯定定地預(yù)知知它們?nèi)∪∈裁粗抵?。因此此，這就就需要對(duì)對(duì)投資項(xiàng)項(xiàng)目進(jìn)行行概率分分析。假設(shè)投資項(xiàng)項(xiàng)目有mm種可能能出現(xiàn)的的凈現(xiàn)金金流量狀狀態(tài)，各各種狀態(tài)態(tài)所對(duì)應(yīng)應(yīng)的凈現(xiàn)現(xiàn)金流量量序列為為yj，各種種狀態(tài)發(fā)發(fā)生的概概率為PPj（），則則在第jj種狀態(tài)態(tài)下，方方案的凈凈現(xiàn)值為為式中，為在在第j種狀態(tài)態(tài)下，第第t周期的的凈現(xiàn)金金流量；n為項(xiàng)目目的壽命命期。則投資方案案的凈現(xiàn)現(xiàn)值期望望值為而凈現(xiàn)值的的方差為為標(biāo)準(zhǔn)差為對(duì)于獨(dú)立方方案，計(jì)計(jì)算其凈凈現(xiàn)值期期望值和和標(biāo)準(zhǔn)差差的大小小，可以以分析其其獲利能能力及風(fēng)風(fēng)險(xiǎn)的大大小。對(duì)對(duì)于幾個(gè)個(gè)互斥方方案，可可以比

40、較較它們的的變異系系數(shù)的大大小，以以便衡量量其相對(duì)對(duì)風(fēng)險(xiǎn)的的高低，從從而作出出決策，變變異系數(shù)數(shù)計(jì)算公公式為8.3.11 獨(dú)立立項(xiàng)目的的概率分分析8.3.11.1 各年年凈現(xiàn)金金流量互互不相關(guān)關(guān)情況下下的獨(dú)立立項(xiàng)目概概率分析析【例8-88】某企業(yè)業(yè)的投資資方案在在其壽命命期內(nèi)可可能出現(xiàn)現(xiàn)5種狀態(tài)態(tài)的凈現(xiàn)現(xiàn)金流量量序列及及其發(fā)生生的概率率如圖88-111所示。各各年的凈凈現(xiàn)金流流量互不不相關(guān)，基基準(zhǔn)收益益率100%，試試對(duì)方案案進(jìn)行概概率分析析。圖8-111 投投資方案案的概率率分析如圖8-111所示示，選取取單元格格C8:G8區(qū)域域，輸入入不同狀狀態(tài)下凈凈現(xiàn)值的的計(jì)算公公式“=PVV(100%

41、,110,-C5:G5)+C66:G6/(1+10%)111+CC4:G4”（數(shù)組組公式輸輸入）；在單元元格C99中輸入入凈現(xiàn)值值期望值值計(jì)算公公式“=SUUMPRRODUUCT(C3:G3,C8:G8)”，在單單元格CC10中中輸入凈凈現(xiàn)值方方差計(jì)算算公式“=SUUMPRRODUUCT(C3:G3,(C88:G8-C9)2)”，在單單元格CC11中中輸入凈凈現(xiàn)值標(biāo)標(biāo)準(zhǔn)差計(jì)計(jì)算公式式“=SQQRT(C100)”，在單單元格CC12中中輸入變變異系數(shù)數(shù)計(jì)算公公式“=C111/CC9”，從而而得到該該方案的的有關(guān)計(jì)計(jì)算結(jié)果果數(shù)據(jù)，如如圖8-11所所示?？煽梢?jiàn)，該該方案的的凈現(xiàn)值值期望值值大于零零，

42、是可可行的，但但風(fēng)險(xiǎn)也也較大。1年金相況獨(dú)目分樹(shù)法【例8-99】某企業(yè)業(yè)擬投資資開(kāi)發(fā)一一項(xiàng)專(zhuān)有有技術(shù)，其其初始投投資為112萬(wàn)元元，該項(xiàng)項(xiàng)技術(shù)預(yù)預(yù)計(jì)在33年內(nèi)有有效，33年內(nèi)每每年為企企業(yè)帶來(lái)來(lái)的現(xiàn)金金流量是是不確定定的，其其有關(guān)資資料如表表8-11所示。該該企業(yè)的的資本成成本為115%，試試對(duì)該投投資項(xiàng)目目的可行行性進(jìn)行行評(píng)價(jià)。表81 投資資項(xiàng)目有有關(guān)資料料 單位：萬(wàn)元項(xiàng)目初始投投資12壽命/年3貼現(xiàn)率15%第1年第2年第3年凈現(xiàn)金流量量概率凈現(xiàn)金流量量概率凈現(xiàn)金流量量概率7.50.6100.7140.6110.37.50.160.390.470.440.240.480.2100.390.4

43、80.370.490.750.350.47.50.150.32.50.6為便于分析析計(jì)算，將將表8-1中的的數(shù)據(jù)填填列在EExceel上，其其格式如如圖8-12所所示，則則具體計(jì)計(jì)算步驟驟如下：（1）在單單元格GG6中輸輸入第一一種現(xiàn)金金流序列列情況下下的凈現(xiàn)現(xiàn)值計(jì)算算公式“=NPPV($F$33,A66,C66,E66)-$B$33”，然后后將此單單元格復(fù)復(fù)制到單單元格GG7G199。（2）在單單元格HH6:H199中輸入入聯(lián)合概概率計(jì)算算公式“=B66:B199*D66:D199*F66:F199”（數(shù)組組公式輸輸入）。（3）在單單元格II6中輸輸入凈現(xiàn)現(xiàn)值期望望值計(jì)算算公式“=SUUMP

44、RRODUUCT(G6:G199,H66:H199)”。（4）在單單元格JJ6中輸輸入凈現(xiàn)現(xiàn)值標(biāo)準(zhǔn)準(zhǔn)差計(jì)算算公式“=SQQRT(SUMMPROODUCCT(HH6:H199,(GG6:G199-I66)22)”。（5）在單單元格JJ6中輸輸入凈現(xiàn)現(xiàn)值為負(fù)負(fù)的概率率計(jì)算公公式“=SUUMIFF(G66:G199,0,H6:H199)”?？梢?jiàn)，該項(xiàng)項(xiàng)目的期期望凈現(xiàn)現(xiàn)值為55.211萬(wàn)元，凈凈現(xiàn)值為為負(fù)的概概率很小小。只有有0.1144，該該項(xiàng)目的的獲利能能力較高高而風(fēng)險(xiǎn)險(xiǎn)不大，故故該項(xiàng)目目是可行行的。圖8-122 投投資項(xiàng)目目概率分分析8.3.22 互互斥項(xiàng)目目的概率率分析在此種情況況下，可可分別

45、計(jì)計(jì)算各個(gè)個(gè)項(xiàng)目的的凈現(xiàn)值值期望值值、標(biāo)準(zhǔn)準(zhǔn)差及變變異系數(shù)數(shù)，對(duì)它它們進(jìn)行行綜合分分析，以以確定最最優(yōu)方案案。【例8-110】某企業(yè)業(yè)有A、B兩個(gè)投投資項(xiàng)目目，其投投資額及及未來(lái)各各年可能能的凈現(xiàn)現(xiàn)金流量量如圖88-133所示，假假設(shè)各年年的凈現(xiàn)現(xiàn)金流量量相互獨(dú)獨(dú)立。那那么，企企業(yè)應(yīng)選選擇哪個(gè)個(gè)項(xiàng)目？圖8-133 各各年現(xiàn)金金流獨(dú)立立的互斥斥方案的的概率分分析如圖8-113所示示，分析析決策過(guò)過(guò)程如下下：（1）在合合并單元元格G55G7中輸輸入項(xiàng)目目A第1年的凈凈現(xiàn)金流流量期望望值計(jì)算算公式“=SUUMPRRODUUCT(C5:C7,D5:D7)”，然后后將此合合并單元元格分別別復(fù)制到到合并單

46、單元格GG8G100、G111G133、G144G166中，得得到項(xiàng)目目A各年的的凈現(xiàn)金金流量期期望值。（2）在合合并單元元格H55H7中輸輸入項(xiàng)目目B第1年的凈凈現(xiàn)金流流量期望望值計(jì)算算公式“=SUUMPRRODUUCT(E5:E7,F5:F7)”，然后后將此合合并單元元格分別別復(fù)制到到合并單單元格HH8H100、H111H133、H144H166中，得得到項(xiàng)目目B各年的的凈現(xiàn)金金流量期期望值。（3）在合合并單元元格I55I7中輸輸入項(xiàng)目目A第1年的凈凈現(xiàn)金流流量標(biāo)準(zhǔn)準(zhǔn)差計(jì)算算公式“=SQQRT(SUMMPROODUCCT(CC5:C7,(D55:D7-G5)2)”，然后后將此合合并單元元格

47、分別別復(fù)制到到合并單單元格II8I100、I111I133、I144I166中，得得到項(xiàng)目目A各年的的凈現(xiàn)金金流量標(biāo)標(biāo)準(zhǔn)差。（4）在合合并單元元格J55J7中輸輸入項(xiàng)目目B第1年的凈凈現(xiàn)金流流量標(biāo)準(zhǔn)準(zhǔn)差計(jì)算算公式“=SQQRT(SUMMPROODUCCT(EE5:E7,(F55:F7-H5)2)”，然后后將此合合并單元元格分別別復(fù)制到到合并單單元格JJ8J100、J111J133、J144J166中，得得到項(xiàng)目目B各年的的凈現(xiàn)金金流量標(biāo)標(biāo)準(zhǔn)差。（5）在單單元格CC20中中輸入項(xiàng)項(xiàng)目A的凈現(xiàn)現(xiàn)值期望望值計(jì)算算公式“=NPPV(BB17,G5,G8,G111,G114)+G4”，在單單元格CC21

48、中中輸入凈凈現(xiàn)值標(biāo)標(biāo)準(zhǔn)差計(jì)計(jì)算公式式“=SQQRT(SUMM(I55:I1662/(1+B177)(2*AA5:A166)”（數(shù)組組公式輸輸入），在在單元格格C222中輸入入變異系系數(shù)公式式“=C221/CC20”；這里里，整個(gè)個(gè)項(xiàng)目的的凈現(xiàn)值值標(biāo)準(zhǔn)差差現(xiàn)值的的計(jì)算公公式為（6）同樣樣在單元元格D220、D211、D222中分別別輸入項(xiàng)項(xiàng)目B的凈現(xiàn)現(xiàn)值期望望值、標(biāo)標(biāo)準(zhǔn)差和和變異系系數(shù)計(jì)算算公式“=NPPV(BB17,H5,H8,H111,H114)+H4”、“=SQQRT(SUMM(J55:J1662/(1+B177)(2*AA5:A166)”（數(shù)組組公式輸輸入）和和“=D221/DD20”。

49、根據(jù)計(jì)算結(jié)結(jié)果可見(jiàn)見(jiàn)，項(xiàng)目目A風(fēng)險(xiǎn)較較低但收收益也低低，而項(xiàng)項(xiàng)目B風(fēng)險(xiǎn)較較高但收收益也高高。但兩兩個(gè)項(xiàng)目目的變異異系數(shù)相相差并不不是很大大，在這這種情況況下，如如何作出出決策，在在很大程程度上取取決于決決策人員員對(duì)風(fēng)險(xiǎn)險(xiǎn)的態(tài)度度。愿意意冒風(fēng)險(xiǎn)險(xiǎn)的決策策人員，會(huì)會(huì)選擇項(xiàng)項(xiàng)目B，而對(duì)對(duì)風(fēng)險(xiǎn)很很敏感的的決策者者可能會(huì)會(huì)傾向于于選擇項(xiàng)項(xiàng)目A。第8章 EExceel在投投資項(xiàng)目目不確定定性風(fēng)險(xiǎn)險(xiǎn)分析中中的應(yīng)用用8.4 蒙蒙特卡羅羅模擬 前面介紹的的概率分分析法在在各年現(xiàn)現(xiàn)金流彼彼此獨(dú)立立、且變變量及其其可能發(fā)發(fā)生的情情況較少少的情況況下，進(jìn)進(jìn)行模擬擬計(jì)算是是比較簡(jiǎn)簡(jiǎn)單的。但但在變量量及其可可能發(fā)生生的情

50、況況較多的的情況下下，則必必須進(jìn)行行大量的的組合計(jì)計(jì)算，例例如，假假設(shè)我們們估計(jì)的的銷(xiāo)售量量、單價(jià)價(jià)、單位位變動(dòng)成成本的可可能發(fā)生生概率次次數(shù)分別別為5次、4次和4次，則則每年就就需要計(jì)計(jì)算544=880個(gè)可可能組合合，假設(shè)設(shè)項(xiàng)目的的壽命為為5年，則則需要計(jì)計(jì)算8005=4400個(gè)個(gè)可能組組合，計(jì)計(jì)算工作作量非常常大。利利用蒙特特卡羅模模擬法可可以解決決這個(gè)問(wèn)問(wèn)題，并并且還可可以對(duì)項(xiàng)項(xiàng)目的風(fēng)風(fēng)險(xiǎn)進(jìn)行行更為深深入的分分析。蒙特卡羅模模擬法，是是根據(jù)隨隨機(jī)數(shù)對(duì)對(duì)影響因因素的概概率分布布進(jìn)行隨隨機(jī)抽樣樣，根據(jù)據(jù)每次抽抽樣值來(lái)來(lái)計(jì)算項(xiàng)項(xiàng)目的凈凈現(xiàn)金流流量、凈凈現(xiàn)值及及內(nèi)部收收益率等等指標(biāo)。在在利用蒙蒙

51、特卡羅羅模擬法法對(duì)投資資項(xiàng)目進(jìn)進(jìn)行分析析計(jì)算時(shí)時(shí)，主要要利用兩兩個(gè)函數(shù)數(shù)：RAANDBBETWWEENN 函數(shù)數(shù)和VLLOOKKUP函函數(shù)，利利用RAANDBBETWWEENN函數(shù)產(chǎn)產(chǎn)生隨機(jī)機(jī)數(shù)，然然后利用用VLOOOKUUP函數(shù)數(shù)來(lái)查找找對(duì)應(yīng)隨隨機(jī)數(shù)的的變量數(shù)數(shù)值。當(dāng)當(dāng)進(jìn)行足足夠的模模擬計(jì)算算次數(shù)后后，即可可得到投投資項(xiàng)目目?jī)衄F(xiàn)值值的期望望值、標(biāo)標(biāo)準(zhǔn)差及及概率分分布。8.4.11 獨(dú)立立項(xiàng)目的的蒙特卡卡羅模擬擬【例8-111】某企業(yè)業(yè)計(jì)劃投投資一項(xiàng)項(xiàng)目，初初始投資資200000元元，項(xiàng)目目壽命33年，期期末無(wú)殘殘值。經(jīng)經(jīng)統(tǒng)計(jì)分分析得出出產(chǎn)品銷(xiāo)銷(xiāo)售量、單單價(jià)、單單位變動(dòng)動(dòng)成本和和付現(xiàn)固固定成本

52、本在各年年可能達(dá)達(dá)到的水水平及有有關(guān)的概概率情況況如圖88-144所示。則則利用蒙蒙特卡羅羅模擬法法進(jìn)行投投資項(xiàng)目目不確定定性分析析的方法法和步驟驟如下：圖8-144 投投資項(xiàng)目目的有關(guān)關(guān)資料及及整理（1）首先先設(shè)計(jì)模模擬計(jì)算算表格，如如圖8-15所所示。圖圖8-115只列列出了對(duì)對(duì)第1年的3次模擬擬計(jì)算過(guò)過(guò)程，其其他各年年的模擬擬計(jì)算過(guò)過(guò)程及表表格排列列順序與與第1年相同同，其中中第2年的模模擬計(jì)算算單元格格在第JJ RR列，第第3年的模模擬計(jì)算算單元格格在第SS AAA列。下下面僅就就第1年的模模擬計(jì)算算過(guò)程進(jìn)進(jìn)行介紹紹。圖8-155 第第1年的前前3次模擬擬計(jì)算結(jié)結(jié)果（2）在AA25:A

53、50024單單元格輸輸入第11年銷(xiāo)售售量的隨隨機(jī)數(shù)計(jì)計(jì)算公式式：“=RAANDBBETWWEENN(0,99)”（第255行輸入入后，再再將其復(fù)復(fù)制到AA26A50024單單元格中中），然然后在BB25:B50024單單元格輸輸入對(duì)應(yīng)應(yīng)的銷(xiāo)售售量公式式：“=VLLOOKKUP(A255:A50024,C3:D6,2)”（數(shù)組組公式輸輸入，共共運(yùn)算550000行，即即進(jìn)行550000次模擬擬計(jì)算）。說(shuō)明：當(dāng)利利用VLLOOKKUP函函數(shù)尋找找符合某某一概率率的銷(xiāo)售售量時(shí)，必必須先對(duì)對(duì)概率及及銷(xiāo)售量量進(jìn)行分分區(qū)。以以第1年為例例，隨機(jī)機(jī)數(shù)為00、22、56和86時(shí)對(duì)對(duì)應(yīng)的銷(xiāo)銷(xiāo)售量分分別為44000

54、0、50000、60000、70000件，其其意義就就是當(dāng)產(chǎn)產(chǎn)生的隨隨機(jī)數(shù)分分別為0021、2255、5685和8699時(shí)，對(duì)對(duì)應(yīng)的銷(xiāo)銷(xiāo)售量分分別為440000、50000、60000、70000件，而而累計(jì)概概率分別別為0.22、0.556、0.886和1.000。（3）在CC25:C50024單單元格輸輸入第11年單價(jià)價(jià)的隨機(jī)機(jī)數(shù)計(jì)算算公式：“=RAANDBBETWWEENN(0,99)”，然后后在D225:D50024單單元格輸輸入對(duì)應(yīng)應(yīng)的單價(jià)價(jià)公式：“=VLLOOKKUP(C255:C50024, C77:D111,2)”（數(shù)組組公式輸輸入）。（4）在EE25:E50024單單元格輸輸

55、入第11年單位位變動(dòng)成成本的隨隨機(jī)數(shù)計(jì)計(jì)算公式式：“=RAANDBBETWWEENN(0,99)”，然后后在F225:F50024單單元格輸輸入對(duì)應(yīng)應(yīng)的單位位變動(dòng)成成本公式式：“=VLLOOKKUP(E255:E50024,C122:D155,2)” （數(shù)組組公式輸輸入）。（5）在GG25:G50024單單元格輸輸入第11年付現(xiàn)現(xiàn)成本的的隨機(jī)數(shù)數(shù)計(jì)算公公式：“=RAANDBBETWWEENN(0,99)”，然后后在H225:H50024單單元格輸輸入對(duì)應(yīng)應(yīng)的付現(xiàn)現(xiàn)固定成成本公式式：“=VLLOOKKUP(G255:G50024,C166:D199,2)” （數(shù)組組公式輸輸入）。（6）在II25

56、:I50024單單元格輸輸入第11年凈現(xiàn)現(xiàn)金流量量計(jì)算公公式：“=(BB25:B50024*(D225:D50024-F255:F50024)-H225:H50024)*(11-F220)+B200/3*F200”（數(shù)組組公式輸輸入），這這里采用用直線法法計(jì)提折折舊，年年折舊=$B$20/3。第2年、第第3年的凈凈現(xiàn)金流流量分別別放在單單元格RR25:R50024和和單元格格AA225:AA550244中，而而凈現(xiàn)值值則存放放在ABB25:AB550244中，其其計(jì)算公公式為：“=I225/(1+$D$220)+R255/(11+$DD$200)22+AAA25/(1+$D$20)3-$B$2

57、0”（可先先輸入AAB255單元格格，然后后再?gòu)?fù)制制到ABB26AB550244單元格格中。（7）最后后的模擬擬計(jì)算結(jié)結(jié)果存放放在單元元格K11:M7中，如如圖8-16所所示，各各單元格格的計(jì)算算公式為為：?jiǎn)卧馦22“=AVVERAAGE(AB225:AB550244)”，單元元格M33“=STTDEVV(ABB25:AB550244)”，單元元格M44“=MAAX(AAB255:AB550244)”，單元元格M55“=MIIN(AAB255:AB550244)”，單元元格M66“=M33/M22”，單元元格M77“=COOUNTTIF(AB225:AB550244,0)/COOUNTT(

58、ABB25:AB550244)”。圖8-166 模模擬計(jì)算算結(jié)果在模擬計(jì)算算完畢后后，還可可以利用用Exccel的的繪圖工工具繪制制凈現(xiàn)值值概率分分布圖，具具體步驟驟如下：（1）首先先對(duì)凈現(xiàn)現(xiàn)值進(jìn)行行分組，以以便于進(jìn)進(jìn)行統(tǒng)計(jì)計(jì)分析，分分組結(jié)果果存放在在單元格格O4:O177中。（2）設(shè)計(jì)計(jì)圖表上上凈現(xiàn)值值的分區(qū)區(qū)，存放放在單元元格P44:P177中。（3）再選選取單元元格區(qū)域域Q4:Q166，輸入入FREEQUEENCYY函數(shù)：“=FRREQUUENCCY(AAB255:AB550244,O44:O166)/550000”（數(shù)組組公式輸輸入），即即可得到到凈現(xiàn)值值概率分分布結(jié)果果，如88-17

59、7所示。然然后插入入直方圖圖，做法法是：選選擇單元元格區(qū)域域P4:Q177，在圖圖表向?qū)?dǎo)中選柱柱形圖，再再依據(jù)有有關(guān)說(shuō)明明輸入有有關(guān)資料料。凈現(xiàn)現(xiàn)值概率率分布圖圖如圖88-188所示。圖8-177 凈凈現(xiàn)值概概率分布布統(tǒng)計(jì)圖8-188 凈凈現(xiàn)值概概率分布布圖根據(jù)計(jì)算結(jié)結(jié)果及凈凈現(xiàn)值概概率分布布圖，就就可以對(duì)對(duì)投資項(xiàng)項(xiàng)目的不不確定性性進(jìn)行分分析。由由結(jié)果可可以看出出，該項(xiàng)項(xiàng)目的凈凈現(xiàn)值期期望值為為106642元元，標(biāo)準(zhǔn)準(zhǔn)差88823元元，凈現(xiàn)現(xiàn)值為負(fù)負(fù)的概率率為0.1122，故該該項(xiàng)目的的投資風(fēng)風(fēng)險(xiǎn)還是是比較小小的。該該項(xiàng)目的的凈現(xiàn)值值主要集集中在00 2200000元，概概率為00.744，

60、其中中在50000 1550000元的概概率為00.422；而凈凈現(xiàn)值為為負(fù)時(shí)主主要集中中在-550000 0之間，概概率為00.088。由圖圖8-118還可可以看出出，該項(xiàng)項(xiàng)目的凈凈現(xiàn)值大大致符合合正態(tài)分分布。8.4.22 互互斥項(xiàng)目目的蒙特特卡羅模模擬在對(duì)互斥方方案進(jìn)行行比較時(shí)時(shí)，可對(duì)對(duì)每個(gè)方方案進(jìn)行行蒙特卡卡羅模擬擬，計(jì)算算出其凈凈現(xiàn)值及及其概率率分布，并并繪制概概率分布布圖，進(jìn)進(jìn)行綜合合比較分分析，選選擇最優(yōu)優(yōu)方案?！纠?-112】以例8-7的有有關(guān)資料料為例，其其模擬準(zhǔn)準(zhǔn)備、模模擬計(jì)算算過(guò)程、模模擬計(jì)算算結(jié)果如如圖8-19所所示。共共進(jìn)行660000次模擬擬計(jì)算，即即從第221行到到