Excel在經(jīng)濟管理中的應用 第三章

第3章

Excel在財務管理與分析中的應用3.2Excel在財務預測中的應用預測技術(shù)是指科學預測過程中所運用的方法和手段的總稱。預測技術(shù)水平的高低直接影響預測水平，影響決策的成效。在我國，現(xiàn)有的預測技術(shù)一般分為定性預測和定量預測兩大類。在計算機幫助下，可以很方便地進行定量分析，本節(jié)主要介紹幾種主要的定量預測方法。3.2.1利用數(shù)據(jù)分析工具解決預測問題1．移動平均法移動平均法是一種改良的算術(shù)平均法，是一種最簡單的自適應預測模型。它根據(jù)近期數(shù)據(jù)對預測值影響較大，而遠期數(shù)據(jù)對預測值影響較小的事實，把平均數(shù)逐期移動。移動期數(shù)的大小視具體情況而定，移動期數(shù)少，能快速地反映變化，但不能反映變化趨勢；移動期數(shù)多，能反映變化趨勢，但預測值帶有明顯的滯后偏差。3.2Excel在財務預測中的應用【例15】某化妝品2001—2008年銷售量如圖3—26所示，試利用移動平均法預測2009年化妝品銷售量（單位：萬元）。預測步驟如下：（1）從“工具”菜單中選擇“加載宏”命令，打開“加載宏”對話框，在其中選擇“分析工具庫”選項，單擊“確定”，如圖3—27所示。（2）從“工具”菜單中選擇“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，在其中選擇“移動平均”選項，如圖3—28所示，單擊“確定”，打開“移動平均”對話框。（3）在“移動平均”對話框中，在“輸入?yún)^(qū)域”框中輸入“$B$3:$B$10”，“間隔”框中輸入“3”表明是進行3期移動平均，“輸出區(qū)域”框中輸入“$C$4”，同時勾選“圖表輸出”選項，如圖3—29所示。（4）單擊“確定”，則運算結(jié)果就顯示在單元格區(qū)域C6:C11中，同時圖表也顯示出來，如圖3—30所示。其中，單元格C11中的預測數(shù)據(jù)415萬元即為2009年的預測銷售量。3.2Excel在財務預測中的應用2．指數(shù)平滑法指數(shù)平滑法是一種改良的加權(quán)平均法，它根據(jù)最近時期的實際數(shù)據(jù)和預測數(shù)據(jù)，并借助于平滑系數(shù)進行預測。計算公式為：Ft=α·Dt-1+(1-α)·Ft-1或Ft=(1-β)·Dt-1+β·Ft-1參數(shù)說明：Ft-1—上一期預測數(shù);Ft—新一期（計劃期）的預測數(shù);Dt-1—上期的實際數(shù)據(jù);α—平滑系數(shù)（0≤α≤1）;β—阻尼系數(shù)（0≤β≤1），β=1-α。Excel中的指數(shù)平滑法需要使用阻尼系數(shù)，阻尼系數(shù)越小，近期實際數(shù)對預測結(jié)果的影響越大，反之，阻尼系數(shù)越大，近期實際數(shù)對預測結(jié)果的影響越小。3.2Excel在財務預測中的應用【例16】現(xiàn)有某汽車零配件公司2008年12個月的銷售數(shù)據(jù)，如圖3—31所示，假定阻尼系數(shù)為0.9，試利用指數(shù)平滑法預測2009年1月的銷售量。預測步驟如下：（1）從“工具”菜單中選擇“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，如圖3—32所示，在其中選擇“指數(shù)平滑”選項，單擊“確定”，打開“指數(shù)平滑”對話框。（2）在“指數(shù)平滑”對話框中，在“輸入?yún)^(qū)域”框中輸入“$B$3:$B$14”，“阻尼系數(shù)”框中輸入“0.9”，“輸出區(qū)域”框中輸入“$C$4”，同時勾選“圖表輸出”選項，如圖3—33所示。（3）單擊“確定”，則運算結(jié)果就顯示在單元格區(qū)域C5:C15中，同時圖表也顯示出來，如圖3—34所示。其中，單元格C15中的預測數(shù)據(jù)132萬件即為2009年1月的預測銷售量。3.2Excel在財務預測中的應用3．回歸法利用公式法進行回歸分析是非常有用的一種預測方法，它既可以對一元線性或多元線形問題進行回歸預測分析，也可以對某些可以轉(zhuǎn)化為線性的非線性問題進行回歸分析，避免了圖表法僅能解決一元線性或非線性回歸問題的局限。回歸分析（regressionanalysis)是確定兩種或兩種以上變數(shù)間相互依賴的定量關(guān)系的一種統(tǒng)計分析方法。運用十分廣泛，回歸分析按照涉及的自變量的多少，可分為一元回歸分析和多元回歸分析；按照自變量和因變量之間的關(guān)系類型，可分為線性回歸分析和非線性回歸分析。如果在回歸分析中，只包括一個自變量和一個因變量，且二者的關(guān)系可用一條直線近似表示，這種回歸分析稱為一元線性回歸分析。如果回歸分析中包括兩個或兩個以上的自變量，且因變量和自變量之間是線性關(guān)系，則稱為多元線性回歸分析。此外，在SPSS的結(jié)果輸出里，還可以匯報R2，F(xiàn)檢驗值和T檢驗值。R2又稱為方程的確定性系數(shù)（coefficientofdetermination），表示方程中變量X對Y的解釋程度。R2取值在0到1之間，越接近1，表明方程中X對Y的解釋能力越強。通常將R2乘以100%來表示回歸方程解釋Y變化的百分比。F檢驗是通過方差分析表輸出的，通過顯著性水平（significantlevel）檢驗回歸方程的線性關(guān)系是否顯著。一般來說，顯著性水平在0.05以下，均有意義。（1）線性趨勢情況下的預測3.2Excel在財務預測中的應用【例17】現(xiàn)有某造紙廠2008年12個月的銷售數(shù)據(jù)，如圖3—35左側(cè)所示。以時間作為自變量，用X表示；銷售量作為因變量，用Y表示；并假設(shè)它們之間有如下線性關(guān)系：Y=a+bX，試利用回歸分析法預測2009年1月的銷售量。預測步驟如下：①從“工具”菜單中選擇“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，如圖3—36所示，在其中選擇“回歸”選項，單擊“確定”，打開“回歸”對話框。②在“回歸”對話框中，在“Y值輸入?yún)^(qū)域”框中輸入“$B$3:$B$14”，在“X值輸入?yún)^(qū)域”框中輸入“$A$3:$A$14”,勾選“置信度”選項，設(shè)置為95%，在“輸出選項”中選“輸出區(qū)域”，輸入“$E$1”，然后根據(jù)實際需要，勾選其他選項，如圖3—37所示。③單擊“確定”，回歸分析的摘要就輸出在該工作表上，如圖3—35右側(cè)所示。④由結(jié)果可以看出：相關(guān)系數(shù)R2的值為0.975894283（單元格F5中的數(shù)據(jù)），說明因變量與自變量之間相關(guān)性很高；其他統(tǒng)計檢測也達到相應的標準，所以可以使用回歸方程Y=a+bX來進行預測。截距a的值在單元格F17中，斜率b的值在單元格F18中，在單元格B15中輸入公式“=F17+F18*13”，即得到第13個月（即2009年1月份）的銷售量預測值為2848噸。3.2Excel在財務預測中的應用（2）非線性趨勢情況下的銷售預測當歷史數(shù)據(jù)的變化趨勢反映的是一條曲線時，就不能直接用回歸工具進行預測分析，此時必須進行相應的變形處理，將非線性問題轉(zhuǎn)化為線性問題后再進行分析。【例18】現(xiàn)有某公司2008年新上市產(chǎn)品的銷售量，如圖3—38左側(cè)所示。將時間作為自變量，銷售量作為因變量，假設(shè)銷售量與時間的關(guān)系為：Y=a×bX,Y為銷售量，X為時間，試利用回歸分析法預測2009年1月的銷售量。預測步驟如下：①通過對數(shù)變換將銷售量與時間的關(guān)系轉(zhuǎn)化為線性關(guān)系：ln(Y)=ln(a)+ln(b)·X。在單元格C3中輸入公式“=LN(B3)”，并將公式復制到C4:C13單元格中，得到銷售量Y的自然對數(shù)。②從“工具”菜單中選擇“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，如圖3—39所示，在其中選擇“回歸”選項，單擊“確定”，打開“回歸”對話框。3.2Excel在財務預測中的應用③在“回歸”對話框中，在“Y值輸入?yún)^(qū)域”框中輸入“$C$3:$C$14”，在“X值輸入?yún)^(qū)域”框中輸入“$A$3:$A$14”,在“輸出選項”中選“輸出區(qū)域”，輸入“$E$1”，然后根據(jù)實際需要，勾選其他選項，如圖3—40所示。④單擊“確定”，回歸分析的摘要就輸出在該工作表上，如圖3—38右側(cè)所示。⑤由結(jié)果可以看出：相關(guān)系數(shù)R2的值為0.9987914（單元格F5中的數(shù)據(jù)），說明因變量與自變量之間相關(guān)性很高；其他統(tǒng)計檢測也達到相應的標準，所以可以使用回歸方程ln(Y)=ln(a)+ln(b)·X來進行預測。截距l(xiāng)n(a)的值在單元格F17中，斜率ln(b)的值在單元格F18中，在單元格B15中輸入公式“=EXP(F17+F18*13)”，即得到第13個月即2009年1月的銷售量預測值為580.596萬件。這里，EXP函數(shù)的功能是計算自然對數(shù)ln函數(shù)的反函數(shù)，即返回e的n次冪。3.2Excel在財務預測中的應用3.2.2利用規(guī)劃求解工具解決預測問題規(guī)劃問題涉及眾多的生產(chǎn)或經(jīng)營領(lǐng)域的常見問題。例如運輸?shù)恼{(diào)度問題，再如原料的恰當搭配問題，還有農(nóng)作物的合理布局問題。Excel提供的規(guī)劃求解工具能夠非常方便地幫助我們完成這些工作。1．規(guī)劃求解工具的使用范圍規(guī)劃求解工具可以分析線性、非線性和整型這三類優(yōu)化問題。3.2Excel在財務預測中的應用2．規(guī)劃求解工具的使用方法（1）規(guī)劃求解工具的安裝“規(guī)劃求解”加載宏是Excel的一個可選安裝模塊，如果在安裝MicrosoftOffice時采用“典型安裝”，則“規(guī)劃求解”工具沒有被安裝，只有在選擇“完全安裝”或者“定制安裝”時才可選擇安裝這個模塊。所以在使用前，務必確認Excel的該功能已被成功安裝。在安裝完成進入Excel后，單擊“工具”菜單，選擇“加載宏”項，在“加載宏”對話框中選定“規(guī)劃求解”復選框，然后單擊“確定”按鈕，則系統(tǒng)就安裝和加載了“規(guī)劃求解”工具，并可以使用它了。3.2Excel在財務預測中的應用（2）規(guī)劃求解工具的操作步驟雖然規(guī)劃問題種類繁多，但是其所要解決的問題大致可以分成兩類：一類是確定了某個任務，研究如何使用最少的人力、物力和財力去完成它；另一類是已經(jīng)有了一定數(shù)量的人力、物力和財力，研究如何使它們獲得最大的收益。從數(shù)學角度來看，規(guī)劃問題都有下述共同特征：決策變量、約束條件、目標。如果約束條件和目標函數(shù)都是線性函數(shù)，則稱作線性規(guī)劃；否則為非線性規(guī)劃。如果要求決策變量的值為整數(shù)，則稱為整數(shù)規(guī)劃。求解規(guī)劃問題的首要問題是將實際問題數(shù)學化、模型化。即將實際問題通過一組決策變量、一組用不等式或等式表示的約束條件以及目標函數(shù)來表示。這是求解規(guī)劃問題的關(guān)鍵，然后即可應用Excel的規(guī)劃求解工具求解。3.5

Excel在營運資金管理中的應用3.5.3存貨的經(jīng)濟訂貨批量決策模型存貨的決策涉及多方面的內(nèi)容，包括決定進貨項目、選擇供貨單位、決定進貨時間和決定進貨批量等，其中最常見的存貨決策是確定經(jīng)濟訂貨批量。所謂經(jīng)濟訂貨批量是指使存貨的總成本最低的一次訂貨批量。1．基本的經(jīng)濟訂貨批量模型基本的經(jīng)濟訂貨批量模型建立在下列假設(shè)基礎(chǔ)之上：（1）企業(yè)能夠瞬時補充存貨；（2）存貨能集中到貨；（3）不允許缺貨；（4）一定時期的存貨總需求量確定；（5）存貨的單價保持不變。3.5

Excel在營運資金管理中的應用在這些假設(shè)前提下，總存貨費用為C，

其中，Q為訂貨批量；D為一定時期存貨的需求量；A為一次訂貨費；

P為存貨單價；K為存貨的存儲費率，PK為單位存儲費用。C對Q求導數(shù)，并令=0，即得存貨的經(jīng)濟訂貨批量為Q*

在此基礎(chǔ)上，還可以進一步計算出一定時期最佳的訂貨次數(shù)為N*

一定時期存貨的最低訂儲費用（訂貨費用和儲存費用合計）為

T*例44某企業(yè)全年需要某種材料5000公斤，一次訂貨費用20元，材料單價25元/公斤，材料的存儲費率為10%，求該材料的經(jīng)濟訂貨批量、全年訂貨次數(shù)和最低訂儲費用。3.5

Excel在營運資金管理中的應用4．有數(shù)量折扣情況下的經(jīng)濟訂貨批量決策模型隨著企業(yè)訂貨數(shù)量的增加，供貨商一般會降低單位產(chǎn)品的價格，給予價格上的優(yōu)惠，這就涉及有數(shù)量折扣情況下的經(jīng)濟訂貨批量決策問題。供應商所提供的數(shù)量折扣有兩種形式：（1）非連續(xù)價格形式的折扣優(yōu)惠非連續(xù)價格形式的折扣優(yōu)惠指的是當達到一定的訂貨批量限度后，訂貨量的單價整體降低的一種折扣優(yōu)惠形式。在這種情況下，進行訂貨批量決策的步驟是：首先分別計算不同折扣價格下的經(jīng)濟訂貨批量（按照傳統(tǒng)的經(jīng)濟訂貨批量模型計算），并判斷這些經(jīng)濟訂貨批量是否有效（即計算出某經(jīng)濟訂貨批量是否與該折扣區(qū)間的價格相符）；其次，計算有效的經(jīng)濟訂貨批量下的總存貨費用，并同時計算不同折扣起點批量下的總存貨費用；然后在這些總存貨費用中找出最低費用對應的經(jīng)濟訂貨批量或折扣起點批量；最后，比較此經(jīng)濟訂貨批量或折扣起點批量與需求量的關(guān)系，若小于需求量，則該經(jīng)濟訂貨批量或折扣起點批量就是最優(yōu)解，否則，需求量是最優(yōu)解。例46某企業(yè)每年需要配件20000件，每次訂貨費用1000元，存儲費率是零件單價的15%。供貨商規(guī)定，凡一次性購買3000件以下的價格為8元/件，3000件或以上但6000件以下的價格為7元/件，6000件或以上但8000件以下的價格為6元/件，8000件或以上的價格為5元/件。問企業(yè)應如何訂貨？3.5

Excel在營運資金管理中的應用（2）連續(xù)價格形式的折扣優(yōu)惠連續(xù)價格形式的折扣優(yōu)惠是指當訂貨量達到或超過折扣限量時，在特定的訂貨批量范圍區(qū)間內(nèi)的訂貨量可以按折扣優(yōu)惠價格計價。假設(shè)有下列的折扣條件：批量（件） 產(chǎn)品價格（元/件）0＜Q＜Q1 P1Q1≤Q＜Q2 P2Q2≤Q＜Q3 P3 …則可得各個折扣區(qū)間的經(jīng)濟訂貨批量Q*為式中,Qi為折扣區(qū)間批量的突變點；Pi為折扣價格的突變點；A為一次訂貨費；K為存貨的存儲費率（以存貨金額的百分比表示）。當按上述公式求出各個Q*i后，根據(jù)其值的大小判斷其是否為有效點，并計算累計費用，再除以相應的各個Q*i

，得出各個有效的經(jīng)濟訂貨批量下的平均訂貨價格，以此平均訂貨價格計算存貨總費用，以總費用最低的訂貨批量為經(jīng)濟訂貨批量。3.6

Excel在投資決策中的應用3.6.1投資決策常用函數(shù)1．投資函數(shù)（1）PV

返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款當前值的累計和。公式為：

PV(rate,nper,pmt,fv,type)參數(shù)說明如下：

rate—各期利率。

nper—總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)。

