EXCEL預(yù)測趨勢功能

1、利用EXCEL預(yù)測趨勢,1,定性預(yù)測法 時間序列預(yù)測法 曲線趨勢預(yù)測法 季節(jié)變動預(yù)測法 回歸分析預(yù)測法,移動平均法 指數(shù)平滑法,不變季節(jié)指數(shù)預(yù)測法 可變、多季節(jié)指數(shù)預(yù)測,目 錄,2,定性預(yù)測法,簡介,定性預(yù)測是指根據(jù)已掌握的歷史資料和直觀材料，運用個人的經(jīng)驗和分析判斷能力，對事物的未來發(fā)展做出性質(zhì)和程度上的判斷，然后，再通過一定形式綜合各方面的意見，作為預(yù)測未來的主要依據(jù)。適用于重大問題或缺乏原式數(shù)據(jù)的預(yù)測。其預(yù)測準(zhǔn)確程度主要取決于預(yù)測者的經(jīng)驗、理論、業(yè)務(wù)水平和分析判斷能力。,3,定性預(yù)測法,原理 1、平均值法是根據(jù)兩組分解物質(zhì)的某種平均值來判斷兩物質(zhì)范圍的解題方法。 2、比重系數(shù)法是根據(jù)各個

2、因素對結(jié)果影響的比重進(jìn)行加權(quán)平均來預(yù)測結(jié)果的方法。 3、中位值法是以一組數(shù)據(jù)的中間數(shù)據(jù)來預(yù)測結(jié)果的方法。,4,定性預(yù)測法,某公司研制出一種新興產(chǎn)品，現(xiàn)在市場上還沒有相似產(chǎn)品出現(xiàn)，因此沒有歷史數(shù)據(jù)可以獲得。公司需要對可能的銷售量做出預(yù)測，以決定敞亮。于是該公司成立專家小組，并聘請業(yè)務(wù)經(jīng)理、市場專家和銷售人員等8為專家，預(yù)測全年可能的銷售量。8為專家提出個人判斷，經(jīng)過三次反饋得到結(jié)果如下表所示。,5,定性預(yù)測法,操作步驟 【平均值法】 輸入給定的數(shù)據(jù) 設(shè)置“E13”單元格的內(nèi)容為“=AVERAGE（H3:H10）” 將“E13”單元格的內(nèi)容復(fù)制到“F13”和“G13”單元格。 設(shè)置“I13”單元格

3、的內(nèi)容為“= AVERAGE（E13: G13）” 【比重系數(shù)法】 假設(shè)最可能銷售量、最低銷售量和最高銷售量比重分別是0.5、0.2和0.3，則可以設(shè)置“I15”單元格內(nèi)容為“=SUMPRODUCT（E13：G13,E15：G15）”，即“= E13* E15+F13*F15+ G13* G15”,之 平均值法 比重系數(shù)法,6,定性預(yù)測法,操作步驟 設(shè)置“E17”單元格的內(nèi)容為“=MEDIAN（H3：H10） 將“E17”單元格的內(nèi)容復(fù)制到“F17”和“G17”單元格 采用比重系數(shù)法中對最可能銷售量、最低銷售量和最高銷售量比重的設(shè)置，設(shè)置“I17”單元格的內(nèi)容為“=SUMPRODUCT（E15

4、：G15,E17：G17）”，即“= E15* E17+F13*F17+ G15* G17”,之 中位值法,7,定性預(yù)測法,8,時間序列預(yù)測法,時間序列是將某種統(tǒng)計指標(biāo)的數(shù)值，按時間先后順序排到所形成的數(shù)列。 時間序列預(yù)測法就是通過編制和分析時間序列，根據(jù)時間序列所反映出來的發(fā)展過程、方向和趨勢，進(jìn)行類推或延伸，借以預(yù)測下一段時間或以后若干年內(nèi)可能達(dá)到的水平。,簡介,9,之 移動平均法,原理 1、一次移動平均法是指將觀察期的數(shù)據(jù)由遠(yuǎn)而近按一定跨越期進(jìn)行一次移動平均，以最后一個移動平均值為確定預(yù)測值的依據(jù)的一種預(yù)測方法。 2、二次移動平均法，是對一次移動平均數(shù)再進(jìn)行第二次移動平均，再以一次移動平

