Excel在投資工程不確定性風(fēng)險分析中的應(yīng)用

8.1盈虧平衡分析（1）

盈虧平衡分析的原理就是根據(jù)量本利之間的關(guān)系，計算工程的盈虧平衡點的銷售量，從而分析工程對市場需求變

化的適應(yīng)能力。一般來說，盈虧平衡點是指企業(yè)既不虧又不盈或營業(yè)利潤為零時的銷售量。根據(jù)是否考慮資金的

時間價值，盈虧平衡分析又可分為靜態(tài)盈虧平衡分析和動態(tài)盈虧平衡分析。

8.1.1靜態(tài)盈虧平衡分析

靜態(tài)盈虧平衡分析是在不考慮資金的時間價值情況下，對投資工程的盈虧平衡進行分析。當某年的營業(yè)利潤為零

時，可以得到該年盈虧平衡點的銷售量為（這里假設(shè)只有一種產(chǎn)品）：

式中，2為第t年的盈虧平衡點銷售量（又稱保本銷售量）；尤為第1年的固定本錢，這里假設(shè)非付現(xiàn)固定本錢

只有折舊，即凡=〃+?,〃為第￡年的折舊；月為付現(xiàn)固定本錢；夕為產(chǎn)品單價；/為產(chǎn)品的單位變動本錢，

并假設(shè)各年的付現(xiàn)固定本錢、產(chǎn)品單價和產(chǎn)品的單位變動本錢均不變。

當產(chǎn)銷量低于盈虧平衡點銷售量時，投資_L程處于虧損狀態(tài)，反之，當產(chǎn)銷量超過盈虧平衡點銷售量時，JL程就

有了盈利。當企業(yè)在盈虧平衡點附近經(jīng)營，即銷售量接近于2?時，投資工程的經(jīng)營風(fēng)險很大，或經(jīng)營上的平安

程度很低，銷售量微小的下降都可能使企業(yè)發(fā)生虧損。

單一產(chǎn)品的盈虧平衡分析比較簡單。根據(jù)給定的各年的付現(xiàn)固定本錢、折舊、產(chǎn)品單價和單位變動本錢，即可由

上述公式計算出各年的靜態(tài)保本銷售量。

當一個投資工程同時生產(chǎn)多種不同的產(chǎn)品，或?qū)?個生產(chǎn)多種產(chǎn)品的整個企業(yè)進行盈虧平衡分析時，則需要考慮

多品種產(chǎn)品的情況。在進行多品種盈虧平衡分析時，加權(quán)平均法是較常用的一種方法。

【例8-1］某企業(yè)生產(chǎn)A、B、C三種產(chǎn)品，A產(chǎn)品年銷售量100000件，單價10元/件，單位變動本錢8.5

元/件；B產(chǎn)品年銷售量25000臺，單價20元/臺，單位變動本錢16元/臺；C產(chǎn)品年銷售量10C00套，單價50

元/套，單位變動本錢25元/套；全，一固定本錢300000元。根據(jù)以上資料?，可以建立分析表格如圖8-1所示。

有關(guān)計算分析公式如下:

銷售收入=銷售量X單價

邊際奉獻=銷售量X（單價一單位變動本錢）

邊際奉獻率二邊際奉獻?銷售收入

銷售匕重=某產(chǎn)品銷售收入+全廠各產(chǎn)品銷售收入合計

全廠綜合邊際奉獻率=￡某產(chǎn)品邊際奉獻率x該產(chǎn)品銷售比重

全廠綜合保本額;全廠固定本錢?全廠綜合邊際奉獻率

某產(chǎn)品保本額=全廠綜合保本額x該產(chǎn)品銷售比重

某產(chǎn)品保本量;某產(chǎn)品保本額+該產(chǎn)品單價

圖8T某企業(yè)的多品種盈虧平衡分析

輸入數(shù)據(jù)及定義完公式后，即可馬上計算出各個可變單元格的數(shù)值來，即全廠綜合保本額元，產(chǎn)品A、B、C的保

本額分別為600000元、300000元和300000元,保本量分別為60000件、15000臺和6000套。

各單元格的計算公式為

單元格E3:E5:“=B3元5*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：rt=B8:B10*Dll"（數(shù)組公式輸入）。

單元格E8:E10:“=D8:D10/C3:C5"（數(shù)組公式輸入）。

單元格DU：“=H6/G6"。

圖8-1建立了各產(chǎn)品的單價、單位變動本錢和固定本錢與保本額或保本量之間的關(guān)系，利用圖8T就可分析它們

對盈虧平衡點的影響。

8.1.2動態(tài)盈虧平衡分析

8.1.2.1獨立工程的動態(tài)盈虧平衡分析

靜態(tài)盈虧平衡分析沒有考慮資金的時間價值、所得稅、利率，以及通貨膨脹等因素的影響，由此計算出的盈虧平

衡點銷售量僅僅能使工程的當期到達盈虧平衡，卻并不能保證工程的凈現(xiàn)值恰好為零。在考慮資金的時間價值和

