Excel環(huán)境下指數(shù)平滑預測法最優(yōu)平滑系數(shù)的確定

蔣昌軍

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

［關鍵詞］ 指數(shù)平滑；平滑系數(shù)；Excel

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

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

1引言

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

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

2最優(yōu)平滑系數(shù)確定原理與思路

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

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

3基于Ｅｘｃｅｌ的最優(yōu)平滑系數(shù)確定模型

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

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

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

（２）在Ｇ２單元格輸入公式“＝Ｈ２／１００”，完成從Ｈ２單元到Ｇ２單元的平滑系數(shù)小數(shù)轉(zhuǎn)換，并建立起平滑系數(shù)與微調(diào)項之間的數(shù)據(jù)關聯(lián)。

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

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

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

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

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

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

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

4結(jié)語

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

主要參考文獻

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

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

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

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

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

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