第4章Excel在財務預測中的應用

4.1財務預測概述

財務預測，是指對企業(yè)未來的收入、成本、利潤、現(xiàn)金流量及融資需求等財務指標所作的

估計和推測。財務預測是編制投資和融資計劃的基礎，是公司制訂成長戰(zhàn)略的基本要素。稱

職的財務管理人員應該能夠充分利用公司的有關信息資料，預測公司的財務需要并做出相應

的安排。公司成長主要由銷售增長來決定，銷售增長需要相應的資產(chǎn)增長，如果企業(yè)已經(jīng)是

滿負荷運轉(zhuǎn)，不僅流動資產(chǎn)、而且固定資產(chǎn)都要增長，而資產(chǎn)增長需要相應的融資增長。同

時，企業(yè)進行對外投資和調(diào)整資本結(jié)構(gòu)，也需要籌措資金。企業(yè)所需要的這些資金，一部分

來自企業(yè)內(nèi)部，另一部分通過外部融資取得。由于對外融資時，企業(yè)不但需要尋找資金提供

者，而且還需做出還本付息的承諾或提供企業(yè)盈利前景等信息，使資金提供者確信其投資是

安全的并可獲利，這個過程往往需要花費較長的時間。因此，企業(yè)需要預先知道自身的財務

需求，確定資金的需要量，提前安排融資計劃，以免影響資金周轉(zhuǎn)。

財務預測有助于改善企業(yè)的投資決策。雖然投資是決定籌資與否和籌資多少的重要因素，但

是根據(jù)銷售前景估計出的融資需求，并不一定能夠得到全部滿足。這時，就需要根據(jù)可能籌

措到的資金來安排銷售增長以及有關的投資項目，使投咨決策建立在可行的基礎匕

財務預測一般按以下幾個步驟進行。

1.銷售預測

銷售預測是指根據(jù)市場調(diào)查所得到的有關資料，通過對有關因索的分析研究，預計和測算特

定產(chǎn)品在未來一定時期內(nèi)的市場銷售量水平及變化趨勢，進而預測企業(yè)產(chǎn)品未來銷售量的過

程。企業(yè)的一切財務需求都可以看作是因銷售引起的，銷售量的增減變化，將會引起庫存最、

現(xiàn)金流量、應收與應付賬款以及公司其他資產(chǎn)和負債的變化。因此銷售預測在企業(yè)預測系統(tǒng)

中處于先導地位，它對于指導利潤預測、成本預測和資金預測，進行長短期決策，安排經(jīng)營

計劃，組織生產(chǎn)等都起著重要的作用。

2.估計收入、費用和利澗

收入和費用與銷售量之間也存在一定的函數(shù)關系，因此，可以根據(jù)銷售數(shù)據(jù)估計收入和費用，

并確定凈利潤。凈利潤和股利支付率，共同決定了內(nèi)部留存收益所能提供的資金數(shù)額。

3.估計需要的資產(chǎn)

資產(chǎn)通常是銷小收入的函數(shù)，根據(jù)歷史數(shù)據(jù)可以分析出二者之間的函數(shù)關系。根據(jù)預計銷售

收入和資產(chǎn)與銷售之間的函數(shù)關系，可以預測所需資產(chǎn)的總量。某些流動負債也是銷售收入

的函數(shù)，相應地也可以預測負債的自發(fā)增長額，這種增長可以減少企業(yè)外部融資的數(shù)額。

4.估計所需融資

根據(jù)預計資產(chǎn)總量，減去已有的資金來源、負債的自發(fā)增長和內(nèi)部提供的留存收益，可得出

所需的外部融資數(shù)額。

第4章ExceI在財務預測中的應用

4.2財務預測的分析方法

預測分析的方法有很多種，企業(yè)應根據(jù)不同的需要選擇不同的預測方法?？偟膩碚f，預測分

析方法可分為兩大類：定量預測法和定性預測法。

4.2.1定量預測法

定量預測法是指在掌握與預測對象有關的各種要素的定量資料的基礎上，運用現(xiàn)代數(shù)學方法

進行數(shù)據(jù)處理，從而建立起能夠反映有關變量之間關系的各類預測模型的方法。

在財務預測中，經(jīng)常使用的定量預測法主要有以下兒種.

