財務預測中構建Excel回歸分析模型

［摘要］ 本文研究在銷售收入、資金占用、成本費用等財務預測中，利用Excel內置的公式與函數、窗體工具交互選擇等功能，構建Excel回歸分析模型的技巧。

［關鍵詞］ 回歸分析；模型構建；Excel函數；窗體工具

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2011 . 06 . 006

［中圖分類號］F232 ［文獻標識碼］A ［文章編號］1673 - 0194（2011）06 -0011- 04

一、導言

預測是對未來狀況的分析、判斷和規劃，Ｅｘｃｅｌ中可使用窗體工具的滾動條、微調項、列表框等，對未來狀況值進行范圍設定，以建立交互選擇模型，簡化復雜的數據計算。回歸分析的運用非常廣泛，在單位理財中主要用于銷售預測、資金預測和成本費用預測等方面。

回歸分析一般用直線方程Ｙ ＝ ａ＋ｂＸ預測未來值。其中，截距參數ａ、斜率參數ｂ的確定方法有：（１）最小二乘法公式計算；（２）Ｅｘｃｅｌ截距函數Ｉｎｔｅｒｃｅｐｔ、斜率函數Ｓｌｏｐｅ確定；（３）用直線擬合函數Ｌｉｎｅｓｔ計算，再將其作為數值引用函數Ｉｎｄｅｘ的嵌套函數，以返回參數值等。

財務預測時，一般都要用到計數函數ＣＯＵＮＴ、求和函數ＳＵＭ、四舍五入函數ＲＯＵＮＤ、條件函數ＩＦ，以及字符運算、顯示公式或計算結果等技術方法，以充分利用Ｅｘｃｅｌ強大的計算、分析、提示等功能。

二、銷售回歸分析的模型構建

Ｅｘｃｅｌ回歸分析時，可用最小二乘法公式確定ａ、ｂ的參數值，再用Ｙ ＝ ａ＋ｂＸ預測未來銷售收入。最小二乘法參數公式如下：

式中，ｙ表示銷售量或銷售額；Ｘ表示時間序列；ａ表示固定不變的銷售；ｂ表示隨時間序列變動的單位銷售；ｎ表示歷史數據的期間項數；∑表示求和。

Ｅｘｃｅｌ提供了計算期間項數ｎ的計數函數ＣＯＵＮＴ，其函數公式如下：

＝ ＣＯＵＮＴ（ｖａｌｕｅ１，ｖａｌｕｅ２，…）

式中，ｖａｌｕｅ表示包含或引用的數字型數據的參數，可以設置１至３０個參數。

【案例１】 某公司甲產品近５年銷量如表１所示。要求用回歸直線法預測該產品未來１３年的銷售情況。

【模型構建】構建的銷售預測模型如圖１所示。單擊Ｂ１２單元格中的組合框下拉箭頭，在列表中選定某年度，該年度的銷量預測即可顯示于Ｄ１２單元格中，并在Ｂ１３單元格中進行提示。該模型的設計方法如下：

１．設計表格。鍵入文字、已知數據，合并單元格區域等。

２．求和、乘積與冪運算。在Ｅ３單元格中鍵入“＝Ｃ３*Ｄ３”，Ｆ３單元格中鍵入“＝Ｃ３＾２”，Ｃ８單元格中鍵入“＝ＳＵＭ（Ｃ３：Ｃ７）”；再自動填充Ｅ４：Ｅ７、Ｆ４：Ｆ７和Ｄ８：Ｆ８單元區域的公式。

３．確定觀察值項數。選定Ｂ８單元格，鍵入“＝ＣＯＵＮＴ（Ｂ３：Ｂ７）”，如圖２所示。由于引用區域有５年，所以計算結果為“５”。

４． 輸入參數公式。在Ｂ９單元格鍵入“＝ＲＯＵＮＤ（（Ｄ８－Ｂ１０*Ｃ８）／Ｂ８，１）”。其中ＲＯＵＮＤ是四舍五入函數，參數“１”表示保留一位小數；“（Ｄ８－Ｂ１０*Ｃ８）／Ｂ８”是參數ａ的計算公式。

在Ｂ１０單元格中鍵入對參數ｂ的計算結果四舍五入并保留一位小數的函數公式“＝Ｒｏｕｎｄ（（Ｂ８*Ｅ８－Ｃ８*Ｄ８）／（Ｂ８*Ｆ８－Ｃ８＾２），１）”。