所得稅等因素的條件下，工程的動態(tài)盈虧平衡點就是工程凈現(xiàn)值為零的那一點，即動態(tài)保本銷售量就是使工程凈

現(xiàn)值為零的銷售量?？紤]單一產(chǎn)品的情況，令NPV=O,則可得到工程各年的動態(tài)保本銷售量的計算公式為

式中，T為所得稅稅率；Q.為各年的保本銷售量；R、匕和凡分別為各年的產(chǎn)品單價、單位變動本錢、付現(xiàn)固定

本錢；，為工程的基準收益率；I為初始投資（并假設(shè)在第0年一次性投入工程）。

這樣，利用上述公式，即可分析各種情況下工程各年保本銷售量的變化情況。

【例8-2】某企業(yè)準備投資生產(chǎn)一種新產(chǎn)品，工程總投資350萬元，工程壽命期5年，期末無殘值，采用直線法

計提折舊。經(jīng)預(yù)測，工程投產(chǎn)后每年可銷售產(chǎn)品85000臺，產(chǎn)品單價40元/臺，單位變動本錢20元/臺，年付現(xiàn)

固定本錢50萬元，企業(yè)的基準收益率為10%,所得稅稅率33%。

此時，各年的折舊額相同，假設(shè)各年的銷售量、產(chǎn)品單價、單位變動本錢、付現(xiàn)固定本錢以及折舊均相同，可以

利用上述公式直接導(dǎo)出動態(tài)盈虧平衡銷售量(保本銷售量)的計算公式如下：

根據(jù)此公式計算出保本銷售量為76664件，如圖8-2所示，單元格E4中的保本銷售量計算公式為:

“=(E3+(B3/PV(F3,B3,-1)-G3)*A3/B3/(1-G3))/(C3-D3)*

10000"o

ABIIFGX

1

產(chǎn)a■?！鲑装茬霤MSUBM基ZMttlQM

23元)《兄州&元的3萬啟&?皿na

3■，見__3__20一501011310

EJBLEUZLa

5

601234S，?現(xiàn)值

7-350MOS9WS980SOT2240

8

9^K*《府》61671

10

IIa付

12無)0293?U22MUI

1320000967"80670T51S5TOKI65562

K<000093192TWTM36X9，

1S60000TTM2TM20?<T4$ncs

16TOOOO7TB<aH135738987S36O

ITICCOOOEISEl3T72S5W50$122?

圖8-2投資工程的動態(tài)盈虧平衡分析

但實際上，各年的俏售量受到企業(yè)內(nèi)部及市場條件的影響，它們是不同的，故可以利用規(guī)劃求解工具來計算各年

的保本銷售量，步驟如下：

(1)如圖8-2所示，將單元格C10:G10作為可變單元格，存放各年的保本銷售量。

(2)在單元格B7中輸入公式“二-A3”,在單元格C7:G7中輸入凈現(xiàn)金流量計算公式

“二(C9:G9/10000*(C3-D3)-E3)*(1-G3)+SLN(A3,H3,B3)*G3”(數(shù)組公式輸入)。

(3)在單元格元中輸入凈現(xiàn)值計算公式“=NPV(F3,C7:G7)+B7"c

(4)單擊工具菜單中的【規(guī)劃求解】項，出現(xiàn)【規(guī)劃求解參數(shù)】對話框，其中【設(shè)置目標單元格】選“$H$7〃，

【等于】選“0",【可變單元格】選“$C$9:$G$9"；單擊【求解】按鈕，即得各年的保本銷售量，如圖8-2所

刁So

當可變單元格C9:G9中賦予不同的初始值時，會得到不同的保本銷售量,如圖8-2所示)，各年的銷售量變化范圍

很大，這正反映了動態(tài)盈虧平衡的特點。實際上，各年保本銷售量組合有無數(shù)個，故為求得符合實際情況的保本

銷售量組合，還要考慮?些約束條件，比方各年的會計利潤應(yīng)大于零，各年取得的現(xiàn)金收入應(yīng)能夠歸還各年到期

的債務(wù)以及支付股利，等等。

8.1.2.2互斥工程的動態(tài)盈虧平衡分析

在需要對若干個方案進行比較的情況下，若是某一個共有的不確定性因素(比方銷售量、產(chǎn)量、聲命、產(chǎn)品價格、

單位變動本錢等)影響這些方案的取舍，則可以利用盈虧平衡分析幫助決策。

【例8-3］某企業(yè)投資生產(chǎn)某種產(chǎn)品，現(xiàn)有兩個方案，有關(guān)資料如圖8-3所示，該產(chǎn)品的市場壽命具有較大的不

確定性，如果基準收益率為15%,不考慮期末資產(chǎn)殘值，那么，企業(yè)應(yīng)如何根據(jù)工程的壽命期來選擇方案？

A1c>■,GII

i方泉基*皆■小X壽命方置分&?R：方成

QWMR貨

j

9《萬元》fiV3IS

4、川NsonxSO00Mer

5力Etso<0151-15包5000

?$?a-<?

圖8-3根據(jù)方案壽命期選擇工程