4.2.1.1移動平均法

移動平均法是一種改良的算術平均法，是一種最簡單的自適應預測模型。它根據(jù)近期數(shù)據(jù)對

預測值影響較大，而遠期數(shù)據(jù)對預測值影響較小的事實，把平均數(shù)逐期移動。移動期數(shù)的大

小視具體情況而定，移動期數(shù)少，能快速地反映變化，但不能反映變化趨勢；移動期數(shù)多，

能反映變化趨勢，但預測值帶有明顯的滯后偏差。

常用的移動平均法主要有一次移動平均法和二次移動平均法。

1.一次移動平均法

一次移動平均法是根據(jù)時間序列，逐期移動，依次計算包含一定項數(shù)的時間序列平均數(shù)，形

成一個平均時間數(shù)序列，并據(jù)此進行預測。預測模型為

式中一第什1期的預測值；

一將被平均的〃個觀測值;

4.2.1.3回歸分析預測法

I可歸分析預測法是通過研究兩組或兩組以上變量之間的關系，建立相應的回歸預測模型，對

變量進行預測的?種預測方法。

1.回歸分析預測法的基本程序

進行回歸分析的步驟如下：

（1）收集有關資料。將各種可能的影響因素的有關數(shù)據(jù)盡可能多地收集起來。

（2）判斷趨勢。根據(jù)收集到的數(shù)據(jù)，判斷其變化趨勢，從而為建立相應的數(shù)學模型做準備。

對于變量不多的問題，可以通過繪制散點圖來判斷變化趨勢。

（3）建立預測數(shù)學模型。根據(jù)歷史數(shù)據(jù)的變化趨勢，選擇相應的描寫該問題的數(shù)學模型，并

采用相關的計算技術來估計數(shù)學模型的參數(shù)。

（4）相關檢驗。對建立的預測數(shù)學模型，必須進行有關的檢驗，主要是通過計算預測模型

的相關系數(shù)、方差（或標準差）以及顯著性等指標，來判斷預測模型的準確性、是否需要修

正、采用何種方法修正等。

2.回歸模型建立的方法

建立回歸模型的一般方法是采用最小二乘法，其原理如下：

考慮m個自變量xi、X2、“、X”,和因變量y的關系，現(xiàn)有n組觀測數(shù)據(jù)，不同Xki

i=12"）下的y的觀測值為》函數(shù)月g）的待估計參數(shù)為依（仁12…,m+1,這里,每個

自變最有一個待估計系數(shù)，還有一個待估計常數(shù)，故有加+1個待估計參數(shù)），通過回歸預測

模型得到不同珈下的預測值為，則有:

殘差平方和SE：

剩余標準差SS：

相關系數(shù)正：

亍為觀測值X的平均值：

那么，最小二乘法的原理就是尋找最優(yōu)的待估計參數(shù)以，使殘差平方和最小。

3.財務預測中常用的幾種回歸模型

（I）一元線性回歸模型

當只有兩個變量（一個自變量和一個因變量），并且它們之間存在線性關系時，可以用一元

線性回歸模型來描述。一元線性回歸模型為

式中〃、?回歸系數(shù)，其中。代表截距，〃代表斜率。

（2）一元非線性回歸模型

當變量x和5，之問的關系不能用線性美系來描述時，則需要建立一元非線性回歸模型。根據(jù)

變量X和），之間的關系，一元非線性回歸模型常見的幾種情況有:

對數(shù)模型：

指數(shù)模型：

乘某模型：

雙曲線模型：

以上幾種一元非線性模型均可通過數(shù)學變換化成一元線性模型。

（3）多元線性回歸模型

當自變最有兩個或兩個以上，且因變最與這些自變最之間呈線性組合關系時，它們就構(gòu)成了

多元線性回歸模型，模型形式為

式中。、bi、岳、…、九一估計參數(shù)；

XI、及、…、Xm—自變量。

（4）多元非線性回歸模型

多元非線性回歸模型用來描述因變量與多個自變量之間呈非線性組合關系的情況。例如，柯

柏一道格拉斯生產(chǎn)函數(shù)就是典型的多元非線性模型：

式中：L和K分別為勞動力和固定資本；a、b、c為系數(shù)。

4.2.1.4模擬法

