應用Excel建立投資項目敏感性分析模型

□（鄭州升達經(jīng)貿管理學院會計系河南鄭州 451191）

敏感性分析是對投資項目進行不確定性分析的內容之一。敏感性分析包括單因素敏感性分析和多因素敏感性分析，進行決策時需結合經(jīng)驗判斷選擇對項目影響比較大的關鍵因素進行分析。實務操作中，敏感性分析計算量比較大，借助Excel分析工具，能夠高效提升投資項目敏感性分析水平。

一、設計Excel敏感性分析表格和內容

Excel模型設計與決策內容相關，敏感性分析的基本要求是依據(jù)具體問題可以用凈現(xiàn)值或內含報酬率進行單因素和多因素敏感性分析，單因素分析需計算敏感系數(shù)，多因素分析需要測算多種影響因素同時變動對評價指標的影響。

（一）案例資料。某企業(yè)擬投資甲項目，經(jīng)可行性分析，有關資料如下：（1）該項目需固定資產(chǎn)投資100萬元，第一年初全部投資到位，第一年末該項目全部竣工交付使用。（2）該項目投產(chǎn)時需墊支營運資金60萬元，用于購買原材料、支付工資以及存貨增加占用等。（3）該項目經(jīng)營期預計為5年（不含建設期），固定資產(chǎn)按直線法計提折舊。預計殘余價值為10萬元。（4）根據(jù)有關部分的市場預測，該項目投產(chǎn)后每年銷售收入均為95萬元，每年的付現(xiàn)成本均為46萬元。（5）該項目籌資的資金成本率為12%，該企業(yè)適用所得稅稅率為25%。要求：運用凈現(xiàn)值對投資項目進行敏感性分析。

（二）設計表格和內容。在設計表格格式和內容時盡量考慮使用者的方便和便于理解，敏感性分析模型整體效果如圖1所示。

圖1內容共有三部分：（1）影響凈現(xiàn)值因素變動分析；（2）影響凈現(xiàn)值因素對凈現(xiàn)值的影響；（3）每年現(xiàn)金流計算表。影響凈現(xiàn)值因素變動分析部分主要包含變動因素當前值、變動比率和變動前后數(shù)值；影響凈現(xiàn)值因素對凈現(xiàn)值的影響分多因素和單因素，單因素分析包含凈現(xiàn)值變動幅度和敏感系數(shù)等；每年現(xiàn)金流量計算表需分年列示計算現(xiàn)金流所需要的其他數(shù)據(jù)及不同影響因素變動時每年的現(xiàn)金流量：當前現(xiàn)金流量，僅投入、收入或成本中一個因素變動時的現(xiàn)金流量，三個因素同時變動時的現(xiàn)金流量。

圖1 投資項目敏感性分析模型

二、設置Excel敏感性分析計算公式

基準收益率為該項目投資的資金成本率12%，圖1中除比率外的數(shù)據(jù)單位為“萬元”，圖中各個部分已知數(shù)據(jù)直接錄入。以Excel 2010為基礎，圖中其他需設置公式和表單控件的具體操作如下。

（一）影響凈現(xiàn)值因素變動分析。

1.設置數(shù)值調節(jié)滾動條。選擇“開發(fā)工具→控件→插入”命令，在“表單控件”中選擇滾動條（窗體控件），將其拖到合適的位置，設置合適的大小，然后放手。在本模型中，需要3個滾動條，最好分別放在D6、D7、D8單元格上，如圖1所示。固定資產(chǎn)投入滾動條：選中D6單元格上的滾動條，右鍵單擊，打開“設置控件格式”對話框；設置當前值5000、最小值0、最大值10000、步長1、頁步長100、單元格鏈接為D6單元格，如圖2所示。同樣的方法設置調節(jié)銷售收入、付現(xiàn)成本滾動條，其單元格鏈接分別為D7、D8。若在功能區(qū)選項卡中沒有“開發(fā)工具”選項，可以通過“文件→選項”命令，打開“Excel選項”對話框：選擇“自定義功能區(qū)”，在“主選項卡”中選中“開發(fā)工具”選項即可顯示“開發(fā)工具”選項卡。

圖2 設置控件格式

2.設置變動百分比。影響因素變動的幅度通過滾動條進行調節(jié)，進而計算變動后的數(shù)值。設置變動百分比計算公式，F(xiàn)6單元格：=D6/10000-50%，將該公式復制到 F7、F8單元格；設置變動后的數(shù)值，H6單元格：=C6*(1+F6)，將該公式復制到H7、H8單元格。

