基于Excel的投資項目風險分析模型設計

摘 要：投資是企業發展生產、提升企業核心競爭力的必經之路。從長期的投資實際來看，投資風險是不可避免的。特別是投資決策涉及的時間比較長，投資支出、每年的現金流量、回報年限都是預測或估算的，任何預計數通常總有能實現和不能實現兩種可能性，總能帶有某種程度的不確定性。如果有一個投資項目的不確定因素較強而投資決策是根據這種不確定情況作出的話，一旦發生變動，就會造成投資決策的失誤，給企業帶來嚴重損失。因此在決策前要充分考慮各種不確定因素，預測未來可能出現的各種情況，分析各個不確定因素對經濟評價指標的影響，以預測項目可能承擔的風險，分析項目經濟上的可行性。本文以Excel2010為平臺，利用它的內置函數與分析工作分別建立了投資項目的概率與模擬分析模型，以供同行借鑒。

關鍵詞：Excel 投資 風險分析 模型 設計

中圖分類號：F204 文獻標識碼：A 文章編號：2096-0298（2017）11（c）-165-03

1 投資項目風險分析概述

項目風險分析的方法和指標有很多，通常有盈虧平衡分析、敏感性分析、概率分析以及模擬分析等。盈虧平衡分析是計算達到盈虧平衡點的產銷量或生產能力利用率，判斷擬建項目適應市場變化的能力和風險大小的一種分析方法。敏感性分析是研究影響項目建設的主要影響因素發生變化時，所導致的項目經濟指標的變化幅度，從而判斷外部條件發生變化時，投資項目的承受能力。概率與模擬分析概率分析是使用概率研究預測各種不確定因素和風險因素對項目經濟評價指標影響的一種定量分析方法。模擬分析是指利用蒙特卡羅模擬法，將敏感性分析與各不確定因素的概率分布兩者結合起來以衡量投資項目風險的一種分析技術。

2 風險分析模型設計中應用的主要函數簡介

2.1 NORMINV（）函數

返回指定平均值和標準偏差的正態累積分布函數的反函數。

語法：NORMINV（probability，mean，standard_dev）。

其中：probability為累積分布F，mean為均值，standard_dev為標準差。

2.2 NPV（）函數

返回按行業基準折現率或其他設定的折現率計算的各年凈現金流量現值的代數和。

語法：NPV（rate，value1，value2….....）。

其中：rate為某一期間的貼現率，value1，value2….....為1到29個參數。

2.3 INDEX（）函數

返回表或區域中的值或對值的引用。

語法：INDEX（array，row_num，column_num）返回數組中指定的單元格或單元格數組的數值。INDEX（reference，row_num，column_num，area_num）返回引用中指定單元格或單元格區域的引用。

其中：Array為單元格區域或數組常數；Row_num為數組中某行的行序號；Column_num是數組中某列的列序號。

3 投資項目的概率與模擬分析模型構建

3.1 案例背景

LY公司是一個開發一種新產品的投資項目，其初始投資額為200萬元，有效期為3年。該項目一旦投入運營后，第一年產品的銷量是一個服從均值為100萬件而標準差為60萬件的正態分布隨機變量，其產品的第二年銷量將在第一年銷量的基礎上增長25%，而第三年銷量則將在第二年小亮的基礎上增加-40%，3年內每年（折舊費用之外）的固定成本都等于110萬元。產品的單價與單位變動成本都是隨機變量，假定3年內每一年的單位變動成本都在15～20元/間之間變化的均勻分布隨機變量，3年內每一年的產品單價都是辦照如表所示的概率，分別為40元/件，50元/件或60元/件三者中的任一數值的隨機變量。公司所使用的貼現率為10%，要求建立一個投資風險評價模型，計算出某一隨機銷量下的特定凈現值。單價變動概率表如表1所示。

3.2 生成隨機變量隨機數

Excel的內建函數可以生成隨機變量的隨機數，如：均勻分布隨機數、離散分布隨機數和正態分布隨機數。

均勻分布隨機數的產生，在一個均勻分布的隨機數中，每一個體出現的概率是均等的。Excel提供了RAND（）函數生成0與1之間均勻分布的隨機數，因此在Excel中要生成a和b之間均勻分布的含n位小數的隨機數X，可以通過下面公式得到。

X=a+ROUND（（b-a）*RAND（），n）

離散性經驗概率分布有兩個重要性質：任何結果的概率總是介于0～1之間，所有結果的概率之和是1。因此，可以將從0～1的范圍看成是一個概率盒子，再把它分割成與離散結果的概率相對應的區間小盒子。于是，任何離散結果的概率隨機數一定落入這些區間的小盒子中。為了正確地構造這些概率小盒子，最常用的方法是從概率分布P構造累計概率分布F，如表2所示。然后用查表法得到離散分布隨機數，利用Excel中的RAND（）函數產生隨機概率，用MATCH（）在累計概率分布中查找，通過INDEX（）返回對照表2中的隨機數。