在企業(yè)的實際經(jīng)濟活動中，各種經(jīng)濟參數(shù)往往并不是確定的，而是隨機變化的，比如產(chǎn)品的

銷售量往往隨市.場的變化而變化，在這種情況下，就需要對這些參數(shù)的不確定性進行分析?，

而對其預測也就需要采用與傳統(tǒng)的確定性分析不同的方法來進行。一般情況下，可以采用模

擬法來解決不確定性情況下的財務預測問題,概率法、蒙恃卡羅模擬方法就是較實用的方法。

4.2.2定性預測法

定性預測法是由有關方面的專業(yè)人員或?qū)＜腋鶕?jù)自己的經(jīng)驗和知識，結(jié)合預測對象的特點進

行綜合分析，對事物的未來狀況和發(fā)展趨勢作出推測的預測方法。定性預測法由于帶有.較多

的個人主觀性，因而在實技中最好作為一種補充的預測方法。

第4章ExceI在財務預測中的應用

4.3Excel中的有關預測函數(shù)及其應用(1)

Excel提供了關于估計線性模型和指數(shù)模型參數(shù)的幾個預測函數(shù)。線性模型利指數(shù)模型的數(shù)

學表達式如下：

線性模型：

y=mx+b或y=zmxi+rmx2+...+b

指數(shù)模型:

或

式中，），為因變量；X是白變量；加、孫、???、〃?"、機八〃分別為預測模型的待估計參數(shù)。

Excel提供的預測函數(shù)主要有UNEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、GROWTH函數(shù)、

FORECAST函數(shù)、SLOPE函數(shù)和INTERCEPT函數(shù)，它們所使用的參數(shù)都基本相同，現(xiàn)列

于表4-1中，以供參考。表4-1預測函數(shù)的參數(shù)及含義

參數(shù)含義

known_/s因變量y的觀測值集合

自變量X的觀測值集合。它可以是一個變量（即一元模型）或多個變量（即

多元模型）的集合。

known/s如果只用到一個變量，只要known-/s和known-x'G維數(shù)相同，它們可

以是任何形狀的選定區(qū)域。如果用到不只一個變量，known_y*s必須是向

量（也就是說，必須是一行或一列的區(qū)域）。如果省略known.x-s,則假

設該數(shù)組是｛1,2,3｝其大小與known_ys相同

邏輯值，指明是否強制使常數(shù)b為。（線性模型）或為1（指數(shù)模型）o如

const果const為TRUE或省略.b將被正常計算。如果const為FALSE,b將

被設為0（線性模型）或設為1（指數(shù)模型）

邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE,則函數(shù)返

回附加回歸統(tǒng)計值，這時返回的數(shù)組為｛mn,mni>mi,b：sen,seni?…,

sei,seb，聲，sey：F.df：ssr%,ss^d｝。如果stats為FALSE或省略，

函數(shù)只返回系數(shù)預測模型的待估計參數(shù)m.mn、mn/、…、和b。

stats

附加回歸統(tǒng)計值返回的順序見表4-2.

表4-2中的各參數(shù)說明見表4-3o

如果要得到附加回歸統(tǒng)計值數(shù)組中的值，需用INDEX函數(shù)將其取出

表4-2陽加回歸統(tǒng)計值返回的順序

123456

1m（）mm???m2mib

2senS6n1,e,se2seiseb

2

3rsey

4Fdf

5SSregSSre&d

表4-3各參數(shù)說明

參數(shù)說明

sei,se2,",sen系數(shù)-.mn的標準誤差值

Seb常數(shù)項b的標準誤差值（當const為FALSE時，seb=#N/A）

參數(shù)說明

相關系數(shù)，范圍在0到1之間。如果為1,則樣本有很好的相關性，

產(chǎn)Y的估計值與實際值之間沒有差別。反之，如果相關系數(shù)為0.則回歸

方程不能用來預測丫值

估計值的標準誤差

seyY

F統(tǒng)計值或F觀察值.，使用F統(tǒng)計可以判斷因變量和自變量之間是否

F

偶爾發(fā)生過觀察到的關系

自由度。用于在統(tǒng)計表上查找F臨界值。所查得的值和函數(shù)LINEST

Df

返回的F統(tǒng)計值的比值可用來判斷模型的置信度

SSreg回歸平方和

