經(jīng)濟(jì)軟件分析第八章What-If分析

1、What-If分析”，是在財(cái)務(wù)、會(huì)計(jì)、管理、統(tǒng)計(jì)等經(jīng)濟(jì)應(yīng)用 領(lǐng)域不可缺少的工具。例如在財(cái)務(wù)分析中，許多指標(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)的影響。這些 分析可以利用Excel的模擬運(yùn)算表工具實(shí)現(xiàn)。以下通過(guò)投資效 益的分析說(shuō)明有關(guān)工具的使用。,第八章 What-If分析,8.1 模擬運(yùn)算表 所謂模擬運(yùn)算表實(shí)際上是工作表中的一個(gè)單元格區(qū)域，它 可以顯示一個(gè)計(jì)算

2、公式中某些參數(shù)的值的變化對(duì)計(jì)算結(jié)果的影 響。由于它可以將所有不同的計(jì)算結(jié)果以列表方式同時(shí)顯示出 來(lái)，因而便于查看、比較和分析。根據(jù)分析計(jì)算公式中的參數(shù) 的個(gè)數(shù)，模擬運(yùn)算表又分為單變量模擬運(yùn)算表和雙變量模擬運(yùn) 算表。 8.1.1 單變量模擬運(yùn)算表 單變量模擬運(yùn)算主要用來(lái)分析當(dāng)其它因素不變時(shí)，一個(gè)參 數(shù)的變化對(duì)目標(biāo)值的影響。例如，要計(jì)算一筆貸款的分期償還 額，可以使用Excel提供的財(cái)務(wù)函數(shù)之一PMT。而如果要分析 不同的利率對(duì)貸款的償還額產(chǎn)生的影響，則可以使用單變量模 擬運(yùn)算表。,8.1.2 雙變量模擬運(yùn)算表 當(dāng)需要其它因素不變時(shí)，兩個(gè)參數(shù)的變化對(duì)目標(biāo)值的影響 時(shí)，需要使用雙變量模擬運(yùn)算表。例如

3、上例，如果不僅要考慮 利率的變化，還可以選擇貸款年限，這時(shí)需要分析不同的利率 和不同的貸款期限對(duì)貸款的償還額的影響，這時(shí)需要使用雙變 量模擬運(yùn)算表。 雙變量模擬運(yùn)算表的操作步驟與單變量模擬運(yùn)算表類似： 選擇某個(gè)單元格區(qū)域作為模擬運(yùn)算表存放區(qū)域，在該區(qū)域的最 左列輸入假設(shè)的利率變化范圍數(shù)據(jù)；在該區(qū)域的第一行輸入可 能的貸款年限數(shù)據(jù)。 在模擬運(yùn)算表區(qū)域的左上角單元格輸入計(jì) 算月償還額的計(jì)算公式。選定整個(gè)模擬運(yùn)算表區(qū)域。,8.1.3 敏感分析 利用模擬運(yùn)算表還可以進(jìn)一步進(jìn)行其他方面的敏感分析。 下面通過(guò)購(gòu)買某個(gè)險(xiǎn)種的保險(xiǎn)時(shí)如何選擇繳款方式，來(lái)說(shuō)明有 關(guān)敏感分析的操作。 設(shè)準(zhǔn)備購(gòu)買某保險(xiǎn)10萬(wàn)元，可以

4、有兩種繳款方式供選擇： 一種是躉交，即一次付清105,490元；另一種是分30年付款， 每年付6,350元。如果單從付款額來(lái)說(shuō)，后一種付款方式累計(jì) 繳款190,500元，大大多于躉交的款額。但是對(duì)于這種長(zhǎng)期投 資問(wèn)題，還必須要考慮利息的收益和利率變動(dòng)的影響。為此， 可以利用Excel提供的現(xiàn)值函數(shù)PV或未來(lái)值函數(shù)FV，計(jì)算和比 較在特定利率情況下兩者的收益。再進(jìn)一步應(yīng)用模擬運(yùn)算表分 析利率變動(dòng)的影響。,在模擬運(yùn)算表的基礎(chǔ)上，還可以進(jìn)一步進(jìn)行敏感分析。可 以通過(guò)改變除行變量和列變量以外其他參數(shù)的值，分析其對(duì)模 擬運(yùn)算表計(jì)算結(jié)果的影響；而改變函數(shù)名稱，則可以方便地得 到其他相關(guān)指標(biāo)的的模擬運(yùn)算表。

