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