利用EXCEL建立季節(jié)預(yù)測(cè)模型

楊 樺

摘要:Microsoft Excel是Microsoft Office應(yīng)用軟件包中的一員,是一個(gè)功能強(qiáng)大、技術(shù)先進(jìn)、使用方便的表格式數(shù)據(jù)綜合管理和分析軟件。它采用電子表格方式進(jìn)行數(shù)據(jù)處理,通過豐富的內(nèi)置函數(shù)和分析工具可以進(jìn)行各種數(shù)據(jù)處理、統(tǒng)計(jì)分析、輔助決策,并具有強(qiáng)大的制圖功能,可以方便地繪出各種專業(yè)圖表,實(shí)現(xiàn)了圖、文、表三者的完美結(jié)合。近幾年來,人們廣泛利用其工具來進(jìn)行財(cái)務(wù)預(yù)測(cè),本文主要闡述了如何利用Excel來建立季節(jié)預(yù)測(cè)模型的方法,相信通過這些方法,有助于我們科學(xué)的分析市場(chǎng),準(zhǔn)確的預(yù)測(cè)市場(chǎng),從而更加靈敏的反應(yīng)市場(chǎng),更加有效的滿足市場(chǎng),有利于提升市場(chǎng)營(yíng)銷主體的基本能力。

關(guān)鍵詞:EXCEL;季節(jié)預(yù)測(cè);模型

季節(jié)指數(shù)是一種以相對(duì)數(shù)表示的季節(jié)變動(dòng)變化大小的衡量指標(biāo)。季節(jié)指數(shù)廣泛用于受季節(jié)影響而出現(xiàn)銷售的淡季和旺季之分的具有季節(jié)性變動(dòng)規(guī)律的商品的預(yù)測(cè)過程中,掌握了季節(jié)變動(dòng)規(guī)律,就可以利用它來對(duì)季節(jié)性的商品進(jìn)行市場(chǎng)需求量的預(yù)測(cè)。實(shí)際預(yù)測(cè)中通常需要掌握和運(yùn)用三年以上的分季歷史數(shù)據(jù)。季節(jié)指數(shù)的周期可以是季度,也可以是月度(月度季節(jié)指數(shù))。

根據(jù)某企業(yè)過去4年的A產(chǎn)品銷量的基礎(chǔ)數(shù)據(jù),首先判斷是否可用季節(jié)指數(shù)法進(jìn)行銷量預(yù)測(cè),如可用的話,則用季節(jié)指數(shù)法預(yù)測(cè)第五年的產(chǎn)品銷量,并用Excel實(shí)現(xiàn)整個(gè)判斷和預(yù)測(cè)過程。

在用季節(jié)指數(shù)模型進(jìn)行產(chǎn)品的銷量預(yù)測(cè)時(shí)要遵循的原則是:許多時(shí)間序列不僅含有趨勢(shì)成分,而且含有季節(jié)成分,對(duì)于這一類型的時(shí)間序列,首先需要剔除季節(jié)影響,然后再看這個(gè)消除季節(jié)影響的時(shí)間序列是否存在長(zhǎng)期趨勢(shì),這種對(duì)時(shí)間序列進(jìn)行分解的方法,一般可建立在乘法模型的基礎(chǔ)上。由于隨機(jī)成分的不可預(yù)測(cè),因此預(yù)測(cè)值就可表示為趨勢(shì)成分和季節(jié)成分的乘積。

用EXCEL 建立季節(jié)預(yù)測(cè)模型,一般的操作步驟是:(1)計(jì)算每一季的季節(jié)指數(shù);(2)用時(shí)間序列的每一個(gè)觀測(cè)值除以季節(jié)指數(shù),消除季節(jié)影響;(3)對(duì)消除了季節(jié)影響的時(shí)間序列建立適當(dāng)?shù)内厔?shì)模型,并用這個(gè)模型進(jìn)行預(yù)測(cè);(4)將預(yù)測(cè)值乘以季節(jié)指數(shù),計(jì)算出最終的受季節(jié)影響的預(yù)測(cè)值。