（二）每年現(xiàn)金流量計算表。計算凈現(xiàn)值需要計算影響因素變動前后的現(xiàn)金流量，考慮到資金的時間價值，現(xiàn)金流量的計算需要逐年進行。計算現(xiàn)金流量所需的其他數(shù)據(jù)，墊支營運資金、固定資產(chǎn)殘值和營運資金收回數(shù)據(jù)等直接根據(jù)時間點填表即可。在本模型中，現(xiàn)金流量計算公式：（銷售收入-付現(xiàn)成本-固定資產(chǎn)折舊）×（1-25%）+固定資產(chǎn)折舊-投資支出+固定資產(chǎn)殘值收入+收回墊支的營運資金，投資支出包括固定資產(chǎn)投資支出和墊支的營運資金，殘值收入和回收營運資金一般發(fā)生在項目結束，據(jù)此計算公式如下：

第一年初只有投入，C23、C25、C26 單元格：=0-C6，當前現(xiàn)金流量、固定資產(chǎn)投入不變，其他因素變化時，投入需用當前值計算；C24、C27單元格：=0-H6，固定資產(chǎn)投入變化及多因素變動時，投入需用變化后的值計算。第二年初只有墊支的營運資金，D23、D24、D25、D26、D27 單元格公式均為：=0-60，因為案例中墊支營運資金均為60萬元。

第二年末到第五年末：

第六年計算公式在第二至第五年計算公式基礎上加上I21和I22即可，比如I23單元格：=($C$7-$C$8-SLN($C$6,10,5))*(1-25%)+SLN($C$6,10,5)+I21+I22。

（三）影響凈現(xiàn)值因素對凈現(xiàn)值的影響。獲得每年的現(xiàn)金流量和基準收益率后就可以計算投資項目的凈現(xiàn)值。影響凈現(xiàn)值因素變動分析中凈現(xiàn)值計算公式C9單元格：=NPV(C3,C23:I23)。單因素變動時計算公式C14單元格：=NPV($C$3,C24:I24)，D14 單元格：=(C14-$C$9)/$C$9，F(xiàn)14 單元格：=D14/F6；選中C14至F14單元格，將公式復制到C16至F16單元格。多因素變動時計算公式G14單元格：=NPV(C3,C27:I27)，H14 單元格：=(G14-C9)/C9。

三、Excel敏感性分析模型的應用

調節(jié)固定資產(chǎn)投入、銷售收入和付現(xiàn)成本三個影響因素的滾動條，使其變動百分比為1%，敏感性分析結果如上頁圖1所示，可以看出，當前情況下，三因素敏感性程度依次是銷售收入、付現(xiàn)成本和固定資產(chǎn)投入。應用模型解決實際問題時，有以下兩個方面需要注意。

（一）敏感性分析內容。敏感性分析有單因素和多因素分析兩種，單因素敏感性分析揭示的是某一不確定性因素發(fā)生變化時，對投資項目評價的影響，通過計算敏感系數(shù)，找出關鍵敏感因素。本模型中選取的是固定資產(chǎn)投入、銷售收入和付現(xiàn)成本三個因素進行敏感性分析，實務中有兩個方面可以改進：（1）通過調整公式和表格，細化敏感因素，比如付現(xiàn)成本可以細化到材料成本和人工成本等；（2）增加敏感因素，只要對投資項目評價有影響的因素都可以通過本模型單因素分析，計算其敏感性程度，從而找出三至四個關鍵因素做進一步的敏感性分析。

實際經(jīng)濟環(huán)境中，往往多個敏感因素同時發(fā)生變化、有些因素之間也是有關聯(lián)的，這時就需要進行多因素敏感性分析，本模型也能滿足這方面的需求。

（二）影響投資項目評價因素變動分析。影響因素變動的幅度往往不一致，有些變動因素之間也是有關聯(lián)的，在具體問題具體分析時通過調節(jié)滾動條均能滿足要求。在本模型中設計敏感因素變動率時，變動范圍是[-50%，50%]，調節(jié)滾動條每次變動幅度有0.01%、1%兩種，能夠滿足敏感性分析要求；若有特殊變動率要求，可以通過改變公式實現(xiàn)。

另外，敏感性分析評價指標常采用的有凈現(xiàn)值和內含報酬率，本模型中選用的是凈現(xiàn)值（NPV），在不能確定基準收益率時，可以把模型中凈現(xiàn)值函數(shù)改為內含報酬率函數(shù)（IRR），通過測算項目本身能達到的收益率進行決策。