EXCEL分解混合成本

EXCEL分解混合成本

EXCEL分解混合成本 量本利分析是企業研究成本、產銷量與利潤之間依存關系和變化規律的重要手段。但量本利分析的前提是應用變動成本法，按成本性態將混合成本（全部成本）分解為變動成本和固定成本兩部分。混合成本分解的方法通常有技術測定法、會計法、高低點法、散布圖法和線性回歸法。而線性回歸法是根據已知若干期間歷史數據，采用數學中的最小二乘法，使所確定的直線與各成本點之間誤差平方和最小，分解的結果最為精確、科學；但其運算工作量大且繁復，尤其是多元回歸分解，手工難以準確解算。為解決線性回歸法成本分解中復雜的數學計算問題，可借助于EXCEL電子制表系統的相關函數，通過計算機進行簡便的操作就可實現。

　　一、分解混合成本的相關函數

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

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

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

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

　　2、索引取值函數INDEX.語法格式：INDEX（單元格區域或數組常量，行序號，列序號）；功能：使用索引從單元格區域或數組中選取值。可用該函數在LINEST函數返回系數序列數組表中根據所需數據所處的行列位置定位選取。

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

下一頁