量本利分析是企業(yè)研究成本、產(chǎn)銷量與利潤之間依存關(guān)系和變化規(guī)律的重要手段,。但量本利分析的前提是應(yīng)用變動成本法,，按成本性態(tài)將混合成本（全部成本）分解為變動成本和固定成本兩部分,�,；旌铣杀痉纸獾姆椒ㄍǔＳ屑夹g(shù)測定法、會計法,、高低點法,、散布圖法和線性回歸法,。而線性回歸法是根據(jù)已知若干期間歷史數(shù)據(jù)，采用數(shù)學(xué)中的最小二乘法,，使所確定的直線與各成本點之間誤差平方和最小,，分解的結(jié)果最為精確,、科學(xué),；但其運算工作量大且繁復(fù)，尤其是多元回歸分解,，手工難以準確解算,。為解決線性回歸法成本分解中復(fù)雜的數(shù)學(xué)計算問題,，可借助于EXCEL電子制表系統(tǒng)的相關(guān)函數(shù),，通過計算機進行簡便的操作就可實現(xiàn),。

　　一,、分解混合成本的相關(guān)函數(shù)

　　EXCEL電子制表系統(tǒng)中函數(shù)的語法分為函數(shù)名和參數(shù)兩部分，參數(shù)用圓括號括起來,，之間以逗號隔開,。參數(shù)可以為單元格區(qū)域、數(shù)組,、函數(shù)、常數(shù)（邏輯型,、數(shù)值型等）,。分解混合成本時，主要采用線性回歸函數(shù)LINEST,，輔以使用索引取值INDEX與四舍五入ROUND函數(shù),。

　　1、線性回歸函數(shù)LINEST.LINEST類底統(tǒng)計分析函數(shù),，通常用于銷售量和成本預(yù)測,。若用于分解混合成本,，該函數(shù)的功能為：運算結(jié)果返回一線性回歸方程的參數(shù)，即當(dāng)已知一組混合成本為Y因變量序列值,、N組Xi有關(guān)自變量因素的數(shù)量序列值時,，函數(shù)返回回歸方程的系數(shù)bi（i=1,，2…n單位變動成本）和常數(shù)a（固定成本或費用）。多元回歸方程模型則為：y=b1x1＋b2X2……＋bnXn＋a語法格式：LINEST（y序列值,，x序列值,，Const常數(shù)項不為零否，Stats系數(shù)檢驗統(tǒng)計量出現(xiàn)否）。

　　其中：Const,、Stats均為邏輯變量，只有TRUE和FALSE兩個輸入選項,。Const為TRUE或被省略,，正常計算a（固定成本）,；否則為FALSE,，a設(shè)置為0.Stats指定是否返回檢驗統(tǒng)計量的值,，如果Stats為TRUE,，LINEST返回有關(guān)檢驗統(tǒng)計量,；否則為FALSE或被省略,，LINEST函數(shù)運算結(jié)果只返回系數(shù)bi（單位變動成本）和常數(shù)a（固定成本）,。LINEST函數(shù)計算結(jié)果是以數(shù)組方式反映的一個系數(shù)序列表,，其中包括檢驗統(tǒng)計量，各系數(shù)的表達次序嚴格,，參見下表，可根據(jù)需要從表中對照取值,。

　　第一行bi為各因素的單位變動成本,，a為固定成本,；第二行為各自變量因素的標準誤差值；第三行為相關(guān)系數(shù)r2與總成本y的標準誤差值,；第四行為統(tǒng)計值,、Df為自由度，分別用于判定自變量與因變量間的關(guān)系式是否偶然出現(xiàn)和確定該模型的置信度水平,；第五行SSreg與SSresid分別為回歸平方和,、殘差平方和。用INDEX函數(shù)可從表中進行行列位置定位取值,。相關(guān)系數(shù)r的取值范圍在十1與-1之間,。若r=0不相關(guān)，即業(yè)務(wù)量與總成本無直接依存關(guān)系,；若r越趨近于1,，說明相關(guān)程度越大；若r=＋1,，表示業(yè)務(wù)量與成本保持正比例相關(guān),；若r=-1，表示業(yè)務(wù)量與成本保持負比例相關(guān),。因此可根據(jù)計算結(jié)果中的相關(guān)系數(shù),，判斷其因素或多因素與混合成本費用是否相關(guān)；若相關(guān),，分解結(jié)果有效,，否則無效。