具體操作步驟如下:

(1)新建一工作簿,激活Sheet1工作表,分別在A、B、C列輸入年、季度和銷售量。

(2)計(jì)算四季度移動(dòng)平均數(shù)與移正平均數(shù)

選擇“工具”菜單→單擊“數(shù)據(jù)分析”命令→在彈出的“數(shù)據(jù)分析”對(duì)話框中選擇“移動(dòng)平均”命令→在“移動(dòng)平均”對(duì)話框的“輸入?yún)^(qū)域”選擇“C2:C17”,“間隔”為“4”,“輸出區(qū)域”選擇“D4:D16”→單擊“確定”按鈕,即可計(jì)算出四季度移動(dòng)平均數(shù)。

因?yàn)樗膫€(gè)季度是偶數(shù),還需再計(jì)算移正平均數(shù),單擊E4單元格,在編輯欄中輸入公式“=(D4+D5)/2”復(fù)制公式至E15單元格即可。

(3)計(jì)算季節(jié)不規(guī)則值

單擊F4單元格,在編輯欄輸入公式“=C4/E4”,復(fù)制公式至F15單元格即可。

(4)計(jì)算季節(jié)指數(shù)

單擊I2單元格,在編輯欄輸入公式“=AVERAGE(F2,F6,F10,F14)”,復(fù)制公式至I5單元格即可,這樣分別計(jì)算出了1至4季度的季節(jié)指數(shù)。計(jì)算結(jié)果如圖1所示。

(5)計(jì)算消除季節(jié)影響的銷售量

激活Sheet2工作表,復(fù)制Sheet1中A、B、C列的數(shù)值,并將A列的“年”,改為相應(yīng)的“序號(hào)”。

單擊D2:D5單元格區(qū)域,在編輯欄輸入公式“Sheet1!$I$2:$I$5”,按“Ctrl+Shift+Enter”鍵確認(rèn),復(fù)制公式至D18:D21單元格區(qū)域即可。

注:“Ctrl+Shift+Enter”三鍵同按,是數(shù)組公式輸入的方法。

單擊E2單元格,在編輯欄輸入公式“=C2/D2”,復(fù)制公式至E17單元格,這樣就計(jì)算出了消除季節(jié)影響的銷售量。

(6)計(jì)算趨勢(shì)預(yù)測(cè)值

單擊F2單元格,在編輯欄輸入公式“=FORECAST(A2,$E$2:$E$17,$A$2:$A$17)”,復(fù)制公式至E21單元格即可。

其中FORECAST是根據(jù)已有的數(shù)值計(jì)算或預(yù)測(cè)未來值函數(shù)。A2 為需要進(jìn)行預(yù)測(cè)的數(shù)據(jù)點(diǎn)、$E$2:$E$17 為因變量數(shù)據(jù)區(qū)域、$A$2:$A$17 為自變量數(shù)據(jù)區(qū)域。

(7)建立消除季節(jié)影響的折線圖,并建立一元線性趨勢(shì)預(yù)測(cè)公式。

利用圖表向?qū)?選擇數(shù)據(jù)點(diǎn)折線圖,并添加趨勢(shì)線,如圖2所示?？芍苯永霉竭M(jìn)行預(yù)測(cè)。

(8)計(jì)算季度預(yù)測(cè)值

單擊G2單元格,在編輯欄輸入公式“=F2*D2”,復(fù)制公式至G21單元格即可。這樣就計(jì)算出了消除季節(jié)影響的預(yù)測(cè)值。計(jì)算結(jié)果如圖3所示。

(9)制作XY散點(diǎn)圖

利用圖表向?qū)?建立實(shí)際值與預(yù)測(cè)值的XY散點(diǎn)圖,可以清晰地反映出各季指標(biāo)值的變化情況。如圖4所示。

參考文獻(xiàn):

[1]Excel中的有關(guān)預(yù)測(cè)函數(shù)及其應(yīng)用http://www.goodsgy.com/article/3/23/html/25501.html.