Excel2000應用案例之十四

第四章 敏感分析

[本章提要]本章主要通過投資分析等問題，介紹了Excel 2000的模擬運算表、方案和單變量求解的應用，著重說明了單變量模擬運算表和雙變量模擬運算表的操作步驟，在模擬運算表的基礎上進行敏感分析的方法，以及應用方案和單變量求解工具輔助決策的方法。

敏感分析也稱作“What-If分析”，是在財務、會計、管理、統(tǒng)計等應用領域不可缺少的工具。例如在財務分析中，許多指標的計算都要涉及到若干個參數(shù)。像長期投資項目，其償還額與利率、付款期數(shù)、每期付款額度等參數(shù)密切相關。又如固定資產(chǎn)的折舊，與固定資產(chǎn)原值、估計殘值、固定資產(chǎn)的生命周期、折舊計算的期次以及余額遞減速率等密切相關。而作為決策者往往需要定量地了解，當這些參數(shù)變動時對有關指標的影響。這些分析可以利用Excel

2000的模擬運算表工具實現(xiàn)。以下通過投資效益的分析說明有關工具的使用。

4.1 模擬運算表

所謂模擬運算表實際上是工作表中的一個單元格區(qū)域，它可以顯示一個計算公式中某些參數(shù)的值的變化對計算結果的影響。由于它可以將所有不同的計算結果以列表方式同時顯示出來，因而便于查看、比較和分析。根據(jù)分析計算公式中的參數(shù)的個數(shù)，模擬運算表又分為單變量模擬運算表和雙變量模擬運算表。

4.1.1 單變量模擬運算表

單變量模擬運算主要用來分析當其它因素不變時，一個參數(shù)的變化對目標值的影響。例如，要計算一筆貸款的分期償還額，可以使用Excel

2000提供的財務函數(shù)之一PMT。而如果要分析不同的利率對貸款的償還額產(chǎn)生的影響，則可以使用單變量模擬運算表。

假設某公司要貸款1000萬元，年限為10年，目前的年利率為5％，分月償還。則利用PMT函數(shù)可以計算出每月的償還額。其具體操作步驟如下：

在工作表中輸入有關參數(shù)，如圖4－1所示。

在B5單元格輸入計算月償還額的公式：“=PMT(B3/12,B4*12,B2)”

在上述公式中，PMT函數(shù)有三個參數(shù)。第一個參數(shù)是利率，因為要計算的償還額是按月計算的，所以要將年利率除以12，將其轉換成月利率。第二個參數(shù)是還款期數(shù)，同樣的原因需要乘以12。第三個參數(shù)為貸款額。該函數(shù)的計算結果為“-106065.52”，即在年利率為5%，年限為10年的條件下，需每月償還106065.52元。

圖4－1

近幾年來，國家為了宏觀調(diào)控經(jīng)濟的發(fā)展，多次調(diào)整了利率。作為投資決策人員，需要全面了解當利率變動時，對償貸能力的影響。這可以使用單變量模擬運算表實現(xiàn)。其具體操作步驟如下：

選擇某個單元格區(qū)域作為模擬運算表存放區(qū)域，在該區(qū)域的最左列輸入假設的利率變化范圍數(shù)據(jù)。因為該數(shù)據(jù)系列通常是等差或是等比數(shù)列，所以可利用Excel

2000的自動填充功能快速建立。

在模擬運算表區(qū)域的第2列第1行輸入計算月償還額的計算公式。

選定整個模擬運算表區(qū)域。如圖4－2所示。

圖4－2

單擊數(shù)據(jù)菜單中的模擬運算表命令。

這時將彈出模擬運算表對話框，如圖4－3所示。

在模擬運算表對話框的輸入引用列的單元格框中輸入“$B$3”。單擊確定。

所謂引用列的單元格，即模擬運算表的模擬數(shù)據(jù)（最左列數(shù)據(jù)）要代替公式中的單元格地址。本例的模擬運算表是關于利率的模擬數(shù)據(jù)，所以指定$B$3，即年利率所在的單元格為引用列的單元格。為了方便，通常稱其為模擬運算表的列變量。

圖4－3

模擬運算表的計算結果如圖4－4所示。

圖4－4

請注意，這時單元格區(qū)域B8:B16中的公式為“{=表(,B3)}”，表示其是一個以B3為列變量的模擬運算表。與一般的計算公式相似，當改變模擬數(shù)據(jù)時，模擬運算表的數(shù)據(jù)會自動重新計算。

除了用于貸款分析之外，函數(shù) PMT 還可以計算出別的以年金方式付款的支付額。例如，如果需要以按月定額存款方式在20年中存款100000，假設存款年利率為4%，則函數(shù)

