1、第4章 Excel在財務預測中的應用4.1 財務預測概述  財務預測，是指對企業未來的收入、成本、利潤、現金流量及融資需求等財務指標所作的估計和推測。財務預測是編制投資和融資計劃的基礎，是公司制訂成長戰略的基本要素。稱職的財務管理人員應該能夠充分利用公司的有關信息資料，預測公司的財務需要并做出相應的安排。公司成長主要由銷售增長來決定，銷售增長需要相應的資產增長，如果企業已經是滿負荷運轉，不僅流動資產、而且固定資產都要增長，而資產增長需要相應的融資增長。同時，企業進行對外投資和調整資本結構，也需要籌措資金。企業所需要的這些資金，一部分來自企業內部，另一部分通過外部融資取得。由于對外融資

2、時，企業不但需要尋找資金提供者，而且還需做出還本付息的承諾或提供企業盈利前景等信息，使資金提供者確信其投資是安全的并可獲利，這個過程往往需要花費較長的時間。因此，企業需要預先知道自身的財務需求，確定資金的需要量，提前安排融資計劃，以免影響資金周轉。財務預測有助于改善企業的投資決策。雖然投資是決定籌資與否和籌資多少的重要因素，但是根據銷售前景估計出的融資需求，并不一定能夠得到全部滿足。這時，就需要根據可能籌措到的資金來安排銷售增長以及有關的投資項目，使投資決策建立在可行的基礎上。財務預測一般按以下幾個步驟進行。1銷售預測銷售預測是指根據市場調查所得到的有關資料，通過對有關因素的分析研究，預計和測

3、算特定產品在未來一定時期內的市場銷售量水平及變化趨勢，進而預測企業產品未來銷售量的過程。企業的一切財務需求都可以看作是因銷售引起的，銷售量的增減變化，將會引起庫存量、現金流量、應收與應付賬款以及公司其他資產和負債的變化。因此銷售預測在企業預測系統中處于先導地位，它對于指導利潤預測、成本預測和資金預測，進行長短期決策，安排經營計劃，組織生產等都起著重要的作用。2估計收入、費用和利潤收入和費用與銷售量之間也存在一定的函數關系，因此，可以根據銷售數據估計收入和費用，并確定凈利潤。凈利潤和股利支付率，共同決定了內部留存收益所能提供的資金數額。3估計需要的資產資產通常是銷售收入的函數，根據歷史數據可以分

4、析出二者之間的函數關系。根據預計銷售收入和資產與銷售之間的函數關系，可以預測所需資產的總量。某些流動負債也是銷售收入的函數，相應地也可以預測負債的自發增長額，這種增長可以減少企業外部融資的數額。4估計所需融資根據預計資產總量，減去已有的資金來源、負債的自發增長和內部提供的留存收益，可得出所需的外部融資數額。4.2 財務預測的分析方法預測分析的方法有很多種，企業應根據不同的需要選擇不同的預測方法。總的來說，預測分析方法可分為兩大類：定量預測法和定性預測法。4.2.1  定量預測法定量預測法是指在掌握與預測對象有關的各種要素的定量資料的基礎上，運用現代數學方法進行數據處理，從而建立起能夠

5、反映有關變量之間關系的各類預測模型的方法。在財務預測中，經常使用的定量預測法主要有以下幾種。4.2.1.1  移動平均法移動平均法是一種改良的算術平均法，是一種最簡單的自適應預測模型。它根據近期數據對預測值影響較大，而遠期數據對預測值影響較小的事實，把平均數逐期移動。移動期數的大小視具體情況而定，移動期數少，能快速地反映變化，但不能反映變化趨勢；移動期數多，能反映變化趨勢，但預測值帶有明顯的滯后偏差。常用的移動平均法主要有一次移動平均法和二次移動平均法。1一次移動平均法一次移動平均法是根據時間序列，逐期移動，依次計算包含一定項數的時間序列平均數，形成一個平均時間數序列，并據此進行預測

6、。預測模型為式中第t+1期的預測值；、將被平均的n個觀測值；n移動平均的項數，即移動期數。在實際預測中，可以多取幾個n數，并將得到的預測值與實際值進行比較，選用誤差最小的n值。2二次移動平均法二次移動平均法是對時間序列計算一次移動平均數后，再對一次移動平均數序列進行一次移動平均運算。預測模型為。式中二次移動平均數；第t+1期的預測值，即。二次移動平均法解決了一次移動平均法只能預測下一期的局限性，它可以進行近、短期的預測。但它仍不能解決中長期的預測問題。4.2.1.2  指數平滑法指數平滑法實際上也是一種加權平均法，是一種改良的加權平均法，預測模型為式中  平滑系數，01。在

7、指數平滑法中，確定合適的值和初始值是非常重要的。越大，t期的實際值對新預測值的貢獻就越大；越小，t期的實際值對新預測值的貢獻就越小。一般情況下，可以取幾個不同的值進行預測，比較它們的預測誤差，選擇預測誤差最小的值。4.2.1.3  回歸分析預測法回歸分析預測法是通過研究兩組或兩組以上變量之間的關系，建立相應的回歸預測模型，對變量進行預測的一種預測方法。1回歸分析預測法的基本程序進行回歸分析的步驟如下：（1）收集有關資料。將各種可能的影響因素的有關數據盡可能多地收集起來。（2）判斷趨勢。根據收集到的數據，判斷其變化趨勢，從而為建立相應的數學模型做準備。對于變量不多的問題，可以通過繪制散

