財(cái)務(wù)盈虧平衡分析原理

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

2、，這里假設(shè)非付現(xiàn)固定成本只有折舊，即Ft= Dt+ Fc, Dt為第t年的折舊；Fc為付現(xiàn)固定成本；p為產(chǎn)品單價(jià)；v為產(chǎn)品的單位變動(dòng)成本，并假設(shè)各年的付現(xiàn)固定成本、產(chǎn)品單價(jià)和產(chǎn)品的單 位變動(dòng)成本均不變。當(dāng)產(chǎn)銷量低于盈虧平衡點(diǎn)銷售量時(shí)，投資項(xiàng)目處于虧損狀態(tài)， 反之，當(dāng)產(chǎn)銷量超過(guò)盈虧平衡點(diǎn)銷售量時(shí)，項(xiàng)目就有了盈利。當(dāng)企業(yè)在盈虧平衡點(diǎn)附近經(jīng)營(yíng)，即銷售量接近于Qt時(shí)，投資項(xiàng)目的經(jīng)營(yíng)風(fēng)險(xiǎn)很大， 或經(jīng)營(yíng)上的安全程度很低， 銷售量微小的下降都可能使企業(yè)發(fā)生虧 損。單一產(chǎn)品的盈虧平衡分析比較簡(jiǎn)單。根據(jù)給定的各年的付現(xiàn)固定成本、折舊、產(chǎn)品單價(jià)和單位變動(dòng)成本，即可由上述公式計(jì)算出各年的靜態(tài)保本銷售量。當(dāng)一個(gè)投資項(xiàng)

3、目同時(shí)生產(chǎn)多種不同的產(chǎn)品，或?qū)σ粋€(gè)生產(chǎn)多種產(chǎn)品的整個(gè)企業(yè)進(jìn)行盈虧平衡分析時(shí)，則需要考慮多品種產(chǎn)品的情況。在進(jìn)行多品種盈虧平衡分析時(shí)，加權(quán)平均法是較常用的一種方法?！纠?-1某企業(yè)生產(chǎn)A、B、C三種產(chǎn)品，A產(chǎn)品年銷售量100000件，單價(jià)10元/件， 單位變動(dòng)成本8.5元/件；B產(chǎn)品年銷售量25000臺(tái)，單價(jià)20元/臺(tái)，單位變動(dòng)成本16元/臺(tái)； C產(chǎn)品年銷售量10000套，單價(jià)50元/套，單位變動(dòng)成本 25元/套；全廠固定成本 300000 元。根據(jù)以上資料，可以建立分析表格如圖8-1所示。有關(guān)計(jì)算分析公式如下：銷售收入=銷售量X單價(jià)邊際貢獻(xiàn)=銷售量X （單價(jià)單位變動(dòng)成本）邊際貢獻(xiàn)率=邊際貢獻(xiàn)+

4、銷售收入銷售比重=某產(chǎn)品銷售收入+全廠各產(chǎn)品銷售收入合計(jì)全廠綜合邊際貢獻(xiàn)率=匯某產(chǎn)品邊際貢獻(xiàn)率X該產(chǎn)品銷售比重全廠綜合保本額=全廠固定成本+全廠綜合邊際貢獻(xiàn)率某產(chǎn)品保本額=全廠綜合保本額X該產(chǎn)品銷售比重某產(chǎn)品保本量=某產(chǎn)品保本額+該產(chǎn)品單價(jià) I q I c= >.“5 . 且 j « 工 i本餐費(fèi)務(wù)Ub t什修 1»二£I of*30* mm 號(hào)moIM廠jmww "IE 卯 mm圖8-1某企業(yè)的多品種盈虧平衡分析毋同博*耳石oat；值總MOOM,皿一3福mm輸入已知數(shù)據(jù)及定義完公式后，即可馬上計(jì)算出各個(gè)可變單元格的數(shù)值來(lái)，即全廠綜合保本額1200

5、000元，產(chǎn)品 A、B、C的保本額分別為 600000元、300000元和300000元，保本量 分別為60000件、15000臺(tái)和6000套。各單元格的計(jì)算公式為單元格E3:E5 :“= B3:B5*C3:C5"（數(shù)組公式輸入）。單元格 F3: F5 : “=B3:B5*（C3 :C5-D3:D5）"（數(shù)組公式輸入）。單元格G3:G6 :“ =F3:F6/E3:E6"（數(shù)組公式輸入）。單元格 E6: “=SUM（E3 :E5）”。單元格 F6: “=SUM（F3 :F5）”。單元格B8:B10 :“ =E3:E5/E6”（數(shù)組公式輸入）。單元格 D8:D10 :

6、“ =B8:B10*D11 "（數(shù)組公式輸入）。單元格E8:E10 :“ =D8:D10/C3:C5"（數(shù)組公式輸入）。單元格 D11 :“ =H6/G6”。利用圖8-1建立了各產(chǎn)品的單價(jià)、單位變動(dòng)成本和固定成本與保本額或保本量之間的關(guān)系, 圖8-1就可分析它們對(duì)盈虧平衡點(diǎn)的影響。8.1.2 動(dòng)態(tài)盈虧平衡分析8.1.2.1 獨(dú)立項(xiàng)目的動(dòng)態(tài)盈虧平衡分析靜態(tài)盈虧平衡分析沒(méi)有考慮資金的時(shí)間價(jià)值、所得稅、利率，以及通貨膨脹等因素的影響，由此計(jì)算出的盈虧平衡點(diǎn)銷售量?jī)H僅能使項(xiàng)目的當(dāng)期達(dá)到盈虧平衡，卻并不能保證項(xiàng)目的凈現(xiàn)值恰好為零。在考慮資金的時(shí)間價(jià)值和所得稅等因素的條件下，項(xiàng)目的動(dòng)態(tài)

7、盈虧平衡點(diǎn)就是項(xiàng)目?jī)衄F(xiàn)值為零的那一點(diǎn)，即動(dòng)態(tài)保本銷售量就是使項(xiàng)目?jī)衄F(xiàn)值為零的銷售量?？紤]單一產(chǎn)品的情況，令 NPV=0,則可得到項(xiàng)目各年的動(dòng)態(tài)保本銷售量的計(jì)算公式為式中，T為所得稅稅率；Qt為各年的保本銷售量；pt、vt和Fct分別為各年的產(chǎn)品單價(jià)、單位變動(dòng)成本、付現(xiàn)固定成本；i為項(xiàng)目的基準(zhǔn)收益率；I為初始投資（并假設(shè)在第 0年一次性 投入項(xiàng)目）。這樣，利用上述公式，即可分析各種情況下項(xiàng)目各年保本銷售量的變化情況?！纠?-2】某企業(yè)準(zhǔn)備投資生產(chǎn)一種新產(chǎn)品，項(xiàng)目總投資 350萬(wàn)元，項(xiàng)目壽命期 5年，期末無(wú)殘值，采用直線法計(jì)提折舊。經(jīng)預(yù)測(cè)，項(xiàng)目投產(chǎn)后每年可銷售產(chǎn)品85000臺(tái)，產(chǎn)品單價(jià)40元/臺(tái)，

