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

謝東聞

摘要：在市場經濟“以需定銷，以銷定產”的條件下，銷售預測顯得非常重要。

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

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

一、引言

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

二、銷售預測模型的創建

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

（一）加權平均法銷售預測模型的創建

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

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

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

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

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

（二）指數平滑法銷售預測模型的創建

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

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

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

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

α取值越大，則近期實際銷售量對預測結果的影響越大；α取值越小，則近期預測銷售量對預測結果的影響越大。平滑指數α是一個經驗數據，其取值范圍通常在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（臺）。

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

（三）回歸直線法銷售預測模型的創建

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

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

（四）多元線性回歸法銷售預測模型的創建

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

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

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

0.304347826*170=41.6957（萬臺）

三、模型的應用問題

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

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

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

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

參考文獻：

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

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

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