5、均值和二次移動平均值為基礎(chǔ)建立預(yù)測模型，計算預(yù)測值的方法。,時間序列預(yù)測法,10,之 移動平均法,操作步驟 （以“一次移動平均法”為例） 某商場2005年112月份彩電的銷售數(shù)據(jù)如表所示，預(yù)測2006年1月銷售額，單位：萬元 點擊EXCEL菜單欄中【工具】菜單下的子菜單【數(shù)據(jù)分析】；打開“數(shù)據(jù)分析”對話框；從“分析工具”列表中選擇“移動平均”，點擊【確定】按鈕。,時間序列預(yù)測法,11,之 移動平均法,在“移動平均”對話框“輸入?yún)^(qū)域”選擇原始數(shù)據(jù)所在的單元格區(qū)域“C2：C13”，“間隔”中輸入“3”，“輸入?yún)^(qū)域”選擇單元格“E2”，同時選擇“圖標(biāo)輸出”和“標(biāo)準(zhǔn)誤差”復(fù)選框，點擊確定按鈕。 此時，

6、單元格“E13”給出了一次移動平均的預(yù)測值，單元格區(qū)域“F6：F13”給出了預(yù)測的標(biāo)準(zhǔn)差，實際值以及一次移動平均法預(yù)測值同時以圖表形式給出。,時間序列預(yù)測法,12,之 指數(shù)平滑法,分類 1、一次指數(shù)平滑法 2、二次指數(shù)平滑法 3、三次指數(shù)平滑法 4、霍爾特雙參數(shù)現(xiàn)行指數(shù)平滑法,時間序列預(yù)測法,13,之 指數(shù)平滑法,操作步驟 （以“一次指數(shù)平滑法”為例） 下表為1980某地區(qū)平板玻璃月產(chǎn)量數(shù)據(jù)，運用一次指數(shù)平滑法對1981年1月份該地區(qū)平板玻璃月產(chǎn)量進(jìn)行預(yù)測（取a=0.3,0.5,0.7），并計算軍方誤差。選擇使均方誤差最小的a進(jìn)行預(yù)測。 單位：t,時間序列預(yù)測法,14,之 指數(shù)平滑法,點擊EX

7、CEL菜單欄中【工具】菜單下面的子菜單【數(shù)據(jù)分析】；打開“數(shù)據(jù)分析”對話框；從“分析工具”列表中選擇“指數(shù)平滑”，點擊【確定】按鈕。 在“移動平均”對話框“輸入?yún)^(qū)域”選擇原始數(shù)據(jù)所在的單元格區(qū)域“C2：C13”，“阻尼系數(shù)”中輸入“0.3”，表示平滑系數(shù)a=0.3，“輸,出區(qū)域”選擇單元格“E2”，同時選擇“圖標(biāo)輸出”和“標(biāo)準(zhǔn)誤差”復(fù)選框，點擊確定按鈕。,時間序列預(yù)測法,15,之 指數(shù)平滑法,時間序列預(yù)測法,此時，單元格“E13”給出了一次指數(shù)平滑的預(yù)測值，單元格區(qū)域“F6：F13”給出了預(yù)測的標(biāo)準(zhǔn)差，實際值以及一次指數(shù)平滑法預(yù)測值同時以圖表形式給出。,16,曲線趨勢預(yù)測法,簡介,曲線趨勢預(yù)測

8、法是利用曲線趨勢模型進(jìn)行預(yù)測的方法,常用的曲線趨勢模型有拋物線和簡單指數(shù)兩種.當(dāng)時間序列在長時期內(nèi)呈現(xiàn)連續(xù)的不斷增長或減少的變動趨勢,其逐期增長量又大致相同時,使用直線趨勢模型進(jìn)行預(yù)測為宜;如果時間序列的二級增長量大體相同,使用拋物線趨勢模型進(jìn)行預(yù)測為宜;當(dāng)時間序列的環(huán)比發(fā)展速度或環(huán)比增長速度大體相同時,使用指數(shù)趨勢模型進(jìn)行預(yù)測為宜.,17,曲線趨勢預(yù)測法,分類 1、二次線性/多項式模型預(yù)測 2、指數(shù)曲線模型,18,曲線趨勢預(yù)測法,之 二次線性/多項式 模型預(yù)測,操作步驟 已知某商店某種產(chǎn)品銷售量如下表所示。試預(yù)測2005年該產(chǎn)品的銷售量。比較符合的模型有二次曲線和指數(shù)曲線模型，但無法確定那一

