利用EXCEL建立季節預測模型

楊 樺

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

關鍵詞:EXCEL;季節預測;模型

季節指數是一種以相對數表示的季節變動變化大小的衡量指標。季節指數廣泛用于受季節影響而出現銷售的淡季和旺季之分的具有季節性變動規律的商品的預測過程中,掌握了季節變動規律,就可以利用它來對季節性的商品進行市場需求量的預測。實際預測中通常需要掌握和運用三年以上的分季歷史數據。季節指數的周期可以是季度,也可以是月度(月度季節指數)。

根據某企業過去4年的A產品銷量的基礎數據,首先判斷是否可用季節指數法進行銷量預測,如可用的話,則用季節指數法預測第五年的產品銷量,并用Excel實現整個判斷和預測過程。

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

用EXCEL 建立季節預測模型,一般的操作步驟是:(1)計算每一季的季節指數;(2)用時間序列的每一個觀測值除以季節指數,消除季節影響;(3)對消除了季節影響的時間序列建立適當的趨勢模型,并用這個模型進行預測;(4)將預測值乘以季節指數,計算出最終的受季節影響的預測值。

具體操作步驟如下:

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

(2)計算四季度移動平均數與移正平均數

選擇“工具”菜單→單擊“數據分析”命令→在彈出的“數據分析”對話框中選擇“移動平均”命令→在“移動平均”對話框的“輸入區域”選擇“C2:C17”,“間隔”為“4”,“輸出區域”選擇“D4:D16”→單擊“確定”按鈕,即可計算出四季度移動平均數。

因為四個季度是偶數,還需再計算移正平均數,單擊E4單元格,在編輯欄中輸入公式“=(D4+D5)/2”復制公式至E15單元格即可。

(3)計算季節不規則值

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

(4)計算季節指數

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

(5)計算消除季節影響的銷售量

激活Sheet2工作表,復制Sheet1中A、B、C列的數值,并將A列的“年”,改為相應的“序號”。

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

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

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

(6)計算趨勢預測值

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

其中FORECAST是根據已有的數值計算或預測未來值函數。A2 為需要進行預測的數據點、$E$2:$E$17 為因變量數據區域、$A$2:$A$17 為自變量數據區域。

(7)建立消除季節影響的折線圖,并建立一元線性趨勢預測公式。

利用圖表向導,選擇數據點折線圖,并添加趨勢線,如圖2所示。可直接利用公式進行預測。

(8)計算季度預測值

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

(9)制作XY散點圖

利用圖表向導,建立實際值與預測值的XY散點圖,可以清晰地反映出各季指標值的變化情況。如圖4所示。

參考文獻:

[1]Excel中的有關預測函數及其應用http://www.goodsgy.com/article/3/23/html/25501.html.