如圖8-3所示，進行決策分析的步驟如下:

(1)首先在單元格F3中輸入“5”,在單元格H3中輸入“11”,單元格G3可先空置。

(2)選取單元格區(qū)域F4:II5,輸入不同壽命期下兩個方案的凈現(xiàn)值計算公式“=PV(D4:D5,F3:I13,-C4:C5)-B4:B5W

(數(shù)組公式輸入)。

(3)在單元格G6中輸入公式“二G4-G5",將單元格G6作為目標單元格，將單元格G3作為可變單元格，利用【規(guī)

劃求解】工具或【單變量求解】工具，可以求得兩個方案凈現(xiàn)值相等時的工程壽命期。

由計算結(jié)果可以看出，兩個方案凈現(xiàn)值相等時的壽命期為9.92年，這就是以工程壽命期為共有變量時方案1與

方案2的凈現(xiàn)值無差異點。因此，當壽命期小于9.92年時，應(yīng)采用方案1：而當壽命期大于9.92年時，應(yīng)采用

方案2。

8.1.3投資工程盈虧平衡分析模型

除了前面介紹的直接利用公式計算盈虧平衡點保本銷售量或設(shè)計工作表格進行分析外，我們還可以設(shè)計盈虧平衡

分析模型來分析每個因素各種可能的變動情況下對保本銷售量的影響。

【例8-4】投資工程的盈虧平衡分析模型如圖87所示，具體設(shè)計步驟如卜.:

ABfDEF

1ft分*ittf

2

3喜目變動百分比支化后方值支化百分比攜g手動》

4?怪投無）3900OOI3S00CH

5騙比805元）01160oc?J>

6SO500t??■±

7斗電■逢僅）?ooo0001950000C.rT

8產(chǎn)皿1介附?左）S/00157W?i?

9?eooi212C?i,

101*^2*CMm-iocm9001?i?

11s

12第麗I

1333%

14

159ti146?5

16

圖8-4投資工程盈虧平衡分析模型

（1）首先設(shè)計好分析模型結(jié)構(gòu)，如圖8-4所示（表中數(shù)據(jù)以例8-2為例）。

（2）單擊【視圖】，選擇【工具欄工再單擊【窗體】，出現(xiàn)【窗體】工具欄，如圖8-5所示，單擊【滾動條】按

鈕，然后在工作表的適宜位置（這里為E4?F4單元格）拖曳出一個矩形【組合框】控件，并調(diào)整其大小。

AA和

口」

PC

港動姿

圖8-5【窗體】工具欄

（3）將鼠標移到新建立的【滾動條】控件上，單擊鼠標右鍵，出現(xiàn)快捷菜單，選擇【設(shè)置控件格式】，出現(xiàn)【設(shè)

置控件格式】對話框，選擇【控制】項，如圖8-6所示。

1sI―I

圖8-6【設(shè)置控件格式】對話框

(4)在【當前解】欄輸入25,【最小值】輸入0,【最大值】輸入50,【步長】輸入1,【頁步長】輸入10,在【單

元格鏈接】填入“E4〃，然后單擊【確定】按鈕，這就建立了初始投資的【滾動條】控件。

(5)其他工程的【滾動條】控件可按照上述方法進行。

(6)在單元格C4:C10中建立變動百分比與【滾動條】控件的聯(lián)系，即：在單元格C4中輸入公式“=E4/100-25曠，

并將單元格C4分別更制到單元格C5:C10中。

說明：本例中各因素的變動范圍為-25%?+25樂而滾動條控制按鈕的值的變化范圍為()?50,為了使?jié)L動條控

制按鈕的變化表示為百分數(shù)的變化，這里將控制按鈕的值除以100后再減去25%,則每次單擊滾動條兩端的箭

頭，單元格C4:C10中的變動百分比就變化1%,而當滾動條在中間位置，變動百分比恰好為零。

(7)選取單元格D4:D10區(qū)域并輸入變化后數(shù)值的計算公式JB4:B10*(l+C4：C10)"(數(shù)組公式輸入)。

(8)在單元格C15中輸入靜態(tài)盈虧平衡銷售量計算公式tt=(SLN(D4,D5,Bl1)+D6)/(D8-D9)*10000".

(9)在單元格C16中輸入動態(tài)盈虧平衡銷售量計算公式：

“=(E6+(I)4-PV(D10,Bl1,-1)*SLN(D4,1)5,B11)*B13-D5/(1+1)10)B11)/PV(I)10,B11,-1)/(I-B13))/(D8-D9)*1000

0〃°

這里，當工程各年的銷售量、付現(xiàn)本錢、產(chǎn)品單價、單位變動本錢、折舊(直線法)相等、且考慮固定資產(chǎn)殘值

回收時，動態(tài)盈虧平衡銷售量計算公式為

式中，S為期末固定資產(chǎn)殘值。

這樣，就建立了投資工程盈虧平衡分析模型。每次單擊滾動欄兩端的箭頭或用鼠標拖曳滑塊，變動百分比就變化