9、個模型能更好擬合該曲線。 產(chǎn)品生產(chǎn)表,19,曲線趨勢預(yù)測法,繪制產(chǎn)品銷售量的“XY散點圖”。從散點圖可以看出，比較符合的模型有二次曲線和指數(shù)曲線模型，但無法確定那一個模型能更好擬合該曲線。下面以二次型為例進(jìn)行參數(shù)擬合。 應(yīng)用公式 計算數(shù)據(jù)的二階差分，即單元格“C3”內(nèi)容為“=B3-B2”，單元格“D4”的內(nèi)容為“=C4-C3” 從二階差分?jǐn)?shù)據(jù)來看，多數(shù)二階差分?jǐn)?shù)據(jù)為-1和-1.5，由此判斷可以使用二次線性模型進(jìn)行預(yù)測。設(shè)定時間值為-44，從而使得 ， 。,之 二次線性/多項式 模型預(yù)測,20,曲線趨勢預(yù)測法,因此，二次線性模型的參數(shù)估計公式可以簡化為 分別計算 ， ， ， ， 的值，將結(jié) 果代

10、入（1）中，得 （2）,之 二次線性/多項式 模型預(yù)測,21,曲線趨勢預(yù)測法,計算公式（2），得到參數(shù)： ， ， 。 將參數(shù) ， ， 代入公式 ，得到各年份預(yù)測值。 計算預(yù)測誤差平方。 計算2005年預(yù)測數(shù)據(jù)為35.6186.,之 二次線性/多項式 模型預(yù)測,22,季節(jié)變動預(yù)測法,簡介,季節(jié)變動預(yù)測法又稱季節(jié)周期法、季節(jié)指數(shù)法、季節(jié)變動趨勢預(yù)測法，季節(jié)變動預(yù)測法是對包含季節(jié)波動的時間序列進(jìn)行的方法。要研究這種方法，就要研究時間序列的變動。,23,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,分類 1、自相關(guān)系數(shù)法 2、方差分析法 3、簡單季節(jié)預(yù)測法 4、Winters指數(shù)平滑預(yù)測法 5、趨勢比率法

11、6、Holt-Winters指數(shù)平滑法,24,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,操作步驟 （以“自相關(guān)系數(shù)法”與“方差分析法”為例）試采用自相關(guān)系數(shù)判斷法判斷下表的時間序列是否存在季節(jié)變動的影響。,25,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,輸入給定的數(shù)據(jù)，并輸入其滯后1期的時間序列。 點擊excel軟件菜單中【工具】菜單下面的子菜單【數(shù)據(jù)分析】，打開“數(shù)據(jù)分析”對話框，從“分析工具”列表中選擇“相關(guān)系統(tǒng)”，并點擊確定按鈕。 在打開的“相關(guān)系數(shù)”對話框中，“輸入?yún)^(qū)域”選擇單元格區(qū)域，“輸出區(qū)域”，并指定“分組方式”為“逐列”，點擊確定按鈕。,26,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法

12、,此時已給出時間序列與其滯后1期所形成的時間序列的相關(guān)系數(shù)計算值為-0.112998。重復(fù)上述操作，計算該時間序列與其滯后28期的時間序列的相關(guān)系數(shù)值。 繪制時間序列自相關(guān)系數(shù)的散點圖。從圖中可以看出，時間序列與其滯后2期，6期的時間序列相關(guān)系數(shù)取負(fù)值，且絕對值很大；與滯后4期，8期的時間序列相關(guān)系數(shù)值取正值，且值很大。由此可以判斷，該時間序列存在季節(jié)變動，且季節(jié)長度為4。,27,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,操作步驟 （以“方差分析法”為例） 應(yīng)用一次線性模型消除時間序列數(shù)據(jù)中的趨勢數(shù)據(jù)，即繪制時間序列的“XY散點圖”，然后通過添加趨勢線的方式獲得一次線性模型的模型方程以及趨勢預(yù)測

