EXCEL分解混合成本的論文 EXCEL 分解混合成本的論文 量本利分析是企業研究成本、產銷量與利潤之間依存關系和變化規律的重要手段。但量本利分析的前提是應用變動成本法，按成本性態將混合成本（全部成本）分解為變動成本和固定成本兩部分。混合成本分解的方法通常有技術測定法、會計法、高低點法、散布圖法和線性回歸法。而線性回歸法是根據已知若干期間歷史數據，采用數學中的最小二乘法，使所確定的直線與各成本點之間誤差平方和最小，分解的結果最為精確、科學；但其運算工作量大且繁復，尤其是多元回歸分解，手工難以準確解算。為解決線性回歸法成本分解中復雜的數學計算問題，可借助于 excel電子 制表系統的相關函數，通過計算機進行簡便的操作就可實現。 一、分解混合成本的相關函數 excel 電子制表系統中函數的語法分為函數名和參數兩部分，參數用圓括號括起來，之間以逗號隔開。參數可以為單元格區域、數組、函數、常數（邏輯型、數值型等）。分解混合成本時，主要采用線性回歸函數 linest，輔以使用索引取值 index與四舍五入 round函數。 1、線性回歸函數 linest.linest類底統計分析函數，通常用于銷售量和成本預測。若用于分解混合成本，該函數的功能為：運算結果返回一線性回歸方程的參數，即 當已知一組混合成本為 y 因變量序列值、 n組 xi 有關自變量因素的數量序列值時，函數返回回歸方程的系數 bi（ i=1， 2n 單位變動成本）和常數 a（固定成本或費用）。多元回歸方程模型則為： y=b1x1 b2x2 bnxn a 語法格式：linest（ y 序列值， x 序列值， const 常數項不為零否， stats 系數檢驗統計量出現否）。 wWW.11665.COM 其中： 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 位小數，用此函數指定保留的小數位數。 二、應用實例 假定某企業前 5 期的動力費用與取暖日數、非生產用煤氣方數、發電度數三因素具有相關性。動力費用與三因素的數學關系模 型應為： 動力費用 y=日取暖費用 b1 取暖日數 x1 十煤氣單位成本 b2煤氣方數 x2十發電單位成本 b3 發電度數 xa 十固定成本 a 源數據資料如圖的 a2： e6區域，目標結果數據將被存放和顯示于 b7： e9區域。 操作步驟如下： 1、選定任一工作表的 a2： e7 區域輸入已知各期動力費用、取暖日數、煤氣方數、發電度數，并進行格式設計；選擇一連續單元格區域 b7： e9，其大小難備放置線性回歸方程的固定及變動系數 a、 b1、b2、 b3 及相關系數 r2. 2、選用插入菜單上名字命令下的定義子命令，將因變量 動力費用所在的區域 b2： b6 定義為 y，將三個自變量取暖日數、煤氣方數及發電度數所形成的連續區域定義為 x. 3、在單元格 b8中輸入計算 a 的公式 =round（ index（ linest（ yx，true， true）， 1， 4）， 2）。其中，與 linest函數嵌套的 index 函數的參數 1 和 4，分別為 index函數從 linest 函數返回的檢驗統計量的系數表中索引 a的行號與列號；與 index嵌套的 round函數中的參數 2 意為保留 2為小數。 4、將 b8單元格的公式復制和粘貼在 b1、 b2、 b3 及相關系數 r2對應的單元植 c8、 d8、 e8、 b9 中，然后僅修改各公式中 index 函數從 linest 函數返回的檢驗統計量的系數表中索引所需系數的行號與列號。計算單位變動成本 b1、 b2、 b3及相關系數 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）。當公式輸入完畢，計算結果則自動存放并顯示于 b7： e9單元區域。 相關系數平方為 0.87，經開方后其值接近 0.93，結果說明取暖日數、煤氣方數、發電度數與動力混合費用高度相關。取暖日數、煤氣方數、發電度數與蒸汽混