魏，當在滾動框與滾動條之間單擊滾動條時，變動百分比就以±10%變化。則通過不同的因素變化可以了解投資

工程盈虧平衡銷售量的變化情況，有利于企業(yè)管理者進行決策。

第8章Excel在投資工程不確定性風(fēng)險分析中的應(yīng)用

8.2敏感性分析(1)

敏感性分析是投資決策中常用的一種重要的分析方法。它是用來衡量當投資方案中某個因素發(fā)生了變動時，對該

方案預(yù)期結(jié)果的影響程度。如果某因素在較小范圍內(nèi)發(fā)生了變動就會影響原定方案的經(jīng)濟效果，即說明該因素的

敏感性強：如果某因素在較大范圍內(nèi)變動時才會影響原定方案的經(jīng)濟效果，即說明該因素的敏感性弱。

通常要作敏感性分析的因素有：

(1)投資額，包括固定資產(chǎn)投資和追加的流動資產(chǎn)投資。

(2)工程壽命期。

(3)產(chǎn)品的產(chǎn)銷量。

(4)產(chǎn)品價格。

(5)經(jīng)營本錢，特別是其中的變動本錢。

(6)工程壽命期末的設(shè)備殘值。

(7)折現(xiàn)率。

在長期投資決策中，敏感性分析通常用來研究有關(guān)投資方案的現(xiàn)金凈流量或固定資產(chǎn)壽命發(fā)生變動時，對該方案

的凈現(xiàn)值和內(nèi)部收益率的影響程度。同時，它也可以用來研究有關(guān)投資工程的內(nèi)部收益率變動時，對該方案的現(xiàn)

金凈流量或使用年限的影響程度。敏感性分析有助于企業(yè)領(lǐng)導(dǎo)了解在執(zhí)行決策方案時應(yīng)注意的問題，從而可以預(yù)

先考慮措施與對策，防止決策上的失誤。

8.2.1一般的敏感性分析方法

【例8-5】圖8-7為某一投資方案的有關(guān)資料，所采用的數(shù)據(jù)是根據(jù)對?未來可能出現(xiàn)的情況預(yù)測的，未來的投資

額、付現(xiàn)本錢和銷售收入都有可能在30%的范圍內(nèi)變動。試對這三個因素做敏感性分析。企業(yè)采用直線法計提

折舊，基準收益率為15%。

圖8-7不確定性因素對凈現(xiàn)值的影響

一般性的敏感性分析方法和步驟如下：

(1)設(shè)計如圖8-7所示的分析表格。

(2)在單元格B10:I110中輸入投資額變動對凈現(xiàn)值的影響計算公式：

tt=PV(E3,10,-((C4-C5)*(1-F3)+SLN(B3*(1+B9:H9),D6,10)*F3))+D6/(1+E3)"10-B3*(1+B9:H9)*。

(3)在單元格中輸入銷售收入變動對凈現(xiàn)值的影響計算公式：

“=PV(E3,10,-((C4*(1+B9:H9)-C5)*(1-F3)+SLN(B3,D6,10)*F3))+D6/(1+E3)*10-B3"o

(4)在單元格B12:H12中輸入付現(xiàn)本錢變動對凈現(xiàn)值的影響計算公式：

JPV(E3,10,-((C4-C5*(1+B9:H9))*(1-F3)+SLN(B3,D6,10)*F3))+D6/(1+E3)-10-B3"。

以上各單元格區(qū)域的公式輸入均為數(shù)組公式輸入，則計算結(jié)果如圖8-7所示。

對計算結(jié)果繪制分析圖如圖8-8所示，步驟如下:

圖8-8敏感性分析圖

(1)選取單元格區(qū)域A9:H12,單擊工具欄上的【圖表向?qū)А堪粹o，在【圖表向?qū)?4步驟之1—圖表類型】對

話框中，【圖表類型】選“XY散點圖”，【子圖表類型】選“平滑線散點圖〃，單擊【下一步】按鈕。

(2)在【圖表向?qū)?4步驟之2—圖表源數(shù)據(jù)】對話框中，不做任何輸入，單擊【下一步】按鈕。

(3)在【圖表向?qū)?4步驟之3—圖表選項】對話框中，在【圖表標題】欄中輸入“敏感性分析圖"，在【數(shù)值

(X)軸】欄中輸入“不確定性因素變動幅度"，在【數(shù)值(Y)軸】欄中輸入“凈現(xiàn)值〃，單擊【下一步】按鈕。

(4)在【圖表向?qū)?4步驟之4一圖表位置】對話框中，不做任何輸入，單擊【確定】按鈕。

(5)對圖表的大小、坐標數(shù)值、標題等格式進行調(diào)整，使圖表賞心悅目，則圖表制作即告完成。

可見，銷售收入對凈現(xiàn)值的影響最大，付現(xiàn)本錢其次，而投資額佗影響最小。

然后可以利用單變量求解工具求出當浮現(xiàn)值為零時每個小確定性因素的變動數(shù)值，方法是：在J10中輸入公式