8、單位變動(dòng)成本 20元/臺(tái)，年付現(xiàn)固定成本 50萬(wàn)元，企業(yè)的基準(zhǔn)收益率為 10%,所 得稅稅率33%。此時(shí)，各年的折舊額相同，假設(shè)各年的銷售量、產(chǎn)品單價(jià)、單位變動(dòng)成本、付現(xiàn)固定成本以及折舊均相同，可以利用上述公式直接導(dǎo)出動(dòng)態(tài)盈虧平衡銷售量（保本銷售量）的計(jì)算公式如下：根據(jù)此公式計(jì)算出保本銷售量為76664件，如圖8-2所示，單元格E4中的保本銷售量計(jì)算公式為：”=(E3+(B3/PV(F3,B3,-1)-G3)*A3/B3/(1-G3)/(C3-D3)*10000”。* ®C*Ir t nt理空事登船E產(chǎn)名事將隰。W.F 招網(wǎng)1J事s3空卬101 in Q.5鋁折埒甲9i軍母曲113

9、itttlTnso霍X B9I'1事本*(伴L(zhǎng)jTOMUM Mint.-1 T泛 T 耳 I* 4HE心3代M3ism13XMDOBET3TE6就1tS4 然TlLiE45SK!8|依TWHTT8HI2TW加nmTMJSItTTIEmsTEa%IT1 W-' 冷博3!圖8-2投資項(xiàng)目的動(dòng)態(tài)盈虧平衡分析但實(shí)際上，各年的銷售量受到企業(yè)內(nèi)部及市場(chǎng)條件的影響，它們是不同的，故可以利用規(guī)劃求解工具來(lái)計(jì)算各年的保本銷售量，步驟如下：(1)如圖8-2所示，將單元格 C10:G10作為可變單元格，存放各年的保本銷售量。(2)在單元格 B7中輸入公式“ =-A3”，在單元格 C7:G7中輸入凈現(xiàn)

10、金流量計(jì)算公式“=(C9G9/10000*(C3-D3)-E3)*(1- G3)+SLN(A3,H3,B3)*G3 "(數(shù)組公式輸入)。(3)在單元格H7中輸入凈現(xiàn)值計(jì)算公式“=NPV(F3,C7G7)+B7 。(4)單擊工具菜單中的【規(guī)劃求解】項(xiàng)，出現(xiàn)【規(guī)劃求解參數(shù)】對(duì)話框，其中【設(shè)置目標(biāo)單元格】選“ $H$7"，【等于】選“ 0"，【可變單元格】選“ $C$9:$G$9"；單擊【求解】按鈕,即得各年的保本銷售量，如圖8-2所示。當(dāng)可變單元格C9:G9中賦予不同的初始值時(shí)，會(huì)得到不同的彳本銷售量，如圖8-2所示)，各年的銷售量變化范圍很大，這正反映了動(dòng)

11、態(tài)盈虧平衡的特點(diǎn)。實(shí)際上，各年保本銷售量組合有無(wú)數(shù)個(gè)，故為求得符合實(shí)際情況的保本銷售量組合，還要考慮一些約束條件， 比如各年的會(huì)計(jì)利潤(rùn)應(yīng)大于零，各年取得的現(xiàn)金收入應(yīng)能夠償還各年到期的債務(wù)以及支付股利，等等。8.1.2.2 互斥項(xiàng)目的動(dòng)態(tài)盈虧平衡分析在需要對(duì)若干個(gè)方案進(jìn)行比較的情況下，若是某一個(gè)共有的不確定性因素(比如銷售量、產(chǎn)量、壽命、產(chǎn)品價(jià)格、單位變動(dòng)成本等)影響這些方案的取舍，則可以利用盈虧平衡分析幫助決策?！纠?-3某企業(yè)投資生產(chǎn)某種產(chǎn)品，現(xiàn)有兩個(gè)方案，有關(guān)資料如圖8-3所示，該產(chǎn)品的市場(chǎng)壽命具有較大的不確定性，如果基準(zhǔn)收益率為15%,不考慮期末資產(chǎn)殘值，那么，企業(yè)應(yīng)如何根據(jù)項(xiàng)目的壽命期

12、來(lái)選擇方案？圖8-3根據(jù)方案壽命期選擇項(xiàng)目如圖8-3所示，進(jìn)行決策分析的步驟如下：(1)首先在單元格 F3中輸入“ 5”，在單元格H3中輸入“11”，單元格G3可先空置。(2)選取單元格區(qū)域F4:H5,輸入不同壽命期下兩個(gè)方案的凈現(xiàn)值計(jì)算公式“=PV(D4 :D5,F3:H3,-C4:C5)-B4:B5"(數(shù)組公式輸入)。(3)在單元格G6中輸入公式“ =G4-G5”，將單元格G6作為目標(biāo)單元格，將單元格 G3作為可變單元格，利用【規(guī)劃求解】工具或【單變量求解】工具，可以求得兩個(gè)方案凈現(xiàn)值相 等時(shí)的項(xiàng)目壽命期。9.92年，這就是以項(xiàng)目壽命期為由計(jì)算結(jié)果可以看出，兩個(gè)方案凈現(xiàn)值相等時(shí)的

13、壽命期為 共有變量時(shí)方案1與方案2的凈現(xiàn)值無(wú)差異點(diǎn)。因此，當(dāng)壽命期小于 9.92年時(shí)，應(yīng)采用方案1;而當(dāng)壽命期大于 9.92年時(shí)，應(yīng)采用方案 2。8.1.3 投資項(xiàng)目盈虧平衡分析模型除了前面介紹的直接利用公式計(jì)算盈虧平衡點(diǎn)保本銷售量或設(shè)計(jì)工作表格進(jìn)行分析外，我們 還可以設(shè)計(jì)盈虧平衡分析模型來(lái)分析每個(gè)因素各種可能的變動(dòng)情況下對(duì)保本銷售量的影響。【例8-4】投資項(xiàng)目的盈虧平衡分析模型如圖8-4所示，具體設(shè)計(jì)步驟如下:圖8-4投資項(xiàng)目盈虧平衡分析模型(1)首先設(shè)計(jì)好分析模型結(jié)構(gòu)，如圖 8-4所示(表中數(shù)據(jù)以例 8-2為例)。(2)單擊【視圖】，選擇【工具欄】，再單擊【窗體】，出現(xiàn)【窗體】工具欄，如圖