13、值。 用各期的時間序列值除以其趨勢預(yù)測值，得到季節(jié)指數(shù)的估計值。假定季節(jié)長度L=4，因此將估計值分為4組，每組數(shù)據(jù)只包含同“季節(jié)”的數(shù)據(jù)。,28,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,點擊excel軟件菜單欄中的【工具】菜單下面的子菜單【數(shù)據(jù)分析】。在打開的“數(shù)據(jù)分析”對話框，選擇“方差分析：單因素方差分析”，點擊確定按鈕。 在“方差分析：單因素方差分析”對話框中，“輸入?yún)^(qū)域”選擇分組后的季節(jié)指數(shù)值數(shù)據(jù)區(qū)域，并且指定分組方式為“列”分組。指定輸出區(qū)域后，點擊確定按鈕。,29,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,從方差分析所得結(jié)果可知，組件差異平方和為3.8509，組內(nèi)差異平方和為0.03

14、89，總差異平方和為3.8898。因此得到F統(tǒng)計量計算值為396.2625。而F分布表給出的F臨界值為3.4903。因為計算得到的F統(tǒng)計量大于F統(tǒng)計量的臨界值，所以各組數(shù)據(jù)的均值有顯著差異，即可認(rèn)為季節(jié)影響存在，季節(jié)長度為4。,30,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,1.計算時間序列值的平均值作為趨勢的估計 值； 2.剔除趨勢，即用各期時間序列值除以平均值，得出季節(jié)指數(shù)值（注意：此處需要采用絕對引用的方式）； 3.由于表6-1所示數(shù)據(jù)具有季節(jié)長度為4的季節(jié)變動，因此，對同季節(jié)的季節(jié)指數(shù)值求平均值； 4.應(yīng)用公式 計算未來一年的數(shù)據(jù)，如第一季度的預(yù)測值為 “=F3*$B$19”,如下圖所示

15、。,簡單季節(jié)預(yù)測法,31,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,32,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,Winters指數(shù)平滑預(yù)測法,1.首先，應(yīng)用公式 計算Winters指數(shù)平滑預(yù)測法的初始值； 2.應(yīng)用公式 計算Winters指數(shù)平滑預(yù)測法的初始值； 3.應(yīng)用公式 計算Winters指數(shù)平滑預(yù)測法的初始值； 4.應(yīng)用公式 計算Winters指數(shù)平滑預(yù)測法的初始值；,33,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,5.應(yīng)用公式 計算預(yù)測值； 6.復(fù)制單元格內(nèi)容，得到各時間點上的平滑值； 7.復(fù)制單元格內(nèi)容，得到未來一個周期內(nèi)的預(yù)測值； 8.根據(jù)公式 ，將“E9”單元格中的內(nèi)容設(shè)置為“=

16、$C$9*D6”，并復(fù)制到單元格“E10:E13”。依此類推，得到后續(xù)的預(yù)測值； 9.計算百分比誤差，得到預(yù)測精度指標(biāo)MAPE值； 10.復(fù)制單元格內(nèi)容，得到未來一個周期的預(yù)測值。,34,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,趨勢比率法,1.繪制時間序列的“XY散點圖”，并通過添加趨線的方式得到趨勢線方程； 2.根據(jù)趨勢線方程，得到各時間點的趨勢值； 3.剔除趨勢，即用各期時間序列值除以趨勢值； 4.初步估計季節(jié)指數(shù)值：對同季節(jié)的季節(jié)指數(shù)值求平均值； 5.最終估計季節(jié)指數(shù)，即用公式 計算最終季節(jié)指數(shù)；,35,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,6.根據(jù)趨勢季節(jié)月模型公式 ，得到各 期的預(yù)

17、測值； 7.計算相對百分比誤差，從而得到預(yù)測精度指標(biāo) MAPE值； 8.利用趨勢線方程得到利潤額的趨勢值； 9.根據(jù)季節(jié)預(yù)測模型得到利潤額的預(yù)測值。,36,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,Holt-Winters指數(shù)平滑法,1.利用前兩個周期的數(shù)據(jù)獲得初始值，設(shè)變量，表示第一個周期和第二個周期各數(shù)據(jù)的平均值，即 ， ； 2.初始值可以利用公式 得到； 3.初始值可以利用公式 得到； 4.初始值可以利用公式 得到； 5.應(yīng)用公式 計算得到下一期的值；,37,季節(jié)變動預(yù)測法,之 不變季節(jié)指數(shù)預(yù)測法,6.應(yīng)用公式 計算得到下一期的的值； 7.應(yīng)用公式 計算得到下一期的的值； 8.復(fù)制單元格內(nèi)