8、點圖來判斷變化趨勢。（3）建立預測數學模型。根據歷史數據的變化趨勢，選擇相應的描寫該問題的數學模型，并采用相關的計算技術來估計數學模型的參數。（4）相關檢驗。對建立的預測數學模型，必須進行有關的檢驗，主要是通過計算預測模型的相關系數、方差（或標準差）以及顯著性等指標，來判斷預測模型的準確性、是否需要修正、采用何種方法修正等。2回歸模型建立的方法建立回歸模型的一般方法是采用最小二乘法，其原理如下：考慮m個自變量x1、x2、x m和因變量y的關系，現有n組觀測數據，不同xki （k=1,2,m；i=1,2,n）下的y的觀測值為yi，函數y=f(xk)的待估計參數為ak（k=1,2,m+1，這里，每

9、個自變量有一個待估計系數，還有一個待估計常數，故有m+1個待估計參數），通過回歸預測模型得到不同xki下的預測值為 ，則有：殘差平方和SE：剩余標準差SS：相關系數R2：y為觀測值yi的平均值：那么，最小二乘法的原理就是尋找最優的待估計參數ak，使殘差平方和最小。3財務預測中常用的幾種回歸模型（1）一元線性回歸模型當只有兩個變量（一個自變量和一個因變量），并且它們之間存在線性關系時，可以用一元線性回歸模型來描述。一元線性回歸模型為式中a、b回歸系數，其中a代表截距，b代表斜率。（2）一元非線性回歸模型當變量x和y之間的關系不能用線性關系來描述時，則需要建立一元非線性回歸模型。根據變量x和y之間

10、的關系，一元非線性回歸模型常見的幾種情況有：對數模型：指數模型：乘冪模型：雙曲線模型：以上幾種一元非線性模型均可通過數學變換化成一元線性模型。（3）多元線性回歸模型當自變量有兩個或兩個以上，且因變量與這些自變量之間呈線性組合關系時，它們就構成了多元線性回歸模型，模型形式為式中a、b1、b2、bm估計參數；x1、x2、xm自變量。（4）多元非線性回歸模型多元非線性回歸模型用來描述因變量與多個自變量之間呈非線性組合關系的情況。例如，柯柏道格拉斯生產函數就是典型的多元非線性模型：式中：L和K分別為勞動力和固定資本；a、b、c為系數。4.2.1.4  模擬法在企業的實際經濟活動中，各種經濟參

11、數往往并不是確定的，而是隨機變化的，比如產品的銷售量往往隨市場的變化而變化，在這種情況下，就需要對這些參數的不確定性進行分析，而對其預測也就需要采用與傳統的確定性分析不同的方法來進行。一般情況下，可以采用模擬法來解決不確定性情況下的財務預測問題，概率法、蒙特卡羅模擬方法就是較實用的方法。4.2.2  定性預測法定性預測法是由有關方面的專業人員或專家根據自己的經驗和知識，結合預測對象的特點進行綜合分析，對事物的未來狀況和發展趨勢作出推測的預測方法。定性預測法由于帶有較多的個人主觀性，因而在實踐中最好作為一種補充的預測方法。4.3 Excel中的有關預測函數及其應用(1)Excel提供了

12、關于估計線性模型和指數模型參數的幾個預測函數。線性模型和指數模型的數學表達式如下：線性模型：y = mx + b 或 y = m1x1 + m2x2 + + b指數模型：或式中，y為因變量；x是自變量；m、m1、.、mn-1、mn、b分別為預測模型的待估計參數。Excel提供的預測函數主要有LINEST函數、LOGEST函數、TREND函數、GROWTH函數、FORECAST函數、SLOPE函數和INTERCEPT函數，它們所使用的參數都基本相同，現列于表4-1中，以供參考。表4-1  預測函數的參數及含義參數含義known_y's 因變量y的觀測值集合known_

13、x's 自變量x的觀測值集合。它可以是一個變量（即一元模型）或多個變量（即多元模型）的集合。如果只用到一個變量，只要 known-y's 和 known-x's 維數相同，它們可以是任何形狀的選定區域。如果用到不只一個變量，known_y's 必須是向量（也就是說，必須是一行或一列的區域）。如果省略 known_x's，則假設該數組是 1,2,3.，其大小與 known_y's 相同const邏輯值，指明是否強制使常數b為0（線性模型）或為1（指數模型）。 如果const 為 TRUE或省略，b將被正常計算。如果const為FALSE，

