EXCEL分解混合成本(1)

1、EXCEL剖析混淆本錢(qián)(1)量本利闡發(fā)是企業(yè)研究本錢(qián)、產(chǎn)銷(xiāo)量與利潤(rùn)之間依存干系和變革紀(jì)律的緊張本領(lǐng)。但量本利闡發(fā)的條件是應(yīng)用變更本錢(qián)法，按成天性態(tài)將混淆本錢(qián)全部本錢(qián)剖析為變更本錢(qián)和結(jié)實(shí)本錢(qián)兩部門(mén)。混淆成天職解的要領(lǐng)通常有技能測(cè)定法、管帳法、凹凸點(diǎn)法、分布圖法和線(xiàn)性回歸法。而線(xiàn)性回歸法是按照多少期間汗青數(shù)據(jù)，接納數(shù)學(xué)中的最小二乘法，使所確定的直線(xiàn)與各本錢(qián)點(diǎn)之間偏向平方和最小，剖析的效果最為正確、科學(xué)；但其運(yùn)算事情量大且繁復(fù)，尤其是多元回歸剖析，手工難以正確解算。為辦理線(xiàn)性回歸法成天職解中龐大的數(shù)學(xué)盤(pán)算題目，可借助于EXEL電子制表體系的相干函數(shù)，通過(guò)盤(pán)算機(jī)舉行輕便的操縱就可實(shí)現(xiàn)。一、剖析混淆本錢(qián)

2、的相干函數(shù)EXEL電子制表體系中函數(shù)的語(yǔ)法分為函數(shù)名和參數(shù)兩部門(mén)，參數(shù)用圓括號(hào)括起來(lái)，之間以逗號(hào)離隔。參數(shù)可以為單位格地區(qū)、數(shù)組、函數(shù)、常數(shù)邏輯型、數(shù)值型等。剖析混淆本錢(qián)時(shí)，重要接納線(xiàn)性回歸函數(shù)LINEST，輔以利用索引取值INDEX與四舍五入RUND函數(shù)。1、線(xiàn)性回歸函數(shù)LINEST.LINEST類(lèi)底統(tǒng)計(jì)闡發(fā)函數(shù)，通常用于販賣(mài)量和本錢(qián)猜測(cè)。假設(shè)用于剖析混淆本錢(qián)，該函數(shù)的成效為：運(yùn)算效果返回一線(xiàn)性回歸方程的參數(shù)，即當(dāng)一組混淆本錢(qián)為Y因變量序列值、N組Xi有關(guān)自變量因素的數(shù)目序列值時(shí)，函數(shù)返回回歸方程的系數(shù)bii=1，2n單位變更本錢(qián)和常數(shù)a結(jié)實(shí)本錢(qián)或用度。多元回歸方程模子那么為：y=b1x1b

3、2X2bnXna語(yǔ)法格式：LINESTy序列值，x序列值，nst常數(shù)項(xiàng)不為零否，Stats系數(shù)查驗(yàn)統(tǒng)計(jì)量出現(xiàn)否。此中：nst、Stats均為邏輯變量，只有TRUE和FALSE兩個(gè)輸入選項(xiàng)。nst為T(mén)RUE或被省略，正常盤(pán)算a結(jié)實(shí)本錢(qián)；不然為FALSE，a設(shè)置為0.Stats指定是否返回查驗(yàn)統(tǒng)計(jì)量的值，假設(shè)Stats為T(mén)RUE，LINEST返回有關(guān)查驗(yàn)統(tǒng)計(jì)量；不然為FALSE或被省略，LINEST函數(shù)運(yùn)算效果只返回系數(shù)bi單位變更本錢(qián)和常數(shù)a結(jié)實(shí)本錢(qián)。LINEST函數(shù)盤(pán)算效果是以數(shù)組方法反響的一個(gè)系數(shù)序列表，此中包羅查驗(yàn)統(tǒng)計(jì)量，各系數(shù)的表達(dá)序次嚴(yán)酷，拜見(jiàn)下表，可按照必要從表中比較取值。第一行bi