SSresid殘差平方

4.3.1LINEST函數(shù)

LINEST函數(shù)的功能是使用最小二乘法計算對已知數(shù)據(jù)進行最佳線性擬合的直線方程，并返

何描述此線性模型的數(shù)組。因為此函數(shù)返回數(shù)值為數(shù)組，故必須以數(shù)組公式的形式輸入。

函數(shù)公式為

=LINEST（known_yrs,known_x's,const,stats）

下面舉例說明LINEST函數(shù)的應用。

1.一元線性回歸分析

LINEST函數(shù)可用于一元線性回歸分析，也可以用于多元線性回歸分析，以及時間數(shù)列的自

回歸分析。

當只有一個自變量x（即一元線性I可歸分析）時，可直接利用下面的公式得到斜率和y軸

的截距值以及相關系數(shù):

斜率：INDEX(LINEST(knowiv/s,known_x*s),1,1)：或INDEX(LINEST

(known_y*s,known_x's),1)

截距：INDEX(LINEST(known_y's,known.x's),1,2)；或INDEX(LINEST

(known_y*s,known_x*s),2)

相關系數(shù)：INDEX(LINEST(known_yrs,known_x's,true,true),3,1)

【例4-1］某企業(yè)1?9月份的總成本與人工小時及機器工時的數(shù)據(jù)如圖4-1所示。假設總成

林與人工出、時之間落在著線?關系，則I］在單元檎B13中振入公式

=INDEX(LINEST(B2:B10,D2:D10),2)””，在單刀沅格B14插入公默

“=INDEX(LINEST(B2:B10,D2:D10),l)"”，在單元^格B15插入公默

“=INDEX(LINEST(B2:BI0,D2:D10,TRUE,TRUE)3D”，即得總成本與人工小時的一元線性

回歸分析方程為：Y=562.72756+4.41444XH相關系數(shù)為R2=0.99801,如圖4-1所示。

人工小跖小機

3Ksy

1000

1120

1230

1350

USO

1590

SOfittY-ybX,

?-SS27275599

b-4414440023

R'-0996010338

圖4-1一元線性回歸分析

2.多元線性回歸分析

仍以例4-1的數(shù)據(jù)為例，首先選取單元格區(qū)域A17:D21,再以數(shù)組公式方式輸入公式

*^LINEST(B2:Bl0,C2:D10,TRUE,TRUE)",即得該二元線性回歸的有關參數(shù)如圖4-2所示，

從而得到:

16￡元我性回舊

1734323459136165335724714365S24?N/A

181S608620503677560814341869856fN/A

19099889827117T91TH9*N/ASN/A

202?19982016W/A?S/A

217S4789?28832.4941765M/AM/A

圖4-2二元線性回歸分析

回歸方程；Y=471.4366-3.6165Xi+3.4323X2

相關系數(shù)：R2=0.9990

標準差:Sey=11.7792o

4.3.2LOGEST函數(shù)

LOGEST函數(shù)的功能是在回歸分析中，計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返

回描述該指數(shù)模型的數(shù)組。由于這個函數(shù)返回一個數(shù)組，必須以數(shù)組公式輸入。

LOGEST函數(shù)的公式為

=LOGEST（known_y,s.known_x's,const,stits）

【例4-2］某企業(yè)12個月某產(chǎn)品的生產(chǎn)量（X）與生產(chǎn)成本（Y）的有關資料如圖4-3所示，

假設'勝們之間有如下關系：o選取單元格區(qū)域B15:CI8,輸入公式

“4LOGEST（C2:C13,B2:B13,TRUE,TRUE）”（數(shù)組公式輸入），即得回歸參數(shù)，如圖4-3所示，

參數(shù)m=0.8887,參數(shù)b=1891.7729,生產(chǎn)成本與生產(chǎn)品的回歸曲線為；Y=179l.7729x0.8887x,

相關系數(shù)R2=0.95885O

ABC

1，月份生產(chǎn)量《萬傳》X切35￡?)丫

21102知3

3210345M8

4310$$$103

S410%IfHS

6$1115m$

76H431

8?115<4

9S12a4237

1091245915

II1012W湖6

121113343912

13121325yn2

K的圻l+lf還累

150W870&4481T91TT2W9

16000HW1S500906M14T

n00O2??1S?

18233022174310

圖4-3指數(shù)回歸

回歸方程的系數(shù)及相關系數(shù)也可以利用下面的公式直接計算

參數(shù)m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887

參數(shù)b：INDEX(LOGESTiC2:C13,B2:B13),1,2)=1791.7729

相關系數(shù)R2：=INDEX(LOGEST(C2:C13,B2：B13,TRUE,TRUE),3J)=0.95885

4.3.3TREND函數(shù)

TREND函數(shù)的功能是返回?條線性回歸擬合線的?組縱坐標值(y值)，即找到適合給定

的數(shù)組known_y's和krx)wn_x,s的直線(用最小二乘法)，并返回指定數(shù)組new_x's值在

直線上對應的y值。

TREND函數(shù)的公式為

=TREND(known_y's.known_x's,ncw_x*s,const)

式中new_x's----需要函數(shù)TREND返回對應y值的新x值。new_xfs與known_x's一

一樣，每個獨立變量必須為單獨的一行(或一列)。因此，如果known_y*s是單列的，

kno\vn_x's和ncw_x's應該有同樣的列數(shù)，如果known_y's是單行的，known_x's和

new_x's應該有同樣的行數(shù)。如果省略new_x，s,將假設它和known_x's一樣。

【例4-3］某企業(yè)過去一年的銷售量為下列數(shù)據(jù)：（300,356,374,410,453,487,

501,534,572,621,650,670｝,將它們保存在單元格A1:A12中，則下一年的1、2、3

月的銷售量預測步驟為:選中單元格區(qū)域B1:B3,輸入公式“=TREND（AI:A12,,113;14;15｝）”

（數(shù)組公式輸入），即得來年的1、2、3月份的銷售量分別為710、743和777。這個公式默

認｛1;2;3;4;5;6;7;8;9;10;11;12｝作為known.x's的參數(shù)，故數(shù)組｛13;14;15｝就對應其后的3個月

份。

4.3.4GROWTH函數(shù)

GROWTH函數(shù)的功能是返回給定的數(shù)據(jù)預測的指數(shù)增長值。根據(jù)已知的x值和y值，函數(shù)

GROWTH返回一組新的x值對應的y值?？梢允褂肎ROWTH工作表函數(shù)來擬合滿足給定

x值和y值的指數(shù)曲線。

GROWTH函數(shù)的公式為

=GROWTH（known_y's,known_x's,new_x's,const）

式中，各參數(shù)的含義同TREND函數(shù)。但需注意的是，如果known』s中的任何數(shù)為零或為

負，函數(shù)GROWTH將返回錯誤值#NUM!o

【例4-4］以例4-3的資料為例，利用GROWTH函數(shù)預測來年的1、2、3月的銷售量。預測

步驟為：選中單元格區(qū)域BI:B3,輸入公式“=GROWTH（A1:A12,,（13；14;15））”（數(shù)組公式

輸入），即得來年的1、2、3月份的銷售量分別為756、811和870。這個公式同樣默認

｛1;2;3;4;5;6;7;8;9;10;11;12｝作為known_x's的參數(shù),故數(shù)組（13;14;15｝就對應后面的3個月份。

4.3.5FORECAST函數(shù)

FORECAST函數(shù)的功能是根據(jù)給定的數(shù)據(jù)計算或預測未來值。此預測值為基于一系列已知

的x值推導出的y值。以數(shù)組或數(shù)據(jù)區(qū)域的形式給定x值和y值后，返回基Fx的線

性回歸預測值。FORECAST函數(shù)的計算公式為a+bx

式中，；。

FORECAST函數(shù)的公式為

=FORECAST(x,known_y's,known_x's)

式中x一需要進行預測的數(shù)據(jù)點。

需要說明的是：

如果X為非數(shù)值型，函數(shù)FORECAST返回錯誤值#VALUE!o

如果known_y's和known_x's為空或含有不同數(shù)目的數(shù)據(jù)點，函數(shù)FORECAST返回錯誤

值#N/Ao

如果known-x's的方差為零，函數(shù)FORECAST返回錯誤值#DIV/0!。

例如：FORECAST(30,{6,7,9,15,21},{20,28,31.38.40))=10.60725。

4.3.6SLOPE函數(shù)

SLOPE函數(shù)的功能是返回根據(jù)known_y's和knownjfs中的數(shù)據(jù)點擬合的線性回歸直線

的斜率。斜率為直線上任意兩點的垂直距離與水平距離的比值，也就是回歸直線的變化率。

SLOPE函數(shù)的公式為

=SLOPE(known_y's,known_x's)

說明：參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)里包含

文本、邏輯值或空白單元格，這些值將被忽略。但包含零值的單元格將計算在內(nèi)。如果

known_y's和known_x's為空或其數(shù)據(jù)點數(shù)目不同，函數(shù)SLOPE返回錯誤值#N/A。

例如:SLOPE({2,3.9,1.8.7,5},{6.5,11,7,5,4,4})=0.305556。

4.3.7INTERCEPT函數(shù)

INTERCEPT函數(shù)的功能是利用己知的x值與y值計算直線與y軸的截距。截距為穿過

known_x's和known_y's數(shù)據(jù)點的線性回歸線與y軸的交點。

公式為

=INTERCEPT(known_y*s,known_x's)

例如：INTERCEPT({2,3,9,1,8},{6,5,11,7,5})=0.048387k

第4章ExceI在財務預測中的應用

4.4利用數(shù)據(jù)分析工具解決預測問題(1)

除了利用前面介紹的幾個預測函數(shù)進行回歸預測分析外，我們還可以使用Excel的數(shù)據(jù)分析

工具庫提供的統(tǒng)計觀測分析工具來解決回歸預測問題。

Excel的數(shù)據(jù)分析工具庫提供了3種統(tǒng)計觀測分析工具，它們是移動平均法、指數(shù)平滑法和

回歸分析法。下面結(jié)合實例來說明這3種方法的具體應用.

4.4.1移動平均法

【例4-51某企業(yè)2000年12個月的銷售額如圖4-4所示，分別按3期、5期和7期移動平

均所做的預測分析如圖4-4中的C4:E13區(qū)域所示。以3期移動平均為例為例，具體計算

步驟如下：

圖4-4一次移動平均法實例

(1)從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對話框，如圖生5所

示。

圖4-5【數(shù)據(jù)分析】對話框

(2)在【數(shù)據(jù)分析】對話框中的【分析工具】框中選中【移動平均】選項，則彈出【移動

平均】對話框，如圖4-6所示。

HE3

.入

QE域9（memi3

r區(qū)有位于第一行3

修牖如；n

俟出埃項一

懈出區(qū)域。）際口

P山寰8出or標唐■建

圖4-6【移動平均】對話框

（3）在【移動平均】對話框中，【輸入?yún)^(qū)域】框中輸入“$B$2：$B$13"，【間隔】框中輸

入“3”，【輸出區(qū)域】框中輸入“$C$2”，最后選中【圖表輸出】選項；

（4）單擊【確定】按鈕，則運算結(jié)果就顯示在單元格區(qū)域C4C13中，如圖4-4所示（圖中

的第13行預測數(shù)據(jù)即為下月即第13月的預測值），并自動出現(xiàn)輸出圖表，如圖4-7所示。

圖4-7移動期數(shù)為3時的輸出圖表

用同樣的方法，可以分析當移動期數(shù)為5和7時的分析結(jié)果，如圖4-4所示。

4.4.2指數(shù)平滑法

【例46】某企業(yè)的有關銷售數(shù)據(jù)如圖4-8所示，利用指數(shù)平滑法進行預測分析,其步驟如下:

D1

帔售赧萬元）明后和曲）2.用后關最4明尼家裁。6

12401240

814561384

⑻016201522

16716501S81

劉IH17401669

囹

迎!9021605

勿

澗2H12003

226240323S02198

178H6ZiZ3

20518819662046

226201

202520<?

圖4-8指數(shù)平滑法實例

（1）從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈由【數(shù)據(jù)分析】對話框，在【數(shù)據(jù)分

析】對話框中的【分析工具】框中選中【指數(shù)平滑】選項，則彈出【指數(shù)平滑】對話框，如

圖4-9所示v

圖4-9【指數(shù)平滑】對話框

（2）在【指數(shù)平滑】對話框中，【輸入?yún)^(qū)域】框中輸入"B$2:$BS13"，【阻尼系數(shù)】框中輸

入“，2”，【輸出區(qū)域】框中輸入“$C$3”,最后選中【圖表輸出】選項。

（3）單擊【確定】按鈕，則運算結(jié)果就顯示在單元格區(qū)域C3:C13中（圖中的笫13行預測

數(shù)據(jù)即為下月即第13月的預測值），如圖4-8所示，并自動出現(xiàn)輸出圖表，如圖4J0所示。

圖4-10指數(shù)平滑法預測輸出圖（阻尼系數(shù)0.2）

用同樣的方法，可以分析當阻尼系數(shù)為0.4和0.6時的分析結(jié)果如圖4-8所示。

需要注意的是，【數(shù)據(jù)分析】中的指數(shù)平滑法所使用的阻尼系數(shù)并不是421.2節(jié)介紹的指數(shù)

平滑法預測方程中的平滑系數(shù)a,二者的關系為：阻尼系數(shù)二1一。。

4.4.3回歸法

利用Excel的回歸工具進行預測分析有兩種方法：一是圖表法；二是回歸分析法。

4.4.3.1圖表法

圖表法僅能解決一元線性或非線性回歸問題，不能解決多元回歸問題。

【例4-7］某企業(yè)連續(xù)9年的產(chǎn)品銷售收入Y（萬元）與廣告支出X,（萬元）和居民平

均收入X2（元）的有關數(shù)據(jù)如圖4-1I所示，則利用圖表法進行回歸分析，其方法和步驟如

下，這里僅以銷售收入Y（萬元）與廣告支出X1（萬元）的一元線性關系為例：

(

mI

m?萬元）（萬元）

Jeo

wo

m刈

fs

MO

圖4-11某企業(yè)的有關銷售數(shù)據(jù)

（i）選擇單元格區(qū)域B2:C10°

（2）單擊工具欄上的【圖表導向】按鈕，在【圖表導向一4步驟之I一圖表類型】中選“XY

散點圖”，其【子圖表類型】選第1種，如圖4-12所示.

圖4-12準備作散點圖

（3）單擊【下一步】按鈕，出現(xiàn)【圖表導向一4步驟之2—圖表源數(shù)據(jù)】對話框，單擊【系

列】,在【名稱】欄中填入“銷售收入”,在【X值】欄中輸入“=Shectl!$C$2:$C$10"，在【Y

值】欄中輸入“=Sheeil!$BS2:$B$10”（用鼠標拾取單元格區(qū)域），如圖4-13所示。

圖4-13填入源數(shù)據(jù)

（4）單擊【下一步】按鈕，出現(xiàn)【圖表導向一4步驟之3—圖表選項】對話框，填入各標題

文字，如圖4-14所示。

圖4-14填入各標題文字

（5）單擊【下一步】按鈕，出現(xiàn)【圖表導向一4步驟之4—圖表位置】對話框，不作任何輸

入，單擊【完成】按鈕，則在工作表上看到輸出的圖形，對其進行必要的調(diào)整（如坐標、字

體、位置等）。

（6）在系列【數(shù)據(jù)點】上的任一點上，按鼠標左鍵，使各數(shù)據(jù)點出現(xiàn)記號，再單擊【工

具欄】上的【圖表】按鈕，選中【添加趨勢線】項，或在數(shù)據(jù)點上按鼠標右鍵，選【添加

趨勢線】項，出現(xiàn)【添加趨勢線】對話框，如圖4-15所示。

圖4-15【添加趨勢線】對話框

（7）在【添加趨勢線】中的【類型】對話框中，有【線性】、【對數(shù)】、【多項式】、【乘塞】、

【指數(shù)】和【移動平均】6個選項。通過觀察XY散點圖可知，產(chǎn)品銷售收入與廣告支出之

間呈明顯的線性關系，故這里選【線性，

（8）在【添加趨勢線】中的【選項】對話框中，勾選【顯示公式】、【顯示R平方值】，如

圖4-16所示。

圖4-161添加趨勢線】的【選項】設置

（9）單擊【確定】按鈕，則在圖形上顯示出較粗的預測線、回歸方程和R平方值，然后進

行必要的調(diào)整,得到如圖4-17的結(jié)果。

漏

“,?

C廣一文匕XI

m

ImM)

lm4M?

we

0

lm燦

to

lmM>

12lsw

m

圖4-17輸出圖形

用同樣的方法還可以確定銷售收入與居民平均收入的關系。

4.4.3.2回歸分析法

回歸分析法可以對一元線性或多元線性以及某些可以轉(zhuǎn)化為線性的非線性問題進行回歸分

析。

1.線性回歸

【例4-8】仍以例4-7的有關資料為例，回歸分析的步驟如下:

(1)從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈鉗【數(shù)據(jù)分析】對話框，在【數(shù)據(jù)分

析】對話框中的【分析工具】框中選中【回歸】選項，如圖4-18所示，則彈出【回歸】對

話框。

圖4-18【數(shù)據(jù)分析】對話框

(2)在【回歸】對話框中，【丫值輸入?yún)^(qū)域】中輸入“SB$1:$B$1O”，【X值輸入?yún)^(qū)域】中

