Excel2000應(yīng)用案例之十六

4.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)部門(mé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

2000的目標(biāo)搜索技術(shù)實(shí)現(xiàn)。

4.3.1 單變量求解

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

2000提供的目標(biāo)搜索技術(shù)，即單變量求解命令可以方便計(jì)算出來(lái)。

首先將有關(guān)數(shù)據(jù)和公式輸入到工作表中，如上例的圖4－12所示。請(qǐng)注意，使用單變量求解命令的關(guān)鍵是在工作表上建立正確的數(shù)學(xué)模型，即通過(guò)有關(guān)的公式和函數(shù)描述清楚相應(yīng)數(shù)據(jù)之間的關(guān)系。例如該表中產(chǎn)品銷(xiāo)售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額分別是按下述公式計(jì)算的：

產(chǎn)品銷(xiāo)售利潤(rùn)＝產(chǎn)品銷(xiāo)售收入－產(chǎn)品銷(xiāo)售成本－產(chǎn)品銷(xiāo)售費(fèi)用－產(chǎn)品銷(xiāo)售稅金

營(yíng)業(yè)利潤(rùn)＝產(chǎn)品銷(xiāo)售利潤(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)品銷(xiāo)售成本、產(chǎn)品銷(xiāo)售費(fèi)用等數(shù)據(jù)也是根據(jù)產(chǎn)品銷(xiāo)售收入按一定公式計(jì)算的。這是保證分析結(jié)果有效和正確的前提。應(yīng)用單變量求解命令的具體操作步驟如下：

選定目標(biāo)單元格C15，單擊工具菜單中的單變量求解命令。

這時(shí)彈出單變量求解對(duì)話框，如圖4－20所示。

圖4－20

Excel 2000自動(dòng)將當(dāng)前單元格的地址“C15”填入到目標(biāo)單元格框中；在目標(biāo)值框中輸入預(yù)定的目標(biāo)“145000”；在可變單元格框中輸入產(chǎn)品銷(xiāo)售收入所在的單元格地址“C3”，也可指定可變單元格后，直接單擊該C3單元格。單擊確定。

這時(shí)彈出單變量求解狀態(tài)對(duì)話框，說(shuō)明已找到一個(gè)解，并與所要求的解一致。

單擊確定按鈕，可以看到求解的結(jié)果如圖4－21所示。

圖4－21

從圖中可以看出，在其他條件基本保持不變的情況下，要使利潤(rùn)總額增加到145000元，即增加3545元，其產(chǎn)品銷(xiāo)售收入需增加到1441020元，即增加38320元。

4.3.2 圖上求解

目標(biāo)搜索技術(shù)還可以利用圖形直觀地進(jìn)行。例如上例，如果要分析使利潤(rùn)總額增加到146000元，相應(yīng)的銷(xiāo)售收入需增加到多少元，可按下述步驟操作：

選定銷(xiāo)售收入和利潤(rùn)總額等數(shù)據(jù)所在的單元格，這里選定銷(xiāo)售收入、銷(xiāo)售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額等數(shù)據(jù)。單擊圖表向?qū)О粹o，按提示制作一個(gè)柱形圖。為了便于查看，在步驟之2中，選定系列選項(xiàng)卡，在分類(lèi)（X）軸標(biāo)志選項(xiàng)中選定銷(xiāo)售收入、銷(xiāo)售利潤(rùn)、營(yíng)業(yè)利潤(rùn)和利潤(rùn)總額標(biāo)志所在的單元格。

慢雙擊利潤(rùn)總額數(shù)據(jù)系列，如圖4－22所示。

圖4－22

將鼠標(biāo)指向利潤(rùn)總額數(shù)據(jù)系列的上沿，并向上拖拽，直到其顯示數(shù)據(jù)為所需的146000為止，注意這時(shí)在Y坐標(biāo)軸相應(yīng)的數(shù)據(jù)位置會(huì)出現(xiàn)一個(gè)橫線，指示當(dāng)前數(shù)據(jù)的大小。如圖4－23所示。

圖4－23

這時(shí)將彈出單變量求解對(duì)話框，Excel 2000自動(dòng)將利潤(rùn)總額單元格的地址“C15”填入到目標(biāo)單元格框，將“146000”填入到目標(biāo)值框中。

在可變單元格框中輸入銷(xiāo)售收入所在的單元格地址“C3”，也可指定可變單元格后，直接單擊C3單元格。單擊確定。計(jì)算結(jié)果如圖4－24所示。

圖4－24

從圖中可以看到銷(xiāo)售收入需增加到1451831元，即增加49131元，才能使利潤(rùn)總額增加4545元，達(dá)到利潤(rùn)總額增加到146000元的目標(biāo)。

4.3.3 其他應(yīng)用

利用目標(biāo)搜索技術(shù)可以求解許多類(lèi)似的問(wèn)題。例如，利用PMT函數(shù)，可以根據(jù)貸款額、利率和周期方便地計(jì)算出每期的付款額。但是反過(guò)來(lái)，已知某企業(yè)近5年每月償還貸款的能力為100000元，要計(jì)算其可以承受的貸款額度，就需要掌握更多的函數(shù)和計(jì)算方法。但是使用目標(biāo)搜索技術(shù)則可以直接求解。再如在宏觀經(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ù) ，給定 的值，反過(guò)來(lái)求解 。一般情況下可以按照 與 的依賴關(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ù)還可以直接求各種方程，特別是非線性方程的根。在數(shù)值分析中解任意方程通常有疊代法、割線法、半間距法等多種算法求解，但大多較為復(fù)雜。而利用Excel

2000的單變量求解命令則是求解方程的方便工具。例如要求解下述非線性方程的根：

其具體操作步驟如下：

設(shè)用A1單元格存放 的解。選定A1單元格，并將其命名為X。

選定B1單元格，并在其中輸入公式：“=2*X^3-5*X^2+7*X-10”。此時(shí)因?yàn)锳1單元格的值為空，故X的值按0計(jì)算，所以B1單元格的值為“-10”。

單擊工具菜單中的單變量求解命令。在單變量求解對(duì)話框中，Excel 2000自動(dòng)將當(dāng)前單元格B1填入目標(biāo)單元格框；在目標(biāo)值框中輸入“0”；指定可變單元格為A1。如圖4－25所示。單擊確定。

圖4－25

這時(shí)出現(xiàn)單變量求解狀態(tài)對(duì)話框，顯示已求得一個(gè)解。這時(shí)A1單元格顯示的是求得的方程的一個(gè)根，近似為2。如圖4－26所示。

圖4－26

通過(guò)本章內(nèi)容的學(xué)習(xí)，應(yīng)掌握模擬運(yùn)算表、方案和單變量求解等輔助決策分析工具的應(yīng)用。通過(guò)上述工具的應(yīng)用使得管理決策數(shù)量化、科學(xué)化，提高管理水平。