基于ExcelVBA的項目投資敏感性分析與預(yù)測

尤崢

（安徽理工大學(xué)，安徽 淮南 232001）

基于ExcelVBA的項目投資敏感性分析與預(yù)測

尤崢

（安徽理工大學(xué)，安徽 淮南 232001）

在項目投資決策過程中，敏感性分析是判斷項目是否可行不可或缺的環(huán)節(jié).通常財務(wù)分析的敏感性分析中必選的分析指標(biāo)是項目投資財務(wù)內(nèi)部收益率，手算內(nèi)部收益率時需進(jìn)行多次試算，過程繁瑣不便.應(yīng)用Excel VBA技術(shù)，在項目評價系統(tǒng)與評價指標(biāo)之間建立分析預(yù)測模型，可以快捷地計算出評價指標(biāo)內(nèi)部收益率、凈現(xiàn)值及其敏感性分析指標(biāo)，為項目投資決策提供依據(jù).

Excel VBA；項目投資；經(jīng)濟(jì)評價指標(biāo)；敏感性分析

1 引言

項目投資決策的依據(jù)之一是項目的可行性，項目投資方案的敏感性分析是判斷項目是否可行的一個不可或缺的環(huán)節(jié).在項目投資決策過程中，經(jīng)濟(jì)評價所采用的基礎(chǔ)數(shù)據(jù)大多來自于預(yù)測和估算，具有不確定性，為分析不確定性因素對項目經(jīng)濟(jì)評價指標(biāo)的影響，應(yīng)在確定性分析的基礎(chǔ)上進(jìn)行不確定性分析.敏感性分析是項目投資決策中常用的一種研究不確定性的方法，它從定量分析的角度研究項目經(jīng)濟(jì)評價指標(biāo)對相關(guān)因素在發(fā)生改變時的影響敏感程度，以及計算相關(guān)因素的變動極限值，為投資決策提供依據(jù).敏感性分析有單因素敏感性分析和多因素敏感性分析.

本文擬采用ExcelVBA技術(shù)，在項目投資方案與評價指標(biāo)之間建立預(yù)測模型，簡單快捷地計算單個不確定性因素對評價指標(biāo)影響的敏感程度以及變動極限值，并據(jù)此判斷項目投資方案在經(jīng)濟(jì)上是否可行、可否接受；同時預(yù)測多個不確定性因素同時發(fā)生變動時對項目評價指標(biāo)的影響，為項目投資方案提供更符合實際的決策依據(jù).

2 敏感性分析預(yù)測模型的建立

在項目投資方案進(jìn)行敏感性分析時，經(jīng)濟(jì)評價指標(biāo)有凈現(xiàn)值、內(nèi)部收益率和投資回收期等；不確定性因素有投資額、產(chǎn)品價格、產(chǎn)品產(chǎn)量、經(jīng)營成本、項目運行期、折現(xiàn)率等；需計算的敏感性指標(biāo)有敏感度系數(shù)和臨界點.

如：某投資方案計劃投產(chǎn)時的總投資額為1400萬元，其中流動資金為100萬元；設(shè)計年生產(chǎn)能力為10萬臺，預(yù)測單位產(chǎn)品售價為45元/臺；銷售稅金及附加為銷售收入的10%；年經(jīng)營成本為160萬元；方案運行期為10年；到期固定資產(chǎn)余值為50萬元；基準(zhǔn)折現(xiàn)率為10%.(忽略其他影響)

該投資方案的經(jīng)濟(jì)評價指標(biāo)選擇凈現(xiàn)值、內(nèi)部收益率；不確定性因素選擇投資額、產(chǎn)品售價、經(jīng)營成本.根據(jù)經(jīng)濟(jì)評價指標(biāo)與不確定性因素之間的關(guān)系.

凈現(xiàn)值的計算表達(dá)式為：凈現(xiàn)值=-投資額×(1+投資額的變動率)+[產(chǎn)品售價×(1+產(chǎn)品售價變動率)×生產(chǎn)能力×(1-銷售稅金及附加)-經(jīng)營成本 (1+經(jīng)營成本變動率)] ×(P/A,基準(zhǔn)折現(xiàn)率,運行期)+期末回收資產(chǎn)×(P/F,基準(zhǔn)折現(xiàn)率,運行期)

內(nèi)部收益率是使投資方案在計算期內(nèi)凈現(xiàn)值為0時的折現(xiàn)率.內(nèi)部收益率的計算表達(dá)式為：-投資額×(1+投資額的變動率)+[產(chǎn)品售價×(1+產(chǎn)品售價變動率)×生產(chǎn)能力× (1-銷售稅金及附加)-經(jīng)營成本 (1+經(jīng)營成本變動率)]× (P/A,內(nèi)部收益率,運行期)+期末回收資產(chǎn)×(P/F,內(nèi)部收益率,運行期)=0

敏感度系數(shù)是項目評價指標(biāo)變化率與不確定性因素變化率的比值，反映項目評價指標(biāo)對不確定性因素的敏感程度.計算公式為：SAF=（ΔA/A)/(ΔF/F).式中，ΔF/F為不確定性因素F的變化率 (%)；ΔA/A為不確定性因素F變化ΔF時，經(jīng)濟(jì)評價指標(biāo)A的變化率(%).

臨界點是指不確定性因素的變化使項目由可行變?yōu)椴豢尚械呐R界數(shù)據(jù).根據(jù)凈現(xiàn)值計算表達(dá)式，將凈現(xiàn)值設(shè)定為0，求解某一不確定性因素的最大變動率即得到該不確定性因素的臨界點.

