Excel回歸分析工具在分解混合成本中的應用
馬元駒
由于企業中存在著相當多的既不是變動成本也不是固定成本的混合成本，將混合成本分解為變動成本和固定成本就成為應用管理會計的前提條件和基礎工作，因此顯然正確地分解混合成本不僅對于計算變動成本、預測、經營決策、全面預算、控制以及業績評價都具有重要的影響，而且直接關系到管理會計的應用效果。目前分解混合成本的方法主要有：“布點圖法”、“高低點法”和“回歸直線法”等。一般說來“回歸直線法”利用了微分極值原理，用它分解混合成本其結果要比采用其他方法更準確；但是值得指出的是采用這種方法是以業務量（x）和成本（y）之間存在顯著的線性關系為前提，也就是說當業務量（x）和成本（y）之間如果不存在顯著的線性關系或線性相關程度較低或存在其他關系時，這種方法的準確性就難以保證。因此為了檢驗業務量（x）和成本（y）之間是否存在顯著的線性關系，“回歸直線法”常常需要計算相關系數r，并以此作為判斷兩者之間的是否存在顯著相關關系的依據。
然而現代統計理論告訴我們，僅僅依賴相關系數r來判斷業務量（x）和成本（y）之間是否存在顯著的線性關系是不可靠的。因為相關系數存在一個明顯的缺點，即它接近于1的程度與觀察數據組數n有密切的關系，當n較小時，相關系數的絕對值容易偏大，當n=2時，相關系數的絕對值總為1。因此僅僅以相關系數是否趨近于1來判斷業務量（x）和成本（y）之間是否存在顯著的線性關系是很不可靠的，從而使建立在變動成本基礎之上的管理會計難以發揮作用。
為了可靠地判斷業務量（x）和成本（y）之間是否存在顯著的線性關系，不僅需要計算相關系數r，還應當使用“決定系數”、“F檢驗”、“殘差圖”等多種統計指標進行檢驗并得出一致結論后，才可以比較準確地確定x與y之間的關系。我們知道在手工條件下計算相關系數r已經是一件相當復雜和費時的事了，如果再計算更多的統計指標其工作量是不言而喻的，然而利用“EXCel電子表”為我們判斷業務量（x）和成本（y）之間是否存在顯著的線性關系提供了極大的方便，使統計指標計算的工作大大簡化，進而為管理會計應用向廣度和深度發展拓展了空間。本文著重介紹如何借助Excel電子表回歸分析工具判斷業務量（x）和成本（y）之間是否存在顯著的線性關系以及分解混合成本的方法和步驟。
一、啟動Excel電子表輸入相關資料
為了便于討論，我們選取某企業18個月的機器小時和制造費用的歷史資料，并應用“Excel電子表”中的“回歸分析工具”計算用于判定該企業機器小時和制造費用之間是否具有相關關系的有關指標。在“Windows”界面中，用鼠標單臺“開始”，再單擊“程序”及其下拉菜單中的“ Microsoft Excel”程序，即可啟動“ Excel電子表格”，出現一張標為“Book1”的空白“Excel電子表”，在該表中輸入業務量（機器小時）和成本制造費用（元）的歷史資料（注意輸入的資料應按列的順序排列）。如圖1所示：
二、調用回歸分析工具計算相關統計指標
在“Excel工作表”的“菜單”欄單擊“工具”選項，在下拉菜單中單擊“數據分析”選項，彈出“數據分析”的“分析工具”選項框，從中選中“回歸”分析工具。如圖2所示：
?。ㄗ⒁猓喝绻凇肮ぞ摺辈藛沃袥]有出現“數據分析”命令，必須在“Excel工作表”中安裝“分析工具庫”。即在“工具”菜單中，單擊“加載宏”命令。然后在“當前加載宏”的對話框中選中“分析工具庫”，單擊“確定”按鈕，“數據分析”工具就加載完成。）
單擊“確定”后，彈出“分析工具”中的“回歸”分析對話框。如圖3所示：
現在只需要在“回歸”分析對話框中輸入相應的數據并確定相關的選項即可。首先，單擊“Y值輸入區域（Y）：”右邊的紅色箭頭，折疊對話框，然后用鼠標選中制造費用Y值。
如圖4所示，單擊折疊對話框，回到“分析工具”的“回歸”分析對話框。用同樣的方法單擊“x值輸入區域（x）：”右邊的紅色箭頭，折疊對話框，然后用鼠標選中業務量x值。再次單擊折疊對話框，回到“分析工具”的“回歸”分析對話框。如圖5所示：
在“回歸分析對話框”中除了在“Y值輸入區域”因變量數據區域的引用和在“x值輸入區域”輸入對自變量數據區域的引用外，還有一些根據分析的需要所作的選項。主要有：標志、置信度、常數為零、輸出區域、新工作表組、新工作簿、殘差、標準殘差、殘差圖、正態概率圖等復選框，根據分析的需要進行選擇。這里我們選中“殘差圖”復選框。
檢查“回歸分析對話框”中的各個選項，然后單擊“確定”按鈕，系統在默認情況下，在當前工作簿中插入新工作表，并從新工作表的A1單元格開始粘貼回歸分析工具的計算結果。如圖6所示：
這樣回歸分析的計算結果就計算出來了。
三、根據計算結果分析確定x與y之間的相關關系
回歸分析工具可以得出許多統計指標以及根據需要生成的相關圖表。這里我們只選擇部分統計指標和圖表作為判斷x與y之間是否存在顯著線性相關的相關關系的依據。主要分析以下指標：
1、技術指標判斷
第一，決定系數r2
r2愈趨近于1，樣本的各個觀測值就愈接近回歸直線，說明反映樣本的各個觀測值擬合回歸議程的緊密程度高。本例在回歸分析的計算結果的“回歸統計”欄中的決定系數r2為0.911242，接近于1，故說明x與y之間具有顯著的線性相關關系。
第二，F檢驗
F檢驗是對線性回歸方程顯著性的一種檢驗。它是根據平方和分解式直接從回歸效果檢驗回歸方程的顯著性。在給定的顯著性水平α下，如果F≤Fα（1，n－2），則認為變量x與y之間沒有明顯的線性關系，如果F＞rα（1，n－2），則認為變量x與y之間有顯著的線性關系。本例在回歸分析的計算結果的“方差分析”欄中的F值為：164.2648
在顯著性水平α=0.05，查F分布表F0.05（1，16）=4.49
F=164.2648＞4.49
說明變量X與y之間有顯著的線性相關關系。
2、殘差圖評價
“殘差圖”以回歸方程的自變量為橫坐標，以殘差εi為縱坐標，將每一個自變量的殘差描在該平面坐標上所形成的圖形。當描繪的點圍繞εi=0的直線上下隨機散布，說明回歸直線對原觀測值的擬合情況良好。否則，說明回歸直線對原觀測值的擬合不理想。在工作表中向右移動水平滾動條，使“殘差圖”顯現全貌。如圖7所示：
從“殘差圖”可以直觀地看出殘差的絕對數值都比較小，所描繪的點都在以0為橫軸的直線上下隨機散布，回歸直線對各個觀測值的擬合情況是良好的。說明變量X與y之間有顯著的線性相關關系。
四、構造回歸模型
通過以上分析得出了一致的結論，這樣我們就可以認為變量X與y之間存在著顯著的線性關系。進而從回歸分析的“計算結果”欄中讀取截距（Intercept）3442.459和斜率（X Variabl）0.559365，并構造回歸模型如下：
y=3442.459＋0.559365x
利用該回歸分析模型可以預測和控制制造費用。即當x（機器小時）增加或減少一個單位時，y（制造費用）平均地增加或減少0.559365個單位。因此一旦給x一個確定的值，相應地也就確定了y的值；相反，一旦給y一個確定的值，相應地也就可以確定x的值。
顯而易見，Excel電子表回歸分析工具的應用，為我們按照成本習性分解混合成本提供了極大的便利，不僅能夠使財會人員能夠非常容易地根據歷史資料從混合成本中分解出固定成本和單位變動成本，建立起用于進行成本預測和成本控制的回歸方程，而且能夠保證所建立的回歸模型中的變量x與y之間具有顯著的線性相關性，使混合成本分解的準確性和可靠性提高?？梢灶A言，Excel電子表回歸分析工具在分解混合成本中的普遍應用，將會使以變動成本計算法為基礎的管理會計在變動成本計算、成本預測、經營決策、全面預算、成本控制以及業績評價等作用得到相當程度的改善。
作者單位：中國人民大學商學院會計系
