Excel模擬運算表在財務模型中的應用

中國華電科工集團有限公司

引言

財務模型主要用于大型投資項目的經濟分析，方案比較和敏感性分析是財務模型的重要組成部分。鑒于Excel的靈活性，財務模型一般使用Excel編制。財務模型的方案比較和敏感性分析通常使用VBA編程實現(xiàn)，這要求模型的編制者具備一定的VBA編程基礎。而Excel提供的模擬運算表無須編程，能夠更簡單、直觀地進行計算分析，無疑是更好的選擇。本文介紹了模擬運算表的功能，詳細說明了其在財務模型的方案比較和敏感性分析中的應用。

一、模擬運算表功能簡介

模擬運算表是Excel提供的一種假設分析工具，用于觀察公式中某一個或兩個變量值的變化對計算結果的影響。根據(jù)觀察變量的個數(shù)，模擬運算表分為單變量模擬運算表和雙變量模擬運算表。

（一）單變量模擬運算表

單變量模擬運算表是在工作表中列出一個觀察變量的多個值，分析這些值對計算結果的影響[1]。例如某公司計劃投資一個項目，需貸款1000萬元，還款期5年、等額本息方式還款，求年利率分別為5.0%、5.5%、6.0%、6.6%、7.0%時年還款金額各是多少。此時可以先將貸款1000萬元，還款期5年、利率5%作為基準情形，然后再將各個利率列于模擬運算表中，用單變量模擬運算表計算對應利率下的年還款金額，見圖1。

圖1 單變量模擬運算表

圖1中C8～F8行是各個利率值，即需要觀察的變量。單元格B9直接引用B5的計算結果。選擇B8～F9區(qū)域然后使用模擬運算表，不同利率下的年還款金額就會顯示在C9～F9區(qū)域中。

（二）雙變量模擬運算表

雙變量模擬運算表是在工作表中列出兩個觀察變量的多個值，分析這些值對計算結果的影響[1]。模擬運算表最多只能分析兩個變量的變化情況。上例中如果增加一個觀察變量，例如需要分析在利率5.0%、5.5%、6.0%、6.6%、7.0%和還款期4年、5年、6年的情況下年還款金額的情況，則應該使用雙變量模擬運算表，如圖2所示。

圖2 雙變量模擬運算表

圖2中F2～J2行對應利率，E3～E5列對應還款期，行列的交叉點E2單元格引用B5的計算結果。在“輸入引用行的單元格”輸入基準情形下利率的絕對引用$B$4，在“輸入引用列的單元格”輸入基準情形下還款期的絕對引用$B$3。此時將生成一個二維表格，給出不同利率和還款期組合下的年還款金額。

二、方案比較

財務模型的輸入的假設條件通常有二三十個，關鍵假設條件一般也有七八個。為分析不同假設條件下項目的經濟性，通常需要將一組關鍵假設組合為一個方案，再對不同方案下財務模型的輸出結果進行比較。模擬運算表本身最多只能計算兩個變量的情況，而一個方案中包含的假設條件往往多于兩個，因此不能直接使用模擬運算表進行方案比較。但是，通過設置輔助表，模擬運算表就能夠突破兩個變量的限制，進行含有任意多個假設條件的方案比較。

例如計劃投資建設一電廠項目，已建立財務模型，現(xiàn)需要比較不同EPC金額、利率、還款期和電價條件下的總投資、收益率和凈現(xiàn)值，各種方案的假設條件見圖3。

圖3 方案比較

圖3中A2～F6區(qū)域為輔助表，列出了各個方案的假設條件組合。B2單元格表示當前選擇方案的編號，虛線框包含的B3～B6列顯示了當前所選擇方案的各項假設條件，可稱為“當前方案列”，列中的數(shù)據(jù)需設置為與右側對應方案的數(shù)據(jù)相同。

在編制財務模型時，所有需要引用輔助表中假設條件的公式都要鏈接到“當前方案列”，這一設置是使用模擬運算表進行方案比較的關鍵。B9單元格直接引用B2單元格，表示當前選擇的是第幾個方案。B10～B12列是當前方案下的計算結果，是模擬運算表的輔助列。需要注意的是，根據(jù)財務模型的結構，輔助列中的總投資、收益率和凈現(xiàn)值的具體計算可能分布于財務模型的不同表中，在進行方案比較時B10～B12列只需將計算結果從別的表中引用過來，而不應在輔助列中直接計算，這是使用模擬運算表進行方案比較的第二個關鍵設置。

對B9～F12區(qū)域使用模擬運算表，Excel計算時會將B2單元格的數(shù)值依次替換為C9～F9區(qū)域的數(shù)值，每替換一次，當前方案列的數(shù)據(jù)就會被替換為該數(shù)值對應的方案的假設條件，財務模型就會進行一次對應方案的計算，并將計算結果依次輸出至C10～F12區(qū)域。通過上述方法，方案的數(shù)量及每個方案包含的假設條件的個數(shù)可以任意增減，突破了模擬運算表只能計算兩個變量的限制，并且計算結果能夠隨假設條件的變化動態(tài)更新。

三、敏感性分析

敏感性分析是指從多個不確定性因素中找出對項目經濟效益指標有重要影響的敏感性因素，并分析其對項目經濟效益指標的影響程度和敏感性程度。仍以上述電廠項目為例，分析項目的收益率對EPC金額的敏感性。保持其他假設條件不變，考慮在EPC金額增減5%和10%的情況下對收益率的影響。

如圖4所示，本例中在基準情形下EPC金額為5.4億元，收益率為14.8%。將基準情形的數(shù)據(jù)列于H7～K7行，在I8～I11列直接用公式計算出不同變化率下的EPC金額，J7單元格直接引用I3單元格，再對I7～J11區(qū)域使用單變量模擬運算表，計算結果顯示于J7～J11列。此時已經得到不同EPC金額對應的收益率，然后再增加一列K6～K11，設置公式直接計算出不同EPC金額下的收益率相對于基準情形下收益率的影響。

圖4 敏感性分析

結語

雖然模擬運算表能夠簡單、直觀、快速地進行假設分析，但因為財務模型和模擬運算表自身的特性，在使用模擬運算表進行方案比較和敏感性分析時需要注意以下幾點：

（1）不要破壞財務模型的總體邏輯結構。使用模擬運算表進行方案比較和敏感性分析時，應在單獨的表中進行設置和計算，不能影響基本假設條件下財務模型的計算。

（2）合理地設置輔助表。財務模型中所有需要引用假設條件的公式都應直接或間接鏈接到輔助表的“當前方案列”，模擬運算表的輔助列中的數(shù)據(jù)應該從財務模型的相應位置引用過來，而不應直接在輔助列中計算。

（3）關注Excel的運行性能。財務模型含有大量公式，并且通常需要進行迭代計算，隨著方案數(shù)量的增加計算量將成倍增加，這會影響Excel的運行速度。此時可在Excel“數(shù)據(jù)”選項卡的“計算選項”中選擇“除模擬運算表外，自動重算”，當完成了財務模型的修改，需要進行方案比較時再把“計算選項”改為“自動”，這時所有方案的計算結果將自動更新。

綜合上述分析與實例，可以看出使用模擬運算表進行方案比較和敏感性分析無須VBA編程，可以簡化財務模型的結構，降低編制難度，還能針對假設條件的變化動態(tài)更新，方便、快速地展示計算結果，提高財務模型的運行效率和可讀性。