應(yīng)用Excel做預(yù)測(cè)

1、應(yīng)用Excel做預(yù)測(cè)4.4利用數(shù)據(jù)分析工具解決預(yù)測(cè)問(wèn)題(1)除了利用前面介紹的幾個(gè)預(yù)測(cè)函數(shù)進(jìn)行回歸預(yù)測(cè)分析外，我們還可以使用Excel的數(shù)據(jù)分析工具庫(kù)提供的統(tǒng)計(jì)觀測(cè)分析工具來(lái)解決回歸預(yù)測(cè)問(wèn)題。Excel的數(shù)據(jù)分析工具庫(kù)提供了 3種統(tǒng)計(jì)觀測(cè)分析工具，它們是移動(dòng)平均法、指數(shù)平滑法和回歸分析法。下面結(jié)合實(shí)例來(lái)說(shuō)明這3種方法的具體應(yīng)用。4.4.1移動(dòng)平均法【例4-5】某企業(yè)2000年12個(gè)月的銷售額如圖4-4所示，分別按3期、5期和7 期移動(dòng)平均所做的預(yù)測(cè)分析如圖 4-4中的C4 : E13區(qū)域所示。以3期移動(dòng)平均 為例為例，具體計(jì)算步驟如下：> 2 x>3r* usoa 30aSX291

2、 4033 網(wǎng)4-4 一次移動(dòng)平均法實(shí)例>11(1) 從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對(duì)話框,如圖4-5所示(2) 在【數(shù)據(jù)分析】對(duì)話框中的【分析工具】框中選中【移動(dòng)平均】選項(xiàng)，則彈出【移動(dòng)平均】對(duì)話框，如圖 4-6所示。圖4-6【移動(dòng)平均】對(duì)話框(3) 在【移動(dòng)平均】對(duì)話框中，【輸入?yún)^(qū)域】框中輸入“ $B$2 : $B$13”，【間隔】框中輸入“ 3”，【輸出區(qū)域】框中輸入“ $C$2”，最后選中【圖表輸出】選項(xiàng)；(4) 單擊【確定】按鈕，則運(yùn)算結(jié)果就顯示在單元格區(qū)域C4:C13中，如圖4-4所示(圖中的第13行預(yù)測(cè)數(shù)據(jù)即為下月即第13月的預(yù)測(cè)值)，并自動(dòng)出現(xiàn)輸

3、出 圖表，如圖4-7所示。移動(dòng)平均1357911數(shù)耀點(diǎn)T-實(shí)時(shí)值 按刪說(shuō)移動(dòng)平均數(shù)據(jù)點(diǎn)圖4-7移動(dòng)期數(shù)為3時(shí)的輸出圖表用同樣的方法，可以分析當(dāng)移動(dòng)期數(shù)為 5和7時(shí)的分析結(jié)果，如圖4-4所示。442指數(shù)平滑法【例4-6】某企業(yè)的有關(guān)銷售數(shù)據(jù)如圖4-8所示，利用指數(shù)平滑法進(jìn)行預(yù)測(cè)分析,其步驟如下：圖4-8指數(shù)平滑法實(shí)例（1）從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對(duì)話框，在【數(shù)據(jù)分析】對(duì)話框中的【分析工具】框中選中【指數(shù)平滑】選項(xiàng)，則彈出【指數(shù)平滑】對(duì)話框，如圖4-9所示。圖4-9【指數(shù)平滑】對(duì)話框（2）在【指數(shù)平滑】對(duì)話框中，【輸入?yún)^(qū)域】框中輸入“ $B$2:$B$13”，【阻尼

4、系數(shù)】框中輸入“0.2”，【輸出區(qū)域】框中輸入“ $C$3”，最后選中【圖表輸出】選項(xiàng)（3）單擊【確定】按鈕，則運(yùn)算結(jié)果就顯示在單元格區(qū)域 C3:C13中（圖中的 第13行預(yù)測(cè)數(shù)據(jù)即為下月即第13月的預(yù)測(cè)值），如圖4-8所示，并自動(dòng)出現(xiàn)輸 出圖表，如圖4-10所示。圖4-10指數(shù)平滑法預(yù)測(cè)輸出圖（阻尼系數(shù) 0.2）用同樣的方法，可以分析當(dāng)阻尼系數(shù)為 0.4和0.6時(shí)的分析結(jié)果如圖4-8所 示。需要注意的是，【數(shù)據(jù)分析】中的指數(shù)平滑法所使用的阻尼系數(shù)并不是421.2節(jié)介紹的指數(shù)平滑法預(yù)測(cè)方程中的平滑系數(shù)：，二者的關(guān)系為：阻尼系數(shù)=1-=4.4利用數(shù)據(jù)分析工具解決預(yù)測(cè)問(wèn)題（2）4.4.3 回歸法利