4、為各因素的單位變更本錢(qián)，a為結(jié)實(shí)本錢(qián)；第二舉動(dòng)各自變量因素的尺度偏向值；第三舉動(dòng)相干系數(shù)r2與總本錢(qián)y的尺度偏向值；第四舉動(dòng)統(tǒng)計(jì)值、Df為自由度，別離用于斷定自變量與因變量間的干系式是否偶爾出現(xiàn)和確定該模子的置信度程度；第五行SSreg與SSresid別離為回歸平方和、殘差平方和。用INDEX函數(shù)可從表中舉行行列位置定位取值。相干系數(shù)r的取值范疇在十1與-1之間。假設(shè)r=0不相干，即業(yè)務(wù)量與總本錢(qián)無(wú)直接依存干系；假設(shè)r越趨近于1，說(shuō)明相干程度越大；假設(shè)r=1，表現(xiàn)業(yè)務(wù)量與本錢(qián)保持正比例相干；假設(shè)r=-1，表現(xiàn)業(yè)務(wù)量與本錢(qián)保持負(fù)比例相干。因此可按照盤(pán)算效果中的相干系數(shù)，斷定其因素或多因素與混淆本

5、錢(qián)用度是否相干；假設(shè)相干，剖析效果有用，不然無(wú)效。2、索引取值函數(shù)INDEX.語(yǔ)法格式：INDEX單位格地區(qū)或數(shù)組常量，行序號(hào)，列序號(hào)；成效：利用索引從單位格地區(qū)或數(shù)組中拔取值。可用該函數(shù)在LINEST函數(shù)返回系數(shù)序列數(shù)組表中按照所需數(shù)據(jù)所處的行列位置定位選齲3、四舍五入函數(shù)RUND.語(yǔ)法格式：RUND數(shù)字，小數(shù)位數(shù)；成效：將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù)，用此函數(shù)指定保存的小數(shù)位數(shù)。二、應(yīng)用實(shí)例假定某企業(yè)前5期的動(dòng)力用度與取暖和日數(shù)、非消費(fèi)用煤氣方數(shù)、發(fā)電度數(shù)三因素具有相干性。動(dòng)力用度與三因素的數(shù)學(xué)干系模子應(yīng)為：動(dòng)力用度y=日取暖和用度b1取暖和日數(shù)X1

6、十煤氣單位本錢(qián)b2煤氣方數(shù)x2十發(fā)電單位本錢(qián)b3發(fā)電度數(shù)xa十結(jié)實(shí)本錢(qián)a源數(shù)據(jù)資料如圖的A2：E6地區(qū)，目的效果數(shù)據(jù)將被存放和表現(xiàn)于B7：E9地區(qū)。操縱步調(diào)如下：1、選定任一事情表的A2：E7地區(qū)輸入各期動(dòng)力用度、取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)，并舉行格式方案；選擇一連續(xù)單位格地區(qū)B7：E9，其巨細(xì)難備安排線(xiàn)性回歸方程的結(jié)實(shí)及變更系數(shù)a、b1、b2、b3及相干系數(shù)r2.2、選用插入菜單上名字下令下的界說(shuō)子下令，將因變量動(dòng)力用度地點(diǎn)的地區(qū)B2：B6界說(shuō)為Y，將三個(gè)自變量取暖和日數(shù)、煤氣方數(shù)及發(fā)電度數(shù)所形成的連續(xù)地區(qū)界說(shuō)為X.3、在單位格B8中輸入盤(pán)算a的公式=RUNDINDEXLINESTYX，

7、TRUE，TRUE，1，4，2。此中，與LINEST函數(shù)嵌套的INDEX函數(shù)的參數(shù)1和4，別離為INDEX函數(shù)從LINEST函數(shù)返回的查驗(yàn)統(tǒng)計(jì)量的系數(shù)表中索引a的行號(hào)與列號(hào)；與INDEX嵌套的RUND函數(shù)中的參數(shù)2意為保存2為小數(shù)。4、將B8單位格的公式復(fù)制和粘貼在b1、b2、b3及相干系數(shù)r2對(duì)應(yīng)的單位植8、D8、E8、B9中，然后僅修改各公式中INDEX函數(shù)從LINEST函數(shù)返回的查驗(yàn)統(tǒng)計(jì)量的系數(shù)表中索引所需系數(shù)的行號(hào)與列號(hào)。盤(pán)算單位變更本錢(qián)b1、b2、b3及相干系數(shù)r2的公式別離為=RUNDINDEXLINESTY，X，TRUE，TRUE，1，3，2；=RUNDINDEXLINESTY，X，TRUE，TRUE，1，2，2；=RUNDINDEXLINESY，X，TRUE，TRUE，1，1，2；=RUNDINDEXLINESTY，X，TRUE，TRUE，3，1，2。當(dāng)公式輸入完畢，盤(pán)算效果那么主動(dòng)存放并表現(xiàn)于B7：E9單位地區(qū)。相干系數(shù)平方為0.87，經(jīng)開(kāi)方后其值靠近0.93，效果說(shuō)明取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)與動(dòng)力混淆用度高度相干。取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)與蒸汽混淆用度干系數(shù)學(xué)模子那