“Excel表”在銷售預測中的應用

謝東聞

摘要：在市場經(jīng)濟“以需定銷，以銷定產(chǎn)”的條件下，銷售預測顯得非常重要。

利用EXCEL常用函數(shù)，分別采用加權平均法、指數(shù)平滑法、回歸直線法和多元線性回歸法創(chuàng)建銷售預測模型，對管理會計人員銷售預測有一定的借鑒意義。

關鍵詞：銷售預測；平均法；回歸直線法；多元線性回歸法

一、引言

銷售預測方法有定性分析法和定量分析法。定性分析法要憑借管理者的經(jīng)驗去判斷銷售量；定量分析法運用現(xiàn)代數(shù)學方法進行數(shù)據(jù)處理，據(jù)以建立能夠反映有關變量之間規(guī)律性聯(lián)系的各類預測模型方法體系。它適用于具備完整的歷史資料或有關變量之間具有明顯的數(shù)量關系等條件下的預測。

二、銷售預測模型的創(chuàng)建

本文的銷售預測模型是利用Excel工作表的數(shù)據(jù)分析功能，根據(jù)銷售預測的基本原理和方法創(chuàng)建的，用于銷售預測的一套計算分析程序，能夠大大提高銷售預測工作的效率和信息的質(zhì)量。銷售預測的方法很多，具體使用哪種方法取決于預測的對象、目的、時間及精確程度，預測時應綜合考慮有關因素，選擇適當?shù)姆椒ㄟM行預測，考慮到基于Excel工作表的實驗平臺，僅介紹加權平均法、指數(shù)平滑法、回歸直線法、多元回歸法預測模型的創(chuàng)建。

（一）加權平均法銷售預測模型的創(chuàng)建

加權平均法銷售預測模型是將若干歷史時期的銷售量作為觀察值，并為各個觀察值確定相應的權數(shù)，將各個觀察值與各自的權數(shù)相乘后加總，將加權平均數(shù)作為銷售量的預測值。具體應用見圖1，圖中黃色單元格代表已知的數(shù)據(jù)，白色單元格代表需要計算的數(shù)據(jù)。

加權平均法在實際應用過程中要注意期數(shù)和權重的選擇，模型中選擇了1-6月份的銷售量來平均，選擇了6期數(shù)據(jù)，如果選擇5期或7期數(shù)據(jù)平均，7月份的預測銷售量就會不同，管理會計人員需要憑借經(jīng)驗選擇適中的期數(shù)來平均，期數(shù)不是越大越好也不是越小越好。模型中1-6月份的權重選取必須遵循近大遠小的原則，與7月份越接近的月份權重應該越大，權重的選擇需要憑借管理會計人員的經(jīng)驗來判斷，權重選取不一樣，預測的銷售量也會不同。

7月份預測銷售量=650*0.1+660*0.1+680*0.1+700*0.2+710*0.2+730*0.3=700

（臺），利用SUMPRODUCT（加權平均函數(shù)）來求更簡便。（見圖1）

加權平均法適用于歷史數(shù)據(jù)（1-6月份銷售量）波動的情況，沒有遞增或遞減的趨勢的時候才能夠使用。這種方法預測出來的結果（7月份銷售量）一定處于最大值和最小值之間。

（二）指數(shù)平滑法銷售預測模型的創(chuàng)建

指數(shù)平滑法銷售預測模型是將若干歷史時期的銷售量作為觀察值，計算指數(shù)平滑平均數(shù)，計算公式為：

預測期銷售量Qt=平滑指數(shù)×前期實際銷售量+（1-平滑指數(shù)）×前期預測銷售量=αXt-1+（1-α）Qt-1（t=2， 3， …， n）

從指數(shù)平滑法的公式可以看出，這仍然是加權平均法，因為：

Qt=αXt-1+（1-α）Qt-1=αXt-1+α（1-α）Xt-2+α（1-α）2Xt-3+…+α（1-α）t-2X1+（1-α）t-1Q1

α取值越大，則近期實際銷售量對預測結果的影響越大；α取值越小，則近期預測銷售量對預測結果的影響越大。平滑指數(shù)α是一個經(jīng)驗數(shù)據(jù)，其取值范圍通常在0.3～0.7之間。具體應用見圖2，α=0.3，選用6期（1-6月份銷售量）平均。1月份預測銷售量=1月份實際銷售量，2月份預測銷售量取決于1月份預測銷售量和1月份實際銷售量，3月份預測銷售量取決于2月份預測銷售量和2月份實際銷售量，依此類推，4、5、6月份預測銷售量可以預測出來，7月份預測銷售量=0.3*1350+（1-0.3）*1197=1243（臺）。

指數(shù)平滑法是一種特殊的加權平均法，因此適用條件和加權平均法一樣。在Excel平臺下，可以調(diào)用指數(shù)平滑工具來求，輸入1月至6月的實際銷售量及平滑指數(shù)α后，可以一次得出2月至7月的預測銷售量。

（三）回歸直線法銷售預測模型的創(chuàng)建

回歸分析法也稱為一元回歸分析法，如果影響銷售量的因素只有一個，可以令直線方程 Y=a+bX，運用數(shù)學上的最小二乘法來確定一條誤差最小并能正確反映自變量X和因變量Y之間關系的直線。