M=PV(E3,10,-((C4-C5)*(1-F3)+SLN(B3*(1+110),D6,10)*F3))+D5/(1+E3)"10-B3*(1+110)",并將J10作為目

標單元格，110作為可變單元格，即可利用單變量求解工具計算出凈現(xiàn)值為零時的投資額最大變動率。用同樣的

方法可以求出凈現(xiàn)值為零時的銷售收入和付現(xiàn)本錢最大變動率?？梢?，當銷售收入和付現(xiàn)本錢不變時：投資額增

加到41.64%以上時會使方案變得不應(yīng)被接受；當投資額和付現(xiàn)本錢不變時，銷售收入低于預(yù)期值的11.48%以上

時會使方案變得不應(yīng)被接受；而當投資額和銷售收入不變時，付現(xiàn)本錢高于預(yù)期值18.79%以上時會使方案變得

不應(yīng)被接受。因此，三個因素的敏感性由強到弱的排序依次為：錯售收入、付現(xiàn)本錢和投資額。

8.2.2投資工程凈現(xiàn)值敏感性分析模型

我們也可以參照前面所述的投資工程盈虧平衡分析模型，來建立投資工程凈現(xiàn)值敏感性分析模型。

【例8-6】建立投資工程凈現(xiàn)值敏感性分析模型。如圖8-9所示，這里最大變化區(qū)間取±5(%,在【設(shè)置控

件格式】對話框的【當前解】欄中輸入“50”,【最大值】欄中輸入“100”,【頁步長】欄中輸入“5"。其他工

程的計算方法同投資工程盈虧平衡分析模型。

定義一個名為“凈現(xiàn)值”的自定義函數(shù)，其語法為：凈現(xiàn)值（初始投資，期末殘值，壽命期，年付現(xiàn)固定本錢，年

銷售量，產(chǎn)品價格，單位變動本錢，基準收益率，所得稅稅率）。自定義函數(shù)可以通過一小段程序?qū)ζ鋮?shù)及參

數(shù)之間的關(guān)系進行描述，這種程序又稱過程代碼?！皟衄F(xiàn)值”自定義函數(shù)的過程代碼如下：

PublicFunction凈現(xiàn)值（初始投資，期末殘值，壽命期，年可現(xiàn)固定本錢，年銷售量，產(chǎn)品價格，單位變

動本錢，基準收益率，所得稅稅率）

凈現(xiàn)金流量=（年銷售量*（產(chǎn)品價格-單位變動本錢）/10000-年付現(xiàn)固定本錢）*（1-所得稅稅率）

+（初始投資-期末殘值）/壽命期*所得稅稅率

中曲期H心

