如何利用EXCEL分解混合本钱-英雄云拓展知识分享

       量本利分析是企业研究本钱、产销量与利润之间依存关系和变化规律的重要手段。但量本利分析的条件是利用变动本钱法，按本钱性态将混合本钱（全部本钱）分解为变动本钱和固定本钱两部份。混合本钱分解的方法通常有技术测定法、会计法、高低点法、散布图法和线性回归法。而线性回归法是针对已知若干期间历史数据，采取数学中的最小2乘法，使所肯定的直线与各本钱点之间误差平方和最小，分解的结果最为精确、科学；但其运算工作量大且繁复，特别是多元回归分解，手工难以准确解算。为解决线性回归法本钱分解中复杂的数学计算问题，可借助于EXCEL电子制表系统的相干函数，经过计算机进行简便的操作便可实现。

      一、分解混合本钱的相干函数

      EXCEL电子制表系统中函数的语法分为函数名和参数两部份，参数用圆括号括起来，之间以逗号隔开。参数可以为单元格区域、数组、函数、常数（逻辑型、数值型等）。分解混合本钱时，主要采取线性回归函数LINEST，辅以使用索引取值INDEX与4舍5入ROUND函数。

      1、线性回归函数LINEST.LINEST类底统计分析函数，通经常使用于销售量和本钱预估。若用于分解混合本钱，该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合本钱为Y因变量序列值、N组Xi有关自变量因素的数量序列值时，函数返回回归方程的系数bi（i=1，2…n单位变动本钱）和常数a（固定本钱或费用）。多元回归方程模型则为：y=b1x1＋b2X2……＋bnXn＋a语法格式：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为固定本钱；第2行动各自变量因素的标准误差值；第3行动相干系数r2与总本钱y的标准误差值；第4行动统计值、Df为自由度，分别用于判定自变量与因变量间的关系式是不是偶然出现和肯定该模型的置信度水平；第5行SSreg与SSresid分别为回归平方和、残差平方和。用INDEX函数可从表中进行行列位置定位取值。相干系数r的取值范围在101与⑴之间。若r=0不相干，即业务量与总本钱无直接依存关系；若r越趋近于1，说明相干程度越大；若r=＋1，表示业务量与本钱保持正比例相干；若r=⑴，表示业务量与本钱保持负比例相干。因此可针对计算结果中的相干系数，判断其因素或多因素与混合本钱费用是不是相干；若相干，分解结果行之有效的，否则无效。

      2、索引取值函数INDEX.语法格式：INDEX（单元格区域或数组常量，行序号，列序号）；功能：使用索引从单元格区域或数组当选取值。可用该函数在LINEST函数返回系数序列数组表中针对所需数据所处的行列位置定位选取。

      3、4舍5入函数ROUND.语法格式：ROUND（数字，小数位数）；功能：将数字4舍5入到指定的小数位数。由于LINEST函数的返回值为6位小数，用此函数指定保存的小数位数。

      2、利用实例

      假定某企业前5期的动力费用与取暖日数、非生产用煤气方数、发电度数3因素具有相干性。动力费用与3因素的数学关系模型应为：

      动力费用y=日取暖费用b1×取暖日数X110煤气单位本钱b2×煤气方数x210发电单位本钱b3×发电度数xa10固定本钱a源数据资料如图的A2：E6区域，目标结果数据将被寄存和显示于B7：E9区域。

      操作步骤以下：

      1、选定任一工作表的A2：E7区域输入已知各期动力费用、取暖日数、煤气方数、发电度数，并进行格式设计；选择一连续单元格区域B7：E9，其大小难备放置线性回归方程的固定及变动系数a、b1、b2、b3及相干系数r2.

      2、选用［插入］菜单上［名字］命令下的［定义内涵］子命令，将因变量动力费用所在的区域B2：B6定义内涵为Y，将3个自变量取暖日数、煤气方数及发电度数所构成的连续区域定义内涵为X.

      3、在单元格B8中输入计算a的公式=ROUND（INDEX（LINEST（YX，TRUE，TRUE），1，4），2）。其中，与LINEST函数嵌套的INDEX函数的参数1和4，分别为INDEX函数从LINEST函数返回的检验统计量的系数表中索引a的行号与列号；与INDEX嵌套的ROUND函数中的参数2意为保存2为小数。

      4、将B8单元格的公式复制和粘贴在b1、b2、b3及相干系数r2对应的单元植C8、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.27X1＋0.08X2＋0.31X3＋190245.1

      用以上方法进行多元混合本钱费用分解，函数运算结果（目标数据）和源数据区域建立了自动链接关系。当源数据变更时，目标数据将针对输入的公式函数自动重新计算得出新的本钱费用分解结果。