EXCEL函數(shù)在混合成本分解中的巧用

王雍欣

EXCEL函數(shù)在混合成本分解中的巧用

王雍欣

混合成本分解是成本性態(tài)分析的前提和基礎。傳統(tǒng)的混合成本分解方法工作量大，計算復雜，難以記憶。利用EXCEL的CORREL、SLOPE和INTERCEPT函數(shù)可以有效簡化混合成本分解問題。

混合成本分解；CORREL；SLOPE；INTERCEPT

一、引言

成本性態(tài)又稱成本習性，是指成本總額的變動與業(yè)務總量（產(chǎn)量或銷售量）之間的依存關系。成本總額與業(yè)務總量的依存關系是客觀存在的，而且具有規(guī)律性。管理會計作為決策會計的角色，其許多決策方法特別是短期決策方法都必須借助于成本性態(tài)這一概念。按成本性態(tài)可以將企業(yè)的全部成本分為固定成本、變動成本和混合成本三類。固定成本是指其總額在一定期間和一定業(yè)務量范圍內(nèi)，不受業(yè)務量變動的影響而保持固定不變的成本。變動成本是指在一定的期間和一定業(yè)務量范圍內(nèi)下隨業(yè)務量的變動而成正比例變動的成本。在實際工作中，許多成本往往介于固定成本和變動成本之間，它們既非完全固定不變，也不隨業(yè)務量成正比例變動，因而稱為混合成本。混合成本的基本特征是發(fā)生額的高低雖然直接受業(yè)務量大小的影響，但不存在嚴格的比例關系。決策時需要將混合成本按性態(tài)分解為變動成本和固定成本，只有這樣才能為決策所用。混合成本的分解方法，一般有歷史成本分析方法、工程分析法、賬戶分析法和合同確認法等。這些方法工作量大，計算復雜，難以記憶。筆者探討利用EXCEL函數(shù)法來簡化混合成本分解問題。

二、EXCEL函數(shù)與基本方法

簡化混合成本分解問題使用的EXCEL函數(shù)有CORREL、SLOPE和INTERCEPT。

1.CORREL函數(shù)

（1）含義：返回單元格區(qū)域array1和array2之間的相關系數(shù)。

（2）語法：CORREL(array1,array2)。

（3）參數(shù)：array1是第一組數(shù)值單元格區(qū)域；array2是第二組數(shù)值單元格區(qū)域。

2.SLOPE函數(shù)

（1）含義：返回經(jīng)過給定數(shù)據(jù)點的線性回歸擬合線方程的斜率(它是直線上任意兩點的垂直距離與水平距離的比值，也就是回歸直線的變化率)。

（2）語法：SLOPE(known_y’s，known_x’s)。

（3）參數(shù)：Known_y’s為數(shù)字型因變量數(shù)組或單元格區(qū)域，Known_x’s為自變量數(shù)據(jù)點集合。

3.INTERCEPT函數(shù)

（1）含義：利用現(xiàn)有的x值與y值計算直線與y軸的截距。截距為穿過已知的known_x's和known_y's數(shù)據(jù)點的線性回歸線與y軸的交點。

（2）語法：INTERCEPT(known_y's,known_x's)。

（3）參數(shù)：Known_y's為因變的觀察值或數(shù)據(jù)集合；Known _x's為自變的觀察值或數(shù)據(jù)集合。

4.基本方法

運行EXCEL程序，分兩列從上至下分別錄入自變量（X）列數(shù)值和相應的因變量（Y）列數(shù)值，選擇空白單元格，點擊“插入”選擇“函數(shù)”，出現(xiàn)插入函數(shù)對話框，在“選擇類別”中選擇“統(tǒng)計”，在下面的滾動菜單中選擇“INTERCEPT”函數(shù)(求線性回歸方程的截距a)，單擊“確定”后出現(xiàn)“函數(shù)參數(shù)”對話框，在Known_y’s框中選擇Y值區(qū)域，在Known_x’s框中選擇X值區(qū)域，對話框下方自動顯示計算結(jié)果，可求出a。同樣的方法，在滾動菜單中選擇“SLOPE”函數(shù)(求線性回歸方程的斜率b)，可求出b。由此，可得出混合成本分解模型y=a+bx。

三、應用舉例

例：已知某公司的2013年1-6月份的有關資料如表1所示。

表1

1.傳統(tǒng)混合成本分解方法一：高低點法