5、,8.2 方案分析 模擬運(yùn)算表主要用來(lái)考查一個(gè)或兩個(gè)決策變量的變動(dòng)對(duì)于 分析結(jié)果的影響，但對(duì)于一些更復(fù)雜的問(wèn)題，常常需要考查更 多的因素。例如為了達(dá)到公司的預(yù)算目標(biāo)，可以從多種途徑入 手?？梢酝ㄟ^(guò)增加廣告促銷，可以提高價(jià)格增收，可以降低包 裝費(fèi)、材料費(fèi)，可以減少非生產(chǎn)開(kāi)支等等。利用Excel提供的 方案管理器，可以模擬為達(dá)到目標(biāo)而選擇的不同方式。對(duì)于每 個(gè)變量改變的結(jié)果都被稱之為一個(gè)方案，根據(jù)多個(gè)方案的對(duì)比 分析，可以考查不同方案的優(yōu)劣，從中選擇最合適公司目標(biāo)的 方案。,8.2.1 創(chuàng)建方案 創(chuàng)建方案是方案分析的關(guān)鍵，應(yīng)根據(jù)實(shí)際問(wèn)題的需要和可 行性來(lái)創(chuàng)建一組方案。在創(chuàng)建方案之前，為了使創(chuàng)建的方

6、案能 夠明確地顯示有關(guān)變量，以及為了將來(lái)進(jìn)行方案總結(jié)時(shí)便于閱 讀方案總結(jié)報(bào)告，需要先給有關(guān)變量所在的單元格命名。 在存放有關(guān)變量數(shù)據(jù)的單元格右側(cè)單元格中輸入相應(yīng)指標(biāo) 的名稱。選定要命名的單元格區(qū)域和單元格名稱區(qū)域。 單擊插入菜單中的名稱命令，然后單擊指定子命令。在名 稱在框中選定最右列復(fù)選框，單擊確定按鈕。 單擊工具菜單中的方案命令，將彈出方案管理器對(duì)話框。 由于現(xiàn)在還沒(méi)有任何方案，所以方案管理器對(duì)話框中間顯示“未 定義方案”的信息。,8.2.2 瀏覽、編輯方案 方案創(chuàng)建好以后，可以根據(jù)需要查看每個(gè)方案對(duì)利潤(rùn)總額 數(shù)據(jù)的影響。其具體操作步驟是： 在方案管理器對(duì)話框的方案列表中，選定要查看的方案

7、。 單擊方案管理器對(duì)話框的顯示按鈕，再單擊確定。這時(shí)工作表 中將顯示該模擬方案的計(jì)算結(jié)果。 如果需要修改某個(gè)方案，其具體操作步驟是：在方案管理 器對(duì)話框的方案列表中，選定要修改的方案。單擊方案管理器 對(duì)話框的編輯按鈕。這時(shí)會(huì)彈出與添加方案一樣的編輯方案對(duì) 話框?？梢愿鶕?jù)需要修改方案名稱，改變可變單元格以及重新 輸入可變單元格的變量值。,8.2.3 方案總結(jié) 上述瀏覽方式只能一個(gè)方案一個(gè)方案地查看，如果將所有 方案匯總到一個(gè)工作表中，然后再對(duì)不同方案的影響比較分 析，這對(duì)于幫助決策人員綜合考查各種方案效果更好。Excel 的方案工具可以根據(jù)需要對(duì)多個(gè)方案創(chuàng)建方案總結(jié)，以便決策 人員做出更明智的決

