1/5用EXCEL分解混合成本量本利分析是企業(yè)研究成本、產(chǎn)銷量與利潤之間依存關(guān)系和變化規(guī)律的重要手段。但量本利分析的前提是應(yīng)用變動成本法，按成本性態(tài)將混合成本分解為變動成本和固定成本兩部分。混合成本分解的方法通常有技術(shù)測定法、會計法、高低點法、散布圖法和線性回歸法。而線性回歸法是根據(jù)已知若干期間歷史數(shù)據(jù)，采用數(shù)學(xué)中的最小二乘法，使所確定的直線與各成本點之間誤差平方和最小，分解的結(jié)果最為精確、科學(xué)；但其運(yùn)算工作量大且繁復(fù)，尤其是多元回歸分解，手工難以準(zhǔn)確解算。為解決線性回歸法成本分解中復(fù)雜的數(shù)學(xué)計算問題，可借助于EXCEL電子制表系統(tǒng)的相關(guān)函數(shù)，通過計算機(jī)進(jìn)行簡便的操作就可實現(xiàn)。一、分解混合成本的相關(guān)函數(shù)EXCEL電子制表系統(tǒng)中函數(shù)的語法分為函數(shù)名和參數(shù)兩部分，參數(shù)用圓括號括起來，之間以逗號隔開。參數(shù)可以為單元格區(qū)域、數(shù)組、函數(shù)、常數(shù)。分解混合成本時，主要采用線性回歸函數(shù)LINEST，輔以使用索引取值INDEX與四舍五入ROUND函數(shù)。1、線性回歸函數(shù)類底統(tǒng)計分析函數(shù)，通常用于銷售量和成本預(yù)測。若用于分解混合成本，該函數(shù)的功能為運(yùn)算結(jié)果返回一線性回歸方程的參數(shù)，即當(dāng)已知一組混合成本為Y因變量序列值、N組XI有關(guān)自變量因素的數(shù)量序2/5列值時，函數(shù)返回回歸方程的系數(shù)BI和常數(shù)A。多元回歸方程模型則為YB1X1B2X2BNXNA語法格式LINEST。LOCALHOST其中CONST、STATS均為邏輯變量，只有TRUE和FALSE兩個輸入選項。CONST為TRUE或被省略，正常計算A；否則為FALSE，A設(shè)置為指定是否返回檢驗統(tǒng)計量的值，如果STATS為TRUE，LINEST返回有關(guān)檢驗統(tǒng)計量；否則為FALSE或被省略，LINEST函數(shù)運(yùn)算結(jié)果只返回系數(shù)BI和常數(shù)A。LINEST函數(shù)計算結(jié)果是以數(shù)組方式反映的一個系數(shù)序列表，其中包括檢驗統(tǒng)計量，各系數(shù)的表達(dá)次序嚴(yán)格，參見下表，可根據(jù)需要從表中對照取值。第一行BI為各因素的單位變動成本，A為固定成本；第二行為各自變量因素的標(biāo)準(zhǔn)誤差值；第三行為相關(guān)系數(shù)R2與總成本Y的標(biāo)準(zhǔn)誤差值；第四行為統(tǒng)計值、DF為自由度，分別用于判定自變量與因變量間的關(guān)系式是否偶然出現(xiàn)和確定該模型的置信度水平；第五行SSREG與SSRESID分別為回歸平方和、殘差平方和。用INDEX函數(shù)可從表中進(jìn)行行列位置定位取值。相關(guān)系數(shù)R的取值范圍在十1與1之間。若R0不相關(guān)，即業(yè)務(wù)量與總成本無直接依存關(guān)系；若R越趨近于1，說明相關(guān)程度越大；若R1，表示業(yè)務(wù)量與成本保持正比例相關(guān)；若R1，表示業(yè)務(wù)量與成本保持負(fù)比例相關(guān)。因此可根據(jù)計算結(jié)果中的相關(guān)系數(shù)，判斷3/5其因素或多因素與混合成本費(fèi)用是否相關(guān)；若相關(guān)，分解結(jié)果有效，否則無效。、索引取值函數(shù)INDEX語法格式INDEX；功能使用索引從單元格區(qū)域或數(shù)組中選取值。可用該函數(shù)在LINEST函數(shù)返回系數(shù)序列數(shù)組表中根據(jù)所需數(shù)據(jù)所處的行列位置定位選取。、四舍五入函數(shù)ROUND語法格式ROUND；功能將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù)，用此函數(shù)指定保留的小數(shù)位數(shù)。二、應(yīng)用實例假定某企業(yè)前5期的動力費(fèi)用與取暖日數(shù)、非生產(chǎn)用煤氣方數(shù)、發(fā)電度數(shù)三因素具有相關(guān)性。動力費(fèi)用與三因素的數(shù)學(xué)關(guān)系模型應(yīng)為動力費(fèi)用Y日取暖費(fèi)用B1取暖日數(shù)X1十煤氣單位成本B2煤氣方數(shù)X2十發(fā)電單位成本B3發(fā)電度數(shù)XA十固定成本A源數(shù)據(jù)資料如圖的A2E6區(qū)域，目標(biāo)結(jié)果數(shù)據(jù)將被存放和顯示于B7E9區(qū)域。操作步驟如下1、選定任一工作表的A2E7區(qū)域輸入已知各期動力費(fèi)用、取暖日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)，并進(jìn)行格式設(shè)計；選擇一連續(xù)單元格區(qū)域B7E9，其大小難備放置線性回歸方程的固定及變動系數(shù)A、B1、B2、B3及相關(guān)系數(shù)R24/5、選用插入菜單上名字命令下的定義子命令，將因變量動力費(fèi)用所在的區(qū)域B2B6定義為Y，將三個自變量取暖日數(shù)、煤氣方數(shù)及發(fā)電度數(shù)所形成的連續(xù)區(qū)域定義為X、在單元格B8中輸入計算A的公式ROUND，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ù)。、將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，1，3），2）；ROUND，1，2），2）；ROUND，1，1），2）；ROUND，3，1），2）。當(dāng)公式輸入完畢，計算結(jié)果則自動存放并顯示于B7E9單元區(qū)域。相關(guān)系數(shù)平方為，經(jīng)開方后其值接近，結(jié)果說明取暖日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)與動力混合費(fèi)用高度相關(guān)。取暖日數(shù)、煤氣方數(shù)