具體應用見圖3，我們要預計2016年壓縮機預計銷售量，首先需要分析2011年至2015年電冰箱銷售量（X）與壓縮機銷售量（Y）這兩組數(shù)據(jù)的擬合優(yōu)度，如果相關系數(shù)（r）<0.8，說明兩組數(shù)據(jù)的擬合優(yōu)度不高，不能采用回歸直線法。如果相關系數(shù)（r）≥0.8，說明兩組數(shù)據(jù)的擬合優(yōu)度高可以采用回歸直線法，相關系數(shù)的計算公式很復雜，可以采用Excel平臺中的統(tǒng)計函數(shù)CORREL（相關系數(shù)）來求，計算結果為0.98973093接近完全正相關，說明電冰箱銷售量（X）與壓縮機銷售量（Y）這兩組數(shù)據(jù)高度正相關，可以采用回歸直線法，然后單位變動成本（b）可以用SLOPE（斜率函數(shù)）來求，結果為0.31346154，固定成本總額（a）可以用INTERCEPT（截距函數(shù)）來求，結果為-12.117308。最后得出2016年壓縮機預計銷售量=-12.117308+0.31346154*180=44.3058（萬臺）

（四）多元線性回歸法銷售預測模型的創(chuàng)建

在實際生產(chǎn)經(jīng)營活動中，影響銷售量變動的因素是多種多樣的，要預測未來的銷售量，必須綜合考慮采用多個自變量，建立多元回歸方程來預測銷售量。可令多元線性回歸方程為Y=a+b1X1+b2X2+…+bnXn（n=1， 2， 3， 4， …）。

影響壓縮機銷售量（Y）的因素有研發(fā)費（X1）、廣告費（X2）和電冰箱銷售量（X3），我們要預計壓縮機銷售量，首先要將固定成本（a）、研發(fā)費支出系數(shù)（b1）、廣告費支出系數(shù)（b2）、電冰箱銷量系數(shù)（b3）計算出來。

固定成本、研發(fā)費支出系數(shù)、廣告費支出系數(shù)和電冰箱銷量系數(shù)的計算是難點，需要采用LINEST（多元回歸函數(shù)）來求。這里LINEST 函數(shù)的應用需要技巧，在電冰箱銷量系數(shù)下面那一格點擊插入LINEST 函數(shù)后，在Known_y's框內(nèi)，輸入要預測值的歷史數(shù)據(jù)，即y，在Known_x's里選中所有影響因素（自變量X1，X2，X3）的歷史數(shù)據(jù)，然后點擊確定，就求出了第一個解0.304347826，也就是電冰箱銷量對壓縮機銷量的影響系數(shù)，然后選擇要計算區(qū)域的單元格，按F2鍵，再接著按組合鍵“Ctrl+Shift+Enter”，后面所有的系數(shù)都求出來了。組合鍵一般應用于數(shù)組公式的計算，一般公式的計算用“Enter”就可以。顯示的結果如圖4，預計壓縮機銷售量=-11.30435+0.260869565*4+0.043478261*5+

0.304347826*170=41.6957（萬臺）

三、模型的應用問題

（一）一般情況下在Excel工作表菜單中找不到指數(shù)平滑工具，2007年版Windows系統(tǒng)中需要在“文件”下拉菜單中選擇“選項”中的“加載宏”，在“分析工具庫”前面打√。點擊確定后選擇“數(shù)據(jù)分析”，“數(shù)據(jù)分析”才會顯示在“數(shù)據(jù)”菜單中，我們才能調(diào)用指數(shù)平滑工具，才能用指數(shù)平滑法預測銷售量。

（二）在回歸直線法模型里，點擊插入函數(shù)以后，先要確定好X 和Y，一般認為建立模型要預測哪個，哪組歷史數(shù)據(jù)就是Y，哪些會影響因變量Y 的結果，哪些就是自變量X。

（三）在多元線性回歸法模型里，自變量系數(shù)（b1，b2， b3）排列是有規(guī)定的，從文中可以看到，原始數(shù)據(jù)里第一行是研發(fā)費，第二行是廣告費，然后是電冰箱銷售量，可結果得出的先是電冰箱銷量系數(shù)，然后是廣告費支出系數(shù)和是研發(fā)費支出系數(shù)，最后才是固定成本，在操作過程中這里特別容易出錯，就是求解系數(shù)的排列跟原始數(shù)據(jù)的排列剛好相反。在設置公式時，一定嚴格按照模型y=a+b1X1+b2X2+…+bnXn計算，意思就是b1一定要乘X1，b2一定要乘X2，b3一定要乘X3，各自系數(shù)一定要對上，不能錯位，否則預測出來的值就不正確。

（四）如果發(fā)現(xiàn)銷售預測模型中的銷售量等數(shù)據(jù)輸入有誤，只需將正確的資料輸入單元格區(qū)域內(nèi)，模型將自動計算出資料修正后的銷售預測結果；如果預測銷售量的效果不夠理想，比如對指數(shù)平滑系數(shù)α的取值進行修正，可以預測出不同的銷售量；最后銷售預測模型非常實用，不僅可用于其他項目銷售量的預測分析，還可以用到成本預測、利潤預測和資金需求量預測上。

參考文獻：

[1]單昭祥，鄧雪雅.新編現(xiàn)代管理會計學[M].大連：東北財經(jīng)大學出版社，2014.

[2]馬元駒，李百興.管理會計模擬實驗教程[M].北京：中國人民大學出版社，2015.

（作者單位：廣東海洋大學寸金學院）