EXCEL分解混合本錢
量本利是本錢、產(chǎn)銷量與利潤之間依存關(guān)系和變化的重要手段。但量本利分析的條件是變動本錢法，按本錢性態(tài)將混合本錢（全部本錢）分解為變動本錢和固定本錢兩部分。混合本錢分解的通常有技術(shù)測定法、法、高低點(diǎn)法、散布圖法和線性回回法。而線性回回法是根據(jù)已知若干期間數(shù)據(jù)，采用數(shù)學(xué)中的最小二乘法，使所確定的直線與各本錢點(diǎn)之間誤差平方和最小，分解的結(jié)果最為精確、；但其運(yùn)算工作量大且繁復(fù)，尤其是多元回回分解，手工難以正確解算。為解決線性回回法本錢分解中復(fù)雜的數(shù)學(xué)，可借助于EXCEL制表系統(tǒng)的相關(guān)函數(shù)，通過計(jì)算機(jī)進(jìn)行簡便的操縱就可實(shí)現(xiàn)。 一、分解混合本錢的相關(guān)函數(shù) EXCEL電子制表系統(tǒng)中函數(shù)的語法分為函數(shù)名和參數(shù)兩部分，參數(shù)用圓括號括起來，之間以逗號隔開。參數(shù)可以為單元格區(qū)域、數(shù)組、函數(shù)、常數(shù)（邏輯型、數(shù)值型等）。分解混合本錢時(shí)，主要采用線性回回函數(shù)LINEST，輔以使用索引取值INDEX與四舍五進(jìn)ROUND函數(shù)。 1、線性回回函數(shù)LINEST.LINEST類底統(tǒng)計(jì)分析函數(shù)，通常用于銷售量和本錢猜測。若用于分解混合本錢，該函數(shù)的功能為：運(yùn)算結(jié)果返回一線性回回方程的參數(shù)，即當(dāng)已知一組混合本錢為Y因變量序列值、N組Xi有關(guān)自變量因素的數(shù)目序列值時(shí)，函數(shù)返回回回方程的系數(shù)bi（i=1，2…n單位變動本錢）和常數(shù)a（固定本錢或用度）。多元回回方程模型則為：y=b1x1＋b2X2……＋bnXn＋a語法格式：LINEST（y序列值，x序列值，Const常數(shù)項(xiàng)不為零否，Stats系數(shù)檢驗(yàn)統(tǒng)計(jì)量出現(xiàn)否）。 其中：Const、Stats均為邏輯變量，只有TRUE和FALSE兩個輸進(jìn)選項(xiàng)。Const為TRUE或被省略，正常計(jì)算a（固定本錢）；否則為FALSE，a設(shè)置為0.Stats指定是否返回檢驗(yàn)統(tǒng)計(jì)量的值，假如Stats為TRUE，LINEST返回有關(guān)檢驗(yàn)統(tǒng)計(jì)量；否則為FALSE或被省略，LINEST函數(shù)運(yùn)算結(jié)果只返回系數(shù)bi（單位變動本錢）和常數(shù)a（固定本錢）。LINEST函數(shù)計(jì)算結(jié)果是以數(shù)組方式反映的一個系數(shù)序列表，其中包括檢驗(yàn)統(tǒng)計(jì)量，各系數(shù)的表達(dá)次序嚴(yán)格，參見下表，可根據(jù)需要從表中對照取值。 第一行bi為各因素的單位變動本錢，a為固定本錢；第二行為各自變量因素的標(biāo)準(zhǔn)誤差值；第三行為相關(guān)系數(shù)r2與總本錢y的標(biāo)準(zhǔn)誤差值；第四行為統(tǒng)計(jì)值、Df為自由度，分別用于判定自變量與因變量間的關(guān)系式是否偶然出現(xiàn)和確定該模型的置信度水平；第五行SSreg與SSresid分別為回回平方和、殘差平方和。用INDEX函數(shù)可從表中進(jìn)行行列位置定位取值。相關(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ù)量與本錢保持負(fù)比例相關(guān)。因此可根據(jù)計(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、四舍五進(jìn)函數(shù)ROUND.語法格式：ROUND（數(shù)字，小數(shù)位數(shù)）；功能：將數(shù)字四舍五進(jìn)到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù)，用此函數(shù)指定保存的小數(shù)位數(shù)。 二、實(shí)例 假定某前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ū)域，目標(biāo)結(jié)果數(shù)據(jù)將被存放和顯示于B7：E9區(qū)域。 操縱步驟如下： 1、選定任一工作表的A2：E7區(qū)域輸進(jìn)已知各期動力用度、取熱日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)，并進(jìn)行格式設(shè)計(jì)；選擇一連續(xù)單元格區(qū)域B7：E9，其大小難備放置線性回回方程的固定及變動系數(shù)a、b1、b2、b3及相關(guān)系數(shù)r2. 2、選用［插進(jìn)］菜單上［名字］命令下的［定義］子命令，將因變量動力用度所在的區(qū)域B2：B6定義為Y，將三個自變量取熱日數(shù)、煤氣方數(shù)及發(fā)電度數(shù)所形成的連續(xù)區(qū)域定義為X. 3、在單元格B8中輸進(jìn)a的公式=ROUND（INDEX（LINEST（YX，TRUE，TRUE），1，4），2）。其中，與LINEST函數(shù)嵌套的INDEX函數(shù)的參數(shù)1和4，分別為INDEX函數(shù)從LINEST函數(shù)返回的檢驗(yàn)統(tǒng)計(jì)量的系數(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ù)返回的檢驗(yàn)統(tǒng)計(jì)量的系數(shù)表中索引所需系數(shù)的行號與列號。計(jì)算單位變動本錢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)公式輸進(jìn)完畢，計(jì)算結(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é)模型則表達(dá)為： y=2282.27X1＋0.08X2＋0.31X3＋190245.1 用以上進(jìn)行多元混合本錢用度分解，函數(shù)運(yùn)算結(jié)果（目標(biāo)數(shù)據(jù)）和源數(shù)據(jù)區(qū)域建立了自動鏈接關(guān)系。當(dāng)源數(shù)據(jù)變更時(shí)，目標(biāo)數(shù)據(jù)將根據(jù)輸進(jìn)的公式函數(shù)自動重新計(jì)算得出新的本錢用度分解結(jié)果。【EXCEL分解混合本錢】相關(guān)文章：
本錢計(jì)量模式與企業(yè)本錢控制機(jī)制研究03-25
資金本錢與資本本錢的混淆及其危害02-26
對本錢計(jì)算中本錢還原方法的探討03-29
本錢治理辦法02-27
偵查本錢論03-24
質(zhì)量本錢探討02-27