在Excel工作表中，建立基礎(chǔ)數(shù)據(jù)表、敏感因素變動率設(shè)定區(qū)、經(jīng)濟(jì)評價指標(biāo)區(qū)以及單因素分析結(jié)果區(qū).在G3、G4、G5單元格，各添加一個“水平滾動條”控件，屬性設(shè)置：Max為100，Min為-100；添加4個“命令按鈕”控件，依次為“計算凈現(xiàn)值”、“計算內(nèi)部收益率”、“計算敏感度系數(shù)”和“計算臨界點”.輸入基礎(chǔ)數(shù)據(jù)，如圖1所示.

圖1 分析預(yù)測界面

按“Alt+F11”組合鍵，打開“Microsoft Visual Basic”窗口.在工作表(如Sheet1)代碼窗口的通用聲明區(qū)(窗口最上部)聲明相關(guān)變量.編寫“水平滾動條”的Change事件、Scroll事件代碼，將有關(guān)單元格顯示的數(shù)值與控件的Value屬性值相關(guān)聯(lián).四個按鈕Click事件的代碼，可根據(jù)上述計算表達(dá)式及分析編寫.“計算凈現(xiàn)值”按鈕Click事件的主要代碼為：

“計算內(nèi)部收益率”按鈕Click事件的主要代碼為：

其他按鈕的代碼，在此不一一贅述.

3 單因素敏感性分析與預(yù)測

3.1 評價指標(biāo)計算

設(shè)定三個敏感性因素的變動率均為0(初始條件)，單擊“計算凈現(xiàn)值”按鈕，或“計算內(nèi)部收益率”按鈕，計算結(jié)果、彈出方案預(yù)測消息框.如圖2所示.項目投資方案的凈現(xiàn)值=163.25(萬元)，凈現(xiàn)值≥0；內(nèi)部收益率=12.54%，內(nèi)部收益率≥基準(zhǔn)折現(xiàn)率，表明該投資方案在經(jīng)濟(jì)上可以接受.

3.2 敏感性分析指標(biāo)計算

圖2 分析預(yù)測1

圖3 分析預(yù)測2

單擊“計算敏感度系數(shù)”、“計算臨界點”按鈕后，顯示的計算結(jié)果，如圖3所示.項目投資方案的凈現(xiàn)值對各不確定性因素的敏感度系數(shù)表明，產(chǎn)品售價是最敏感的因素.因此，從項目投資決策的角度來看，對產(chǎn)品售價應(yīng)進(jìn)行更準(zhǔn)確的測算.此外，項目投資方案的各不確定性因素的臨界點的數(shù)值也表明，產(chǎn)品售價在下降6.56%時，凈現(xiàn)值由正變負(fù)，項目在經(jīng)濟(jì)上就由可行變?yōu)椴豢尚辛?這也預(yù)示項目在市場產(chǎn)品售價下降較大時，項目投資的風(fēng)險性也增大.

4 多因素敏感性分析與預(yù)測

在現(xiàn)實中，不確定性因素投資額、產(chǎn)品售價、經(jīng)營成本等往往會同時發(fā)生變動.如市場上原材料、燃料動力價格的上漲、人工工資的增加等，將導(dǎo)致投資額、經(jīng)營成本增加；市場競爭的加劇、同類產(chǎn)品產(chǎn)量的增多等，會使產(chǎn)品售價下降.

對上述投資方案，如果投資額增加5%，經(jīng)營成本增加5%，產(chǎn)品售價下降2%，若僅從單因素敏感性分析，投資方案似乎在經(jīng)濟(jì)上是可行的.但是，當(dāng)這些因素同時出現(xiàn)變動時，投資方案在經(jīng)濟(jì)上還可行嗎？

設(shè)定投資額變動率為5%，產(chǎn)品售價變動率為-2%，經(jīng)營成本變動率為5%，單擊“計算凈現(xiàn)值”按鈕、或“計算內(nèi)部收益率”按鈕，計算結(jié)果、彈出的方案預(yù)測判斷消息框.如圖4所示.從經(jīng)濟(jì)評價指標(biāo)的計算結(jié)果來看，凈現(xiàn)值=-5.68(萬元)，凈現(xiàn)值＜0；或內(nèi)部收益率=9.91%，內(nèi)部收益率＜基準(zhǔn)折現(xiàn)率.據(jù)此，結(jié)論是該投資方案在經(jīng)濟(jì)上是不可行的，應(yīng)予拒絕.

圖4 分析預(yù)測3

5 結(jié)語

應(yīng)用ExcelVBA技術(shù)搭建敏感性分析預(yù)測模型，對項目投資方案可行性的預(yù)測，是一種相對比較快捷簡便的方法.可避免手算內(nèi)部收益率時的繁瑣的試算過程；在確定敏感度系數(shù)、臨界點時，不再需要計算多個變動率時的結(jié)果；也不再需要采用內(nèi)插法、或繪圖法.對于多因素敏感性分析也可簡便直觀地得到經(jīng)濟(jì)評價指標(biāo).為項目投資方案提供更符合實際的決策依據(jù).

〔1〕全國造價工程師執(zhí)業(yè)資格考試培訓(xùn)教材編審委員會.建設(shè)工程造價管理(2013版)[M].北京：中國計劃出版社，2013．

〔2〕全國注冊咨詢工程師(投資)資格考試參考教材編寫委員會．項目決策分析與評價(2012版)[M].北京：中國計劃出版社，2012．

〔3〕何非，葉萃娟．ExcelVBA高效辦公從入門到精通[M].北京：中國青年出版社，2006．

TP311

A

1673-260X（2 0 1 3）12-0020-02