pmt—各期所應付給（或得到）的金額，其數(shù)值在整個年金期間（或投資期內(nèi)）保持不變。通常pmt包括本金和利息，但不包括其他費用及稅款。

fv—未來值或在最后一次支付后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零（一筆貸款的未來值即為零）。

type—數(shù)字0或1，用以指定各期的付款時間是在期初還是在期末。如果省略type，則假設(shè)其值為零，期末付款。說明：應確認所指定的rate和nper單位的一致性。（2）NPV

基于一系列現(xiàn)金流和固定的各期貼現(xiàn)率，返回一項投資的凈現(xiàn)值。投資的凈現(xiàn)值是指未來各期支出（負值）和收入（正值）的當前值的總和。公式為：

NPV(rate,value1,value2,…）參數(shù)說明如下：rate—各期貼現(xiàn)率，是一固定值；

value1,value2,…—代表1~29筆支出及收入的參數(shù)值。3.6

Excel在投資決策中的應用（3）FV基于固定利率及等額分期付款方式，返回某項投資的未來值。公式為：

FV(rate,nper,pmt,pv,type)有關(guān)函數(shù)FV中各參數(shù)的詳細內(nèi)容，請參閱函數(shù)PV的參數(shù)說明。（4）PMT基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。

公式為：

PMT(rate,nper,pv,fv,type)有關(guān)函數(shù)PMT中參數(shù)的詳細描述，請參閱函數(shù)PV的參數(shù)說明.（5）IPMT基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期間內(nèi)的利息償還額。有關(guān)函數(shù)IPMT的參數(shù)和年金函數(shù)的詳細內(nèi)容，請參閱函數(shù)PV的參數(shù)說明。公式為：

IPMT(rate,per,nper,pv,fv,type)3.6

Excel在投資決策中的應用（6）PPMT基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期間內(nèi)的本金償還額。公式為：

PPMT(rate,per,nper,pv,fv,type)有關(guān)函數(shù)PPMT中參數(shù)的詳細內(nèi)容，請參閱函數(shù)PV的參數(shù)說明。（7）NPER基于固定利率及等額分期付款方式，返回某項投資（或貸款）的總期數(shù)。公式為：

NPER(rate,pmt,pv,fv,type)有關(guān)函數(shù)NPER中各參數(shù)的詳細說明及有關(guān)年金函數(shù)的詳細內(nèi)容，請參閱函數(shù)PV的參數(shù)說明。3.6

Excel在投資決策中的應用2．償還率函數(shù)（1）RATE返回年金的各期利率。函數(shù)RATE通過迭代法計算得出，并且可能無解或有多個解。公式為：RATE(nper,pmt,pv,fv,type,guess)參數(shù)說明如下：per—總投資（或貸款）期，即該項投資（或貸款）的付款期總數(shù)。pmt—各期付款額，其數(shù)值在整個投資期內(nèi)保持不變。通常pmt包括本金和利息，但不包括其他費用或稅金。pv—現(xiàn)值，即從該項投資（或貸款）開始計算時已經(jīng)入賬的款項，或一系列未來付款當前值的累計和，也稱為本金。fv—未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略fv，則假設(shè)其值為零（例如，一筆貸款的未來值即為零）。type—數(shù)字0或1，用以指定各期的付款時間是在期初還是在期末。如果省略type，則假設(shè)其值為零，期末付款。guess—預期利率（估計值），如果省略預期利率，則假設(shè)該值為10%；如果函數(shù)RATE不收斂，請改變guess的值。通常當guess位于0~1之間時，函數(shù)RATE是收斂的。3.6

Excel在投資決策中的應用（2）IRR返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。這些現(xiàn)金流不一定要均衡，但作為年金，它們必須按固定的間隔發(fā)生，如按月或按年。內(nèi)部收益率為投資的回收利率，其中包含定期支付（負值）和收入（正值）。公式為：IRR(values,guess)參數(shù)說明如下：values—數(shù)組或單元格的引用，包含用來計算內(nèi)部收益率的數(shù)字，values必須包含至少一個正值和一個負值，以計算內(nèi)部收益率。guess—對函數(shù)IRR計算結(jié)果的估計值：（1）MicrosoftExcel使用迭代法計算函數(shù)IRR。從guess開始，函數(shù)IRR不斷修正收益率，直至結(jié)果的精度達到0.00001%。如果函數(shù)IRR經(jīng)過20次迭代，仍未找到結(jié)果，則返回錯誤值＃NUM!。（