(0000tt?50

：ottow

眼力a

附上?雄川”鎮(zhèn)。南?個=-?

的“R值臭給￡“城Q?????*女的3

防油.麗甌物技產(chǎn)

9BEJWE分比爻艙口冷底工

3001?21-1?

MM004

-II05

產(chǎn)B?9

■awu?5001€4321M8101

■ms惠$StW?"4%

圖8-9投資工程凈現(xiàn)值敏感性分析模型

凈現(xiàn)值=凈現(xiàn)金流量*（1-（1+基準收益率）--壽命期）/基準收益率-初始投資+期末殘值/（1+基

準收益率）,壽命期

EndFunction

在單元格A16中輸入預(yù)計凈現(xiàn)值計算公式“=凈現(xiàn)值（B4,B5,B6,B7,B8,B9,

,（步驟為：單擊工具欄的【粘貼函數(shù)】按鈕，選擇“用戶定義”，選中"凈現(xiàn)值”函數(shù)，出

現(xiàn)該函數(shù)對話框，輸入相應(yīng)的內(nèi)容即可），并復(fù)制到單兀格B16中；在單兀格C16中輸入公式“=B16-A16",在

單元格D16輸入公式“=C16/A16"。這樣，就得到了多因素變動對凈現(xiàn)值的綜合影響結(jié)果。

設(shè)計單因素變動影響分析表格，如圖8-9所示，在單元格B19:B26中輸入公式“二D4:D11"(數(shù)組公式輸入)，在

單元格C19:C26中分別粘貼各個因素單獨變動時的凈現(xiàn)值計算函數(shù)如下：

單元格C19：凈現(xiàn)值(C4,B5,B6,B7,B8,B9,B1O,B11,B12)

單元格C20：凈現(xiàn)值(B4,C5,B6,B7,B8,B9,B1O,B11,B12)

單兀格C21：凈現(xiàn)值(B4,B5,C6,B7,B8,B9,BIO,

單元格C22：凈現(xiàn)值(B4,B5,B6,C7,B8,B9,B10,BU,B12)

單元格C23：凈現(xiàn)值(B4,B5,B6,B7,C8,B9,B12)

單元格C24：凈現(xiàn)值(B4,B5,B6,B7,B8,C9,B1O,B11,B12)

單元格C25：凈現(xiàn)值(B4,B5,B6,B7,B8,B9,C1O,B11,B12)

單元格C26：凈現(xiàn)值(B4,B5,B6,B7,B8,B9,BIO,C11,B12)

在單元格D19:D26中輸入公式“=C19:C26-A16〃(數(shù)組公式輸入)，在單元格E19:E26中輸入公式“=D19:D26/A16"

(數(shù)組公式輸入)。

則投資工程敏感性分析模型就建立起來了。單擊各個影響因素滾動條的箭頭，改變其變動幅度，就可以很方便地

了解各個因素對投資工程凈現(xiàn)值的單獨影響程度以及綜合影響程度。

這樣，通過單擊滾動欄兩端的笳頭或用鼠標拖曳滑塊，即可改變各種因素的變動率，并分析其對工程凈現(xiàn)值的影

響。

8.2.3投資工程內(nèi)部收益率敏感性分析模型

【例8-7］建立投資工程內(nèi)部收益率敏感性分析模型。我們也可以對投資工程的內(nèi)部收益率的敏感性進行分析，

方法與投資工程凈現(xiàn)值敏感性分析模型是一樣的。但需要注意的是，當要分析單因素變動對內(nèi)部收益率的影響時，

內(nèi)部收益率的計算是一件很麻煩的事，因為當投資工程壽命期內(nèi)各年的凈現(xiàn)金流量不相等時，不能使用RATE函

數(shù)來計算內(nèi)部收益率，不過可以通過自定義內(nèi)部收益率函數(shù)來解決這個問題。作者研究了?種內(nèi)部收益率的穩(wěn)定

迭代計算方法，具有穩(wěn)定、快速、收斂性好的優(yōu)點，計算原理及步驟如下：

（1）首先假定一個內(nèi)部收益率的初始值，并以此內(nèi)部收益率作為貼現(xiàn)率人計算工程的凈現(xiàn)值NPV；

（2）根據(jù)計算出的凈現(xiàn)值數(shù)據(jù)，利用下面的公式計算第1次迭代后的內(nèi)部收益率IRR：

式中/一初始投資現(xiàn)值。

若相鄰兩次計算的內(nèi)部收益率相差不大，或計算出的凈現(xiàn)值接近于零，則停止計算，就得到了內(nèi)部收益率的近似

值，否則重復(fù)上述迭代步驟。

內(nèi)部收益率函數(shù)的過程代碼如下：

PublicFunction內(nèi)部收益率（初始投資，期末殘值，壽命期，年付現(xiàn)本錢，年銷俘量，產(chǎn)品價格，單位變動本

錢，所得稅稅率）

凈現(xiàn)金流量=（年銷售量*（產(chǎn)品價格-單位變動本錢）/10000-年付現(xiàn)本錢）*（1-所得稅稅率）

+（初始投資-期末殘值）/壽命期*所得稅稅率

xl=0.1

10jxz=凈現(xiàn)金流量*（1-（1+xl）.-壽命期）/xl-初始投資+期末殘值/（1+xl）*壽命期

x2=（1+xl）*（1+jxz/初始投資）C（1/壽命期）-1

IfAbs（x2-xl）<=0.0000000001Then內(nèi)部收益率=x2Elsexl=x2:GoTo10

EndFunction

這樣，就可以分析不同因素變動對內(nèi)部收益率的影響。圖8T0為投資工程內(nèi)部收益率敏感性分析模型。其中單

元格B14中的計算公式為“=內(nèi)部收益率（8415,86,137,88,139幫1（）方11）"；單元格D14中的計算公式為“=內(nèi)部

收益率94<5"6"74849,。0111）”,單元格C17:C23中的計算公式分別如下:

isano：65ooooo:ooot」___

teonsooiom]_

nosoo-ioooi3」

（萬元）

XB色父支的百分ttm斑6內(nèi)?貨fii惠變M<S

1?■兩界丹1$0OOI

|5-0011

||

19V1?-om

20彳上《rr口21”41U

21”?■

221產(chǎn)ttT31

29:?d支動翼Xi3m

圖8-10投資工程內(nèi)部收益率敏感性分析模型

單元格C17：“二內(nèi)部收益率(C4,B5,B6,B7,B8,B9,BIO,B11)”

單元格C18：”=內(nèi)部收益率(B4,C5,B6,B7,B8,B9,BIO,Bl1)”

單元格C19：“二內(nèi)部收益率(B4,B5,C6,B7,B8,B9,BIO,B11)“

單元格C20：”二內(nèi)部收益率(B4,B5,B6,C7,B8,B9,BIO,B11)"

單元格C21：“=內(nèi)部收益率(B4,B5,B6,B7,C8,B9,BIO,Bl1)”

單元格C22：“二內(nèi)部收益率(B4,B5,B6,B7,B8,C9,BIO,Bl1)”

單元格C23：“=內(nèi)部收益率(B4,B5,B6,B7,B8,B9,CIO,B11)”

其他各單元格的計算公式可參閱例8-6。

笫8章Excel在投資工程不確定性風(fēng)險分析中的應(yīng)用

8.3概率分析

概率分析是通過研究各種不確定性因素發(fā)生不同幅度變動的概率分布及其對投資方案經(jīng)濟效果的影響，對方案的

凈現(xiàn)金流量及其經(jīng)濟效果指標作出某種概率描述，從而對方案的風(fēng)險情況作出比較準確的判斷。

在實際經(jīng)濟活動中，影響投資方案經(jīng)濟效果的大多數(shù)因素（如投資額、本錢、銷售量、產(chǎn)品價格、工程壽命期等）

都是隨機變鼠，我們可以預(yù)測其未來可能的取值范圍，估計各種取值或值域發(fā)生的概率，但不能肯定地預(yù)知它們

取什么值。因此，這就需要對投資工程進行概率分析。

假設(shè)投資工程有勿種可能出現(xiàn)的凈現(xiàn)金流量狀態(tài)，各種狀態(tài)所對應(yīng)的凈現(xiàn)金流量序列為｛力｝,各種狀態(tài)發(fā)生的概

率為月（）,則在第J種狀態(tài)下，方案的凈現(xiàn)值為

式中，為在第./種狀態(tài)下，第1周期的凈現(xiàn)金流量；〃為工程的壽命期。

則投資方案的凈現(xiàn)值期望值為

而凈現(xiàn)值的方差為

標準差為

對于獨立方案，計算其凈現(xiàn)值期望值和標準差的大小，可以分析其獲利能力及風(fēng)險的大小。對于幾個互斥方案，

可以叱較它們的變異系數(shù)的大小，以便衡量其相對風(fēng)險的上下，從而作出決策，變異系數(shù)計算公式為

8.3.1獨立工程的概率分析

8.3.1.1各年凈現(xiàn)金流量互不相關(guān)情況下的獨立工程概率分析

【例8-8】某企業(yè)的投資方案在其壽命期內(nèi)可能出現(xiàn)5種狀態(tài)的凈現(xiàn)金流量序列及其發(fā)生的概率如圖8T1所示。

各年的凈現(xiàn)金流量互不相關(guān)，基準收益率10%,試對方案進行概率分析。

圖871投資方案的概率分析

如圖8-11所示，選取單元格C8:G8區(qū)域，輸入不同狀態(tài)下凈現(xiàn)值的計算公式

“=PV(10%,10,-C5:G5)+C6:G6/(1+10%)11+C4:G4w(數(shù)組公式輸入)；在單元格C9中輸入凈現(xiàn)值期望值計算公

式“=SUMPRODUCT(C3:G3,C8:G8)",在單元格C10中輸入凈現(xiàn)值方差計算公式

u=SCMPRODUCT(C3:G3,(C8:G8-C9)-2)",在單元格Cl1中輸入凈現(xiàn)值標準差計算公式“二SQRT(CIO)”,在單元

格C12中輸入變異系數(shù)計算公式“=CU/C9〃，從而得到該方案的有關(guān)計算結(jié)果數(shù)據(jù)，如圖8T1所示?？梢?，該

方案的凈現(xiàn)值期望值大于零，是可行的，但風(fēng)險也較大。

8.3.1.2各年凈現(xiàn)金流量相關(guān)情況下的獨立工程概率分析一概率樹分析法

【例8-9】某企業(yè)擬投資開發(fā)一項專有技術(shù)，其初始投資為12萬元，該項技術(shù)預(yù)計在3年內(nèi)有效，3年內(nèi)

每年為企業(yè)帶來的現(xiàn)金流量是不確定的，其有關(guān)資料如表8T所示。該企業(yè)的資本本錢為15%,試對該投資工

程的可行性進行評價。

表8—：投資工程有關(guān)資料單位：萬元

工程初始投資12壽命/年3貼現(xiàn)率15%

第1年第2年第3年

凈現(xiàn)金流量概率凈現(xiàn)金流量概率凈現(xiàn)金流量概率

140.6

100.7110.3

7.50.6

7.50.1

60.390.4

70.4

40.2

100.3

80.290.4

80.3

90.7

40.470.4

50.3

7.50.1

50.150.3

2.50.6

為便于分析計算，將表8-1中的數(shù)據(jù)填列在Excel上，其格式如圖8T2所示，則具體計算步驟如下:

(1)在單元格G6中輸入第一種現(xiàn)金流序列情況下的凈現(xiàn)值計算公式“二NPV($F$3,A6,C6,E6)-$BS3",然后將此

單元格復(fù)制到單元格G7?G19。

(2)在單元格H6:H19中輸入聯(lián)合概率計算公式“=B6:B19*D6:D19*F6:F19〃(數(shù)組公式輸入)。

(3)在單元格16中輸入凈現(xiàn)值期望值計算公式“二SUMPRODUCT(G6:G19,

H6:H19)Z，o

(4)在單元格J6中輸入凈現(xiàn)值標準差計算公式“=SQRT(SUMPRODUCT

(H6:H19,(G6:G19-I6)，2))”。

(5)在單元格J6中輸入凈現(xiàn)值為負的概率計算公式“=SUMIF(G6:G19,〃<0",

可見，該工程的期望凈現(xiàn)值為5.21萬元，凈現(xiàn)值為負的概率很小。只有0.144,該工程的獲利能力較高而風(fēng)險

不大，故該工程是可行的。

A?C?17GM

投史JJH的巾”分代什??位：萬元

2月即智

312叼3snzit

“現(xiàn)m"理的“a值力

4年M不序列的冷R合懵不

Mf0標漕建sr)ffi?

fte

6T506100T

12404.02104100024

岐

1)404802901345J