14、b將被設為0（線性模型）或設為1（指數模型）stats邏輯值，指明是否返回附加回歸統計值。 如果 stats 為 TRUE，則函數返回附加回歸統計值，這時返回的數組為 mn，mn-1，.，m1，b；sen，sen-1，.，se1，seb，r2，sey；F，df；ssreg，ssresid。如果 stats為FALSE或省略，函數只返回系數預測模型的待估計參數m、mn、mn-1、.、m1和b。附加回歸統計值返回的順序見表4-2。表4-2中的各參數說明見表4-3。如果要得到附加回歸統計值數組中的值，需用INDEX函數將其取出表4-2  附加回歸統計值返回的順序 1234561m

15、nmn-1m2m1b2sensen-1se2se1se b3r2sey    4Fdf    5ssregssresid    表4-3  各參數說明參數說明se1,se2,.,sen系數 m1,m2, .,mn 的標準誤差值Seb常數項 b 的標準誤差值（當 const 為 FALSE時，seb = #N/A ）  參數說明r2相關系數，范圍在 0 到 1 之間。如果為 1，則樣本有很好的相關性，Y 的估計值與實際值之間沒有差別。反之，如果相關

16、系數為 0，則回歸方程不能用來預測 Y 值seyY 估計值的標準誤差FF 統計值或F 觀察值。使用F 統計可以判斷因變量和自變量之間是否偶爾發生過觀察到的關系Df自由度。用于在統計表上查找 F 臨界值。所查得的值和函數 LINEST 返回的F統計值的比值可用來判斷模型的置信度ssreg回歸平方和ssresid殘差平方和4.3 Excel中的有關預測函數及其應用(2)     4.3.1  LINEST函數LINEST函數的功能是使用最小二乘法計算對已知數據進行最佳線性擬合的直線方程，并返回描述此線性模型的數組。因為此函數返回數值為數組，故必須以

17、數組公式的形式輸入。函數公式為= LINEST（known_y's，known_x's，const，stats）下面舉例說明LINEST函數的應用。1一元線性回歸分析LINEST函數可用于一元線性回歸分析，也可以用于多元線性回歸分析，以及時間數列的自回歸分析。當只有一個自變量 x （即一元線性回歸分析）時，可直接利用下面的公式得到斜率和 y 軸的截距值以及相關系數： 斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST（known_y's,known_x's）,1）截距：INDEX（LIN

18、EST（known_y's，known_x's）,1,2）；或INDEX（LINEST（known_y's,known_x's）,2）相關系數：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）【例4-1】某企業19月份的總成本與人工小時及機器工時的數據如圖4-1所示。假設總成本與人工小時之間存在著線性關系，則在單元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”，在單元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”，在單元格B1

19、5插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”，即得總成本與人工小時的一元線性回歸分析方程為：Y=562.72756+4.41444X1，相關系數為R2=0.99801，如圖4-1所示。圖4-1  一元線性回歸分析2多元線性回歸分析仍以例4-1的數據為例，首先選取單元格區域A17:D21，再以數組公式方式輸入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”，即得該二元線性回歸的有關參數如圖4-2所示，從而得到：圖4-2  二元線性回歸分析回歸方程：Y = 471.4366+3.6165X1+3.4

20、323X2相關系數：R2 =0.9990標準差：Sey =11.7792。4.3.2  LOGEST函數LOGEST函數的功能是在回歸分析中，計算最符合觀測數據組的指數回歸擬合曲線，并返回描述該指數模型的數組。由于這個函數返回一個數組，必須以數組公式輸入。LOGEST函數的公式為= LOGEST(known_y's,known_x's,const,stats) 【例4-2】某企業12個月某產品的生產量（X）與生產成本（Y）的有關資料如圖4-3所示，假設它們之間有如下關系：。選取單元格區域B15:C18，輸入公式“=LOGEST(C2:C13,B2:B13,TRUE,T

21、RUE)”（數組公式輸入），即得回歸參數，如圖4-3所示，參數m=0.8887，參數b=1891.7729，生產成本與生產量的回歸曲線為：Y=1791.7729×0.8887X，相關系數R2=0.95885。圖4-3  指數回歸回歸方程的系數及相關系數也可以利用下面的公式直接計算參數m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887參數b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729相關系數R2：=INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.958854.3.

22、3  TREND函數TREND函數的功能是返回一條線性回歸擬合線的一組縱坐標值（y 值），即找到適合給定的數組 known_y's 和 known_x's 的直線（用最小二乘法），并返回指定數組 new_x's 值在直線上對應的 y 值。TREND函數的公式為= TREND(known_y's,known_x's,new_x's,const)式中  new_x's 需要函數 TREND 返回對應 y 值的新 x 值。 new_x's 與 known_x's 一樣，每個獨立變量必須為單獨的一行（或一列）。

23、因此，如果 known_y's 是單列的，known_x's 和 new_x's 應該有同樣的列數，如果 known_y's 是單行的，known_x's 和 new_x's 應該有同樣的行數。如果省略 new_x's，將假設它和 known_x's 一樣。【例4-3】某企業過去一年的銷售量為下列數據：300，356，374，410，453，487，501，534，572，621，650，670，將它們保存在單元格A1:A12中，則下一年的1、2、3月的銷售量預測步驟為：選中單元格區域B1:B3，輸入公式“=TREND(A1:A1

24、2,13;14;15)”（數組公式輸入），即得來年的1、2、3月份的銷售量分別為710、743和777。這個公式默認1;2;3;4;5;6;7;8;9;10;11;12作為known_x's的參數，故數組13;14;15就對應其后的3個月份。4.3 Excel中的有關預測函數及其應用(3)     4.3.4  GROWTH函數GROWTH函數的功能是返回給定的數據預測的指數增長值。根據已知的x值和y值，函數GROWTH返回一組新的x值對應的y值。可以使用GROWTH工作表函數來擬合滿足給定x值和y值的指數曲線。GROWTH函數的公式為

