利用Excel實(shí)現(xiàn)財務(wù)預(yù)測的回歸分析
唐飛兵
關(guān)鍵字：EXCEL 財務(wù)預(yù)測 回歸分析
在企業(yè)財務(wù)管理工作中，存在著大量的財務(wù)預(yù)測工作。通過財務(wù)預(yù)測有助于改善投資決策，提高企業(yè)對不確定事件的反應(yīng)能力，從而減少不利事件出現(xiàn)帶來的損失。通常銷售百分比法是一種簡單和常用的方法，其主要是假設(shè)資產(chǎn)、負(fù)債、收入、成本與銷售額成正比例。但由于規(guī)模經(jīng)濟(jì)現(xiàn)象和批量購銷問題的存在，銷售百分比法的假設(shè)經(jīng)常不成立，這使其應(yīng)用范圍受到限制。為了改進(jìn)財務(wù)預(yù)測的質(zhì)量，回歸分析則不失為一種有效的方法，利用數(shù)理統(tǒng)計的相關(guān)原理使數(shù)據(jù)預(yù)測結(jié)果更具有說服力。隨著Excel電子表的廣泛使用，利用其穩(wěn)定的性能、強(qiáng)大的功能來解決財務(wù)預(yù)測的回歸分析問題則顯得十分有效。
一、財務(wù)預(yù)測的回歸分析原理
財務(wù)預(yù)測的回歸分析，是利用一系列的歷史資料求得各資產(chǎn)負(fù)債表項(xiàng)目和銷售額的函數(shù)關(guān)系，據(jù)此預(yù)測計劃銷售額與資產(chǎn)、負(fù)債數(shù)量，然后預(yù)測融資需求。
在財務(wù)預(yù)測的回歸分析中，首先必須收集一些影響被預(yù)測對象相關(guān)變量的歷史資料，然后再將收集到的數(shù)據(jù)輸入計算機(jī)進(jìn)行自動計算得到回歸方程和相關(guān)參數(shù)。計算出的回歸方程是否能夠作為財務(wù)預(yù)測的依據(jù)取決于對相關(guān)參數(shù)進(jìn)行分析，故需要運(yùn)用數(shù)據(jù)統(tǒng)計的方法如擬合檢驗(yàn)、顯著性檢驗(yàn)得出檢驗(yàn)結(jié)果。如果檢驗(yàn)結(jié)果表明回歸方程是可靠的，最后把已擬好的相關(guān)變量值代入回歸方程得出最終的預(yù)測值。下面以銷售額的多元回歸分析預(yù)測為例來說明Excel在財務(wù)預(yù)測回歸分析中的應(yīng)用。
二、操作方法與步驟
（一）新建工作簿
1、單擊”開始”菜單，再在彈出的開始菜單項(xiàng)中，單擊”新建office文檔”，出現(xiàn)”新建office文檔”對話框窗口。
2、在”新建office文檔”對話框窗口中的”常用”活頁夾中，雙擊”空工作簿”，出現(xiàn)名為”Book1”的空工作簿。
3、按【Ctrl+S】鍵：或者在剛剛建立的空工作簿”Book1”中單擊磁盤圖標(biāo)：或者單擊”文件”菜單并在彈出的菜單中單擊”保存”。
4、在”另存為”對話框中將文件名”Book1”改為”財務(wù)預(yù)測回歸分析”，然后單擊保存。
（二）定義工作表名稱和歷史數(shù)據(jù)
1、雙擊”sheet1”工作表標(biāo)簽，輸入”銷售額預(yù)測回歸分析”后按【Enter】鍵。
2、選擇”銷售額預(yù)測回歸分析”，在A1至D9輸入標(biāo)題（銷售額、電視廣告費(fèi)用、報紙廣告費(fèi)用、年份）和相應(yīng)數(shù)據(jù)。限于篇幅及僅為說明問題，這里只設(shè)8年數(shù)據(jù)來進(jìn)行分析。
銷售額（萬元） 電視廣告費(fèi)用（萬元） 報紙廣告費(fèi)用（萬元） 年份
960 50 15 1994
900 20 20 1995
950 40 15 1996
920 25 25 1997
950 30 33 1998
940 35 23 1999
940 25 42 2000
940 30 25 2001
(三)定義公式
1、用鼠標(biāo)選擇A11到C15的結(jié)果輸出區(qū)域，輸入公式”=LINEST(A2:A9,B2:C9,true,true)”后按【Ctrl+Shift+Enter】，在A11到C15的區(qū)域中顯示如下結(jié)果：
1.300989098 2.290183621 832.3009169
0.320701597 0.304064556 15.73868952
0.9190356 6.425873026 #N/A
28.37776839 5 #N/A
2343.540779 206.4592208 #N/A
說明1：公式LINEST(A2:A9,B2:C9,1,1)中A2:A9是回歸方程y = m1*x1+m2*x2 + b 中已知被預(yù)測對象y值集合，B2:C9是方程中已知可選變量值x1和 x2的集合。兩個true均為邏輯值，前一個true 指明b 將被正常計算，如為false則強(qiáng)制b為0值；后一個true表示指明返回附加回歸統(tǒng)計值，如為false則不返回附加回歸統(tǒng)計值。
2、為了便于后面對結(jié)果進(jìn)行分析，可將上述結(jié)果進(jìn)行重新表達(dá)，使之更為清淅。根據(jù)上表中的結(jié)果和結(jié)果的排列順序（見說明2），可在A17至E21區(qū)域輸入對上述結(jié)果的解釋：
多元回歸方程： y=2.290183621*x1+1.300989098*x2+832.3009169
標(biāo)準(zhǔn)差： m1=0.304064556 m2=0.320701597 b=15.73868952
判定系數(shù)=0.9191356 y估計值的標(biāo)準(zhǔn)誤差=6.425873026
F統(tǒng)計值=28.37776839 自由度=5
回歸平方和=2343.540779 殘差平方和=206.4592208
說明2：①返回回歸分析的結(jié)果是按一定順序排列的，排列順序如下表：
A B C D E
11 mn mn-1 …… m1 b
12 sen sen-1 …… se1 seb
13 r2 sey
14 F df
15 ssreg ssresid
②上表中se1,se2,...,sen表示系數(shù) m1,m2,...,mn 的標(biāo)準(zhǔn)誤差值；Seb表示常數(shù)項(xiàng) b 的標(biāo)準(zhǔn)誤差值；r2表示判定系數(shù)，可用于擬合檢驗(yàn)；Sey表示y 估計值的標(biāo)準(zhǔn)誤差；F表示F 統(tǒng)計值或F觀察值；df表示自由度；ssreg表示回歸平方和；ssresid表示殘差平方和。
（四）檢驗(yàn)回歸方程的可靠性
在上例中，判定系數(shù)（或 r2）為 0.9191356（函數(shù) LINEST 的輸出單元格 A13 中的值），表明在電視廣告費(fèi)用x1、報紙廣告費(fèi)用x2與銷售額y之間存在很大的相關(guān)性。然后可以通過 F 統(tǒng)計來確定具有如此高的 r2 值的結(jié)果偶然發(fā)生的可能性。假設(shè)事實(shí)上在變量間不存在相關(guān)性，但選用 8 年數(shù)據(jù)作為小樣本進(jìn)行統(tǒng)計分析卻導(dǎo)致很強(qiáng)的相關(guān)性?！盇lpha”表示得出這樣的相關(guān)性結(jié)論錯誤的概率。如果 F 觀測統(tǒng)計值大于 F 臨界值，表明變量間存在相關(guān)性。假設(shè)一項(xiàng)單尾實(shí)驗(yàn)的 Alpha 值為 0.05，根據(jù)自由度（在大多數(shù)F 統(tǒng)計臨界值表中縮寫成 v1 和 v2）v1 = k = 2，v2 = df=n - (k + 1) = 8 - (2 + 1) =5，其中 k 是回歸分析中的變量數(shù)，n 是數(shù)據(jù)點(diǎn)的個數(shù)，可以在F 統(tǒng)計臨界值表中查到F 臨界值為 5.79。而在單元格 A14 中的 F 觀測值為 28.37776839，遠(yuǎn)大于 F 臨界值 5.79。由此可以得出結(jié)論：此回歸方程適用于對銷售額的預(yù)測。
（五）預(yù)測未來的銷售額
假設(shè)2002年的電視廣告費(fèi)用預(yù)算為35萬元，報紙廣告費(fèi)用預(yù)算為18萬元，則根據(jù)多元線性回歸方程y=2.290183621*x1+1.300989098*x2+832.3009169可計算出2002年的銷售額為2.290183621*35+1.300989098*18+832.3009169即913.7583萬元。
三、結(jié)束語
文中僅以預(yù)測銷售額為例說明Excel在財務(wù)預(yù)測的回歸分析中應(yīng)用和實(shí)現(xiàn)問題，其他項(xiàng)目可以用同樣的方法來預(yù)測。比如，假設(shè)存貨與銷售額之間存在直線關(guān)系，其直線方程為”存貨=a+b×銷售額”，根據(jù)歷史資料和一元回歸分析可以求出直線方程的系數(shù)a和b，然后根據(jù)預(yù)計銷售額和直線方程預(yù)計存貨的金額。通過假設(shè)銷售額與資產(chǎn)、負(fù)債等存在線性關(guān)系，利用一系列的歷史資料求得各資產(chǎn)負(fù)債表項(xiàng)目和銷售額的函數(shù)關(guān)系，據(jù)此預(yù)測計劃銷售額與資產(chǎn)、負(fù)債數(shù)量，然后預(yù)測融資需求。須注意的是，函數(shù)變量的選擇直接關(guān)系到回歸分析結(jié)果的正確性和可靠性，只要變量選擇恰當(dāng)，Excel可以為財務(wù)管理工作帶來極大的效率，使得在手工作業(yè)下難以完成的思想和方法得以順利實(shí)施。
（作者單位：廣州白云山和記黃埔中藥有限公司）
作者：唐飛兵 文章來源：廣州白云山和記黃埔中藥有限公司
