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

王雍欣

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

王雍欣

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

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

一、引言

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

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

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

1.CORREL函數(shù)

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

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

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

2.SLOPE函數(shù)

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

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

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

3.INTERCEPT函數(shù)

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

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

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

4.基本方法

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

三、應(yīng)用舉例

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

表1

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

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

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

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

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

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

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

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

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

因?yàn)閞趨向于+1，表明x和y基本正相關(guān)，可以建立成本模型。

則：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 回歸分析計(jì)算表

3.EXCEL函數(shù)法

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

表3

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

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

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

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

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

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

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

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

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

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

（6）在空白單元格內(nèi)插入統(tǒng)計(jì)函數(shù)INTERCEPT。選擇空白單行格——插入——函數(shù)——統(tǒng)計(jì)——選擇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)：高低點(diǎn)法的優(yōu)點(diǎn)在于簡便易行，易于理解。缺點(diǎn)是由于它只選擇了歷史資料中的兩組作為計(jì)算依據(jù)，使得建立起來的成本性態(tài)模型很可能不具有代表性，容易導(dǎo)致較大的計(jì)算誤差。如固定成本為32萬元，與后兩種方法計(jì)算的24萬元相差8萬元。與高低點(diǎn)法相比，回歸直線法由于選擇了包括高低兩點(diǎn)在內(nèi)的全部觀測數(shù)據(jù)，因而避免了高低點(diǎn)法中高低兩點(diǎn)的選取帶來的偶然性，比較科學(xué)，精確度高，但比較麻煩，計(jì)算復(fù)雜，公式不易記憶。EXCEL函數(shù)法，不但科學(xué)，精確度更高，操作簡單方便。

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

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

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

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