25、= GROWTH(known_y's,known_x's,new_x's,const)式中，各參數的含義同TREND函數。但需注意的是，如果known_y's中的任何數為零或為負，函數 GROWTH將返回錯誤值 #NUM!。 【例4-4】以例4-3的資料為例，利用GROWTH函數預測來年的1、2、3月的銷售量。預測步驟為：選中單元格區域B1:B3，輸入公式“=GROWTH(A1:A12,13;14;15)”（數組公式輸入），即得來年的1、2、3月份的銷售量分別為756、811和870。這個公式同樣默認1;2;3;4;5;6;7;8;9;10;11;12作為kno

26、wn_x's的參數，故數組13;14;15就對應后面的3個月份。4.3.5  FORECAST函數FORECAST函數的功能是根據給定的數據計算或預測未來值。此預測值為基于一系列已知的 x 值推導出的 y 值。以數組或數據區域的形式給定 x 值和 y 值后，返回基于 x 的線性回歸預測值。FORECAST函數的計算公式為 a+bx式中，；。FORECAST函數的公式為= FORECAST(x,known_y's,known_x's)式中x需要進行預測的數據點。需要說明的是： 如果 x 為非數值型，函數 FORECAST 返回錯誤值 #VALUE!。如果 kno

27、wn_y's 和 known_x's 為空或含有不同數目的數據點，函數 FORECAST 返回錯誤值 #N/A。如果 known_x's 的方差為零，函數 FORECAST 返回錯誤值 #DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40) = 10.60725。4.3.6  SLOPE函數SLOPE函數的功能是返回根據 known_y's 和 known_x's 中的數據點擬合的線性回歸直線的斜率。斜率為直線上任意兩點的垂直距離與水平距離的比值，也就是回歸直線的變化率。SLOPE函數的公式為 =

28、SLOPE(known_y's,known_x's)說明：參數可以是數字，或者是涉及數字的名稱、數組或引用。如果數組或引用參數里包含文本、邏輯值或空白單元格，這些值將被忽略。但包含零值的單元格將計算在內。如果 known_y's 和 known_x's 為空或其數據點數目不同，函數 SLOPE 返回錯誤值 #N/A。例如：SLOPE(2,3,9,1,8,7,5,6,5,11,7,5,4,4) = 0.305556。4.3.7  INTERCEPT函數INTERCEPT函數的功能是利用已知的 x 值與 y 值計算直線與 y 軸的截距。截距為穿過 know

29、n_x's 和 known_y's 數據點的線性回歸線與 y 軸的交點。公式為 = INTERCEPT (known_y's,known_x's)例如：INTERCEPT(2, 3, 9, 1, 8, 6, 5, 11, 7, 5) = 0.0483871。4.4 利用數據分析工具解決預測問題(1)除了利用前面介紹的幾個預測函數進行回歸預測分析外，我們還可以使用Excel的數據分析工具庫提供的統計觀測分析工具來解決回歸預測問題。Excel的數據分析工具庫提供了3種統計觀測分析工具，它們是移動平均法、指數平滑法和回歸分析法。下面結合實例來說明這3種方法的具體應用。

30、4.4.1  移動平均法【例4-5】某企業2000年12個月的銷售額如圖4-4所示，分別按3期、5期和7期移動平均所做的預測分析如圖4-4中的C4E13區域所示。以3期移動平均為例為例，具體計算步驟如下：圖4-4  一次移動平均法實例（1）從【工具】菜單中選中【數據分析】命令，則彈出【數據分析】對話框，如圖4-5所示。圖4-5  【數據分析】對話框（2）在【數據分析】對話框中的【分析工具】框中選中【移動平均】選項，則彈出【移動平均】對話框，如圖4-6所示。圖4-6  【移動平均】對話框（3）在【移動平均】對話框中，【輸入區域】框中輸入“$B$2$B$13

31、”，【間隔】框中輸入“3”，【輸出區域】框中輸入“$C$2”，最后選中【圖表輸出】選項；（4）單擊【確定】按鈕，則運算結果就顯示在單元格區域C4:C13中，如圖4-4所示（圖中的第13行預測數據即為下月即第13月的預測值），并自動出現輸出圖表，如圖4-7所示。圖4-7 移動期數為3時的輸出圖表用同樣的方法，可以分析當移動期數為5和7時的分析結果，如圖4-4所示。4.4.2  指數平滑法【例4-6】某企業的有關銷售數據如圖4-8所示，利用指數平滑法進行預測分析，其步驟如下：圖4-8 指數平滑法實例（1）從【工具】菜單中選中【數據分析】命令，則彈出【數據分析】對話框，在【數據分析】對話框

32、中的【分析工具】框中選中【指數平滑】選項，則彈出【指數平滑】對話框，如圖4-9所示。圖4-9  【指數平滑】對話框（2）在【指數平滑】對話框中，【輸入區域】框中輸入“$B$2:$B$13”，【阻尼系數】框中輸入“0.2”，【輸出區域】框中輸入“$C$3”，最后選中【圖表輸出】選項。（3）單擊【確定】按鈕，則運算結果就顯示在單元格區域C3:C13中（圖中的第13行預測數據即為下月即第13月的預測值），如圖4-8所示，并自動出現輸出圖表，如圖4-10所示。圖4-10  指數平滑法預測輸出圖（阻尼系數0.2）用同樣的方法，可以分析當阻尼系數為0.4和0.6時的分析結果如圖4-8所

33、示。需要注意的是，【數據分析】中的指數平滑法所使用的阻尼系數并不是4.2.1.2節介紹的指數平滑法預測方程中的平滑系數a，二者的關系為：阻尼系數=1a。4.4 利用數據分析工具解決預測問題(2)4.4.3  回歸法利用Excel的回歸工具進行預測分析有兩種方法：一是圖表法；二是回歸分析法。4.4.3.1  圖表法圖表法僅能解決一元線性或非線性回歸問題，不能解決多元回歸問題。【例4-7】某企業連續9年的產品銷售收入Y（萬元）與廣告支出X1（萬元）和居民平均收入X2（元）的有關數據如圖4-11所示，則利用圖表法進行回歸分析，其方法和步驟如下，這里僅以銷售收入Y（萬元）與廣告支出

34、X1（萬元）的一元線性關系為例：圖4-11  某企業的有關銷售數據（1）選擇單元格區域B2:C10。（2）單擊工具欄上的【圖表導向】按鈕，在【圖表導向4步驟之1圖表類型】中選“XY散點圖”，其【子圖表類型】選第1種，如圖4-12所示。圖4-12  準備作散點圖（3）單擊【下一步】按鈕，出現【圖表導向4步驟之2圖表源數據】對話框，單擊【系列】，在【名稱】欄中填入“銷售收入”，在【X值】欄中輸入“=Sheet1!$C$2:$C$10”，在【Y值】欄中輸入“=Sheet1!$B$2:$B$10”（用鼠標拾取單元格區域），如圖4-13所示。圖4-13  填入源數據（4）單

35、擊【下一步】按鈕，出現【圖表導向4步驟之3圖表選項】對話框，填入各標題文字，如圖4-14所示。圖4-14  填入各標題文字（5）單擊【下一步】按鈕，出現【圖表導向4步驟之4圖表位置】對話框，不作任何輸入，單擊【完成】按鈕，則在工作表上看到輸出的圖形，對其進行必要的調整（如坐標、字體、位置等）。（6）在系列【數據點】上的任一點上，按鼠標左鍵，使各數據點出現記號，再單擊【工具欄】上的【圖表】按鈕，選中【添加趨勢線】項，或在數據點上按鼠標右鍵，選【添加趨勢線】項，出現【添加趨勢線】對話框，如圖4-15所示。圖4-15  【添加趨勢線】對話框（7）在【添加趨勢線】中的【類型】對話框

36、中，有【線性】、【對數】、【多項式】、【乘冪】、【指數】和【移動平均】6個選項。通過觀察XY散點圖可知，產品銷售收入與廣告支出之間呈明顯的線性關系，故這里選【線性】。（8）在【添加趨勢線】中的【選項】對話框中，勾選【顯示公式】、【顯示R平方值】，如圖4-16所示。圖4-16  【添加趨勢線】的【選項】設置（9）單擊【確定】按鈕，則在圖形上顯示出較粗的預測線、回歸方程和R平方值，然后進行必要的調整，得到如圖4-17的結果。圖4-17 輸出圖形用同樣的方法還可以確定銷售收入與居民平均收入的關系。4.4 利用數據分析工具解決預測問題(3)     4

37、.4.3.2  回歸分析法回歸分析法可以對一元線性或多元線性以及某些可以轉化為線性的非線性問題進行回歸分析。1線性回歸【例4-8】仍以例4-7的有關資料為例，回歸分析的步驟如下：（1）從【工具】菜單中選中【數據分析】命令，則彈出【數據分析】對話框，在【數據分析】對話框中的【分析工具】框中選中【回歸】選項，如圖4-18所示，則彈出【回歸】對話框。圖4-18  【數據分析】對話框（2）在【回歸】對話框中，【Y值輸入區域】中輸入“$B$1:$B$10”，【X值輸入區域】中輸入“$C$1:$D$10”，在【輸出選項】中勾選【輸出區域】，填入“$A$12”，然后根據實際需要，勾選其他

38、需要的選項，如圖4-19所示。圖4-19 【回歸】選項（3）單擊【確定】按鈕，回歸分析的摘要就輸出在本工作表上，如圖4-20所示。對這些數據進行分析可知：R平方值為0.9903，說明因變量與自變量之間相關性很高；F的顯著值為2.961E-07，已達0.05的檢驗標準；其他統計檢驗也達到相應的標準。從而得到回歸方程為：Y=229.8409+9.2794X1+0.0082 X2。圖4-20  回歸分析的計算機輸出當自變量只有一個（即一元回歸）時，上述方法同樣適用。2非線性回歸對于某些可以化為線性關系的非線性問題，同樣可以進行回歸分析。舉例如下。【例4-9】某地區科研系統近10年的凈收入Y

39、（千萬元）與研究經費X1（千萬元）和研究人員數X2（萬人）的統計資料，如圖4-21所示，假設它們之間存在著以下的函數關系：式中，a、b、c為待估計參數。若利用回歸工具求解此類非線性問題，解決的辦法是將此方程進行數學變換，即對方程兩邊取對數，得，將各個觀測值進行變換，如圖4-21所示，即在單元格E2:E11中輸入公式“=LN(B2:B11)”（數組公式輸入），然后將單元格E2:E11復制到單元格F2:F11和G2:G11中。圖4-21 某地區科研系統有關資料再對變換后的數據利用Excel的回歸工具進行回歸分析，具體步驟可參閱【例4-7】，其中【Y值輸入區域】中輸入“$E$1:$E$11”，在【X

40、值輸入區域】中輸入“$F$1:$G$11”，在【輸出選項】中勾選【輸出區域】，填入“$A$12”，得到如圖4-22所示的分析結果，最后得到：a = e-0.08214 = 0.9211，b =0.4477，c = 0.6046，相關系數為0.9808（注意此相關系數是變換后的線性方程的相關系數，并不是原非線性方程的相關系數）， 回歸方程為：。圖4-22  回歸分析結果4.5 利用規劃求解工具解決預測問題        雖然我們可以利用Excel提供的各種預測分析工具解決大多數財務預測中的實際問題，但這些預測分析工具并不是萬能

41、的，其預測誤差也隨著實際問題的復雜化而增大。比如對于一些非線性預測問題，常常是將其通過變量替換而轉換為線性問題。但是，這種變換過程一方面增加了計算工作量，另一方面也可能導致分析精度下降，因為變換后的數據容易使觀測數據的性質發生變化，導致自變量與因變量之間的關系發生扭曲，從而影響回歸方程的精度，因此，這種將非線性轉換為線性的做法是存在一定的缺陷的。此外，有些非線性問題根本無法直接轉換為線性問題，除非作出大量的簡化，這必然使得到的回歸方程嚴重失真。因此，對于非線性回歸問題，最好的方法是直接進行回歸分析，即求解使殘差平方和最小、或使相關系數最大的回歸方程，但非線性回歸過程是一個循環尋優過程，需要先設

42、置回歸方程系數的初值，然后計算觀測值與預測值的殘差平方和，不斷尋找使殘差平方和最小的回歸方程系數，這實際上是一個優化問題，因此，可以利用Excel的規劃求解工具求解非線性回歸問題，當然也可以用來求解線性回歸問題。在利用規劃求解工具直接求解非線性回歸問題時，需要使用以下幾個計算公式：自由度df為式中，n為觀測次數；m為待估計參數個數。殘差平方和SE為式中，Yi、 分別為第i個觀測值和預測值（i =1,2,n）。剩余標準差SS為相關系數R2為式中，為觀測值的平均值。下面結合實例說明在Excel上進行非線性回歸的具體方法和步驟。【例4-10】以例4-9的有關資料為例，利用Excel的規劃求解工具來求

43、解非線性回歸問題的方法和步驟如下：（1）如圖4-23所示，單元格G2:G4為變動單元格，分別存放待估計參數a、b、c，其初值可設為0。（2）在單元格E2:E11中輸入預測值公式“=G2*(C2:C11)G3*(D2:D11)G4”（數組公式輸入）。圖4-23 利用規劃求解工具進行非線性回歸分析（3）在單元格G5中輸入觀測值的平均值公式“=AVERAGE(B2:B11)”； 在單元格G6中輸入自由度公式“=COUNT(B2:B11)-COUNT(G2:G4)”；在單元格G7中輸入殘差平方和公式“=SUM(B2:B11-E2:E11)2)”（數組公式輸入）；在單元格G8中輸入剩余標準差公式“=SQ