14、 8-5所示,單擊【滾動(dòng)條】按鈕，然后在工作表的合適位置(這里為E4F4單元格)拖曳出一個(gè)矩形【組合框】控件，并調(diào)整其大小。圖8-5【窗體】工具欄(3)將鼠標(biāo)移到新建立的【滾動(dòng)條】控件上，單擊鼠標(biāo)右鍵，出現(xiàn)快捷菜單，選擇【設(shè)置8-6所示。圖8-61設(shè)置控件格式】對(duì)話框控件格式】，出現(xiàn)【設(shè)置控件格式】對(duì)話框，選擇【控制】項(xiàng)，如圖(4)在【當(dāng)前解】欄輸入 25,【最小值】輸入0,【最大值】輸入50,【步長(zhǎng)】輸入1,1頁(yè)步長(zhǎng)】輸入10,在【單元格鏈接】填入“ E4”，然后單擊【確定】按鈕，這就建立了初始投 資的【滾動(dòng)條】控件。(5)其他項(xiàng)目的【滾動(dòng)條】控件可按照上述方法進(jìn)行。(6)在單元格C4:C1

15、0中建立變動(dòng)百分比與【滾動(dòng)條】控件的聯(lián)系，即：在單元格 C4中輸入公式“ =E4/100-25%”，并將單元格 C4分別復(fù)制到單元格 C5:C10中。說(shuō)明：本例中各因素的變動(dòng)范圍為-25%+25% ,而滾動(dòng)條控制按鈕的值的變化范圍為100后50,為了使?jié)L動(dòng)條控制按鈕的變化表示為百分?jǐn)?shù)的變化，這里將控制按鈕的值除以再減去25%,則每次單擊滾動(dòng)條兩端的箭頭，單元格 C4:C10中的變動(dòng)百分比就變化1%,而當(dāng)滾動(dòng)條在中間位置，變動(dòng)百分比恰好為零。(7)選取單元格 D4:D10區(qū)域并輸入變化后數(shù)值的計(jì)算公式“=B4:B10*(1+C4 :C10)”(數(shù)組公式輸入)。(8 ) 在單元格 C15 中輸入靜

16、態(tài)盈虧平衡銷售量計(jì)算公式 “=(SLN(D4,D5,B11)+D6)/(D8-D9)*10000”。(9)在單元格C16中輸入動(dòng)態(tài)盈虧平衡銷售量計(jì)算公式：“=(D6+(D4-PV(D10,B11,-1)*SLN(D4,D5,B11)*B13-D5/(1+D10)AB11)/PV(D10,B11,-1)/(1-B13)/(D8-D9)*10000 ”。這里，當(dāng)項(xiàng)目各年的銷售量、付現(xiàn)成本、產(chǎn)品單價(jià)、單位變動(dòng)成本、折舊(直線法)相等、且考慮固定資產(chǎn)殘值回收時(shí)，動(dòng)態(tài)盈虧平衡銷售量計(jì)算公式為式中，S為期末固定資產(chǎn)殘值。這樣，就建立了投資項(xiàng)目盈虧平衡分析模型。每次單擊滾動(dòng)欄兩端的箭頭或用鼠標(biāo)拖曳滑塊，變動(dòng)

17、百分比就變化 1%,當(dāng)在滾動(dòng)框與滾動(dòng)條之間單擊滾動(dòng)條時(shí)，變動(dòng)百分比就以土10%變化。則通過(guò)不同的因素變化可以了解投資項(xiàng)目盈虧平衡銷售量的變化情況，有利于企業(yè)管理者進(jìn)行決策。第8章Excel在投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析中的應(yīng)用8.2 敏感性分析(1)敏感性分析是投資決策中常用的一種重要的分析方法。它是用來(lái)衡量當(dāng)投資方案中某個(gè)因素發(fā)生了變動(dòng)時(shí)，對(duì)該方案預(yù)期結(jié)果的影響程度。 如果某因素在較小范圍內(nèi)發(fā)生了變動(dòng)就會(huì)影 響原定方案的經(jīng)濟(jì)效果，即表明該因素的敏感性強(qiáng)；如果某因素在較大范圍內(nèi)變動(dòng)時(shí)才會(huì)影 響原定方案的經(jīng)濟(jì)效果，即表明該因素的敏感性弱。通常要作敏感性分析的因素有：(1)投資額，包括固定資產(chǎn)投資和追

18、加的流動(dòng)資產(chǎn)投資。(2)項(xiàng)目壽命期。(3)產(chǎn)品的產(chǎn)銷量。(4)產(chǎn)品價(jià)格。(5)經(jīng)營(yíng)成本，特別是其中的變動(dòng)成本。(6)項(xiàng)目壽命期末的設(shè)備殘值。(7)折現(xiàn)率。在長(zhǎng)期投資決策中，敏感性分析通常用來(lái)研究有關(guān)投資方案的現(xiàn)金凈流量或固定資產(chǎn)壽命發(fā)生變動(dòng)時(shí)，對(duì)該方案的凈現(xiàn)值和內(nèi)部收益率的影響程度。同時(shí)，它也可以用來(lái)研究有關(guān)投資項(xiàng)目的內(nèi)部收益率變動(dòng)時(shí)，對(duì)該方案的現(xiàn)金凈流量或使用年限的影響程度。敏感性分析有助于企業(yè)領(lǐng)導(dǎo)了解在執(zhí)行決策方案時(shí)應(yīng)注意的問(wèn)題，從而可以預(yù)先考慮措施與對(duì)策，避免決策上的失誤。8.2.1一般的敏感性分析方法【例8-5】圖8-7為某一投資方案的有關(guān)資料，所采用的數(shù)據(jù)是根據(jù)對(duì)未來(lái)可能出現(xiàn)的情況預(yù)

19、測(cè)的，未來(lái)的投資額、付現(xiàn)成本和銷售收入都有可能在30%的范圍內(nèi)變動(dòng)。試對(duì)這三個(gè)因素做敏感性分析。企業(yè)采用直線法計(jì)提折舊，基準(zhǔn)收益率為15%。圖8-7不確定性因素對(duì)凈現(xiàn)值的影響一般性的敏感性分析方法和步驟如下：(1)設(shè)計(jì)如圖8-7所示的分析表格。(2)在單元格B10:H10中輸入投資額變動(dòng)對(duì)凈現(xiàn)值的影響計(jì)算公式：“=PV(E3,10,-(C4-C5)*(1-F3)+SLN(B3*(1+B9: H9),D6,10)*F3)+D6/(1+E3)A10-B3*(1+B9: H9)”。(3)在單元格B11:H11中輸入銷售收入變動(dòng)對(duì)凈現(xiàn)值的影響計(jì)算公式：“=PV(E3,10,-(C4*(1+B9 :H9

20、)-C5)*(1-F3)+SLN(B3,D6,10)*F3)+D6/(1+E3)A10-B3”。(4)在單元格B12:H12中輸入付現(xiàn)成本變動(dòng)對(duì)凈現(xiàn)值的影響計(jì)算公式：“=PV(E3,10,-(C4-C5*(1+B9 : H9)*(1-F3)+SLN(B3,D6,10)*F3)+D6/(1+E3)A10-B3”。以上各單元格區(qū)域的公式輸入均為數(shù)組公式輸入，則計(jì)算結(jié)果如圖8-7所示。對(duì)計(jì)算結(jié)果繪制分析圖如圖8-8所示，步驟如下：圖8-8敏感性分析圖(1)選取單元格區(qū)域 A9:H12,單擊工具欄上的【圖表向?qū)А堪粹o，在【圖表向?qū)б?4步驟 之1圖表類型】對(duì)話框中，【圖表類型】選“ XY散點(diǎn)圖”，【子