3.3 概率與模擬分析建模步驟

首先在確定條件下求出項目投資凈現值，如價格是隨機變化的，則可以先假定某特定價格；其次根據題目分清那些是隨機變量，確定隨機變量產生的分布規律，找到該隨機變量產生的方法；再次利用隨機變量替代固定變量后，建立起具有隨機性的投資評價模型，由于使用了隨機函數，工作表每計算一次即得出某一特定試驗下的項目投資凈現值；最后通過多次試驗即隨機模擬，再經過統計分析，即可以得到目標量項目投資凈現值的可能性的范圍和概率。

3.4 模型構建

（1）建立確定條件下的模型框架，在確定條件下求出項目投資凈現值，即不考慮隨機因素。假定初始銷量為150萬件，新產品價格為40元，單位變動成本為16元，將已知數據整理在名為“確定條件下投資評價模型”工作表的單元格F6∶F13中，如圖1所示。

（2）求出某一確定條件下的投資項目凈現值，在單元格D15：D18中輸入如表3所示。

算出3年中銷售量、銷售收入、總成本和利潤后，在單元格F20中輸入公式“=-F6+NPV（F10，D18，F18）”，就可以算出在確定條件下投資項目的凈現值12305.18萬元，如圖2所示。

（3）利用隨機變量替代固定變量后，求出某一隨機條件下的投資項目凈現值。如圖2所示的模型框架。

（4）本案例中初始銷量、產品價格、單位變動成本都是隨機變量。由于初始銷量是一個服從均值為100萬件而標準差為60晚間的正態分布隨機變量，可以直接使用內建正態分布隨機變量函數，在單元格F6、F7中輸入1和0.6，在單元格F13中輸入“=NORMINV（RAND（），F6，F7）”。產品單價隨機變量產生按以下方式得到，在單元格D3：F4中輸入一個不同概率的價格表，在單元格H5和J5中輸入0，在單元格H5：J8中輸入公式，如表4所示。

形成如圖2所示的結果，由于0～0.3、0.3～0.8和0.8～1這三個間隔的寬度比例正好是0.3∶0.5∶0.2，RAND（）函數產生0～1之間均勻分布的隨機數，可以通過INDEX（）和MATCH（）在單元格H5∶J8內找到相應的價格，找到價格“40元/件”的概率是0.3，找到價格“50元/件”的概率是0.5，找到價格“60元/件”的概率是0.2。在單元格F14中輸入公式“=INDEX（H5∶H8，MATCH（RAND（），J5：J8，1）=1）”；在單元格F15中輸入公式“=15+ROUND（5*RAND（），2）”，得到單元變動成本隨機變量。這樣就得到某一特定隨機試驗下的“產品初始銷量”“產品價格”和“單位變動成本”。此時單元格F22已算出在不確定條件下投資項目的凈現值隨機量，如圖2所示。

（5）通過多次試驗（即隨機模擬）后，再進行統計分析。在工作表Q11∶Q1010輸入1～1000整數，在R10輸入公式“=F22”，選中區域Q11∶R1010，選擇Excel“數據”菜單中的“模擬運算表”功能，列飲用可以選擇本工作表的任意位置，這樣激發1000個隨機數的計算，也即是進行了1000次試驗，如圖3所示。在011輸入公式“=MAX（R11：R1010）”，在012輸入公式“=MIN（R11：R1010）”，在013輸入公式“=AVERAGE（R11：R1010）”，在014輸入公式“=COUNTIF（R11：R1010，”>1”）/1000”，如圖3所示。這樣就得到了模擬實驗的各種數據。

3.5 模型分析

由于市場的隨機變化以及各種成本和收入的不確定性等因素的存在，投資時常會有風險。為了最大限度地規避風險，獲得預期回報，就必須在投資決策時考慮到各種風險，分析風險對回報影響程度，從而作出正確的決策。

4 結語

在市場經濟環境下，企業競爭日益激烈，財務管理在企業管理中占有核心地位，其問題也日益突顯，面臨著巨大的挑戰。伴隨計算機技術在企業財務管理工作中的應用，其管理的環境與手段都發生著巨大改變，許多問題得到了有效解決，從而提升了企業的競爭能力。

參考文獻

[1] 劉蘭娟主編.財經管理中的計算機應用（第三版）[M].上海財經大學出版社，2014（3）.

[2] 李經霞.基于Excel的投資項目盈虧平衡分析模型[J].中國市場，2014（29）.

[3] 楊樺.基于Excel的最優投資決策模型設計[J].中國管理信息化，2012，15（9）.

[4] 楊樺.利用Excel制作基于凈現值的投資決策模型[J].中國管理信息化，2011（19）.

[5] 楊樺.基于EXCEL的項目投資決策模型設計[J].科技經濟市場，2010（1）.

[6] 彭宏超.基于凈現值的EXCEL投資決策敏感性分析[J].財會月刊，2014（23）.