EXCEL分解混合成本(一)

EXCEL分解混合成本 (一)量本利分析是企业研究成本、产销量与利润之间依存关系和变化规律的重要手段。但量本利分析的前提是应用变动成本法，按成本性态将混合成本（全部成本）分解为变动成本和固定成本两部分。混合成本分解的方法通常有技术测定法、会计法、高低点法、散布图法和线性回归法。而线性回归法是根据已知若干期间历史数据，采用数学中的最小二乘法，使所确定的直线与各成本点之间误差平方和最小，分解的结果最为精确、科学；但其运算工作量大且繁复，尤其是多元回归分解，手工难以准确解算。为解决线性回归法成本分解中复杂的数学计算问题，可借助于 EXCEL电子制表系统的相关函数，通过计算机进行简便的操作就可实现。一、分解混合成本的相关函数EXCEL电子制表系统中函数的语法分为函数名和参数两部分， 参数用圆括号括起来， 之间以逗号隔开。 参数可以为单元格区域、 数组、函数、常数（逻辑型、数值型等）。分解混合成本时，主要采用线性回归函数LINEST，辅以使用索引取值 INDEX与四舍五入 ROUND函数。1、线性回归函数 LINEST.LINEST类底统计分析函数， 通常用于销售量和成本预测。若用于分解混合成本，该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合成本为Y 因变量序列值、 N组 Xi 有关自变量因素的数量序列值时， 函数返回回归方程的系数bi（i=1，2 n单位变动成本）和常数a（固定成本或费用）。多元回归方程模型则为： y=b1x1b2X2 bnXna 语法格式： 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 位小数，用此函数指定保留的小数位数。