Excel 財(cái)務(wù)應(yīng)用指數(shù)平滑預(yù)測(cè)

1、Excel財(cái)務(wù)應(yīng)用指數(shù)平滑預(yù)測(cè)指數(shù)平滑預(yù)測(cè)方法是美國(guó)經(jīng)濟(jì)學(xué)家羅伯特G布朗于1959年在他的庫(kù)存管理統(tǒng)計(jì)預(yù)測(cè)一書(shū)中首先提出來(lái)的。布朗（RobertG.Brown）認(rèn)為時(shí)間序列的態(tài)勢(shì)具有穩(wěn)定性或規(guī)則性，所以時(shí)間序列可被合理地順勢(shì)推延；他認(rèn)為最近的過(guò)去態(tài)勢(shì)，在某種程度上會(huì)持續(xù)到最近的未來(lái)，所以將較大的權(quán)數(shù)放在最近的資料。指數(shù)平滑法是生產(chǎn)預(yù)測(cè)中常用的一種方法。也用于中短期經(jīng)濟(jì)發(fā)展趨勢(shì)預(yù)測(cè)，所有預(yù)測(cè)方法中，指數(shù)平滑是用得最多的一種。簡(jiǎn)單的全期平均法是對(duì)時(shí)間數(shù)列的過(guò)去數(shù)據(jù)一個(gè)不漏地全部加以同等利用；移動(dòng)平均法則不考慮較遠(yuǎn)期的數(shù)據(jù)，并在加權(quán)移動(dòng)平均法中給予近期資料更大的權(quán)重；而指數(shù)平滑法則兼容了全期平均和移

2、動(dòng)平均所長(zhǎng)，不舍棄過(guò)去的數(shù)據(jù)，但是僅給予逐漸減弱的影響程度，即隨著數(shù)據(jù)的遠(yuǎn)離，賦予逐漸收斂為零的權(quán)數(shù)。也就是說(shuō)指數(shù)平滑法是在移動(dòng)平均法基礎(chǔ)上發(fā)展起來(lái)的一種時(shí)間序列分析預(yù)測(cè)法，它是通過(guò)計(jì)算指數(shù)平滑值，配合一定的時(shí)間序列預(yù)測(cè)模型對(duì)現(xiàn)象的未來(lái)進(jìn)行預(yù)測(cè)。其原理是任一期的指數(shù)平滑值都是本期實(shí)際觀察值與前一期指數(shù)平滑值的加權(quán)平均。該方法指以某種指標(biāo)的本期實(shí)際數(shù)和本期預(yù)測(cè)數(shù)為基礎(chǔ)，引入一個(gè)簡(jiǎn)化的加權(quán)因子，即平滑系數(shù)，以求得平均數(shù)的一種指數(shù)平滑預(yù)測(cè)法。它是加權(quán)移動(dòng)平均預(yù)測(cè)法的一種變化。平滑系數(shù)必須呈大于0、小于1，如0.1、0.4、0.6等。其計(jì)算公式為：下期預(yù)測(cè)數(shù)=本期實(shí)際數(shù)x平滑系數(shù)+本期預(yù)測(cè)數(shù)x（1-平

3、滑系數(shù)），上列公式是從下列公式演變而成：下期預(yù)測(cè)數(shù)=本期預(yù)測(cè)數(shù)+平滑系數(shù)（本期實(shí)際數(shù)-本期預(yù)測(cè)數(shù)）。這個(gè)公式的含義是：在本期預(yù)測(cè)數(shù)上加上一部分用平滑系數(shù)調(diào)整過(guò)的本期實(shí)際數(shù)與本期預(yù)測(cè)數(shù)的差，就可求出下期預(yù)測(cè)數(shù)。一般說(shuō)來(lái)，下期預(yù)測(cè)數(shù)常介于本期實(shí)際數(shù)與本期預(yù)測(cè)數(shù)之間。平滑系數(shù)的大小，可根據(jù)過(guò)去的預(yù)測(cè)數(shù)與實(shí)際數(shù)比較而定。差額大，則平滑系數(shù)應(yīng)取大一些；反之，則取小一些。平滑系數(shù)愈大，則近期傾向性變動(dòng)影響愈大；反之，則近期的傾向性變動(dòng)影響愈小，愈平滑。這種預(yù)測(cè)法簡(jiǎn)便易行，只要具備本期實(shí)際數(shù)、本期預(yù)測(cè)數(shù)和平滑系數(shù)三項(xiàng)資料，就可預(yù)測(cè)下期數(shù)。1一次指數(shù)平滑預(yù)測(cè)一次指數(shù)平滑法是指以最后的一個(gè)第一次指數(shù)平滑。如果為