44、RT(G7/G6)”；在單元格G9中輸入相關系數R2的計算公式“=1-G7/SUM(B2:B11-G5)2)”（數組公式輸入）。（4）單擊EXCEL工具菜單，選擇【規劃求解】項，出現【規劃求解參數】對話框；（5）在【規劃求解參數】對話框中，【設置目標單元格】設置為單元格“$G$7”，即目標函數為殘差平方和；【等于】設置為“最小”；【可變單元格】設置為“$G$2:$G$4”。然后單擊【求解】，即可得到回歸方程的系數a、b、c，出現“規劃求解結果”對話框，然后單出【確定】按鈕，保存規劃求解結果。需要注意的是，若系數a、b、c的初值設置不合適的話，則一次求解過程（即在Excel上進行【工具】

45、4;【規劃求解】 ®【求解】 ®【確定】這樣一個求解過程）可能得不到最優結果（或得不到解），這時需要進行多次求解，即在第一次求解結果的基礎上，再進行第二次求解，得到第二次求解結果，然后在第二次求解結果的基礎上，再進行第三次求解，得到第三次求解結果，如此繼續下去，直到求出的系數a、b、c的值不再變化、且殘差平方和最小為止，即得到最優結果。在上例中，當a、b、c的初始值設為0時，經過2次求解過程即得到最優結果，如圖4-23所示。若采用非線性轉換為線性的方法，如例4-9所示，可得到有關系數如圖4-22所示，將此系數代入回歸方程，計算不同X1和X2下的預測值，進而計算出殘差平方和與