21、圖表類型】選“平滑線散點(diǎn) 圖”，單擊【下一步】按鈕。(2)在【圖表向?qū)?步驟之2圖表源數(shù)據(jù)】對(duì)話框中，不做任何輸入，單擊【下一步】 按鈕。(3)在【圖表向?qū)?步驟之3一圖表選項(xiàng)】對(duì)話框中，在【圖表標(biāo)題】欄中輸入“敏感性 分析圖”，在【數(shù)值(X)軸】欄中輸入“不確定性因素變動(dòng)幅度”，在【數(shù)值(Y)軸】欄中輸入“凈現(xiàn)值”，單擊【下一步】按鈕。(4)在【圖表向?qū)?步驟之4圖表位置】 對(duì)話框中，不做任何輸入，單擊【確定】按鈕。(5)對(duì)圖表的大小、坐標(biāo)數(shù)值、標(biāo)題等格式進(jìn)行調(diào)整，使圖表賞心悅目，則圖表制作即告完成?？梢?jiàn)，銷售收入對(duì)凈現(xiàn)值的影響最大，付現(xiàn)成本其次，而投資額的影響最小。然后可以利用單變量求解工

22、具求出當(dāng)凈現(xiàn)值為零時(shí)每個(gè)不確定性因素的變動(dòng)數(shù)值，方法是：在 J10 中 輸 入 公 式 “=PV(E3,10,-(C4-C5)*(1-F3)+SLN(B3*(1 + I10),D6,10)*F3)+D6/(1+E3410-B3*(1 + I10)”，并將 J10 作為目標(biāo)單元格，I10 作為可變單元格，即可利用單變量求解工具計(jì)算出凈現(xiàn)值為零時(shí)的投資額最大變動(dòng)率。用同樣的方法可以求出凈現(xiàn)值為零時(shí)的銷售收入和付現(xiàn)成本最大變動(dòng)率?？梢?jiàn)，當(dāng)銷售收入和付現(xiàn)成本不變時(shí)，投資額增加到41.64%以上時(shí)會(huì)使方案變得不應(yīng)被接受；當(dāng)投資額和付現(xiàn)成本不變時(shí)，銷售收入低于預(yù)期值的11.48%以上時(shí)會(huì)使方案變得不應(yīng)被接

23、受；而當(dāng)投資額和銷售收入不變時(shí)，付現(xiàn)成本高于預(yù)期值18.79%以上時(shí)會(huì)使方案變得不應(yīng)被接受。因此，三個(gè)因素 的敏感性由強(qiáng)到弱的排序依次為：銷售收入、付現(xiàn)成本和投資額。8.2.2投資項(xiàng)目?jī)衄F(xiàn)值敏感性分析模型我們也可以參照前面所述的投資項(xiàng)目盈虧平衡分析模型，來(lái)建立投資項(xiàng)目?jī)衄F(xiàn)值敏感性分析模型?！纠?-6】建立投資項(xiàng)目?jī)衄F(xiàn)值敏感性分析模型。如圖 8-9所示，這里最大變化區(qū)間取 ±50% ,在【設(shè)置控件格式】 對(duì)話框的【當(dāng)前解】欄中輸入“50”，【最大值】欄中輸入“100”,【頁(yè)步長(zhǎng)】欄中輸入“ 5”。其他項(xiàng)目的計(jì)算方法同投資項(xiàng)目盈虧平衡分析模型。定義一個(gè)名為“凈現(xiàn)值”的自定義函數(shù)，其語(yǔ)法為

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

25、lalaHlal>土4廿味歌盛13可裳切母.ttnuoo也幅» titaa0i C -0 工 虐$丹&. . -i*日t哼T 打*由除二型V萬(wàn)弋t弄IBH泉它工| 也亶出碇如是由基*畜口1. I，第 產(chǎn)草情la。第g犯況2It I 3- 一圖8-9投資項(xiàng)目?jī)衄F(xiàn)值敏感性分析模型凈現(xiàn)值=凈現(xiàn)金流量* （1 - （1 +基準(zhǔn)收益率）人-壽命期）/基準(zhǔn)收益率-初始投資+期末 殘值/ （1 +基準(zhǔn)收益率）A壽命期End Function在單元格A16中輸入預(yù)計(jì)凈現(xiàn)值計(jì)算公式“=凈現(xiàn)值（B4,B5,B6,B7,B8,B9,B10,B11,$B$12） ”，（步驟為：?jiǎn)螕艄ぞ邫诘摹菊?/p>

26、貼函數(shù)】按鈕，選擇“用戶定義”，選中“凈現(xiàn)值”函數(shù)，出現(xiàn)該函數(shù)對(duì)話框，輸入相應(yīng)的內(nèi)容即可），并復(fù)制到單元格 B16中；在單元格C16中輸入公式“ =B16-A16"，在單元格D16輸入公式“ =C16/A16"。這樣，就得到了多 因素變動(dòng)對(duì)凈現(xiàn)值的綜合影響結(jié)果。設(shè)計(jì)單因素變動(dòng)影響分析表格，如圖 8-9所示，在單元格 B19:B26中輸入公式“ =D4:D11" （數(shù)組公式輸入），在單元格C19:C26中分別粘貼各個(gè)因素單獨(dú)變動(dòng)時(shí)的凈現(xiàn)值計(jì)算函數(shù)如下：?jiǎn)卧?C19:凈現(xiàn)值（C4,B5,B6,B7,B8,B9,B10,B11,B12）單元格 C20:凈現(xiàn)值(B4,C

27、5,B6,B7,B8,B9,B10,B11,B12)單元格 C21:凈現(xiàn)值(B4,B5,C6,B7,B8,B9,B10,B11,B12)單元格 C22:凈現(xiàn)值(B4,B5,B6,C7,B8,B9,B10,B11,B12)單元格 C23:凈現(xiàn)值(B4,B5,B6,B7,C8,B9,B10,B11,B12)單元格 C24:凈現(xiàn)值(B4,B5,B6,B7,B8,C9,B10,B11,B12)單元格 C25:凈現(xiàn)值(B4,B5,B6,B7,B8,B9,C10,B11,B12)單元格 C26:凈現(xiàn)值(B4,B5,B6,B7,B8,B9,B10,C11,B12)在單元格 D19:D26中輸入公式“ =C1