4、了使指數(shù)平滑值敏感地反映最新觀察值的變化，應(yīng)取較大阿爾法值，如果所求指數(shù)平滑值是用來(lái)代表該時(shí)間序列的長(zhǎng)期趨勢(shì)值，則應(yīng)取較小阿爾法值。同時(shí)，對(duì)于市場(chǎng)預(yù)測(cè)來(lái)說(shuō)，還應(yīng)根據(jù)中長(zhǎng)期趨勢(shì)變動(dòng)和季節(jié)性變動(dòng)情況的不同而取不同的阿爾法值，一般來(lái)說(shuō)，應(yīng)按以下情況處理：如果觀察值的長(zhǎng)期趨勢(shì)變動(dòng)接近穩(wěn)定的常數(shù)，應(yīng)取居中阿爾法值（一般取0.60.4）使觀察值在指數(shù)平滑中具有大小接近的權(quán)數(shù)。如果觀察值呈現(xiàn)明顯的季節(jié)性變動(dòng)時(shí)，則宜取較大的阿爾法值（一般取0.60.9），使近期觀察在指數(shù)平滑值中具有較大作用，從而使近期觀察值能迅速反映在未來(lái)的預(yù)測(cè)值中。如果觀察值的長(zhǎng)期趨勢(shì)變動(dòng)較緩慢，則宜取較小的e值（一般取0.10.4）,使

5、遠(yuǎn)期觀察值的特征也能反映在指數(shù)平滑值中。在確定預(yù)測(cè)值時(shí)，還應(yīng)加以修正，在指數(shù)平滑值S,的基礎(chǔ)上再加一個(gè)趨勢(shì)值b，因而，原來(lái)指數(shù)平滑公式也應(yīng)加一個(gè)b。當(dāng)時(shí)間數(shù)列無(wú)明顯的趨勢(shì)變化，可用一次指數(shù)平滑預(yù)測(cè)。例如，若某廠2007年1月至10月份的銷售所額如下圖8-80所示。取a=0.3或者a=0.5時(shí)，按上月的實(shí)際銷售額和預(yù)測(cè)值，即可求得各月的預(yù)測(cè)值（設(shè)第一個(gè)月的預(yù)測(cè)值與實(shí)際銷售額相同）。4946BSH：i伽萬(wàn)沅J甲方涅玉平方涅玉卸赭肯8(如萬(wàn)元計(jì)算初.始值上-AVERHC&IE5JETIE描費(fèi)平潘西渭1-實(shí)帶數(shù)平葡彊渭:.：“：，i；T預(yù)滯=0.5預(yù)鼎a=0.S3:ai方咸萬(wàn)元）-J-土總11!55D

6、&DE747J3151&1輸入95491LQ6M信息.13yElL2g4DL3g1410DEM4HShai.1:L酬七2”話帀訶-J圖8-80數(shù)據(jù)模型圖8-81計(jì)算初始值技巧選擇B4單元格，輸入“=AVERAGE(B5:B7)”公式，即可求出前3個(gè)月的觀察值的平均值為初始值，如圖8-81所示。在單元格中輸入“=SUM(B5:B7)/3”公式，也可求出前3個(gè)月的觀察值的平均值為初始值。單擊【分析】組中的【數(shù)據(jù)分析】按鈕，在彈出的【數(shù)據(jù)分析】對(duì)話框中，選擇【指數(shù)平滑】項(xiàng)，如圖8-82所示。0忻工具味i選擇設(shè)置.rg0?國(guó)管rsiBijuI匚*ZlL*JI妒助曲fcAJZMCP：SSU妣也曲Udfz

7、爭(zhēng)即圖8-82選擇【指數(shù)平滑】項(xiàng)圖8-83設(shè)置指數(shù)平滑系數(shù)提示注意在彈出的【指數(shù)平滑】對(duì)話框中，設(shè)置【輸入?yún)^(qū)域】為$B$4:$B$14”；【阻尼系數(shù)】為0.7；【輸出區(qū)域】為“$C$4”,如圖8-83所示。在【指數(shù)平滑】對(duì)話框中，各參數(shù)的功能如下：輸入?yún)^(qū)域在此輸入待分析數(shù)據(jù)區(qū)域的單元格引用。該區(qū)域必須為包含4個(gè)或4個(gè)以上數(shù)據(jù)單元格的單行或單列。阻尼系數(shù)在此輸入需要用作指數(shù)平滑常數(shù)的阻尼系數(shù)。阻尼系數(shù)是用來(lái)將總體樣本中收集的數(shù)據(jù)的不穩(wěn)定性最小化的修正因子。默認(rèn)的阻尼系數(shù)為0.3。其他參數(shù)與上面介紹的【移動(dòng)平均】對(duì)話框中的參數(shù)相同。介于0.2到0.3的值是合理的平滑常數(shù)。這些值表明應(yīng)將當(dāng)前預(yù)測(cè)調(diào)整

