excel應(yīng)用實(shí)例之二——敏感分析

1、5.125.12ExcelExcel應(yīng)用實(shí)例之二應(yīng)用實(shí)例之二——敏感分析敏感分析[本節(jié)提要提要]本節(jié)主要通過投資分析等問題，介紹了Excel2000的模擬運(yùn)算表、方案和單變量求解的應(yīng)用，著重說明了單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表的操作步驟，在模擬運(yùn)算表的基礎(chǔ)上進(jìn)行敏感分析的方法，以及應(yīng)用方案和單變量求解工具輔助決策的方法。敏感分析也稱作“WhatIf分析”，是在財(cái)務(wù)、會(huì)計(jì)、管理、統(tǒng)計(jì)等應(yīng)用領(lǐng)域不可缺少的工具。例如在財(cái)務(wù)分析中，許多指標(biāo)

2、的計(jì)算都要涉及到若干個(gè)參數(shù)許多指標(biāo)的計(jì)算都要涉及到若干個(gè)參數(shù)。像長(zhǎng)期投資項(xiàng)目，其償還額與利率、付款期數(shù)、每期付款額度等參數(shù)密切相關(guān)。又如固定資產(chǎn)的折舊，與固定資產(chǎn)原值、估計(jì)殘值、固定資產(chǎn)的生命周期、折舊計(jì)算的期次以及余額遞減速率等密切相關(guān)。而作為決策者往往需要定量地了作為決策者往往需要定量地了解，當(dāng)這些參數(shù)變動(dòng)時(shí)對(duì)有關(guān)指標(biāo)的影響。解，當(dāng)這些參數(shù)變動(dòng)時(shí)對(duì)有關(guān)指標(biāo)的影響。這些分析可以利用Excel2000的模擬運(yùn)算表工具實(shí)現(xiàn)。以下通過投資效

3、益的分析說明有關(guān)工具的使用。5.12.15.12.1模擬運(yùn)算表模擬運(yùn)算表所謂模擬運(yùn)算表模擬運(yùn)算表實(shí)際上是工作表中的一個(gè)單元格區(qū)域，它可以顯示一個(gè)計(jì)算公式中某些參數(shù)值的變化對(duì)計(jì)算結(jié)果的影響。由于它可以將所有不同的計(jì)算結(jié)果以列表方式同時(shí)顯示出來，因而便于查看、比較和分析。根據(jù)分析計(jì)算公式中的參數(shù)的個(gè)數(shù)，模擬運(yùn)算表又分為單變量模擬運(yùn)算表單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表。一、單變量模擬運(yùn)算表一、單變量模擬運(yùn)算表單變量模擬運(yùn)算主

4、要用來分析當(dāng)其它因素不變時(shí)，一個(gè)參數(shù)的變化對(duì)目標(biāo)值的影響。例如，要計(jì)算一筆貸款的分期償還額，可以使用Excel2000提供的財(cái)務(wù)函數(shù)之PMT。而如果要分析不同的利率對(duì)貸款的償還額產(chǎn)生的影響，則可以使用單變量模擬運(yùn)算表。假設(shè)某公司要貸款1000萬元，年限為10年，目前的年利率為5%，分月償還。則利用PMT函數(shù)[PMT(ratenperpvfvtype)]可以計(jì)算出每月的償還額。其具體操作步驟如下：(1)在工作表中輸入有關(guān)參數(shù)，如圖5121

5、所示。(2)在B5單元格輸入計(jì)算月償還額的公式：“=PMT(B312B412B2)”在上述公式中，PMT函數(shù)有三個(gè)參數(shù)。第一個(gè)參數(shù)是利率，因?yàn)橐?jì)算的償還額是按月計(jì)算的，所以要將年利率除以12，將其轉(zhuǎn)換成月利率。第二個(gè)參數(shù)是還款期數(shù)，同樣的原因需要乘以12。第三個(gè)參數(shù)為貸款額。該函數(shù)的計(jì)算式中的單元格地址。本例模擬運(yùn)算表是關(guān)于利率的模擬數(shù)據(jù)，所以指定$B$3，即年利率所在的單元格為引用列的單元格。為了方便，通常稱其為模擬運(yùn)算表模擬運(yùn)算表

6、的列變量的列變量。圖5123模擬運(yùn)算表的計(jì)算結(jié)果如圖5124所示。圖5124請(qǐng)注意，這時(shí)單元格區(qū)域B8:B16中的公式為“=表(B3)”，表示其是一個(gè)以B3為列變量的模擬運(yùn)算表。與一般的計(jì)算公式相似，當(dāng)改變模擬數(shù)據(jù)時(shí)，模擬運(yùn)算表的數(shù)據(jù)會(huì)自動(dòng)重新計(jì)算。除了用于貸款分析之外，函數(shù)PMT還可以計(jì)算出別的以年金方式付款的支付額。例如，如果需要以按月定額存款方式在20年中存款100000，假設(shè)存款年利率為4%，則函數(shù)PMT可以用來計(jì)算月存款額：“