1、1EXCEL 分解混合成本量本利分析是企业研究成本、产销量与利润之间依存关系和变化规律的重要手段。但量本利分析的前提是应用变动成本法，按成本性态将混合成本（全部成本）分解为变动成本和固定成本两部分。混合成本分解的方法通常有技术测定法、会计法、高低点法、散布图法和线性回归法。而线性回 归法是根据已知若干期间历史数据，采用数学中的最小二乘法，使所确定的直线与各成本点之间误差平方和最小，分解的结果最为精确、科学；但其运算工作量大且繁复，尤其是多元回 归分解，手工难以准确解算。为解决线性回归法成本分解中复杂的数学计算问题，可借助于 EXCEL 电子制表系统的相关函数，通过计算机进行简便的操作就可实现。
2、一、分解混合成本的相关函数EXCEL 电子制表系统中函数的语法分为函数名和参数两部分，参数用圆括号括起来，之间以逗号隔开。参数可以为单元格区域、数组、函数、常数（ 逻辑型、数值型等）。分解混合成本时，主要采用 线性回归函数 LINEST，辅以使用索引取值INDEX 与四舍五入 ROUND 函数。1、线性回归函数 LINEST.LINEST 类底统计分析函数，通常用于销售量和成本预测。若用于分解混合成本，该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合成本为 Y 因变量序列值、N 组 Xi 有关自变量因素的数量序列值时，函数返回回归方程的系数 bi（i=1，2n 单位变动成本）
3、和常数 a（固定成本或费用）。多元回归方程模型则为：y=b1x1b2X2bnXn a 语法格式：LINEST（y 序列 值，x 序列值， Const 常数项不为零否，Stats 系数检验统计量出现否）。2其中：Const 、Stats 均为逻辑变量，只有 TRUE 和 FALSE 两个输入选项。Const为 TRUE 或被省略，正常 计算 a（固定成本）；否则为 FALSE，a 设置为 0.Stats 指定是否返回检验统计量的值，如果 Stats 为 TRUE，LINEST 返回有关检验统计量；否则为 FALSE 或被省略， LINEST 函数运算结果只返回系数 bi（单位变动成本）和常数 a
4、（固定成本）。LINEST 函数计算结果是以数组方式反映的一个系数序列表，其中包括检验统计量，各系数的表达次序严格，参 见下表，可根据需要从表中对照取值。第一行 bi 为各因素的单位变动成本，a 为固定成本；第二行为各自变量因素的标准误差值；第三行为相关系数 r2 与总成本 y 的标准误差值；第四行为统计值、Df 为 自由度，分 别用于判定自变量与因变量间的关系式是否偶然出 现和确定该模型的置信度水平；第五行 SSreg 与 SSresid 分别为回归平方和、残差平方和。用INDEX 函数可从表中 进行行列位置定位取值。相关系数 r 的取值范围在十 1 与-1之间。若 r=0 不相关，即业务量
5、与总成本无直接依存关系；若 r 越趋近于 1，说明相关程度越大；若 r=1，表示业务量与成本保持正比例相关；若 r=-1，表示业务量与成本保持负比例相关。因此可根据计算结果中的相关系数，判断其因素或多因素与混合成本费用是否相关；若相关，分解结果有效，否则无效。2、索引取值函数 INDEX.语法格式：INDEX（单元格区域或数组常量，行序号，列序号）；功能：使用索引从单元格区域或数组中选取值。可用该函数在 LINEST函数返回系数序列数组表中根据所需数据所处的行列位置定位选取。3、四舍五入函数 ROUND.语法格式：ROUND （数字，小数位数）；功能：将数字四舍五入到指定的小数位数。由于 LI
6、NEST 函数的返回 值为 6 位小数，用此函数3指定保留的小数位数。 二、 应用实例假定某企业前 5 期的动力费用与取暖日数、非生产用煤气方数、发电度数三因素具有相关性。动力费用与三因素的数学关系模型应为：动力费用 y=日取暖费用 b1取暖日数 X1 十煤气单位成本 b2煤气方数 x2 十发电单位成本 b3发电度数 xa 十固定成本 a 源数据 资料如图的 A2：E6 区域，目标结果数据将被存放和显示于 B7：E9 区域。操作步骤如下：1、选定任一工作表的 A2：E7 区域输入已知各期动力费用、取暖日数、煤气方数、发电度数，并进行格式设计；选择一连续单元格区域 B7：E9，其大小难备放置线性
7、回归方程的固定及变动系数 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 为小数。
8、4、将 B8 单元格的公式复制和粘 贴在 b1、b2、b3 及相关系数 r2 对应的单元植4C8、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，结果说明取暖日数、煤气方数、发电度数与动力混合费用高度相关。取暖日数、煤气方数、发电度数与蒸汽混合费用关系数学模型则表达为：y=2282.27X10.08X20.31X3190245.1用以上方法进行多元混合成本费用分解，函数运算结果（目标数据）和源数据区域建立了自动链接关系。当源数据变更时，目 标数据将根据输入的公式函数自动重新计算得出新的成本费用分解结果。