8、20%到30%以修正以前的預(yù)測(cè)誤差。常數(shù)越大響應(yīng)越快，但是預(yù)測(cè)變得不穩(wěn)定。常數(shù)較小將導(dǎo)致預(yù)測(cè)值的滯后。在做一次平滑預(yù)測(cè)時(shí)要注意阻尼系數(shù)為1-a，而不是a,計(jì)算要注意。單擊【指數(shù)平滑】對(duì)話框中的【確定】按鈕，即可計(jì)算出各月的預(yù)測(cè)銷售額及預(yù)測(cè)銷售圖表，如圖8-84和8-85所示。AECDEF日MM0=0.3M鼎a=fl.5gYd”元！-:l.V-i)八:飛.10666667ffl/A515049GE6GT62Q2塩TKBT7475Cl43bG7ra51話-ItSbTy,.Pl954924|計(jì)算L0640961T6|殲E7LL7E屯乩玉結(jié)果1204ii旳.922L394B46.724IL105247

9、.ItJbT|4dStotSCJl*K指數(shù)平滑預(yù)測(cè)圖表43dTaQl口11h1丄百.hri3廣圖8-85預(yù)測(cè)銷售圖表圖8-84預(yù)測(cè)銷售額選擇圖表中的標(biāo)題文字，并輸入“a=0時(shí)預(yù)測(cè)銷售額”文字，如圖8-86所示。更改-1294E,67DIQIt圖8-86更改標(biāo)題文字運(yùn)用上述相同的方法，求出當(dāng)預(yù)測(cè)a=0.5時(shí)，預(yù)測(cè)銷售量和平方誤差的值及預(yù)測(cè)銷售圖表，如圖8-87和8-88所示。盤(pán)EcDEJ2“；Y15Mfl-O53/TWKU11Tr萬(wàn)無(wú)TriTiS壬QF49i茹66ETff/ft1K667Qllllll檢.6667oiiiim3252心-他當(dāng)C9&?T?g49.K2?j4_&蟲(chóng)1百翻7gt?3Xt

10、SEie?11.ELOBS50.&Lbfi715.S4D:：3451電石&r2.5UBN45.W033Litres予545t?.683冊(cè)aTaaw町，37917a9MT67LQG4BLEL3T3EZ：iL2.218BS975149.11U丈IBS電4S.TJ4795.06596512e4049.附32曉、J5501440.斫349T.4fl421394B46.7同羽:14T?Gtl44.3BZ93K0B3L4lu!SZ4,7.14967西.ESEOTZ4E.4EEL30.01EG,I-S：l,.i.tL預(yù)測(cè)圖表世=0占時(shí)預(yù)測(cè)銷售額ECi?111111111圖8-87計(jì)算預(yù)測(cè)銷售量和誤差值圖8-