輸入在【輸出選項】中勾選【輸出區(qū)域】，填入然后根據(jù)實際

需要，勾選其他需要的選項，如圖4-19所示。

圖4-19【回歸】選項

(3)單擊【確定】接鈕,回歸分析的摘要就輸出在本工作表上，如圖4-20所示。對該叱數(shù)

據(jù)進行分析可知：R平方值為0.9903,說明因變量與自變量之間相關性很高；F的顯著值為

2.961E-07,已達0.05的檢驗標準；其他統(tǒng)計檢驗也達到相應的標準。從而得到回歸方程為：

Y=229.8409+9.2794X?+0.0082X2<>

Acp1TGM2

徐￡33WTTVT

13

11-C

15Iowsiceu

21s0WWW

oarmoiz

19一拗B9

8

?夕溫*

2

224(9_______T_Dkificweif

C定做代.M56Utewe(MM?tH-Cr?

246M3arwon：5I3K)mM

a十yr

“壟渦蔓tS?tF-VUKUv?rwnrw/”用sot

2t229MOMVT1531031321501W55sr-06圉ym?T3M197g2flXX

e「HSItfix“萬光）2C45l?Wt351gM。331“2TW?：15COOT?2neosii$wm

30任民，分E人心《oOO^MOCo8im$m0?322$2$oeo：9：e-OOMM006499PQ69M0064S

31

乂gtCALovn?T

35

*?*?B帖產(chǎn)MMMAY（萬克3行嚏H量

7T13?1561819H$818019?1W9M096

%2161mo-ozwww

第3mnastoi6?>3em40底

<044?T0e?16OW

<1,wewwtt°iwnrrtowtenw

eeUl3MMee6J5ISW0

<3T5110523m8HT?e2Mloenr$221T

440如-0?0OCCWWTt

9e&)-133tmiM3-1:17g