46、剩余標準差分別為157.1139和4.7376，與圖4-23的直接非線性回歸的結果（殘差平方和154.03、剩余標準差4.6909）進行比較，可見非線性轉換為線性的方法得出的結果誤差要大于直接進行非線性回歸的誤差。4.6 銷售預測       銷售預測的準確程度，對企業的興衰成敗會產生很重要的影響。銷售預測比較準確，會使企業在有計劃的財務安排下順利運作；而如果銷售預測與實際情況偏離很遠，則會使企業遇到麻煩，甚至陷入困境。因此，銷售預測是企業進行財務預測的首要工作，是企業制定財務計劃的基礎。  銷售預測主要應根據市場需求的變化，結合企業

47、的利潤目標、實現企業市場份額的目標，并綜合考慮企業內外部的各種限制條件的影響來進行。一般情況下，可首先分別對未來各期的銷售量和銷售價格進行預測，在此基礎上，根據預測的產品銷售價格乘以預測的銷售量得到預測的銷售額；也可以直接根據銷售額的有關歷史資料，采用適當的方法進行預測。4.6.1  銷售預測的基本方法銷售預測是一項比較復雜的工作，需要考慮的因素很多，作出準確的預測是非常困難的。通常可利用企業過去的數據進行統計分析，并結合經濟環境對未來市場的影響以及企業內外部各種條件的限制，作出銷售預測。進行銷售預測的方法很多，常用的方法包括以下幾種。1時間序列預測法時間序列預測法，是指將觀察或記錄

