Excel回归分析工具在分解混合成本中的应用.doc

Excel回归分析工具在分解混合成本中的应用Excel回归分析工具在分解混合成本中的应用 马元驹 由于企业中存在着相当多的既不是变动成本也不是固定成本的混合成本，将混合成本分解为变动成本和固定成本就成为应用管理会计的前提条件和基础工作，因此显然正确地分解混合成本不仅对于计算变动成本、预测、经营决策、全面预算、控制以及业绩评价都具有重要的影响，而且直接关系到管理会计的应用效果。目前分解混合成本的方法主要有：“布点图法”、“高低点法”和“回归直线法”等。一般说来“回归直线法”利用了微分极值原理，用它分解混合成本其结果要比采用其他方法更准确；但是值得指出的是采用这种方法是以业务量（x）和成本（y）之间存在显著的线性关系为前提，也就是说当业务量（x）和成本（y）之间如果不存在显著的线性关系或线性相关程度较低或存在其他关系时，这种方法的准确性就难以保证。因此为了检验业务量（x）和成本（y）之间是否存在显著的线性关系，“回归直线法”常常需要计算相关系数r，并以此作为判断两者之间的是否存在显著相关关系的依据。然而现代统计理论告诉我们，仅仅依赖相关系数r来判断业务量（x）和成本（y）之间是否存在显著的线性关系是不可靠的。因为相关系数存在一个明显的缺点，即它接近于1的程度与观察数据组数n有密切的关系，当n较小时，相关系数的绝对值容易偏大，当n=2时，相关系数的绝对值总为1。因此仅仅以相关系数是否趋近于1来判断业务量（x）和成本（y）之间是否存在显著的线性关系是很不可靠的，从而使建立在变动成本基础之上的管理会计难以发挥作用。为了可靠地判断业务量（x）和成本（y）之间是否存在显著的线性关系，不仅需要计算相关系数r，还应当使用“决定系数”、“F检验”、“残差图”等多种统计指标进行检验并得出一致结论后，才可以比较准确地确定x与y之间的关系。我们知道在手工条件下计算相关系数r已经是一件相当复杂和费时的事了，如果再计算更多的统计指标其工作量是不言而喻的，然而利用“EXCel电子表”为我们判断业务量（x）和成本（y）之间是否存在显著的线性关系提供了极大的方便，使统计指标计算的工作大大简化，进而为管理会计应用向广度和深度发展拓展了空间。本文着重介绍如何借助Excel电子表回归分析工具判断业务量（x）和成本（y）之间是否存在显著的线性关系以及分解混合成本的方法和步骤。一、启动Excel电子表输入相关资料为了便于讨论，我们选取某企业18个月的机器小时和制造费用的历史资料，并应用“Excel电子表”中的“回归分析工具”计算用于判定该企业机器小时和制造费用之间是否具有相关关系的有关指标。在“Windows”界面中，用鼠标单台“开始”，再单击“程序”及其下拉菜单中的“ Microsoft Excel”程序，即可启动“ Excel电子表格”，出现一张标为“Book1”的空白“Excel电子表”，在该表中输入业务量（机器小时）和成本制造费用（元）的历史资料（注意输入的资料应按列的顺序排列）。如图1所示：二、调用回归分析工具计算相关统计指标在“Excel工作表”的“菜单”栏单击“工具”选项，在下拉菜单中单击“数据分析”选项，弹出“数据分析”的“分析工具”选项框，从中选中“回归”分析工具。如图2所示：（注意：如果在“工具”菜单中没有出现“数据分析”命令，必须在“Excel工作表”中安装“分析工具库”。即在“工具”菜单中，单击“加载宏”命令。然后在“当前加载宏”的对话框中选中“分析工具库”，单击“确定”按钮，“数据分析”工具就加载完成。）单击“确定”后，弹出“分析工具”中的“回归”分析对话框。如图3所示：现在只需要在“回归”分析对话框中输入相应的数据并确定相关的选项即可。首先，单击“Y值输入区域（Y）：”右边的红色箭头，折叠对话框，然后用鼠标选中制造费用Y值。如图4所示，单击折叠对话框，回到“分析工具”的“回归”分析对话框。用同样的方法单击“x值输入区域（x）：”右边的红色箭头，折叠对话框，然后用鼠标选中业务量x值。再次单击折叠对话框，回到“分析工具”的“回归”分析对话框。如图5所示：在“回归分析对话框”中除了在“Y值输入区域”因变量数据区域的引用和在“x值输入区域”输入对自变量数据区域的引用外，还有一些根据分析的需要所作的选项。主要有：标志、置信度、常数为零、输出区域、新工作表组、新工作簿、残差、标准残差、残差图、正态概率图等复选框，根据分析的需要进行选择。这里我们选中“残差图”复选框。检查“回归分析对话框”中的各个选项，然后单击“确定”按钮，系统在默认情况下，在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴回归分析工具的计算结果。如图6所示：这样回归分析的计算结果就计算出来了。三、根据计算结果分析确定x与y之间的相关关系回归分析工具可以得出许多统计指标以及根据需要生成的相关图表。这里我们只选择部分统计指标和图表作为判断x与y之间是否存在显著线性相关的相关关系的依据。主要分析以下指标：1、技术指标判断第一，决定系数r2r2愈趋近于1，样本的各个观测值就愈接近回归直线，说明反映样本的各个观测值拟合回归议程的紧密程度高。本例在回归分析的计算结果的“回归统计”栏中的决定系数r2为0.911242，接近于1，故说明x与y之间具有显著的线性相关关系。第二，F检验F检验是对线性回归方程显著性的一种检验。它是根据平方和分解式直接从回归效果检验回归方程的显著性。在给定的显著性水平下，如果FF（1，n2），则认为变量x与y之间没有明显的线性关系，如果Fr（1，n2），则认为变量x与y之间有显著的线性关系。本例在回归分析的计算结果的“方差分析”栏中的F值为：164.2648在显著性水平=0.05，查F分布表F0.05（1，16）=4.49F=164.26484.49说明变量X与y之间有显著的线性相关关系。2、残差图评价“残差图”以回归方程的自变量为横坐标，以残差i为纵坐标，将每一个自变量的残差描在该平面坐标上所形成的图形。当描绘的点围绕i=0的直线上下随机散布，说明回归直线对原观测值的拟合情况良好。否则，说明回归直线对原观测值的拟合不理想。在工作表中向右移动水平滚动条，使“残差图”显现全貌。如图7所示：从“残差图”可以直观地看出残差的绝对数值都比较小，所描绘的点都在以0为横轴的直线上下随机散布，回归直线对各个观测值的拟合情况是良好的。说明变量X与y之间有显著的线性相关关系。四、构造回归模型通过以上分析得出了一致的结论，这样我们就可以认为变量X与y之间存在着显著的线性关系。进而从回归分析的“计算结果”栏中读取截距（Intercept）3442.459和斜率（X Variabl）0.559365，并构造回归模型如下：y=3442.4590.559365x利用该回归分析模型可以预测和控制制造费用。即当x（机器小时）增加或减少一个单位时，y（制造费用）平均地增加或减少0.559365个单位。因此一旦给x一个确定的值，相应地也就确定了y的值；相反，一旦给y一个确定的值，相应地也就可以确定x的值。显而易见，Excel电子表回归分析工具的应用，为我们按照成本习性分解混合成本提供了极大的便利，不仅能够使财会人员能够非常容易地根据历史资料从混合成本中分解出固定成本和单位变动成本，建立起用于进行成本预测和成本控制的回归方程，而且能够保证所建