8、策。具體操作步驟如下： 單擊工具菜單中的方案命令，將彈出方案管理器對(duì)話框。 單擊方案管理器對(duì)話框中的總結(jié)按鈕，將彈出方案總結(jié)對(duì)話 框，根據(jù)需要在方案總結(jié)對(duì)話框中選擇適當(dāng)?shù)慕Y(jié)果類型，一般 情況下可選擇方案總結(jié)，如果需要對(duì)報(bào)告進(jìn)一步分析，可選方 案數(shù)據(jù)透視表。在結(jié)果單元格框中指定利潤(rùn)總額所在的單元 格。單擊確定按鈕。,在方案總結(jié)中，“當(dāng)前值”列顯示的是在建立方案匯總時(shí)， 可變單元格原來(lái)的數(shù)值。每組方案的可變單元格均以灰色底紋 突出顯示。根據(jù)各方案的模擬數(shù)據(jù)計(jì)算出的目標(biāo)值也同時(shí)顯 示在總結(jié)中（單元格區(qū)域D13:G13），便于管理人員比較分 析。比較三個(gè)方案的結(jié)果單元格“利潤(rùn)總額”的數(shù)值，可以看出 “

9、降低成本”方案效果最好，“減少費(fèi)用”方案次之，“增加收入”方 案對(duì)目標(biāo)值的影響最小。,8.3 目標(biāo)搜索 “What-If”分析方法主要采用模擬計(jì)算的方法解決不同因素 或不同方案對(duì)目標(biāo)的影響。這對(duì)于計(jì)劃人員、決策人員都是常 用的工具。但是對(duì)于生產(chǎn)的組織和實(shí)施人員來(lái)說(shuō)，經(jīng)常遇到的 是相反的問(wèn)題。例如，根據(jù)上級(jí)有關(guān)部門制定的某個(gè)目標(biāo)，分 析要實(shí)現(xiàn)該目標(biāo)，需要實(shí)現(xiàn)的具體指標(biāo)，再逐一落實(shí)。當(dāng)然也 可以根據(jù)每個(gè)具體指標(biāo)，進(jìn)一步分析要達(dá)到的更詳細(xì)的指標(biāo)。 在進(jìn)行這樣的分析時(shí)，往往由于計(jì)算方法較為復(fù)雜或是許多因 素交織在一起而很難進(jìn)行。這可以利用Excel的目標(biāo)搜索技術(shù) 實(shí)現(xiàn)。,8.3.1 單變量求解 仍以上

10、一節(jié)的蒙龍公司損益表為例。假設(shè)該公司下個(gè)月的 利潤(rùn)總額指標(biāo)定為145000，要考查當(dāng)其他條件基本保持不變的 情況下，銷售收入需要增加到多少。由于利潤(rùn)總額與銷售收入 的關(guān)系不是簡(jiǎn)單的同量增加的關(guān)系（即不是銷售收入增加1 元，利潤(rùn)總額也增加1元），也不是簡(jiǎn)單的同比例增長(zhǎng)關(guān)系 （即不是銷售收入增加1元，利潤(rùn)總額按70%比例增加0.7 元），而可能要涉及到其它多方面因素。比如說(shuō)，銷售收入增 加，可能需要增加銷售人員的獎(jiǎng)金、差旅費(fèi)、運(yùn)輸費(fèi)和裝卸費(fèi) 等開(kāi)支等等。所以手工計(jì)算是比較復(fù)雜的，需要根據(jù)工作表中 的計(jì)算公式一項(xiàng)一項(xiàng)的倒推計(jì)算。而Excel提供的目標(biāo)搜索技 術(shù)，即單變量求解命令可以方便計(jì)算出來(lái)。,注