18、容，得到逐期 ， ， 值； 9.應(yīng)用公式 得到各期的預(yù)測值。,38,季節(jié)變動預(yù)測法,之 可變多季節(jié)季節(jié)指數(shù)預(yù)測,分類 1、可變季節(jié)指數(shù)預(yù)測法 2、雙季節(jié)指數(shù)預(yù)測法,39,季節(jié)變動預(yù)測法,之 可變多季節(jié)季節(jié)指數(shù)預(yù)測,操作步驟 （以“可變季節(jié)指數(shù)預(yù)測法”為例）根據(jù)下表中的數(shù)據(jù)，試采用可變季節(jié)指數(shù)預(yù)測法預(yù)測2006年各季度的市場銷售量。,40,季節(jié)變動預(yù)測法,之 可變多季節(jié)季節(jié)指數(shù)預(yù)測,繪制時間序列的“XY散點圖”，并通過添加趨勢線的方式獲得時間序列的線性趨勢方程，利用該方程計算各期的長期趨勢值。 剔除趨勢，得到季節(jié)指數(shù)的估計值；將相同季節(jié)的數(shù)據(jù)放在一起，繪制其“XY散點圖”。 從散點圖可以看出，同

19、一季節(jié)的季節(jié)指數(shù)具有一定的變化趨勢。因此，針對同一季節(jié)的季節(jié)指數(shù)采用線性模型進(jìn)行趨勢擬合。,41,季節(jié)變動預(yù)測法,之 可變多季節(jié)季節(jié)指數(shù)預(yù)測,由各季節(jié)指數(shù)的趨勢線得到2006年各季節(jié)指數(shù)預(yù)測值。 由原始數(shù)據(jù)的趨勢方程得到2006年的趨勢預(yù)測值。 應(yīng)用趨勢季節(jié)預(yù)測模型進(jìn)行預(yù)測。,42,回歸分析預(yù)測法,簡介,回歸分析預(yù)測法，是在分析市場現(xiàn)象自變量和因變量之間相關(guān)關(guān)系的基礎(chǔ)上，建立變量之間的回歸方程，并將回歸方程作為預(yù)測模型，根據(jù)變量在預(yù)測期的數(shù)量變化來預(yù)測因變量關(guān)系大多表現(xiàn)為相關(guān)關(guān)系，因此，當(dāng)我們在對市場現(xiàn)象未來發(fā)展?fàn)顩r和水平進(jìn)行預(yù)測是，如果能將影響市場預(yù)測對象的主要因素找到，并且能夠取得其數(shù)量資

20、料，就可以采用回歸分析預(yù)測法進(jìn)行預(yù)測。它是一種具體的、行之有效的、使用價值很高的常用市場預(yù)測方法。,43,回歸分析預(yù)測法,分類 1、一元線性回歸分析預(yù)測法 2、多遠(yuǎn)線性回歸分析預(yù)測法 3、非線性回歸分析預(yù)測法,44,回歸分析預(yù)測法,操作步驟 （以一元線性回歸分析預(yù)測法為例） 某飲料公司發(fā)現(xiàn)，飲料的銷售量與氣溫之間存在相關(guān)關(guān)系，即氣溫越高，人們對飲料的需求量就越大。 飲料銷售量與氣溫關(guān)系表,45,回歸分析預(yù)測法,點擊Excel軟件菜單欄中【工具】菜單下面的子菜單【數(shù)據(jù)分析】，打開“數(shù)據(jù)分析”對話框，在“分析工具”中選擇“回歸”，點擊【確定】按鈕。 在“回歸”對話框中，“Y值輸入?yún)^(qū)域”選擇銷售量數(shù)據(jù)所在單元格區(qū)域，表示模型中因變量為“銷售量”；“X值輸入?yún)^(qū)域”選擇氣溫數(shù)據(jù)所在單元格區(qū)域，表示模型中自變量為“氣溫”。,46,回歸分析預(yù)測法,選中“置信度”復(fù)選框，并設(shè)定置信度為95%，表示F檢驗中