　　2,、索引取值函數(shù)INDEX.語法格式：INDEX（單元格區(qū)域或數(shù)組常量，行序號,，列序號）,；功能：使用索引從單元格區(qū)域或數(shù)組中選取值�,？捎迷摵瘮�(shù)在LINEST函數(shù)返回系數(shù)序列數(shù)組表中根據(jù)所需數(shù)據(jù)所處的行列位置定位選取,。

　　3、四舍五入函數(shù)ROUND.語法格式：ROUND（數(shù)字,，小數(shù)位數(shù)）,；功能：將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù),，用此函數(shù)指定保留的小數(shù)位數(shù),。

　　二、應(yīng)用實例

　　假定某企業(yè)前5期的動力費用與取暖日數(shù),、非生產(chǎn)用煤氣方數(shù),、發(fā)電度數(shù)三因素具有相關(guān)性,。動力費用與三因素的數(shù)學(xué)關(guān)系模型應(yīng)為：

　　動力費用y=日取暖費用b1×取暖日數(shù)X1十煤氣單位成本b2×煤氣方數(shù)x2十發(fā)電單位成本b3×發(fā)電度數(shù)xa十固定成本a源數(shù)據(jù)資料如圖的A2：E6區(qū)域，目標結(jié)果數(shù)據(jù)將被存放和顯示于B7：E9區(qū)域,。

　　操作步驟如下：

　　1,、選定任一工作表的A2：E7區(qū)域輸入已知各期動力費用、取暖日數(shù),、煤氣方數(shù),、發(fā)電度數(shù)，并進行格式設(shè)計,；選擇一連續(xù)單元格區(qū)域B7：E9,，其大小難備放置線性回歸方程的固定及變動系數(shù)a、b1,、b2,、b3及相關(guān)系數(shù)r2.

　　2、選用［插入］菜單上［名字］命令下的［定義］子命令,，將因變量動力費用所在的區(qū)域B2：B6定義為Y,，將三個自變量取暖日數(shù)、煤氣方數(shù)及發(fā)電度數(shù)所形成的連續(xù)區(qū)域定義為X.

　　3,、在單元格B8中輸入計算a的公式=ROUND（INDEX（LINEST（YX,，TRUE，TRUE）,，1,，4），2）,。其中,，與LINEST函數(shù)嵌套的INDEX函數(shù)的參數(shù)1和4，分別為INDEX函數(shù)從LINEST函數(shù)返回的檢驗統(tǒng)計量的系數(shù)表中索引a的行號與列號,；與INDEX嵌套的ROUND函數(shù)中的參數(shù)2意為保留2為小數(shù),。

　　4、將B8單元格的公式復(fù)制和粘貼在b1,、b2,、b3及相關(guān)系數(shù)r2對應(yīng)的單元植C8、D8,、E8,、B9中，然后僅修改各公式中INDEX函數(shù)從LINEST函數(shù)返回的檢驗統(tǒng)計量的系數(shù)表中索引所需系數(shù)的行號與列號,。計算單位變動成本b1,、b2、b3及相關(guān)系數(shù)r2的公式分別為=ROUND（INDEX（LINEST（Y，X,，TRUE,，TRUE），1,，3）,，2）；=ROUND（INDEX（LINEST（Y,，X,，TRUE，TRUE）,，1,，2），2）,；=ROUND（INDEX（LINES（Y,，X，TRUE,，TRUE）,，1，1）,，2）,；=ROUND（INDEX（LINEST（Y，X,，TRUE,，TRUE），3,，1）,，2）。當(dāng)公式輸入完畢,，計算結(jié)果則自動存放并顯示于B7：E9單元區(qū)域,。

　　相關(guān)系數(shù)平方為0.87，經(jīng)開方后其值接近0.93,，結(jié)果說明取暖日數(shù)、煤氣方數(shù),、發(fā)電度數(shù)與動力混合費用高度相關(guān),。取暖日數(shù)、煤氣方數(shù),、發(fā)電度數(shù)與蒸汽混合費用關(guān)系數(shù)學(xué)模型則表達為：

　　y=2282.27X1＋0.08X2＋0.31X3＋190245.1

　　用以上方法進行多元混合成本費用分解,，函數(shù)運算結(jié)果（目標數(shù)據(jù)）和源數(shù)據(jù)區(qū)域建立了自動鏈接關(guān)系。當(dāng)源數(shù)據(jù)變更時，目標數(shù)據(jù)將根據(jù)輸入的公式函數(shù)自動重新計算得出新的成本費用分解結(jié)果,。