5、用Excel的回歸工具進(jìn)行預(yù)測(cè)分析有兩種方法：一是圖表法；二是回歸分析法。4.4.3.1 圖表法圖表法僅能解決一元線性或非線性回歸問(wèn)題，不能解決多元回歸問(wèn)題。【例4-7】某企業(yè)連續(xù)9年的產(chǎn)品銷售收入丫（萬(wàn)元）與廣告支出X1 （萬(wàn)元） 和居民平均收入X2 （元）的有關(guān)數(shù)據(jù)如圖4-11所示，則利用圖表法進(jìn)行回歸分 析，其方法和步驟如下，這里僅以銷售收入丫（萬(wàn)元）與廣告支出X1 （萬(wàn)元）的一元線性關(guān)系為例：圖4-11某企業(yè)的有關(guān)銷售數(shù)據(jù)(1) 選擇單元格區(qū)域B2:C10。(2) 單擊工具欄上的【圖表導(dǎo)向】按鈕，在【圖表導(dǎo)向一4步驟之1 圖表類型】 中選“XY散點(diǎn)圖”，其【子圖表類型】選第1種，如圖4

6、-12所示。圖4-12準(zhǔn)備作散點(diǎn)圖(3) 單擊【下一步】按鈕，出現(xiàn)【圖表導(dǎo)向一4步驟之2圖表源數(shù)據(jù)】對(duì)話框, 單擊【系列】，在【名稱】欄中填入“銷售收入”，在【X值】欄中輸入“ =Sheet1!$C$2$C$10”,在【Y 值】欄中輸入“ =Sheet1!$B$2:$B$10”(用鼠標(biāo)拾取單元格區(qū)域)，如圖4-13所示。圖4-13填入源數(shù)據(jù)（4）單擊【下一步】按鈕，出現(xiàn)【圖表導(dǎo)向一 4步驟之3圖表選項(xiàng)】對(duì)話框, 填入各標(biāo)題文字，如圖4-14所示。圖4-14填入各標(biāo)題文字（5）單擊【下一步】按鈕，出現(xiàn)【圖表導(dǎo)向一 4步驟之4圖表位置】對(duì)話框, 不作任何輸入，單擊【完成】按鈕，則在工作表上看到輸出

7、的圖形，對(duì)其進(jìn)行必 要的調(diào)整（如坐標(biāo)、字體、位置等）。（6）在系列【數(shù)據(jù)點(diǎn)】上的任一點(diǎn)上，按鼠標(biāo)左鍵，使各數(shù)據(jù)點(diǎn)出現(xiàn)記號(hào)，再 單擊【工具欄】上的【圖表】按鈕，選中【添加趨勢(shì)線】項(xiàng)，或在數(shù)據(jù)點(diǎn)上按 鼠標(biāo)右鍵，選【添加趨勢(shì)線】項(xiàng)，出現(xiàn)【添加趨勢(shì)線】對(duì)話框，如圖4-15所示（7）在【添加趨勢(shì)線】中的【類型】對(duì)話框中，有【線性】、【對(duì)數(shù)】、【多項(xiàng)式】、【乘幕】、【指數(shù)】和【移動(dòng)平均】6個(gè)選項(xiàng)。通過(guò)觀察XY散點(diǎn)圖可知，產(chǎn)品銷售收入與廣告支出之間呈明顯的線性關(guān)系，故這里選【線性】。（8）在【添加趨勢(shì)線】中的【選項(xiàng)】對(duì)話框中，勾選【顯示公式】、【顯示R平方值】，如圖4-16所示。廣 autcfI圖 4-16

