應(yīng)用Excel建立投資項(xiàng)目敏感性分析模型

□（鄭州升達(dá)經(jīng)貿(mào)管理學(xué)院會(huì)計(jì)系河南鄭州 451191）

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

一、設(shè)計(jì)Excel敏感性分析表格和內(nèi)容

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

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

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

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

圖1 投資項(xiàng)目敏感性分析模型

二、設(shè)置Excel敏感性分析計(jì)算公式

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

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

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

圖2 設(shè)置控件格式

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

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

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

第二年末到第五年末：

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

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

三、Excel敏感性分析模型的應(yīng)用

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

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

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

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

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