11、意， 使用單變量求解命令的關(guān)鍵是在工作表上建立正確的數(shù)學(xué)模 型，即通過(guò)有關(guān)的公式和函數(shù)描述清楚相應(yīng)數(shù)據(jù)之間的關(guān)系。 例如該表中產(chǎn)品銷售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額分別是按下述 公式計(jì)算的： 產(chǎn)品銷售利潤(rùn)產(chǎn)品銷售收入產(chǎn)品銷售成本產(chǎn)品銷售費(fèi)用產(chǎn)品銷售稅金 營(yíng)業(yè)利潤(rùn)產(chǎn)品銷售利潤(rùn)其它業(yè)務(wù)利潤(rùn)管理費(fèi)用財(cái)務(wù)費(fèi)用 利潤(rùn)總額營(yíng)業(yè)利潤(rùn)投資收益營(yíng)業(yè)外收入營(yíng)業(yè)外支出 而產(chǎn)品銷售成本、產(chǎn)品銷售費(fèi)用等數(shù)據(jù)也是根據(jù)產(chǎn)品銷售收入 按一定公式計(jì)算的。這是保證分析結(jié)果有效和正確的前提。,8.3.2 圖上求解 目標(biāo)搜索技術(shù)還可以利用圖形直觀地進(jìn)行。例如上例，如 果要分析使利潤(rùn)總額增加到146000元，相應(yīng)的銷售收入需增加 到多少元，

12、可按下述步驟操作： 選定銷售收入和利潤(rùn)總額等數(shù)據(jù)所在的單元格，這里選定 銷售收入、銷售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額等數(shù)據(jù)。單擊圖表 向?qū)О粹o，按提示制作一個(gè)柱形圖。為了便于查看，在步驟之 2中，選定系列選項(xiàng)卡，在分類（X）軸標(biāo)志選項(xiàng)中選定銷售收 入、銷售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額標(biāo)志所在的單元格。,8.3.3 其他應(yīng)用 利用目標(biāo)搜索技術(shù)可以求解許多類似的問(wèn)題。例如，利用 PMT函數(shù)，可以根據(jù)貸款額、利率和周期方便地計(jì)算出每期的 付款額。但是反過(guò)來(lái)，已知某企業(yè)近5年每月償還貸款的能力 為100000元，要計(jì)算其可以承受的貸款額度，就需要掌握更多 的函數(shù)和計(jì)算方法。但是使用目標(biāo)搜索技術(shù)則可以直接求解。

13、再如在宏觀經(jīng)濟(jì)分析中要求控制投資規(guī)模，在固定資產(chǎn)投資總 額降低5%的目標(biāo)下，相應(yīng)的自籌投資應(yīng)控制在多少。這可 能需要涉及到諸多因素，例如預(yù)算內(nèi)投資、貸款投資、利用外 資投資、國(guó)民生產(chǎn)總值、物價(jià)指數(shù)等等，而且這些因素之間還 存在著相互制約的關(guān)系，用手工計(jì)算是相當(dāng)復(fù)雜的。利用目標(biāo) 搜索技術(shù)，只要在工作表中建立了上述方程也可以直接求解。,上述這些問(wèn)題歸納起來(lái)都是數(shù)學(xué)上的求解反函數(shù)問(wèn)題，即 對(duì)已有的函數(shù) ，給定y 的值，反過(guò)來(lái)求解x 。一般情 況下可以按照 與 的依賴關(guān)系，構(gòu)造一個(gè)反函數(shù) 。但是當(dāng)變量 之間的依賴關(guān)系較為復(fù)雜，特別是對(duì)于非線性函數(shù)，構(gòu)造反函 數(shù)的工作也是較為復(fù)雜繁瑣的。而利用目標(biāo)搜索技術(shù)，則可以 利用直接函數(shù)方便地完成反函數(shù)的計(jì)算。 利用目標(biāo)搜索技術(shù)還可以直接求各種方程，特別是非