8、【添加趨勢(shì)線】的【選項(xiàng)】設(shè)置（9）單擊【確定】按鈕，則在圖形上顯示出較粗的預(yù)測(cè)線、回歸方程和R平方值，然后進(jìn)行必要的調(diào)整，得到如圖 4-17的結(jié)果。rtzt&xi 祝）圖4-17輸出圖形用同樣的方法還可以確定銷售收入與居民平均收入的關(guān)系4.4利用數(shù)據(jù)分析工具解決預(yù)測(cè)問(wèn)題(3)443.2回歸分析法回歸分析法可以對(duì)一元線性或多元線性以及某些可以轉(zhuǎn)化為線性的非線性問(wèn)題 進(jìn)行回歸分析。1.線性回歸【例4-8】仍以例4-7的有關(guān)資料為例，回歸分析的步驟如下：從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對(duì)話框，在【數(shù)據(jù)分析】對(duì)話框中的【分析工具】框中選中【回歸】選項(xiàng)，如圖4-18所示，

9、則彈出【回歸】對(duì)話框。圖4-18【數(shù)據(jù)分析】對(duì)話框(2) 在【回歸】對(duì)話框中，【丫值輸入?yún)^(qū)域】中輸入“ $B$1:$B$10',【X值輸 入?yún)^(qū)域】中輸入“ $C$1$D$10',在【輸出選項(xiàng)】中勾選【輸出區(qū)域】，填入“ $A$12', 然后根據(jù)實(shí)際需要，勾選其他需要的選項(xiàng)，如圖 4-19所示。圖4-19【回歸】選項(xiàng)（3）單擊【確定】按鈕，回歸分析的摘要就輸出在本工作表上， 如圖4-20所示 對(duì)這些數(shù)據(jù)進(jìn)行分析可知：R平方值為0.9903，說(shuō)明因變量與自變量之間相關(guān)性 很高；F的顯著值為2.961E-07,已達(dá)0.05的檢驗(yàn)標(biāo)準(zhǔn)；其他統(tǒng)計(jì)檢驗(yàn)也達(dá)到相 應(yīng)的標(biāo)準(zhǔn)。從而得到回

10、歸方程為： 丫=229.8409+9.2794Xi+0.0082 X2。圖4-20回歸分析的計(jì)算機(jī)輸出當(dāng)自變量只有一個(gè)（即一元回歸）時(shí)，上述方法同樣適用。2 非線性回歸對(duì)于某些可以化為線性關(guān)系的非線性問(wèn)題，同樣可以進(jìn)行回歸分析。舉例如下?！纠?-9】某地區(qū)科研系統(tǒng)近10年的凈收入丫 （千萬(wàn)元）與研究經(jīng)費(fèi)X（千萬(wàn)元）和研究人員數(shù)X2 （萬(wàn)人）的統(tǒng)計(jì)資料，如圖4-21所示，假設(shè)它們之間存在著以 下的函數(shù)關(guān)系：式中，a b、c為待估計(jì)參數(shù)。若利用回歸工具求解此類非線性問(wèn)題， 解決的辦法是將此方程進(jìn)行數(shù)學(xué)變換，即對(duì)方程兩邊取對(duì)數(shù)，得-；1' -1：' ' ': 1，將各

11、個(gè)觀測(cè)值進(jìn)行變換，如圖4-21所示，即在單元格E2E11中輸入公式“ =LN（B2B11）”（數(shù)組公式輸入），然后將單元格E2E11復(fù)制到單元格F2F11和G2G11中。19W23S1991 23B1992 25&1W3W419M271.K L * _ -年f& 袴收入¥1>L_>liKV)ItKXJKX0 153S 47Z271 E> stwrre5 O*?9©&65 SCSI77 > &T332：as oenrsoft1753 ?26e« 弓茍軸IVK2?12«41WTaoe3 MB3141WB

12、326ie?56；3l2S5550*00?l$tS6*64；?ST6SL如&2S2?731MS71OUTStt2U4各27S1 tS301S 7«ftM3 5 023046 5 3CS3OS21Z5. 76£?97 5 855OT2 5 2r5*5B6圖4-21某地區(qū)科研系統(tǒng)有關(guān)資料再對(duì)變換后的數(shù)據(jù)利用 Excel的回歸工具進(jìn)行回歸分析，具體步驟可參閱【例4-7】，其中【丫值輸入?yún)^(qū)域】中輸入“ $E$1:$E$11”，在【X值輸入?yún)^(qū)域】中輸 入“$F$1:$G$11”，在【輸出選項(xiàng)】中勾選【輸出區(qū)域】，填入“ $A$12”，得到如圖4-22所示的分析結(jié)果，最后得到：

