在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电子表”，在该表中输入业务量（机器小时）和成本制造费用（元）的历史资料（注意输入的资料应按列的顺序排列）。
    二、调用回归分析工具计算相关统计指标
    在“Excel工作表”的“菜单”栏单击“工具”选项，在下拉菜单中单击“数据分析”选项，弹出“数据分析”的“分析工具”选项框，从中选中“回归”分析工具。
    （注意：如果在“工具”菜单中没有出现“数据分析”命令，必须在“Excel工作表”中安装“分析工具库”。即在“工具”菜单中，单击“加载宏”命令。然后在“当前加载宏”的对话框中选中“分析工具库”，单击“确定”按钮，“数据分析”工具就加载完成。）
    单击“确定”后，弹出“分析工具”中的“回归”分析对话框。如图3所示：
    现在只需要在“回归”分析对话框中输入相应的数据并确定相关的选项即可。首先，单击“Y值输入区域（Y）：”右边的红色箭头，折叠对话框，然后用鼠标选中制造费用Y值。
    单击折叠对话框，回到“分析工具”的“回归”分析对话框。用同样的方法单击“x值输入区域（x）：”右边的红色箭头，折叠对话框，然后用鼠标选中业务量x值。再次单击折叠对话框，回到“分析工具”的“回归”分析对话框。
    在“回归分析对话框”中除了在“Y值输入区域”因变量数据区域的引用和在“x值输入区域”输入对自变量数据区域的引用外，还有一些根据分析的需要所作的选项。主要有：标志、置信度、常数为零、输出区域、新工作表组、新工作簿、残差、标准残差、残差图、正态概率图等复选框，根据分析的需要进行选择。这里我们选中“残差图”复选框。
    检查“回归分析对话框”中的各个选项，然后单击“确定”按钮，系统在默认情况下，在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴回归分析工具的计算结果。这样回归分析的计算结果就计算出来了。