巧用Ｅｘｃｅｌ實施項目的敏感性分析

［摘 要］ 本文在項目評價系統原理的基礎上，設計了一種用Excel進行敏感性分析的簡便方法，創建了項目評價系統體系與評價指標之間的計算關系，當不確定性因素變化時，財務報表的編制、數據的平衡和評價指標的計算自動完成，減輕了敏感性分析的工作量。

［關鍵詞］ Excel；項目；敏感性分析

［中圖分類號］F232；F275［文獻標識碼］A［文章編號］1673-0194(2008)03-0050-03

一、問題的提出

敏感性分析是一種處理風險和不確定性問題的常用方法，它是通過分析、預測主要因素變化對評價指標的影響，以預測項目所承擔的風險。它可以幫助決策者識別投資機會中最值得注意的地方，從而為決策者提供制定決策所需要的重要信息，指導決策者確定最值得深入研究的因素。由于敏感性分析是在原有確定性數據的基礎上進行的，當一個因素變化時，相互關聯的財務報表和各項評價指標全部都要重新編制和計算，不僅工作量大，而且相當煩瑣。筆者利用Excel提供的功能，結合項目評價系統體系，創建主要因素變化與評價指標之間的計算關系，以減輕敏感性分析中復雜的運算及圖表制作中的困難。

二、 確定性條件下項目評價指標計算模型與相關函數模型

1. 評價指標計算模型

項目評價最常用的指標有內部收益率（IRR）和凈現值（NPV），在確定性條件下，其計算公式分別如下：

（1）內部收益率指標

式中：NPV為凈現值；i為基準收益率或設定的折現率；CI為現金流入；CO為現金流出；n為項目計算期；Ft為項目第t年的凈現金流量；（P / F，i，t）為一次支付現值系數。

由式（1）、式（2）可知，各指標的計算要先確定凈現金流量的值，而數據來源于項目投資現金流量表等財務報表，因此，在進行敏感性分析之前，必須編制出項目評價的一系列相關的財務報表，并根據資金籌措、財務條件、還款方式、市場變化等方面的要求，對各報表進行平衡計算。項目評價財務報表的編制是一項復雜的、系統的動態過程，而敏感性分析是在此基礎上進行的，涉及的數據是共享的，不僅量大，也是動態變化的。

2. Excel中的相關函數模型

（1）內部收益率指標函數模型

格式為：IRR（values，guess） （3）

式中：values為一個數組或對數字單元格區的引用；guess為內部收益率的猜測值。

（2）凈現值指標函數模型

格式為：NPV（rate，value1，value2，…） （4）

式中：rate為基準收益率或設定折現率；value1，value2，…為各年凈現金流量的數組或對單元格區的引用。

三、 應用Excel進行敏感性分析實例

本案例為建設期2年，生產期12年的某新建項目，根據項目的投資、銷價、銷量、稅率、利率、借款還本付息方式等各項資料，其確定性條件下的財務報表已全部編出，在此基礎上對該項目進行敏感性分析。

1. 確定分析指標

確定內部收益率指標和凈現值指標作為分析指標。確定性條件下已求出內部收益率為10.78%，凈現值為107 207元。

2. 設定不確定性因素

根據各因素對項目的重要程度及在預測后采用的數據準確性的把握程度，現選擇固定資產投資、銷售收入和經營成本3個因素為不確定因素。3個因素的變化幅度分別取± 10%和± 20%的范圍。

3. 主要評價報表與敏感性分析關系設計

該項目的投資現金流量如圖1所示。數據主要來自于投資計劃與資金籌措表、總成本費用估算表、銷售收入與稅金附加估算表和損益表（篇幅所限，沒有全部列出）。為使敏感性分析簡便快捷，把評價指標一覽表、不確定因素及變化幅度表和因素變化后對內部收益率和凈現值指標的影響表置于一個工作表內，見圖2。

4. 因素變化與敏感性分析計算

項目投資現金流量表（圖1）和3個因素的變化幅度（圖2）以及相對應的其他相關報表相互自動鏈接成評價系統，如第4年銷售收入1 296 000 =銷售收入、稅金及附加估算表！H6等。當各因素依據估算的基本數據進行正、負幅度變化時，相關數據互相影響，如固定資產投資變化會影響到總投資和折舊費，銷售收入變化會影響到銷售稅金和附加以及所得稅，經營成本變化會影響到流動資金投資等，進而影響到現金流入、現金流出和凈現金流量（見圖1），從而使內部收益率和凈現值指標發生變化。所以，當3個因素從變化幅度0%開始按照±10%和±20%的變動范圍重新賦值時，其他相關數據則自動更新，內部收益率的值和凈現值的值則分別由（3）式和（4）式自動求得，即IRR（values，guess）= IRR('財務現金流量表（全投資）' !D15:Q15)，NPV（rate，value1，value2，…）= NPV(10%，'財務現金流量表（全投資）' !D15:Q15)。例如，當固定資產投資和經營成本不變，銷售收入減少10%時，內部收益率由基本數據10.78%降為8.78%，凈現值由107 207元降為-170 968元（見圖2中的不確定因素與變化幅度表和評價指標一覽表）。其他因素變化時，重復上述程序，直到3個因素全部變動完畢，然后把不確定因素每一次幅度變化后更新的指標值填入圖2對內部收益率和凈現值影響表相應欄目中，便可找出哪一個因素是敏感因素。從分析可知，經營成本為最敏感因素，其次是銷售收入。為了方便尋找，可分別求出各因素變化+1%和-1%時的指標變化值，則判斷更容易。

5. 制作敏感性分析圖

根據各因素變動后內部收益率和凈現值的指標值，利用Excel可分別作兩個指標的敏感性分析圖（見圖3）。當單元格和數據引用區域的值發生變化時，敏感性分析圖隨之更新，方便快捷。另外，從圖中不僅能找出最敏感因素，而且可以確定內部收益率和凈現值指標達到臨界點（IRR=基準收益率，本例為10%；NPV=0）時的某一影響因素變化的最大值。從圖3中可知，當經營成本增加4%左右時，該項目的NPV =0，如果經營成本再增加，則項目不可行，可以很快幫助決策者預測項目所承擔的風險。

四、結 論

通過計算機輔助方法的設計和實施，利用Excel提供的函數、鏈接、引用、圖表制作等強大功能，減少了項目評價中各評價報表的編制和平衡過程以及各項評價指標計算中復雜的重復運算，不僅能找出最敏感因素，而且能判斷各評價指標達到臨界點時某一影響因素變化的范圍，大大提高了項目評價敏感性分析的速度，并提高了數據計算的準確性和圖表制作的規范性。

主要參考文獻

［1］ ［英］菲爾·荷馬斯. 投資評價［M］. 王嗣俊，白玉明譯. 北京：機械工業出版社，1999.

［2］ 吳添祖編. 技術經濟學概論［M］. 北京：高等教育出版社，2004.

注：“本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文。”