28、9:C26-A16"(數(shù)組公式輸入)，在單元格 E19:E26中輸 入公式“ =D19:D26/A16 "(數(shù)組公式輸入)。則投資項(xiàng)目敏感性分析模型就建立起來(lái)了。單擊各個(gè)影響因素滾動(dòng)條的箭頭，改變其變動(dòng)幅度，就可以很方便地了解各個(gè)因素對(duì)投資項(xiàng)目?jī)衄F(xiàn)值的單獨(dú)影響程度以及綜合影響程度。這樣，通過(guò)單擊滾動(dòng)欄兩端的箭頭或用鼠標(biāo)拖曳滑塊，即可改變各種因素的變動(dòng)率，并分析其對(duì)項(xiàng)目?jī)衄F(xiàn)值的影響。8.2.3投資項(xiàng)目?jī)?nèi)部收益率敏感性分析模型【例8-7】建立投資項(xiàng)目?jī)?nèi)部收益率敏感性分析模型。我們也可以對(duì)投資項(xiàng)目的內(nèi)部收益率的敏感性進(jìn)行分析， 方法與投資項(xiàng)目?jī)衄F(xiàn)值敏感性分析模型是一樣的。但需要注

29、意的是，當(dāng)要分析單因素變動(dòng)對(duì)內(nèi)部收益率的影響時(shí)，內(nèi)部收益率的計(jì)算是一件很麻煩的事，因?yàn)楫?dāng)投資項(xiàng)目壽命期內(nèi)各年的凈現(xiàn)金流量不相等時(shí)，不能使用RATE函數(shù)來(lái)計(jì)算內(nèi)部收益率， 不過(guò)可以通過(guò)自定義內(nèi)部收益率函數(shù)來(lái)解決這個(gè)問(wèn)題。作者研究了一種內(nèi)部收益率的穩(wěn)定迭代計(jì) 算方法，具有穩(wěn)定、快速、收斂性好的優(yōu)點(diǎn)，計(jì)算原理及步驟如下:(1)首先假定一個(gè)內(nèi)部收益率的初始值，并以此內(nèi)部收益率作為貼現(xiàn)率i,計(jì)算項(xiàng)目的凈現(xiàn)值 NPV ;(2)根據(jù)計(jì)算出的凈現(xiàn)值數(shù)據(jù)，利用下面的公式計(jì)算第1次迭代后的內(nèi)部收益率 IRR:式中I一初始投資現(xiàn)值。若相鄰兩次計(jì)算的內(nèi)部收益率相差不大，或計(jì)算出的凈現(xiàn)值接近于零， 則停止計(jì)算，就得到了

30、內(nèi)部收益率的近似值，否則重復(fù)上述迭代步驟。內(nèi)部收益率函數(shù)的過(guò)程代碼如下：Public Function內(nèi)部收益率(初始投資,期末殘值,壽命期,年付現(xiàn)成本,年銷售量,產(chǎn)品價(jià)格，單位變動(dòng)成本，所得稅稅率)凈現(xiàn)金流量=(年銷售量* (產(chǎn)品價(jià)格-單位變動(dòng)成本)/ 10000 -年付現(xiàn)成本)* (1 -所 得稅稅率)+ (初始投資-期末殘值)/壽命期*所得稅稅率x1 = 0.110 jxz =凈現(xiàn)金流量 * (1 - (1 + x1) A -壽命期)/ x1 -初始投資+期末殘值/ (1 + x1) A 壽命期x2 = (1 + x1) * (1 + jxz /初始投資)a (1 / 壽命期)-1If

31、Abs(x2 - x1) <= 0.0000000001 Then內(nèi)部收益率 =x2 Else x1 = x2 : GoTo 10End Function這樣，就可以分析不同因素變動(dòng)對(duì)內(nèi)部收益率的影響。圖 8-10為投資項(xiàng)目?jī)?nèi)部收益率敏感性分析模型。其中單元格B14中的計(jì)算公式為“=內(nèi)部收益率(B4,B5,B6,B7,B8,B9,B10,B11) ”；單元格D14中的計(jì)算公式為 “=內(nèi)部收益率(C4,C5,C6,C7,C8,C9,C10,B11) ”，單元格C17:C23中的計(jì)算公式分別如下:圖8-10投資項(xiàng)目?jī)?nèi)部收益率敏感性分析模型單元格 C17: “ =內(nèi)部收益率(C4,B5,B6,

32、B7,B8,B9,B10,B11)單元格 C18: “ =內(nèi)部收益率(B4,C5,B6,B7,B8,B9,B10,B11)單元格 C19: “ =內(nèi)部收益率(B4,B5,C6,B7,B8,B9,B10,B11)單元格 C20: “ =內(nèi)部收益率(B4,B5,B6,C7,B8,B9,B10,B11)單元格 C21 : “ =內(nèi)部收益率(B4,B5,B6,B7,C8,B9,B10,B11)單元格 C22: “ =內(nèi)部收益率(B4,B5,B6,B7,B8,C9,B10,B11)單元格 C23: “ =內(nèi)部收益率(B4,B5,B6,B7,B8,B9,C10,B11)其他各單元格的計(jì)算公式可參閱例8-6

33、。第8章Excel在投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析中的應(yīng)用8.3 概率分析概率分析是通過(guò)研究各種不確定性因素發(fā)生不同幅度變動(dòng)的概率分布及其對(duì)投資方案經(jīng)濟(jì)效果的影響，對(duì)方案的凈現(xiàn)金流量及其經(jīng)濟(jì)效果指標(biāo)作出某種概率描述，從而對(duì)方案的風(fēng)險(xiǎn)情況作出比較準(zhǔn)確的判斷。在實(shí)際經(jīng)濟(jì)活動(dòng)中，影響投資方案經(jīng)濟(jì)效果的大多數(shù)因素(如投資額、成本、銷售量、產(chǎn)品 價(jià)格、項(xiàng)目壽命期等)都是隨機(jī)變量，我們可以預(yù)測(cè)其未來(lái)可能的取值范圍，估計(jì)各種取值 或值域發(fā)生的概率， 但不能肯定地預(yù)知它們?nèi)∈裁粗?。因此，這就需要對(duì)投資項(xiàng)目進(jìn)行概率分析。假設(shè)投資項(xiàng)目有 m種可能出現(xiàn)的凈現(xiàn)金流量狀態(tài)，各種狀態(tài)所對(duì)應(yīng)的凈現(xiàn)金流量序列為yj,各種狀態(tài)發(fā)生的