14404q?2”0S4

1540470407690\\2

16404?04504

??<04504—?2」幺

18<04504■■3g

19<0450?2506-3t00096

圖8T2投資工程概率分析

8.3.2互斥工程的概率分析

在此種情況下，可分別計算各個工程的凈現(xiàn)值期望值、標準差及變異系數(shù)，對它們進行綜合分析，以確定最優(yōu)方

案。

【例8-10】某企業(yè)有A、B兩個投燙工程，其投資額及未來各年可能的凈現(xiàn)金流量如圖8T3所示，假設(shè)各年的

凈現(xiàn)金流量相互獨立。那么，企業(yè)應(yīng)選擇哪個工程？

圖8-13各年現(xiàn)金流獨立的互斥方案的概率分析

如圖873所示，分析決策過程如下：

(1)在合并單元格G5?G7中輸入工程A第1年的凈現(xiàn)金流量期望值計算公式“=SLMPR0DUCT(C5:C7,D5:D7『，

然后將此合并單元格分別復(fù)制到合并單元格G8?GIO、G11?G13、G14?G16中，得到工程A各年的凈現(xiàn)金流量期

望值。

(2)在合并單元格H5?H7中輸入工程B第1年的凈現(xiàn)金流量期望值計算公式“二5必0^(2尤1'位547胎5小7)”,

然后將此合并單元格分別復(fù)制到合并單元格H8?H10、H11?H13、H14?H16中，得到工程B各年的凈現(xiàn)金流量

期望值。

(3)在合并單元格15?17中輸入工程A第1年的凈現(xiàn)金流量標準差計算公式

“二SQRT(SUMPRODUCT(C5：C7,(D5:D7-G5)-2))”,然后將此合并單元格分別復(fù)制到合并單元格18?110、Ill?

113、114?116中，得到工程A各年的凈現(xiàn)金流量標準差。

(4)在合并單元格J5?二7中輸入工程B第1年的凈現(xiàn)金流量標準差計算公式

M=SQRT(SUMPRODUCT(E5:E7,(F5:F7-H5)^2))w,然后將此合并單元格分別復(fù)制到合并單元格J8?J10、JU?

J13、J14?J16中，得到工程B各年的凈現(xiàn)金流量標準差。

(5)在單元格C20中輸入工程A的凈現(xiàn)值期望值計算公式“=NPV：B17,G5,G8,Gll,G14)+G4”,在單元格C21中

輸入凈現(xiàn)值標準差計算公式“=SQRT(SUU(I5:116c2/(l+B17)，(2*A5:A16)))〃(數(shù)組公式輸入)，在單元格C22

中輸入變異系數(shù)公式"X21/C20"；這里，整個工程的凈現(xiàn)值標準差現(xiàn)值的計算公式為

(6)同樣在單元格1)20、D21.D22中分別輸入工程B的凈現(xiàn)值期望值、標準差和變異系數(shù)計算公式