PMT 可以用來計算月存款額：“＝PMT(4%/12, 20*12, 0, 100000)”，公式計算結果為“272.65”。即向年利率4%的存款賬戶每月存入272.65元，20年后連本帶利可獲得100000元。

4.1.2 雙變量模擬運算表

當需要其它因素不變時，兩個參數(shù)的變化對目標值的影響時，需要使用雙變量模擬運算表。例如上例，如果不僅要考慮利率的變化，還可以選擇貸款年限，這時需要分析不同的利率和不同的貸款期限對貸款的償還額的影響，這時需要使用雙變量模擬運算表。

雙變量模擬運算表的操作步驟與單變量模擬運算表類似：

選擇某個單元格區(qū)域作為模擬運算表存放區(qū)域，在該區(qū)域的最左列輸入假設的利率變化范圍數(shù)據(jù)；在該區(qū)域的第一行輸入可能的貸款年限數(shù)據(jù)。

在模擬運算表區(qū)域的左上角單元格輸入計算月償還額的計算公式。

選定整個模擬運算表區(qū)域。如圖4－5所示。

圖4－5

單擊數(shù)據(jù)菜單中的模擬運算表命令。

在模擬運算表對話框的輸入引用行的單元格框中輸入“$B$4”；在輸入引用列的單元格框中輸入“$B$3”。單擊確定。

雙變量模擬運算表的計算結果如圖4－6所示。其中B8:F16單元格區(qū)域的計算公式為“{=表(B4,B3)}”，表示其是一個以B4為行變量，B3為列變量的模擬運算表。

圖4－6

4.1.3 敏感分析

利用模擬運算表還可以進一步進行其他方面的敏感分析。下面通過購買某個險種的保險時如何選擇繳款方式，來說明有關敏感分析的操作。

設準備購買某保險10萬元，可以有兩種繳款方式供選擇：一種是躉交，即一次付清105,490元；另一種是分30年付款，每年付6,350元。如果單從付款額來說，后一種付款方式累計繳款190,500元，大大多于躉交的款額。但是對于這種長期投資問題，還必須要考慮利息的收益和利率變動的影響。為此，可以利用Excel

2000提供的現(xiàn)值函數(shù)PV或未來值函數(shù)FV，計算和比較在特定利率情況下兩者的收益。再進一步應用模擬運算表分析利率變動的影響。

首先將有關數(shù)據(jù)輸入到工作表中，再利用PV和FV函數(shù)計算分期付款方式在特定年利率情況下的現(xiàn)值和未來值。這里設年利率為5％，則計算結果如圖4－7所示。

圖4－7

從計算結果可以看出，在年利率為5％的情況下，分期付款方式相當于現(xiàn)在一次付款102,495元。也就是說，在年利率為5％的情況下，采用分期付款方式較好。

近幾年來，國家為了宏觀調(diào)控經(jīng)濟的發(fā)展，曾多次調(diào)整銀行利率。為了比較不同利率對保險收益的影響，可建立以年利率3.50％～5.50％為行模擬數(shù)據(jù)的模擬運算表。如圖4－8所示。

圖4－8

從模擬運算表中可以看出，當年利率為4.75時，兩種繳款方式效果近似，當年利率低于4.75時，宜采用躉交方式；而高于4.75時，宜采用分期付款方式�；蛘哒f，如果有其他年利率大于4.75元的投資途徑時，采用分期付款方式可以獲得更好的收益。

如果還要考查不同支付額的影響，可以使用雙變量模擬運算表。圖4－9是以年利率3.50％～5.50％為行模擬數(shù)據(jù)，支付額6,150～6,550為列模擬數(shù)據(jù)的雙變量模擬運算表。

圖4－9

在模擬運算表的基礎上，還可以進一步進行敏感分析�？梢酝ㄟ^改變除行變量和列變量以外其他參數(shù)的值，分析其對模擬運算表計算結果的影響；而改變函數(shù)名稱，則可以方便地得到其他相關指標的的模擬運算表。

例如，在上例的現(xiàn)值分析中，年限都是30年，如果要考查年限為15年或是25年時，各模擬數(shù)據(jù)的變動情況，可以直接修改年限數(shù)據(jù)，這時整個模擬運算表會自動重新計算。圖4－10即年限為20年時的雙變量模擬運算表。

圖4－10

如果要分析這筆投資30年后的效益，可以使用FV函數(shù)計算其未來值。顯然未來值的計算也是同利率、付款額和年限等參數(shù)相關。這里只需在原來模擬運算表的基礎上，將原來的計算公式中的函數(shù)名由“PV”改成“FV”即可。圖4－11

即為有關分期付款方式未來值的模擬運算表。如果保險回報與之相比過低，而且風險不大時，可以考慮采取其他投資方式。

圖4－11