48、的一些歷史數據，按時間的先后排列成數據系列，進行統計分析，找出過去長期的銷售量或銷售額的增減變化趨勢，再根據此變化趨勢分析的結果，預測未來時期的銷售量或銷售額。常見的時間序列的預測方法有簡單平均法、移動平均法、指數平滑法、或以時間為自變量的回歸分析法等，這些方法的基本原理可參閱前面的有關內容。2因果關系預測法因果關系預測法，是指利用有關因素與產品銷售量或銷售額之間的固有因果關系，通過建立一定的數學模型來預測企業未來的產品銷售水平的一種方法。企業產品銷售水平的高低，往往受到諸多宏觀或微觀、外部或內部、客觀或主觀等因素的影響，通常可以通過回歸分析的方法檢驗出哪些因素與銷售水平之間具有因果關系，在此

49、基礎上可建立回歸方程，進行銷售預測。有關如何建立回歸方程及進行相關檢驗的方法可參閱前面的有關內容。3通過生產能力或訂貨合同進行銷售量（銷售額）預測企業生產的產品如果在市場占有穩定的份額或供不應求，則可按本企業的生產能力預測產品的銷售量，計算公式如下：計劃期銷售量=計劃期初庫存量+計劃期預計生產量-計劃期末預計庫存量4.6.2  銷售預測模型及其應用在很多情況下，通過建立企業的銷售預測模型，可以很方便地實現銷售預測。下面介紹兩個銷售預測模型。4.6.2.1  一元線性（非線性）回歸預測模型【例4-11】根據圖4-24中所給的資料建立一元線性（非線性）回歸預測模型。圖4-24&

50、#160; 一元線性（非線性）回歸銷售預測模型下面利用線性回歸中的LINEST函數和指數回歸中的LOGEST函數，來建立一元線性（非線性）回歸預測模型。（1）首先建立銷售預測模型，如圖4-24所示，這里以過去12期的銷售量為歷史數據（可以是以年計算，也可以是以月計算，圖4-24為以年計算）。（2）設置回歸模型選擇控件，控件的數據源區域為$A$7:$A$8，單元格鏈接為$B$7，下拉顯示項數為2。（3）選取單元格區域B3:M3，單擊【插入】®【名稱】®【定義】命令，或直接單擊編輯欄中的名稱框，將影響因素所在的單元格區域B3:M3定義為“影響因素序列”；用同樣的方法，將銷售量所

51、在的單元格區域B4:M4定義為“銷售序列”。（4）在單元格E8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,2)”，計算系數A。（5）在單元格F8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,1)”，計算系數B。（6）在單元格G8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRU

52、E,TRUE),3,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),3,1)”，計算相關系數R2。（7）在單元格J8:M8中輸入公式“=IF(B7=1,E8+F8*J7:M7,E8*F8J7:M7)”（數組公式輸入），計算未來第14期的預測值。在影響因素和銷售量兩列輸入歷史數據，并在J7:M7中輸入未來4期的影響因素預測數值后，即可得到回歸預測模型及未來的預測值。通過選擇不同的回歸模型，可以分別計算一元線性模型和一元指數模型下的回歸結果及預測值。由計算結果可知，采用指數模型（相關系數為0.9742）要比線性模型（相關系數為0.9452）更為準確。4.6.2.2&#