a=NPV(B17,H5,H8,Hll,H14)+H4"、"=SQRT(SUM(J5:J16-2/(l+B17)"(2*A5:A16)))”(數(shù)組公式輸入)和

“=D21/D20”。

根據(jù)計算結(jié)果可見，工程A風(fēng)險較低但收益也低，而工程B風(fēng)險較高但收益也高。但兩個工程的變異系數(shù)相差并

不是很大，在這種情況下，如何作出決策，在很大程度上取決于決簧人員對風(fēng)險的態(tài)度。愿意冒風(fēng)險的決策人員，

會選擇工程B,而對風(fēng)險很敏感的決策者可能會傾向于選擇工程A,

第8章Excel在投資工程不確定性風(fēng)險分析中的應(yīng)用

8.4蒙特卡羅模擬

前面介紹的概率分析法在各年現(xiàn)金流彼此獨匯、且變量及其可能發(fā)生的情況較少的情況下，進行模擬計算是比較

簡單的。但在變量及其可能發(fā)生的情況較多的情況下，則必須進行大量的組合計算，伊IJ如，假設(shè)我們估計的銷售

量、單價、單位變動本錢的可能發(fā)生概率次數(shù)分別為5次、4次和4次，則每年就需要計算5X4X4=80個可能

組合，假設(shè)，程的壽命為5年，則需要計算80X5=400個口」能組合，計算工作量非常大。利用蒙特卡羅模擬法可

以解決這個問題，并且還可以對工程的風(fēng)險進行更為深入的分析。

蒙特卡羅模擬法，是根據(jù)隨機數(shù)對影響因素的概率分布進行隨機抽樣，根據(jù)每次抽樣值來計算工程的凈現(xiàn)金流量、

凈現(xiàn)值及內(nèi)部收益率等指標。在利用蒙特