高低點法是以最高業(yè)務量的混合成本與最低業(yè)務量的混合成本之差，除以最高業(yè)務量與最低業(yè)務量之差，計算出單位變動成本，然后再代入總成本公式y(tǒng)＝a+bx，根據(jù)最高業(yè)務量或最低業(yè)務量的數(shù)據(jù)，即可分解出混合成本中的固定成本。計算過程如下：

解：本例中最高點業(yè)務量為9萬件，對應的混合成本為230；最低點業(yè)務量為4萬件，對應的混合成本為120，根據(jù)定義：

單位變動成本b=(230-120)/(9-4)=22（萬元）

固定成本a=230-22*9=32（萬元）

因此混合成本分解模型為：y=32+22x

2.傳統(tǒng)混合成本分解方法二：回歸直線法

回歸直線法是運用數(shù)理統(tǒng)計中常用的最小平方法的原理，對所觀察到的全部數(shù)據(jù)加以計算，從而勾畫出最能代表平均水平的直線。這條通過回歸分析而得到的直線就叫回歸直線，它的截距就是固定成本a，斜率就是單位變動成本b。如果采用回歸分析法進行成本性態(tài)分析，計算過程如下：

解：根據(jù)歷史資料數(shù)據(jù)列表，計算出n,∑x,∑y,∑xy,∑x2,∑y2的值。計算結(jié)果如表2所示。

因為r趨向于+1，表明x和y基本正相關，可以建立成本模型。

則：a=(271×1085-39×7480)÷(6×271-392)≈22.05(元)

b=(6×7480-39×1085)÷(6×271-392)≈24.43（元/件）

故該企業(yè)的成本模型為：y=a+bx=22.05+24.43x

表2 回歸分析計算表

3.EXCEL函數(shù)法

如果采用EXCEL函數(shù)處理，計算步驟和過程如下：（1）在EXCEL空白表中錄入數(shù)據(jù)，見表3。

表3

(2)在空白單元格內(nèi)插入統(tǒng)計函數(shù)CORREL。

選擇空白單元格——插入——函數(shù)——統(tǒng)計——選擇CORREL，結(jié)果如圖1：

圖1：插入CORREL函數(shù)

（3）圖1確定后，選擇區(qū)域X數(shù)據(jù)區(qū)域和Y數(shù)據(jù)區(qū)域，結(jié)果如圖2。從圖2中可見相關系數(shù)r為0.977888199。

圖2：CORREL函數(shù)參數(shù)

（4）在空白單元格內(nèi)插入統(tǒng)計函數(shù)SLOPE。

選擇空白單行格——插入——函數(shù)——統(tǒng)計——選擇SLOPE，結(jié)果如圖3。

圖3：插入SLOPE函數(shù)

（5）圖3確定后，選擇區(qū)域Y數(shù)據(jù)區(qū)域和X數(shù)據(jù)區(qū)域，結(jié)果如圖4。從圖4中可見單位變動成本b為24.42857143。

圖4：SLOPE函數(shù)參數(shù)

（6）在空白單元格內(nèi)插入統(tǒng)計函數(shù)INTERCEPT。選擇空白單行格——插入——函數(shù)——統(tǒng)計——選擇INTERCEPT函數(shù)，結(jié)果如圖5。

圖5：插入INTERCEPT函數(shù)

（7）圖5確定后，選擇區(qū)域Y數(shù)據(jù)區(qū)域和X數(shù)據(jù)區(qū)域，結(jié)果如圖6。從圖6中可見固定成本a為22.04761905。如果保留兩位小數(shù)，由此可得混合成本分解模型為y=24.43+22.05b。

四、結(jié)論

對比上述三種方法可以發(fā)現(xiàn)：高低點法的優(yōu)點在于簡便易行，易于理解。缺點是由于它只選擇了歷史資料中的兩組作為計算依據(jù)，使得建立起來的成本性態(tài)模型很可能不具有代表性，容易導致較大的計算誤差。如固定成本為32萬元，與后兩種方法計算的24萬元相差8萬元。與高低點法相比，回歸直線法由于選擇了包括高低兩點在內(nèi)的全部觀測數(shù)據(jù)，因而避免了高低點法中高低兩點的選取帶來的偶然性，比較科學，精確度高，但比較麻煩，計算復雜，公式不易記憶。EXCEL函數(shù)法，不但科學，精確度更高，操作簡單方便。

圖6：INTERCEPT函數(shù)參數(shù)

[1]宗國恩.管理會計學.西南財經(jīng)大學出版社，2012年1月.

[2]Excel Home.Excel2010實戰(zhàn)技巧精粹,人民郵電出版社，2013年4月.

（作者單位：安陽工學院經(jīng)濟管理學院）