５．字符運算提示信息。在Ｃ１１單元格鍵入“＝＂Ｙ ＝ ＂＆Ｂ９＆＂＋＂＆Ｂ１０＆＂Ｘ＂”。

６．設計組合框，方法如下：

（１）選擇菜單“視圖／工具欄／窗體”命令，調出窗體工具欄，如圖１上部所示。

７．在Ｄ１２單元格中鍵入Ｙ＝ａ＋ｂＸ的預測公式“＝Ｂ９＋Ｂ１０*（Ｃ７＋Ｂ１２）”；在Ｂ１３單元格中鍵入信息的動態提示公式“＝\"預測結論：\"＆Ｂ７＋Ｂ１２＆\"年銷量\"＆Ｄ１２＆\"萬臺\"”。

８．選擇“工具／選項”菜單命令，在“視圖”選項卡中勾選“公式”復選框，則單元格中顯示公式，如圖２所示；取消該選項，則單元格中顯示計算結果，如圖１所示。

三、資金回歸分析的模型構建

Ｅｘｃｅｌ回歸分析時，可采用線性方程截距函數Ｉｎｔｅｒｃｅｐｔ確定參數ａ的值，斜率函數Ｓｌｏｐｅ確定參數ｂ的值，然后用Y ＝ ａ＋ｂＸ預測資金占用額。參數公式如下：

＝ Ｉｎｔｅｒｃｅｐｔ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ）

＝ Ｓｌｏｐｅ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ）

式中，Ｋｎｏｗｎ＿ｙ’ｓ表示因變量數據組或單元區域；Ｋｎｏｗｎ＿ｘ’ｓ 表示自變量數據點集合。

【案例２】某公司近６年銷售量與資金占用額如表２所示。該公司未來的銷售量可能為２７９萬～３７９萬臺，請用回歸直線法預測其資金占用額。

【模型構建】構建的資金占用預測模型如圖４所示。單擊Ｃ８單元格的微調按鈕，Ｄ８單元格將顯示不同的產銷量，Ｇ８單元格將預測出該產量下的資金占用額，Ｂ９單元格進行信息的動態提示。該模型設計方法如下：

１．設計表格、鍵入已知數據等。

２．計算參數ａ、ｂ值，方法如下：

（１）計算參數ａ的方法是：在Ｃ５單元格鍵入公式“＝Ｉｎｔｅｒｃｅｐｔ（Ｃ４：Ｈ４，Ｃ３：Ｈ３）”。

（２）計算參數ｂ的方法是：在Ｃ６單元格鍵入公式“＝Ｓｌｏｐｅ（Ｃ４：Ｈ４，Ｃ３：Ｈ３）”。

（３）單擊“確定”按鈕回到工作表界面，Ｃ８單元格顯示代碼“１”（當前值或最小值），單擊微調按鈕的上下箭頭，代碼值在１～１００之間變動；選定Ｃ８單元格將其格式設置為居中對齊，使代碼值隱藏于微調按鈕后部。

４．預測資金占用額。在Ｄ８單元格鍵入“＝Ｈ３＋Ｃ８”；在Ｇ８單元格鍵入“＝Ｃ５＋Ｃ６*Ｄ８”，這是回歸資金占用預測公式。

５．字符運算提示信息。在Ｃ７單元格鍵入“＝＂Ｙ＝＂＆Ｃ５＆＂＋＂＆Ｃ６＆＂Ｘ＂”；在Ｂ９單元格鍵入“＝Ｂ８＆Ｄ８＆＂萬臺時，＂＆Ｅ８＆Ｇ８＆＂萬元＂”，以提示不同產銷量下的資金占用額。

四、成本費用回歸分析的模型構建

Ｅｘｃｅｌ回歸分析時，可用直線擬合函數Ｌｉｎｅｓｔ計算參數值，再將其作為Ｉｎｄｅｘ的嵌套函數，以返回這些參數值；然后用Y ＝ ａ＋ｂＸ預測成本費用。參數的函數公式如下：

ｂ ＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ，ｔｒｕｅ，ｔｒｕｅ），１，１）

ａ ＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ，ｔｒｕｅ，ｔｒｕｅ），１，２）

Ｒ２ ＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ，ｔｒｕｅ，ｔｒｕｅ），３，１）