13、a = e0.08214 = 0.9211, b =0.4477，c =0.6046,相關(guān)系數(shù)為0.9808 （注意此相關(guān)系數(shù)是變換后的線性方程的相關(guān)系數(shù)，并不是原非線性方程的相關(guān)系數(shù)），回歸方程為：1J1'''*_JC _®_tFaiswlmj ttumrrB V W»5£ £ q-a Kf Afl »OT9Br ao oi?»e1 gMiiHBio53017曽知僑ssrE 卜t £ ,町 * £ Ffel 舊 HthjLi “ 冒:.0如1】7喝IT# 1MB9 O11»E-W

14、T0 OKI0 000X754*<+*9>D IGKTMTTr L.男勺導(dǎo)一TJ“r“ VT. 丁詩(shī)審'："L±re' ?5_EIalI *jf «-O OKI 40 3O4%3?9E2O 2fi92Sa K T9S421-O. 8DO1BTSTED-a- «JtMalnClD0 447TIBD 1»>T5462& <E252ft0 <»I42<WP0 &110Ko eiiOMQ 55"0鳩譏停心令"尊S ts舍n ma sk博呻雋o zajewG

15、吟欣4Uf!XJl*AL aurnrrJM 一I 弓 tSATliBD OCOB&941 3KOOZK»I2 5 5屮-U M531?<%43 S EHTSe心0 7«1TX7»« 5 wmen ©oit-O5 5-1 (KKWWt鼻 5 «3BLK-fl* O5|»TO31遼R59M聒? 5 6144*9p ooeo«4«5DD9 5 TIZWIQ OlALMTTOlI IBS3VM&90. J5 TTTT50 CCTMlTi0 5«TK0W4.5利用規(guī)劃求解工具解決預(yù)測(cè)

16、問(wèn)題雖然我們可以利用Excel提供的各種預(yù)測(cè)分析工具解決大多數(shù)財(cái)務(wù)預(yù)測(cè)中的實(shí) 際問(wèn)題，但這些預(yù)測(cè)分析工具并不是萬(wàn)能的， 其預(yù)測(cè)誤差也隨著實(shí)際問(wèn)題的復(fù)雜 化而增大。比如對(duì)于一些非線性預(yù)測(cè)問(wèn)題，常常是將其通過(guò)變量替換而轉(zhuǎn)換為線 性問(wèn)題。但是，這種變換過(guò)程一方面增加了計(jì)算工作量，另一方面也可能導(dǎo)致分析精度下降，因?yàn)樽儞Q后的數(shù)據(jù)容易使觀測(cè)數(shù)據(jù)的性質(zhì)發(fā)生變化，導(dǎo)致自變量與因變量之間的關(guān)系發(fā)生扭曲，從而影響回歸方程的精度，因此，這種將非線性轉(zhuǎn) 換為線性的做法是存在一定的缺陷的。 此外，有些非線性問(wèn)題根本無(wú)法直接轉(zhuǎn)換 為線性問(wèn)題，除非作出大量的簡(jiǎn)化，這必然使得到的回歸方程嚴(yán)重失真。因此，對(duì)于非線性回歸問(wèn)題，

17、最好的方法是直接進(jìn)行回歸分析， 即求解使殘差平 方和最小、或使相關(guān)系數(shù)最大的回歸方程，但非線性回歸過(guò)程是一個(gè)循環(huán)尋優(yōu)過(guò) 程，需要先設(shè)置回歸方程系數(shù)的初值，然后計(jì)算觀測(cè)值與預(yù)測(cè)值的殘差平方和， 不斷尋找使殘差平方和最小的回歸方程系數(shù)，這實(shí)際上是一個(gè)優(yōu)化問(wèn)題，因此， 可以利用Excel的規(guī)劃求解工具求解非線性回歸問(wèn)題，當(dāng)然也可以用來(lái)求解線性 回歸問(wèn)題。在利用規(guī)劃求解工具直接求解非線性回歸問(wèn)題時(shí)，需要使用以下幾個(gè)計(jì)算公式:自由度df為-匸式中，n為觀測(cè)次數(shù)；m為待估計(jì)參數(shù)個(gè)數(shù)殘差平方和SE為式中,AY、A分別為第i個(gè)觀測(cè)值和預(yù)測(cè)值=1,2, , n）。相關(guān)系數(shù)r2為一八式中，一為觀測(cè)值的平均值。F面

18、結(jié)合實(shí)例說(shuō)明在Excel上進(jìn)行非線性回歸的具體方法和步驟?！纠?-10】以例4-9的有關(guān)資料為例，利用Excel的規(guī)劃求解工具來(lái)求解非線性回歸問(wèn)題的方法和步驟如下：(1) 如圖4-23所示，單元格G2G4為變動(dòng)單元格，分別存放待估計(jì)參數(shù) a、b、 c，其初值可設(shè)為0。(2) 在單元格E2E11中輸入預(yù)測(cè)值公式“ =G2*(C2 C11)P3*(D2: D11FG4' (數(shù)組公式輸入)TCr%DECS遷a 63372JS#7性菱甲方阿SFiix毛m址| * i h闿10圖4-23利用規(guī)劃求解工具進(jìn)行非線性回歸分析(3) 在單元格G5中輸入觀測(cè)值的平均值公式“ =AVERAGE(BB11)

19、”；在單元 格G6中輸入自由度公式“ =COUNT(B2311)-COUNT(G2G4)”；在單元格 G7中輸 入殘差平方和公式“ =SUM(B2B11-E2: E11F2) ” (數(shù)組公式輸入)；在單元格 G8中輸入剩余標(biāo)準(zhǔn)差公式“ =SQRT(G7/G6” ；在單元格G9中輸入相關(guān)系數(shù)戌 的計(jì)算公式“ =1-G7/SUM(B2 B11-G5F2) ” (數(shù)組公式輸入)。(4) 單擊EXCE工具菜單，選擇【規(guī)劃求解】項(xiàng)，出現(xiàn)【規(guī)劃求解參數(shù)】對(duì)話 框；（5）在【規(guī)劃求解參數(shù)】對(duì)話框中，【設(shè)置目標(biāo)單元格】設(shè)置為單元格“$G$7 , 即目標(biāo)函數(shù)為殘差平方和；【等于】設(shè)置為“最小”；【可變單元格】設(shè)

20、置為“$G$2$G$4'。然后單擊【求解】，即可得到回歸方程的系數(shù)a b、c,出現(xiàn)“規(guī)劃求解結(jié)果”對(duì)話框，然后單出【確定】按鈕，保存規(guī)劃求解結(jié)果。需要注意的是，若系數(shù) a b、c的初值設(shè)置不合適的話，則一次求解過(guò)程（即在 Excel上進(jìn)行【工具】 【規(guī)劃求解】 【求解】【確定】這樣一個(gè)求解過(guò)程）可能得不到最優(yōu)結(jié)果（或得不到解），這時(shí)需要進(jìn)行多次求解，即在第一次 求解結(jié)果的基礎(chǔ)上，再進(jìn)行第二次求解，得到第二次求解結(jié)果，然后在第二次求 解結(jié)果的基礎(chǔ)上，再進(jìn)行第三次求解，得到第三次求解結(jié)果，如此繼續(xù)下去，直 到求出的系數(shù)a、b、c的值不再變化、且殘差平方和最小為止，即得到最優(yōu)結(jié)果。 在上例中

21、，當(dāng)a、b、c的初始值設(shè)為0時(shí)，經(jīng)過(guò)2次求解過(guò)程即得到最優(yōu)結(jié)果， 如圖4-23所示。若采用非線性轉(zhuǎn)換為線性的方法，如例 4-9所示，可得到有關(guān)系數(shù)如圖4-22所示，將此系數(shù)代入回歸方程，計(jì)算不同 Xi和茨下的預(yù)測(cè)值，進(jìn)而計(jì)算出殘差平方和與剩余標(biāo)準(zhǔn)差分別為157.1139和4.7376，與圖4-23的直接非線性回歸的結(jié)果（殘差平方和154.03、剩余標(biāo)準(zhǔn)差4.6909 ）進(jìn)行比較，可見(jiàn)非線性轉(zhuǎn)換為線性的方法得出的結(jié)果誤差要大于直接進(jìn)行非線性回歸的誤差4.6 銷售預(yù)測(cè)銷售預(yù)測(cè)的準(zhǔn)確程度， 對(duì)企業(yè)的興衰成敗會(huì)產(chǎn)生很重要的影響。 銷售預(yù)測(cè)比較準(zhǔn) 確，會(huì)使企業(yè)在有計(jì)劃的財(cái)務(wù)安排下順利運(yùn)作； 而如果銷售預(yù)

22、測(cè)與實(shí)際情況偏離 很遠(yuǎn)，則會(huì)使企業(yè)遇到麻煩，甚至陷入困境。因此，銷售預(yù)測(cè)是企業(yè)進(jìn)行財(cái)務(wù)預(yù) 測(cè)的首要工作，是企業(yè)制定財(cái)務(wù)計(jì)劃的基礎(chǔ)。銷售預(yù)測(cè)主要應(yīng)根據(jù)市場(chǎng)需求的變化，結(jié)合企業(yè)的利潤(rùn)目標(biāo)、實(shí)現(xiàn)企業(yè)市場(chǎng)份 額的目標(biāo)，并綜合考慮企業(yè)內(nèi)外部的各種限制條件的影響來(lái)進(jìn)行。一般情況下， 可首先分別對(duì)未來(lái)各期的銷售量和銷售價(jià)格進(jìn)行預(yù)測(cè)， 在此基礎(chǔ)上， 根據(jù)預(yù)測(cè)的 產(chǎn)品銷售價(jià)格乘以預(yù)測(cè)的銷售量得到預(yù)測(cè)的銷售額； 也可以直接根據(jù)銷售額的有 關(guān)歷史資料，采用適當(dāng)?shù)姆椒ㄟM(jìn)行預(yù)測(cè)。4.6.1 銷售預(yù)測(cè)的基本方法銷售預(yù)測(cè)是一項(xiàng)比較復(fù)雜的工作， 需要考慮的因素很多， 作出準(zhǔn)確的預(yù)測(cè)是非常 困難的。通?？衫闷髽I(yè)過(guò)去的數(shù)據(jù)進(jìn)行

23、統(tǒng)計(jì)分析， 并結(jié)合經(jīng)濟(jì)環(huán)境對(duì)未來(lái)市場(chǎng) 的影響以及企業(yè)內(nèi)外部各種條件的限制，作出銷售預(yù)測(cè)。進(jìn)行銷售預(yù)測(cè)的方法很多，常用的方法包括以下幾種。1時(shí)間序列預(yù)測(cè)法時(shí)間序列預(yù)測(cè)法， 是指將觀察或記錄的一些歷史數(shù)據(jù)， 按時(shí)間的先后排列成數(shù)據(jù) 系列，進(jìn)行統(tǒng)計(jì)分析， 找出過(guò)去長(zhǎng)期的銷售量或銷售額的增減變化趨勢(shì)， 再根據(jù) 此變化趨勢(shì)分析的結(jié)果， 預(yù)測(cè)未來(lái)時(shí)期的銷售量或銷售額。 常見(jiàn)的時(shí)間序列的預(yù) 測(cè)方法有簡(jiǎn)單平均法、 移動(dòng)平均法、 指數(shù)平滑法、 或以時(shí)間為自變量的回歸分析 法等，這些方法的基本原理可參閱前面的有關(guān)內(nèi)容。2 因果關(guān)系預(yù)測(cè)法因果關(guān)系預(yù)測(cè)法，是指利用有關(guān)因素與產(chǎn)品銷售量或銷售額之間的固有因果關(guān)系，通過(guò)建

24、立一定的數(shù)學(xué)模型來(lái)預(yù)測(cè)企業(yè)未來(lái)的產(chǎn)品銷售水平的一種方法。企業(yè)產(chǎn)品銷售水平的高低，往往受到諸多宏觀或微觀、外部或內(nèi)部、客觀或主觀等因 素的影響，通?？梢酝ㄟ^(guò)回歸分析的方法檢驗(yàn)出哪些因素與銷售水平之間具有因 果關(guān)系，在此基礎(chǔ)上可建立回歸方程，進(jìn)行銷售預(yù)測(cè)。有關(guān)如何建立回歸方程及進(jìn)行相關(guān)檢驗(yàn)的方法可參閱前面的有關(guān)內(nèi)容。3 通過(guò)生產(chǎn)能力或訂貨合同進(jìn)行銷售量（銷售額）預(yù)測(cè)企業(yè)生產(chǎn)的產(chǎn)品如果在市場(chǎng)占有穩(wěn)定的份額或供不應(yīng)求，則可按本企業(yè)的生產(chǎn)能力預(yù)測(cè)產(chǎn)品的銷售量，計(jì)算公式如下：計(jì)劃期銷售量=計(jì)劃期初庫(kù)存量+計(jì)劃期預(yù)計(jì)生產(chǎn)量-計(jì)劃期末預(yù)計(jì)庫(kù)存量462銷售預(yù)測(cè)模型及其應(yīng)用在很多情況下，通過(guò)建立企業(yè)的銷售預(yù)測(cè)模型

25、，可以很方便地實(shí)現(xiàn)銷售預(yù)測(cè)。下 面介紹兩個(gè)銷售預(yù)測(cè)模型。4.6.2.1 一元線性（非線性）回歸預(yù)測(cè)模型【例4-11】根據(jù)圖4-24中所給的資料建立一元線性（非線性）回歸預(yù)測(cè)模型。AP1/4H1J匸I11脈1«1L就再和199Q 礙？XOlnos3匸砂123*56T«« Wun*2.61T .T0 ?笳】建官99 210! 55回桿皿或Km ufl端刃«4nT-stem卜如馳創(chuàng)Y-A*BXA B 計(jì)13 l<u6Y-AB*L叭陽(yáng).: 圖4-24 元線性（非線性）回歸銷售預(yù)測(cè)模型下面利用線性回歸中的LINEST函數(shù)和指數(shù)回歸中的LOGEST函數(shù)，來(lái)建立

26、一 元線性（非線性）回歸預(yù)測(cè)模型。（1）首先建立銷售預(yù)測(cè)模型，如圖 4-24所示，這里以過(guò)去12期的銷售量為歷史數(shù)據(jù)（可以是以年計(jì)算，也可以是以月計(jì)算，圖4-24為以年計(jì)算）。（2） 設(shè)置回歸模型選擇控件，控件的數(shù)據(jù)源區(qū)域?yàn)?A$7 :$A$8，單元格鏈接為 $B$7,下拉顯示項(xiàng)數(shù)為2。（3）選取單元格區(qū)域B3:M3，單擊【插入】 【名稱】 【定義】命令，或直 接單擊編輯欄中的名稱框，將影響因素所在的單元格區(qū)域 B3:M3定義為“影響 因素序列”；用同樣的方法，將銷售量所在的單元格區(qū)域 B4:M4定義為“銷售序 列”。（4）在單元格E8中輸入公式“ =IF（B7=1,INDEX（LINEST（

27、銷售序列，影響因素序 列，TRUE,TRUE）,1,2）,INDEX（LOGEST（銷售序列，影響因素序 列,TRUE,TRUE）,1,2）”，計(jì)算系數(shù) A。（5）在單元格F8中輸入公式“ =IF（B7=1,INDEX（LINEST（銷售序列，影響因素序 列，TRUE,TRUE）,1,1）,INDEX（LOGEST（ 銷 售序列，影響 因素序 列,TRUE,TRUE）,1,1）”，計(jì)算系數(shù) B。（6）在單元格G8中輸入公式“=IF（B7=1,INDEX（LINEST（銷售序列，影響因素序 列，TRUE,TRUE）,3,1）,INDEX（LOGEST（ 銷 售序列，影響 因素序 列,TRUE,T

28、RUE）,3,1）”，計(jì)算相關(guān)系數(shù) R2。(7) 在單元格 J&M8 中輸入公式“ =IF(B7=1,E8+F8*J7:M7,E8*F8AJ7:M7)”(數(shù) 組公式輸入），計(jì)算未來(lái)第14期的預(yù)測(cè)值。在影響因素和銷售量?jī)闪休斎霘v史數(shù)據(jù)，并在J7:M7中輸入未來(lái)4期的影響因素預(yù)測(cè)數(shù)值后，即可得到回歸預(yù)測(cè)模型及未來(lái)的預(yù)測(cè)值。 通過(guò)選擇不同的回歸模 型，可以分別計(jì)算一元線性模型和一元指數(shù)模型下的回歸結(jié)果及預(yù)測(cè)值。由計(jì)算結(jié)果可知，采用指數(shù)模型（相關(guān)系數(shù)為0.9742）要比線性模型（相關(guān)系數(shù)為0.9452） 更為準(zhǔn)確。4.622多元線性回歸預(yù)測(cè)模型【例4-12】根據(jù)圖4-25中所給的資料建立多元線性回歸預(yù)測(cè)模