34、概率為 Pj (),則在第j種狀態(tài)下，方案的凈現(xiàn)值為式中， 為在第j種狀態(tài)下，第t周期的凈現(xiàn)金流量；n為項(xiàng)目的壽命期。則投資方案的凈現(xiàn)值期望值為而凈現(xiàn)值的方差為標(biāo)準(zhǔn)差為對(duì)于獨(dú)立方案，計(jì)算其凈現(xiàn)值期望值和標(biāo)準(zhǔn)差的大小，可以分析其獲利能力及風(fēng)險(xiǎn)的大小。對(duì)于幾個(gè)互斥方案，可以比較它們的變異系數(shù)的大小，以便衡量其相對(duì)風(fēng)險(xiǎn)的高低，從而作 出決策，變異系數(shù)計(jì)算公式為8.3.1 獨(dú)立項(xiàng)目的概率分析8.3,1.1各年凈現(xiàn)金流量互不相關(guān)情況下的獨(dú)立項(xiàng)目概率分析【例8-8】某企業(yè)的投資方案在其壽命期內(nèi)可能出現(xiàn)5種狀態(tài)的凈現(xiàn)金流量序列及其發(fā)生的概率如圖8-11所示。各年的凈現(xiàn)金流量互不相關(guān)，基準(zhǔn)收益率10%,試對(duì)方

35、案進(jìn)行概率分析。圖8-11投資方案的概率分析如圖8-11所示，選取單元格C8:G8區(qū)域，輸入不同狀態(tài)下凈現(xiàn)值的計(jì)算公式“=PV(10%,10,-C5 :G5)+C6 :G6/(1+10%)A11+C4 : G4” (數(shù)組公式輸入);在單元格 C9 中輸入凈現(xiàn)值期望值計(jì)算公式“ =SUMPRODUCT(C3 :G3,C8:G8)"，在單元格 C10中輸入凈現(xiàn)值方差計(jì)算公式“ =SUMPRODUCT(C3 : G3,(C8:G8-C92) ”，在單元格 C11中輸入凈現(xiàn)值標(biāo)準(zhǔn)差計(jì)算公式“ =SQRT(C10)”，在單元格 C12中輸入變異系數(shù)計(jì)算公式“ =C11/C9"，從而得