圖4-20回歸分析的計算機輸出

當自變量只有一個（即一元回歸）時，上述方法同樣適用。

2.非線性回歸

對于某些可以化為線性關系的非線性問題，同樣可以進行回歸分析C舉例如下。

【例4-9］某地區(qū)科研系統(tǒng)近10年的凈收入丫（千萬元）與研究經(jīng)費X1（千萬元）和研究

人員數(shù)X?（萬人）的統(tǒng)計資料，如圖4-21所示，假設它們之間存在著以下的函數(shù)關系：

式中，〃、收。為待估計參數(shù)。

若利用回歸工具求解此類非線性問題，解決的辦法是將此方程進行數(shù)學變換，即對方程兩邊

取對數(shù)，得，將各個觀測值進行變換，如圖4-21所示，即

在單元格E2：EU中輸入公式飛LN（B2：B11）”（數(shù)組公式輸入），然后將單元格E2：E11復制到

單元格F2F11和G2:GI1中。

-a(=UF32B1I)}

A

BjgicD

1科

冷收入YW5￡i2?X,毗究人員敢X,

山MY)WXJ

31990

>99|

4由25TIM5*72271554WT85099866

5陰16255451775.6T33235087596

6所17155TS94956$nr0935.14I6M

7相307IT95€02119S.T2C8?$.187366

8停182561312s5749393S取8?

9的1915(66427576$1$152S2273

IM571042759021185278U5

1998

1:0201S74ft?3S8Z3D46S303X6

:1999

"192125