混合成本-量-成本-利润分析的Excel分解是企业研究成本、产销量和利润之间依赖关系和变化规律的重要手段。然而，批量成本利润分析的前提是应用可变成本法，根据成本性质将混合成本（总成本）分解为可变成本和固定成本。混合成本分解方法通常包括技术计量法、会计核算法、高低点法、散点图法和线性回归法。

    线性回归法以已知的几个时期的历史数据为基础，采用数学上的最小二乘法，使确定的直线与各成本点之间的误差平方和最小，分解结果最为准确、科学；然而，计算量大且复杂，尤其是多元回归分解，很难用手工精确求解。为了解决线性回归法成本分解中复杂的数学计算问题，借助Excel电子制表系统的相关函数，通过简单的计算机操作即可实现。

    我

    混合成本相关函数的分解

    Excel电子表格系统中函数的语法分为函数名和参数。参数用括号括起，并用逗号分隔。参数可以是单元格范围、数组、函数、常量（逻辑、数字等）。分解混合成本时，主要使用线性回归函数Liest，辅以指标值指数和舍入函数。

    1.线性回归函数Liest。Liest是一种底部统计分析函数，通常用于销售量和成本预测。如果用于分解混合成本，则函数为：

    运算结果返回线性回归方程的参数，

    也就是说，当一组混合成本已知为Y相关变量的序列值和席西相关独立变量因子的个组的数量序列值时，函数返回回归方程的系数毕（i＝1, 2…单位可变成本）和常数A（固定成本或费用）。多元回归方程模型为：y=b1x1+b2x2…+bx+a语法格式：Liest（Y序列值，x序列值，常数项不为零，否，统计系数测试统计是否出现）。

    Cost和stats是逻辑变量，只有两个输入选项：true和false。

    常量为真或省略，

    a（固定成本）正常计算；否则就是假的,，

    将a设置为0.stats以指定是否返回测试统计信息的值。如果stats为true，Liest返回相关的测试统计信息；否则为false或省略，Liest函数的运算结果仅返回系数Bi（单位可变成本）和常数a（固定成本）。Liest函数的计算结果是以数组形式反映的系数序列表，包括测试统计。每个系数的表达顺序都很严格。见下表，可根据需要从表中比较值。

    第一行Bi为各因素的单位变动成本，a为固定成本；第二

    各变量因子的标准误差值；第三

    行为相关系数R2和总成本y之间的标准误差；第四，行为统计和DF是自由度，分别用于确定自变量和因变量之间的关系是否偶然发生，以及确定模型的置信水平；第五行ssreg和ssresid分别是回归的平方和和和残差的平方和。索引函数可用于从表中定位行和列的位置。相关系数r的取值范围在10-1和-1之间。如果r=0是不相关的，也就是说，

    业务量和总成本之间没有直接的依赖关系；如果R接近1，

    关联度越大；如果r=+1，则表示业务量与成本成正比；如果r=-1，则表示业务量与成本呈负相关。因此，根据计算结果中的相关系数，可以判断其因素或多个因素是否与混合成本相关；如果相关，则分解结果有效，否则无效。

    2.索引值函数索引。语法格式：索引（单元格范围或数组常量、行序列号、列序列号）；功能：使用索引从单元格范围或数组中选择值。

    此函数可用于根据Liest函数返回的系数序列数组表中所需数据的行和列位置进行定位和选择。

    3.舍入函数舍入。语法格式：四舍五入（数字，小数位）；函数：将数字舍入到指定的小数位数。由于Liest函数的返回值为小数点后6位，因此使用此函数可指定保留的小数点。

    二、应用实例

    假设企业前五个期间的电力成本与三个因素相关：供暖天数、非生产用气量和发电程度。电力成本与三个因素之间的数学关系模型应为：

    电力成本y=日供热成本B1×供热天数X1×燃气单位成本B2×燃气量x2+发电单位成本B3×发电度XA+固定成本a源数据如图A2:E6区域所示，目标结果数据将存储并显示在B7:E9区域。

    操作步骤如下：

    1.选择任意工作表的A2:E7区域，输入各时段的已知电费、采暖天数、气量、发电度，并设计格式；选择连续单元格区域B7:E9，其大小难以放置线性回归方程的固定和可变系数a、B1、B2、B3和相关系数R2

    2.

    在[isert]菜单上的[ame]命令下选择[defiitio]子命令，将因变量电力成本所在的区域B2:B6定义为y，将三个自变量加热天数、燃气量和发电度形成的连续区域定义为X

    3.在单元格B8中输入计算a=roud（索引（Liest（YX，true，true），1，4），2）的公式。与Liest函数嵌套的索引函数的参数1和4分别是索引函数从Liest函数返回的测试统计数据的系数表中索引a的行号和列号；与索引嵌套的roud函数中的参数2意味着将2保留为十进制。

    4.

    将B8单元格的公式复制粘贴到B1、B2、B3和相关系数R2对应的C8、D8、E8和B9单元格中，然后只修改每个公式中Liest函数的idex函数返回的测试统计系数表中所需系数的行号和列号。计算单位可变成本B1、B2、B3和相关系数R2的公式为=四舍五入（指数（线性（y、x、真、真）、1、3、2）；

    =ROUD（索引（LIEST（Y，X，TRUE，TRUE），1,2），2）；=ROUD（索引（lie（Y，X，TRUE，TRUE），1,1），2）；=ROUD（索引（LIEST（Y，X，TRUE，TRUE，TRUE），3,1），2）。输入公式后，计算结果将自动存储并显示在B7:E9单元格区域中。

    相关系数的平方为0.87，经平方计算其值接近0.93。结果表明，供热天数、燃气面积、发电度与混合动力发电成本高度相关。加热天数、燃气量、发电度和蒸汽混合成本之间关系的数学模型表示如下：

    　　y=.27X1＋0.08X2＋0.31X3＋45.1

    利用上述方法对多元混合成本进行分解，建立函数运算结果（目标数据）与源数据区域的自动链接关系。当源数据更改时，

    目标数据将根据输入的公式函数自动重新计算新的成本分解结果。

    （转载于友商网）

    有关报告：

    Excel会计应学会35个技巧

    如何使用Excel进行快速财务分析

    

    

本文来源 ：用友畅捷通全国服务联盟，原文地址：/yonyou/xyyyrj59/4402.html