36、到該方案的有關(guān)計(jì)算結(jié)果數(shù)據(jù)，如圖8-11所示?？梢?jiàn)，該方案的凈現(xiàn)值期望值大于零，是可行的，但風(fēng)險(xiǎn)也較大。8.3,1.2各年凈現(xiàn)金流量相關(guān)情況下的獨(dú)立項(xiàng)目概率分析一概率樹(shù)分析法【例8-9】某企業(yè)擬投資開(kāi)發(fā)一項(xiàng)專有技術(shù)，其初始投資為 12萬(wàn)元，該項(xiàng)技術(shù)預(yù)計(jì)在3年內(nèi)有效，3年內(nèi)每年為企業(yè)帶來(lái)的現(xiàn)金流量是不確定的，其有關(guān)資料如表8-1所示。該企業(yè)的資本成本為 15%，試對(duì)該投資項(xiàng)目的可行性進(jìn)行評(píng)價(jià)。表8 1投資項(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

37、.440.240.480.2100.390.480.370.490.750.350.47.50.150.32.50.6為便于分析計(jì)算，將表8-1中的數(shù)據(jù)填列在 Excel上，其格式如圖8-12所示，則具體計(jì)算步驟如下：(1 )在單元格G6中輸入第一種現(xiàn)金流序列情況下的凈現(xiàn)值計(jì)算公式“=NPV($F$3,A6,C6,E6)-$B$3 ”，然后將此單元格復(fù)制到單元格G7G19。(2)在單元格 H6:H19中輸入聯(lián)合概率計(jì)算公式“=B6:B19*D6 :D19*F6 : F19"(數(shù)組公式輸(3)在單元格I6中輸入凈現(xiàn)值期望值計(jì)算公式“ =SUMPRODUCT(G6 :G19,H6:H19

38、)”。(4)在單元格 J6中輸入凈現(xiàn)值標(biāo)準(zhǔn)差計(jì)算公式“=SQRT(SUMPRODUCT(H6:H19,(G6 :G19-I6)A2) ”。(5)在單元格J6中輸入凈現(xiàn)值為負(fù)的概率計(jì)算公式“=SUMIF(G6 :G19,"<0",H6:H19)”。可見(jiàn)，該項(xiàng)目的期望凈現(xiàn)值為5.21萬(wàn)元，凈現(xiàn)值為負(fù)的概率很小。只有 0.144,該項(xiàng)目的獲利能力較高而風(fēng)險(xiǎn)不大，故該項(xiàng)目是可行的。圖8-12投資項(xiàng)目概率分析8.3.2 互斥項(xiàng)目的概率分析在此種情況下，可分別計(jì)算各個(gè)項(xiàng)目的凈現(xiàn)值期望值、標(biāo)準(zhǔn)差及變異系數(shù)， 對(duì)它們進(jìn)行綜合分析，以確定最優(yōu)方案?！纠?-10】某企業(yè)有A、B兩個(gè)投資項(xiàng)

39、目，其投資額及未來(lái)各年可能的凈現(xiàn)金流量如圖8-13所示，假設(shè)各年的凈現(xiàn)金流量相互獨(dú)立。那么，企業(yè)應(yīng)選擇哪個(gè)項(xiàng)目？161科珀*-J,E 口聞可珥節(jié)加Ali鼻”.0-urn-U3*0 41Q3柄tn tt萬(wàn)03.DIJOSIU蒯*5m a41仙餐QA44j,r09MQ晚內(nèi)W *T| 11Q 111年aiH4OJ0.1胃G.EtHl U一1?皿若X1B件豳日MB4tT DUT1* W IC*MHb* EDI 重 同UU 費(fèi) 2理3M圖8-13各年現(xiàn)金流獨(dú)立的互斥方案的概率分析如圖8-13所示，分析決策過(guò)程如下：(1)在合并單元格G5G7中輸入項(xiàng)目A第1年的凈現(xiàn)金流量期望值計(jì)算公式“=SUMPRODU

40、CT(C5 :C7,D5:D7) ”，然后將此合并單元格分別復(fù)制到合并單元格G8G10、G11G13、G14G16中，得到項(xiàng)目A各年的凈現(xiàn)金流量期望值。(2)在合并單元格H5H7中輸入項(xiàng)目B第1年的凈現(xiàn)金流量期望值計(jì)算公式“=SUMPRODUCT(E5 :E7,F5:F7)”，然后將此合并單元格分別復(fù)制到合并單元格H8H10、H11H13、H14H16中，得到項(xiàng)目 B各年的凈現(xiàn)金流量期望值。(3)在 合并單 元格I5I7中輸入項(xiàng)目A第1年的凈 現(xiàn)金流量標(biāo)準(zhǔn)差 計(jì)算公 式“=SQRT(SUMPRODUCT(C5 :C7,(D5 :D7-G5)A2) ”，然后將此合并單元格分別復(fù)制到合并單元格I8

41、110、I11113、I14I16中，得到項(xiàng)目 A各年的凈現(xiàn)金流量標(biāo)準(zhǔn)差。(4)在合并單元格J5J7中輸入項(xiàng)目B第1年的凈現(xiàn)金流量標(biāo)準(zhǔn)差計(jì)算公式“=SQRT(SUMPRODUCT(E5 :E7,(F5:F7-H542) ”，然后將此合并單元格分別復(fù)制到合并單元格J8J10、J11J13、J14J16中，得到項(xiàng)目B各年的凈現(xiàn)金流量標(biāo)準(zhǔn)差。(5 )在單元格 C20 中輸入項(xiàng)目 A 的凈現(xiàn)值期望值計(jì)算公式“ =NPV(B17,G5,G8,G11,G14)+G4 ”，在單元格 C21中輸入凈 現(xiàn)值標(biāo) 準(zhǔn)差計(jì) 算公式“=SQRT(SUM(I5 :I16A2/(1+B17)A(2*A5 : A16) ”(

42、數(shù)組公式輸入)，在單元格 C22 中輸入變異系數(shù)公式“ =021/020"這里，整個(gè)項(xiàng)目的凈現(xiàn)值標(biāo)準(zhǔn)差現(xiàn)值的計(jì)算公式為(6)同樣在單元格 D20、D21、D22中分別輸入項(xiàng)目B的凈現(xiàn)值期望值、標(biāo)準(zhǔn)差和變異系數(shù) 計(jì) 算 公 式 “=NPV(B17,H5,H8,H11,H14)+H4”“=SQRT(SUM(J5 :J16A2/(1+B17)A(2*A5 :A16) ” (數(shù)組公式輸入)和 “ =D21/D20”。根據(jù)計(jì)算結(jié)果可見(jiàn)，項(xiàng)目 A風(fēng)險(xiǎn)較低但收益也低，而項(xiàng)目 B風(fēng)險(xiǎn)較高但收益也高。但兩個(gè) 項(xiàng)目的變異系數(shù)相差并不是很大， 在這種情況下，如何作出決策，在很大程度上取決于決策人員對(duì)風(fēng)險(xiǎn)的

43、態(tài)度。 愿意冒風(fēng)險(xiǎn)的決策人員，會(huì)選擇項(xiàng)目B,而對(duì)風(fēng)險(xiǎn)很敏感的決策者可能會(huì)傾向于選擇項(xiàng)目 Ao第8章Excel在投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析中的應(yīng)用8.4 蒙特卡羅模擬前面介紹的概率分析法在各年現(xiàn)金流彼此獨(dú)立、且變量及其可能發(fā)生的情況較少的情況下，進(jìn)行模擬計(jì)算是比較簡(jiǎn)單的。但在變量及其可能發(fā)生的情況較多的情況下，則必須進(jìn)行大量的組合計(jì)算，例如，假設(shè)我們估計(jì)的銷售量、單價(jià)、單位變動(dòng)成本的可能發(fā)生概率次數(shù)分別為5次、4次和4次，則每年就需要計(jì)算 5X 4 X 4=80個(gè)可能組合，假設(shè)項(xiàng)目的壽命為5年,則需要計(jì)算80X 5=400個(gè)可能組合，計(jì)算工作量非常大。利用蒙特卡羅模擬法可以解決這個(gè)問(wèn)題，并且還可以

44、對(duì)項(xiàng)目的風(fēng)險(xiǎn)進(jìn)行更為深入的分析。蒙特卡羅模擬法，是根據(jù)隨機(jī)數(shù)對(duì)影響因素的概率分布進(jìn)行隨機(jī)抽樣，根據(jù)每次抽樣值來(lái)計(jì)算項(xiàng)目的凈現(xiàn)金流量、 凈現(xiàn)值及內(nèi)部收益率等指標(biāo)。在利用蒙特卡羅模擬法對(duì)投資項(xiàng)目進(jìn)行分析計(jì)算時(shí)，主要利用兩個(gè)函數(shù)：RANDBETWEEN 函數(shù)和 VLOOKUP 函數(shù)，利用RANDBETWEEN 函數(shù)產(chǎn)生隨機(jī)數(shù)，然后利用 VLOOKUP函數(shù)來(lái)查找對(duì)應(yīng)隨機(jī)數(shù)的變量數(shù) 值。當(dāng)進(jìn)行足夠的模擬計(jì)算次數(shù)后，即可得到投資項(xiàng)目?jī)衄F(xiàn)值的期望值、標(biāo)準(zhǔn)差及概率分布。8.4.1 獨(dú)立項(xiàng)目的蒙特卡羅模擬【例8-11】某企業(yè)計(jì)劃投資一項(xiàng)目，初始投資20000元，項(xiàng)目壽命 3年，期末無(wú)殘值。經(jīng)統(tǒng)計(jì)分析得出產(chǎn)品銷售

45、量、單價(jià)、單位變動(dòng)成本和付現(xiàn)固定成本在各年可能達(dá)到的水平及有 關(guān)的概率情況如圖 8-14所示。則利用蒙特卡羅模擬法進(jìn)行投資項(xiàng)目不確定性分析的方法和 步驟如下：*G1!4HJJ痔i 1用定*耳gUKUHM1QS7.UHc n0vm6 1*40ao*DMwoeG甌as的i» EIBSOODa*提t(yī)ie »研014TEUD山T5WP.TSXl Pa拒e nQ00電EIT2tV E£al£J« n咕210婚JT加&瑪n制e前詞鳴修i北押T2mAO.1ttilm6旭電le14A14* ll*“等必O 24ITe 35.聞X5ft ITt5LS1聚

46、 季OK拈修 at蝸lbV *我14ITTITD gf)IT審IP而一舞0TWq0口 N立Tose小什3ffl 3西HOG M13領(lǐng)0 JB21MODQM曜0制uoo« »n皿DJI帽3000Q24Hs amBK9eiMM?IH陽(yáng)圖8-14投資項(xiàng)目的有關(guān)資料及整理(1)首先設(shè)計(jì)模擬計(jì)算表格，如圖 8-15所示。圖8-15只列出了對(duì)第1年的3次模擬計(jì)算過(guò)程，其他各年的模擬計(jì)算過(guò)程及表格排列順序與第1年相同，其中第2年的模擬計(jì)算單元格在第J R歹U,第3年的模擬計(jì)算單元格在第 S AA歹U。下面僅就第1年的模擬計(jì)算過(guò) 程進(jìn)行介紹。圖8-15第1年的前3次模擬計(jì)算結(jié)果(2)在A2

47、5: A5024單元格車入第1年銷售量的隨機(jī)數(shù)計(jì)算公式：“ =RANDBETWEEN(0,99)'(第25行輸入后，再將其復(fù)制到 A26A5024單元格中)，然后在B25:B5024單元格輸入對(duì)應(yīng)的銷售量公式：“=VLOOKUP(A25 :A5024c3 :D6,2) ”(數(shù)組公式輸入，共運(yùn)算 5000行, 即進(jìn)行5000次模擬計(jì)算)。說(shuō)明：當(dāng)利用VLOOKUP函數(shù)尋找符合某一概率的銷售量時(shí)，必須先對(duì)概率及銷售量進(jìn)行 分區(qū)。以第1年為例，隨機(jī)數(shù)為0、22、56和86時(shí)對(duì)應(yīng)的銷售量分別為 4000、5000、6000、 7000件，其意義就是當(dāng)產(chǎn)生的隨機(jī)數(shù)分別為021、2255、5685

48、和8699時(shí)，對(duì)應(yīng)的銷售量分別為 4000、5000、6000、7000件,而累計(jì)概率分別為 0.22、0.56、0.86和1.00。(3)在C25:C5024單元格輸入第1年單價(jià)的隨機(jī)數(shù)計(jì)算公式： “ =RANDBETWEEN(0,99) ”, 然后在D25:D5024單元格輸入對(duì)應(yīng)的單價(jià)公式： " =VLOOKUP(C25 :C5024, C7:D11,2)”(數(shù) 組公式輸入)。(4)在E25:E5024單元格輸入第1年單位變動(dòng)成本的隨機(jī)數(shù)計(jì)算公式： “=RANDBETWEEN(0,99) ”，然后在 F25:F5024單元格輸入對(duì)應(yīng)的單位變動(dòng)成本公式： “=VLOOKUP(E2

49、5 :E5024,C12:D15,2) ”(數(shù)組公式輸入)。(5 )在 G25:G5024單元格輸入第1年付現(xiàn)成本的隨機(jī)數(shù)計(jì)算公式： “=RANDBETWEEN(0,99) ”，然后在 H25:H5024單元格輸入對(duì)應(yīng)的付現(xiàn)固定成本公式： “=VLOOKUP(G25 :G5024,C16:D19,2) ”(數(shù)組公式輸入)。(6)在I25:I5024單元格輸入第1年凈現(xiàn)金流量計(jì)算公式：“=(B25:B5024*(D25:D5024-F25:F5024)-H25:H5024)*(1-F20)+B20/3*F20 "(數(shù)組公式輸入)，這里采用直線 法計(jì)提折舊，年折舊=$B$20/3。第2年

50、、第3年的凈現(xiàn)金流量分別放在單元格 R25:R5024和單元格AA25 :AA5024中，而凈現(xiàn)值則存放在 AB25 :AB5024 中， 其計(jì)算公式為：“=I25/(1+$D$20)+R25/(1+$D$2042+AA25/(1+$D$20)A3-$B$20”(可先輸入 AB25 單元格，然后再?gòu)?fù)制到 AB26AB5024單元格中。(7)最后的模擬計(jì)算結(jié)果存放在單元格K1:M7中，如圖8-16所示，各單元格的計(jì)算公式為：?jiǎn)卧?M2 "=AVERAGE(AB25 :AB5024)”，單元格 M3 "=STDEV(AB25 :AB5024)”，單元格 M4 "=M

51、AX(AB25 :AB5024) ”，單元格 M5 “ =MIN(AB25 :AB5024) ”，單元格 M6“=M3/M2 "，單元格 M7 "=COUNTIF(AB25 :AB5024,”<0")/COUNT(AB25 :AB5024) ”。sees 喀ge An o m圖8-16模擬計(jì)算結(jié)果在模擬計(jì)算完畢后，還可以利用Excel的繪圖工具繪制凈現(xiàn)值概率分布圖，具體步驟如下：(1)首先對(duì)凈現(xiàn)值進(jìn)行分組，以便于進(jìn)行統(tǒng)計(jì)分析，分組結(jié)果存放在單元格O4:O17中。(2)設(shè)計(jì)圖表上凈現(xiàn)值的分區(qū)，存放在單元格P4:P17中。(3)再選取單元格區(qū)域 Q4:Q16,輸

52、入FREQUENCY函數(shù)："=FREQUENCY(AB25 : AB5024,O4:O16)巧000”(數(shù)組公式輸入)，即可得到凈現(xiàn)值概率分布結(jié)果，如8-17所示。然后插入直方圖，做法是：選擇單元格區(qū)域P4:Q17,在圖表向?qū)е羞x柱形圖，再依據(jù)有關(guān)說(shuō)明輸入有關(guān)資料。凈現(xiàn)值概率分布圖如圖8-18所示。油皿UXED -JUT0 otna «皿 t CM號(hào)卻 TBqrtLIlruLIHLiLr"k4MA=«-it"idem"STK oW»Erobm7HW0 DTK » IK4 0 EL-H心 « QMQ

53、71; UH 加知圖8-17凈現(xiàn)值概率分布統(tǒng)計(jì)圖8-18凈現(xiàn)值概率分布圖根據(jù)計(jì)算結(jié)果及凈現(xiàn)值概率分布圖， 就可以對(duì)投資項(xiàng)目的不確定性進(jìn)行分析。由結(jié)果可以看出，該項(xiàng)目的凈現(xiàn)值期望值為 10642元，標(biāo)準(zhǔn)差8823元，凈現(xiàn)值為負(fù)的概率為 0.112,故該項(xiàng)目的投資風(fēng)險(xiǎn)還是比較小的。該項(xiàng)目的凈現(xiàn)值主要集中在0 20000元，概率為0.74,其中在5000 15000元的概率為0.42;而凈現(xiàn)值為負(fù)時(shí)主要集中在 -5000 0之間，概率為0.08。 由圖8-18還可以看出，該項(xiàng)目的凈現(xiàn)值大致符合正態(tài)分布。8.4.2 互斥項(xiàng)目的蒙特卡羅模擬在對(duì)互斥方案進(jìn)行比較時(shí)， 可對(duì)每個(gè)方案進(jìn)行蒙特卡羅模擬，計(jì)算出其凈現(xiàn)值及其概率分布，并繪制概率分布圖，進(jìn)行綜合比較分析，選擇最優(yōu)方案。【例8-12】以例8-7的有關(guān)資料為例，其模擬準(zhǔn)備、模擬計(jì)算過(guò)程、模擬計(jì)算結(jié)果如圖8-19 所示。共進(jìn)行6000次模擬計(jì)算，即從第 21行到6020行。表中僅列舉了三次模擬，即第 21 到23行，其中，隨機(jī)數(shù)計(jì)算公式為“=RANDBETWEEN (1,100)”，項(xiàng)目A第