11、88預(yù)測(cè)銷售圖表從上面輸出的圖表結(jié)果不難看出，一次指數(shù)平滑預(yù)測(cè)的結(jié)果存在滯后偏差，即當(dāng)時(shí)間序列呈下降趨勢(shì)時(shí)，預(yù)測(cè)值往往偏高。反之，則偏低。另外，一次指數(shù)平滑預(yù)測(cè)也只能做下-期的預(yù)測(cè)。分別在D15至D16單元格和F15至F16單元格中，分別輸入“=SUM(D5:D14)”、“=AVERAGE(D5:D14)”、“=SUM(F5:F14)”和“=AVERAGE(F5:F14)”公式，計(jì)算出“平方誤差”的合計(jì)值和平均值，如圖8-89所示。AB0DE：F2月備兵廊涓議i=0.3幀g5I31(7-:YK-平P識(shí).垂rt(H56)干7!riR5tU也66b667iff/i519Q490,11111*GB6

12、G7ajiunii63羽497B66T4.STTTB15LB33334E&U+H斗-734.7S0-436S731.B10GB50-91GE7ITS4i：i27T7E449-4072.E41S99iBL9BS324u&34U2T7烏54史別旳Td.7B1S97il979170L95376736JW64B2.62M3B旳卿昭2.2190S5r1174913S343.40：iL53電T44TS5n0E39646Z34已6汨E電亂9M1449.B7Z4ffT.4641594IS91.計(jì)Efill4-i.&2fi214Ld4T-liWr25.52ST24A.4AB130.&01929115nttits

13、.兇W170.O3C336平肉値計(jì)算1-k5Sj9【Iti?13H33EIH4卜.SbMtl.ihrtf?Slw*+S.1.圖8-89計(jì)算合計(jì)值和平均值2二次指數(shù)平滑預(yù)測(cè)二次指數(shù)平滑法是對(duì)一次指數(shù)平滑值作再一次指數(shù)平滑的方法。它不能單獨(dú)地進(jìn)行預(yù)測(cè)必須與一次指數(shù)平滑法配合，建立預(yù)測(cè)的數(shù)學(xué)模型，然后運(yùn)用數(shù)學(xué)模型確定預(yù)測(cè)值。對(duì)于初始值的確定，一般來(lái)說(shuō)，對(duì)于變化趨勢(shì)較穩(wěn)定的觀察值可以直接用第一個(gè)數(shù)據(jù)作為初始值；如果觀察值的變動(dòng)趨勢(shì)有起伏波動(dòng)時(shí)，則應(yīng)以n個(gè)數(shù)據(jù)的平均值為初始值，以減少初始值對(duì)平滑值的影響。本節(jié)主要介紹二次平滑預(yù)測(cè)方法的運(yùn)用。例如，已知某公司2003年到2007年的產(chǎn)品產(chǎn)量如圖8-90所示。試對(duì)其產(chǎn)量進(jìn)行二次指數(shù)平滑預(yù)測(cè)。圖8-90預(yù)測(cè)基本數(shù)據(jù)取初始值Y10=Y20=Y1，故在單元格C3至D3和H3至13單元格中，輸入72。在單元格C4和D4中，分別輸入“=0.7*B4+0.3*C3”和“=0.7*C4+0.3*D3”公式，并復(fù)制這兩個(gè)公式至相應(yīng)列的其他單元格中，如圖8-91所示。在單元格E4和F4單元格中，分別輸入“=2*C4-D4”和“=0.7*(C4-D4)/(1-0.7)”公式,計(jì)算相應(yīng)的平滑系數(shù)at和bt,如圖8-92所示。得到以上預(yù)測(cè)系