53、160; 多元線性回歸預測模型【例4-12】根據圖4-25中所給的資料建立多元線性回歸預測模型。圖4-25  多元線性回歸銷售預測模型當影響銷售量（額）的因素不止一個時，就需要建立多元線性回歸模型。下面就二元線性回歸預測模型的建立進行說明，對于影響因素在兩個以上的情況，可參照本模型建立。（1）首先建立銷售預測模型，如圖4-25所示，這里以過去12期的歷史數據為依據（可以是按年計算，也可以是按月計算，圖4-25為按月計算）。（2）選取單元格區域B3:M4，單擊【插入】®【名稱】®【定義】命令，或直接單擊編輯欄中的名稱框，將影響因素所在的單元格區域B3:M4定義為“影

54、響因素序列”；用同樣的方法，將銷售額所在的單元格區域B5:M5定義為“銷售序列”。（3）在單元格D9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,3)”，計算系數A。（4）在單元格E9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2)”，計算系數B。（5）在單元格F9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1)”，計算系數C。（6）在單元格G9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),3,1)”，計算相關系數R2。

55、（7）在單元格J9:M9中輸入公式“=D9+E9*J7:M7+F9*J8:M8”（數組公式輸入），計算未來第14期的預測值。在影響因素和銷售額各列輸入歷史數據，并在J7:M8中輸入未來4期的影響因素預測數值后，即可得到回歸預測模型及未來的預測值，如圖4-25所示。4.7 成本預測     4.7.1  成本預測的方法成本是指企業為生產和銷售產品所花費的全部費用。成本可以按很多不同的標準進行分類，如常見的按經濟職能分類和按成本性態分類：成本按經濟職能劃分可分為生產成本和非生產成本兩大類。生產成本又稱制造成本，包括生產過程中發生的直接材料、直接人工

56、和制造費用三個項目；非生產成本又稱非制造成本，包括為銷售產品所花費的銷售費用和為組織企業的生產所花費的管理費用。成本性態又稱成本習性，是指成本總額對業務量（如產銷量）總數的依存關系。按成本性態可將全部成本分為變動成本、固定成本和混合成本三類。變動成本是指總額與業務量總數成正比例變動關系的成本，如直接材料、直接人工、變動性制造費用等。固定成本是指在一定的業務量范圍內，總額不受業務量增減變動影響的成本，如按直線法計提的固定資產折舊費、管理人員工資等。混合成本是指總額隨業務量總數發生變化、但不成正比例變動的成本，通過采用適當的方法可以將混合成本分解為變動成本和固定成本兩大類。成本預測是根據企業未來的

57、發展目標和現實條件，參考其他資料，利用專門的方法對企業未來成本水平及其變動趨勢進行估算和預測。成本預測可為成本決策和實施成本控制提供有用的信息。成本預測的方法主要有：（1）歷史成本法。這種方法主要是根據成本的歷史資料來預測未來的成本水平，常用的方法有高低點法和回歸分析法。（2）目標利潤推算法。這種方法主要是根據有關的經濟預測（銷售量、銷售價格）和企業的目標利潤等數據，來反算成本的一種方法。（3）因素分析法。這種方法是根據預期影響成本的各種因素的變化來預測未來成本，它需要企業擁有較為詳細的資料。（4）比例推算法。這種方法是利用企業的生產消耗與企業有關重大經營成果指標之間的依存關系，按被確認的報告

58、年度成本與這些指標的比例關系推算預測期的成本水平。這種方法只是一種大概的推算方法。上述的成本預測方法中，回歸分析法較為科學也比較常用。下面主要介紹在Excel上利用回歸分析法進行成本預測的具體方法。4.7.2  成本預測模型利用回歸分析法進行成本預測首先需要建立回歸分析模型。回歸分析模型就是將總成本分解成與銷售量無關的固定成本和與銷售量有關的變動成本，從而根據未來的預計銷售量對成本進行預測。模型的數學表達式如下：一元一次模型（線性模型）：      銷售成本=固定成本+單位變動成本×銷售量   

59、   一元二次模型（非線性模型）：銷售成本=固定成本+單位變動成本×銷售量+混合成本×銷售量平方【例4-13】建立一元一次（二次）成本預測模型。在Excel上建立銷售成本的回歸分析模型方法可參閱前面介紹的回歸分析方法，具體步驟如下：（1）首先建立成本預測模型，如圖4-26所示，這里假設已有過去12期的數據為歷史數據；（2）建立線性或非線性模型選擇控件，控件的數據源區域為$A$8:$A$9，單元格鏈接為$B$8，下拉顯示項數為2。（3）在單元各B4:M4中輸入銷售量平方計算公式“=B3:M32”（數組公式輸入），以準備進行一元二次回歸分析。圖4-26 銷售成本預測模型（4）在單元格E9中輸入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,2),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRU