Excel環境下指數平滑預測法最優平滑系數的確定

蔣昌軍

［摘要］指數平滑是財務預測中使用頻率較高的方法，其應用的關鍵在于選擇最優平滑系數。本文對平滑系數的確定方法進行了梳理，指出在Excel環境下進行平滑系數的確定于實際工作中更有意義，在此基礎上探討了Excel環境下運用模擬運算表和規劃求解進行最優平滑系數確定的方法。

［關鍵詞］ 指數平滑；平滑系數；Excel

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 02. 007

［中圖分類號］F275［文獻標識碼］A［文章編號］1673 - 0194（2012）02- 0013- 03

1引言

指數平滑法（Ｅｘｐｏｎｅｎｔｉａｌ Ｓｍｏｏｔｈｉｎｇ）是較為常用的時間序列預測方法，這種預測法認為：在未來一定時期內，預測對象在數量上的演變特征不會脫離該對象過去的發展趨勢，即預測對象的發展具有連續性和規律性，因此可以通過對不同時期歷史數據賦予不同的權數（通常賦予近期數據較大權數，遠期數據較小權數）來推測預測對象未來的發展趨勢。指數平滑最早由霍爾特（Ｃ．Ｃ．Ｈｏｌｔ）于１９５７年提出，布朗（Ｂｒｏｗｎ）于１９６２年在其著作中詳細論述了這一預測方法。憑借易理解、易操作、計算工作量較小等優勢，指數平滑預測法在國民經濟各領域得到廣泛應用，財務預測中也經常使用這種方法，統計資料顯示，指數平滑在預測方法中的使用頻率僅次于回歸分析，達到１３．１６％。

指數平滑預測法的核心在于平滑初值的確定以及平滑系數的選擇。雖然平滑初值和平滑系數都對預測結果產生影響，但理論與實踐證明，平滑系數是其中的瓶頸因素。這是因為指數平滑允許通過選取較大的平滑系數來削弱平滑初值對預測結果的影響，因此如何確定最優平滑系數就成為指數平滑預測的關鍵。國內理論工作者對指數平滑的研究有相當一部分是針對平滑系數如何確定：袁立（１９８５）探討了分階段平滑系數的選擇，將預測分為初始階段和一般階段，并就各階段分別介紹了平滑系數的確定方法；張紹和 等（１９８９）指出采用最小二乘法確定平滑系數于手工計算不實用，提出了不斷用預測誤差來修正預測值的季節性指數平滑預測方法；唐炎森（１９９７）探討了傳統方式下平滑系數的確定，并利用最小平方法導出了確定平滑系數的近似公式；徐大江（１９９９）指出合適的平滑系數必須根據實際問題背景及所選預測模型的特性加以選取；熊國強（２０００）對指數平滑預測模型進行了精度分析，建立了估計指數平滑系數的最優化模型。這些研究都是以手工計算為基礎研討平滑系數的確定，而討論如何借助計算機確定平滑系數的文獻卻較少。葉海華 等（２００２）提出了用Ｍａｔｌａｂ實現平滑系數和求導系數的精確表達方法，但由于Ｍａｔｌａｂ軟件的普及率及操作等原因，適用性并不廣泛。在數據處理軟件中，微軟公司的Ｅｘｃｅｌ是運用最多、安裝最為廣泛的軟件之一，絕大多數計算機使用人員都具備基本的Ｅｘｃｅｌ操作技能，因此探討在Ｅｘｃｅｌ環境下如何進行平滑系數的選擇更具有現實意義。

2最優平滑系數確定原理與思路

Ｅｘｃｅｌ數據分析中的指數平滑預測工具需要事先錄入阻尼系數（阻尼系數＝１－平滑系數），并未提供平滑系數測算的參數，因此要確定最優平滑系數，仍需通過公式計算獲得。因為計算機數據處理的優勢，最優平滑系數確定方法的選擇原則應當是使預測結果越精確越好，而不必像手工條件那樣顧慮計算是否復雜。這就意味著原來手工計算條件下不實用的方法在計算機條件下仍然適用。

手工條件下平滑系數的確定通常有3種方法。第一種是利用有關方法推導出近似計算公式，這種方法適合手工計算，但推導公式計算值只能是最優平滑系數的近似值。第二種是經驗判斷法，這種方法又分為3種情形：（１）當時間序列呈現較穩定的水平趨勢時，應選較小的平滑系數，一般可在０．０５～０．２０之間取值；（２）當時間序列有波動但長期趨勢變化不大時，可選稍大的平滑系數，一般在０．１～０．４之間取值；（３）當時間序列波動很大，長期趨勢變化幅度較大且呈現明顯而迅速的上升或下降趨勢時，宜選擇較大的平滑系數，一般在０．６～１之間選值。顯然這種方法需要結合經驗判斷，主觀性太強，但可以在Ｅｘｃｅｌ數據預測中作為平滑系數選擇范圍的參考。第三種方法是誤差試算法，即最優平滑系數的選擇一般以預測誤差作為判斷標準，使預測誤差達到最小的平滑系數就是最優平滑系數。這種方法最為科學合理，而且借助Ｅｘｃｅｌ中的模擬運算表或數組公式可以方便快捷地計算不同系數情況下的預測值及預測標準誤差。使用這種方法需要大致確定一個范圍及平滑系數的精度（可通過經驗判斷法），再結合計算機進行不同系數情況下的計算，根據計算結果比較預測標準誤差，選取標準誤差最小的系數作為指數平滑的最優系數。

3基于Ｅｘｃｅｌ的最優平滑系數確定模型

本文以某公司銷售額預測為例來說明在誤差試算法下如何借助Ｅｘｃｅｌ進行最優平滑系數的選擇。該公司有關銷售額歷史觀測值見表１。

由于需要進行若干次平滑系數測算，而Ｅｘｃｅｌ指數平滑預測工具不能使用模擬運算表，所以模型中的預測值將直接通過指數平滑公式計算。相關操作步驟如下：

（１）輸入已知數據并做好模型布局（包含平滑系數可能的取值），在Ｇ２單元格添加一個窗體微調項控件，該控件用于設定平滑系數的變動范圍。微調項控件的控制選項參數設置為：最小值１，最大值３０（假定平滑系數取值范圍為０．０１~０．３，不同預測條件下可根據情況設定。理論上平滑系數的取值在０~１之間可以有無數個，但通常對平滑系數的取值精度不會超過0.001，因此事實上在Ｅｘｃｅｌ環境下采用不超過一定取值精度的窮舉法設置平滑系數范圍也是可行的），步長為１，單元格鏈接為Ｈ２（因為控件參數的最小值最大值不允許為小數，而平滑系數只能是０~１之間的小數，需通過Ｈ２單元進行數據轉換才能作為平滑系數使用）。

（２）在Ｇ２單元格輸入公式“＝Ｈ２／１００”，完成從Ｈ２單元到Ｇ２單元的平滑系數小數轉換，并建立起平滑系數與微調項之間的數據關聯。

（３）在Ｃ４單元格輸入公式“＝Ｂ４”，作為平滑初值。

（５）在Ｇ７單元格輸入ＭＳＥ計算公式“＝ＳＵＭＸＭＹ２（Ｂ５：Ｂ１９，Ｃ５：Ｃ１９）／ＣＯＵＮＴ（Ｂ５：Ｂ１９）”，如果不使用ＳＵＭＸＭＹ２函數，也可分步驟計算均方誤差，但稍顯麻煩，工作量也增加許多。

（６）這一步是最優平滑系數確定的關鍵，需要在平滑系數可能的取值范圍內借助Ｅｘｃｅｌ分別對平滑系數進行均方誤差的模擬運算。方法是選擇Ｆ７：Ｇ３７單元區域，點擊“模擬運算表”，在模擬運算表“輸入引用列的單元格”參數中輸入“Ｇ２”，完成所有平滑系數變動范圍內的均方誤差計算。如果此步驟不用模擬運算表，也可通過直接輸入數組公式計算。

（７）在Ｇ３單元格輸入“＝ＭＩＮ（Ｇ８：Ｇ３７）”，通過函數查找均方誤差的最小值，此時計算機顯示最小ＭＳＥ為３．９１２ ６。

（８）在Ｇ４單元格輸入“＝ＩＮＤＥＸ（Ｆ８：Ｆ３７，ＭＡＴＣＨ（Ｇ３，Ｇ８：Ｇ３７，０））”，該公式表示借助最小ＭＳＥ的相對位置在平滑系數所有可能取值中取得對應位置的最優值，至此，最優平滑系數已經找到：０．１３。整個模型的公式及最終計算結果分別見圖１、圖２。

除了以上介紹的利用模擬運算表確定最優平滑系數方法外，利用Ｅｘｃｅｌ的規劃求解也能較快查找到平滑系數，而且與模擬運算表方法相比，規劃求解并不需要將平滑系數的可能取值列出，它通過多次迭代計算自動返回最優平滑系數，并且返回的平滑系數精度更高。

該種方式下操作步驟（１）至（５）與前面使用模擬計算表的步驟（1）至（5）相同。在第（６）步，點擊“工具”—“規劃求解”菜單輸入規劃求解參數，其中“目標單元格”輸入“Ｇ７”，“等于”選擇“最小值”，“可變單元格”選擇“Ｇ２”，“約束條件”添加“Ｇ２＜＝１，Ｇ２＞＝０”，點擊“求解”后系統自動進行計算，并在Ｇ２單元格返回最優平滑系數：０．１３４，這與采用前述方法得到的結果一致，見圖３。

4結語

從以上計算結果來看，只要將指數平滑的有關理論應用到Ｅｘｃｅｌ中并設定好相關運算參數的計算公式，Ｅｘｃｅｌ就能輕松地根據要求求解出結果，不但運算速度快，而且運算結果準確、精度也高。ＩＴ技術的進步以及預測的復雜性要求進一步挖掘Ｅｘｃｅｌ的應用功能，本文介紹的操作方法只是拋磚引玉，如何充分利用Ｅｘｃｅｌ建立方便快捷的財務預測解決方案還有待進一步探討。

主要參考文獻

［１］袁立.指數平滑常數的特性及其選擇［Ｊ］.預測，１９８５（z１）:１３７－１４２.

［２］葉海華，張錄達，吉海彥，齊小明.利用ＭＡＴＬＡＢ實現平滑系數的精確表達［Ｊ］.北京農學院學報，２００２（３）：４６－５０.

［３］金旭星，盛奎川.指數平滑參數與初值的選取研究［Ｊ］.江南大學學報：自然科學版，２００５（３）：３１６－３１９.

［４］徐大江.預測模型參數的指數平滑估計法及其應用的進一步研究［Ｊ］.系統工程理論與實踐，１９９９（２）：２５－３０，４３.

［５］唐炎森.確定平滑系數的新方法［Ｊ］.統計與信息論壇，１９９７（３）：１５－１７，２１.

［６］黎鎖平，劉坤會.平滑系數自適應的二次指數平滑模型及其應用［Ｊ］.系統工程理論與實踐，２００４（２）：９５－９９.