直線擬合函數Ｌｉｎｅｓｔ參數：ｋｎｏｗｎ＿ｙ’ｓ表示因變量；ｋｎｏｗｎ＿ｘ’ｓ表示自變量；第三個參數設為ＴＲＵＥ或空置表示按正常的ａ值計算，若設為ＦＡＬＳＥ則將ａ設為０；第四個參數設為ＴＲＵＥ表示要計算相關系數Ｒ２、ａ和ｂ的值，若設為ＦＡＬＳＥ則只計算ａ和ｂ的值。

數值引用函數Ｉｎｄｅｘ參數：“１，１”返回Ｌｉｎｅｓｔ中的ｂ值；“１，２”返回Ｌｉｎｅｓｔ中的ａ值；“３，１”返回Ｌｉｎｅｓｔ中的Ｒ２值。

相關系數Ｒ２的值為１則完全正相關，為－１則完全負相關，為０則不相關，大于０．８則顯著相關，在０．５～０．８之間則相關，０～０．５則弱相關。只有相關系數大于０．５時，回歸直線法的預測結果才具有參考價值。

【案例３】某公司近６年銷售收入與成本費用總額如表３所示。要求判斷兩者之間的相關程度，并預測未來１０年不同銷售額（最高４ ０００萬元）對應的成本費用。

【模型構建】 構建的成本費用預測模型如圖６所示，單擊Ｂ１０單元格的微調按鈕以確定預測年度并顯示于Ｃ１０單元格；單擊Ｆ１０單元格的滾動條左右箭頭將增減１，單擊滾動條空白處將增減１０，以確定該年的銷售額并顯示于Ｈ１０單元格；第１１行自動提示該年成本費用的預測值。設計方法如下：

１．設計表格。錄入文字，鍵入前４行的已知數據，合并單元格區域等。

２．計算相關參數。在Ｂ５單元格中鍵入 “＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（Ｃ４：Ｈ４，Ｃ３：Ｈ３，ＴＲＵＥ，ＴＲＵＥ），１，２）”，這是相關系數Ｒ２的函數公式；在Ｂ７ 單元格鍵入預測直線的截距參數ａ的函數公式“＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（Ｃ４：Ｈ４，Ｃ３：Ｈ３，ＴＲＵＥ，ＴＲＵＥ），１，２）”；Ｂ８單元格鍵入斜率參數ｂ的函數公式“＝Ｉｎｄｅｘ（Ｌｉｎｅｓｔ（Ｃ４：Ｈ４，Ｃ３：Ｈ３，ＴＲＵＥ，ＴＲＵＥ），１，１）”。

３．參數信息提示。在Ｂ６單元格中鍵入“＝ＩＦ（Ｃ５＞０．８，＂顯著＂，ＩＦ（Ｃ５＞０．５，＂相關＂，＂弱＂））”，以提示相關程度；Ｂ９單元格中鍵入“＝＂Ｙ＝＂＆Ｒｏｕｎｄ（Ｃ７，２）＆＂＋＂＆Ｒｏｕｎｄ（Ｃ８，２）＆＂Ｘ＂”，以提示帶參數的預測公式。

４．信息提示與預測。在Ｃ１０單元格中鍵入“＝Ｈ２＋Ｂ１０＆＂年銷售額＂”，提示以哪一年為預測期；在Ｈ１０單元格中鍵入“＝Ｈ３＋Ｆ１０”，提示該年的銷售額是多少；在Ｂ１１單元格中鍵入“＝Ｈ２＋Ｂ１０＆＂年成本費用額＂”，提示要預測哪一年的成本費用；在Ｅ１１單元格中鍵入在計算時四舍五入并保留兩位小數的直線公式“＝Ｒｏｕｎｄ（Ｃ７＋Ｃ８*Ｈ１０，２）”，顯示該年的成本費用總額。

主要參考文獻

［１］彭韶兵． 管理會計學［Ｍ］． 成都：西南財經大學出版社，２００７．

［２］王順金，莊小歐． Ｅｘｃｅｌ財務與會計應用精粹［Ｍ］． 北京：北京理工大學出版社，２００９．

［３］王順金，鄒俊霞． 財務管理［Ｍ］． 北京：北京理工大學出版社，２００９．

［４］徐興恩． 管理會計學［Ｍ］． 成都：西南財經大學出版社，２００２．