预测制造费用中回归分析的Excel应用

预测制造费用中回归分析的Ｅｘｃｅｌ应用作者：董云芝黄国良

来源：《财会通讯》2008年第03期

一、数据的获取

首先，判断企业的生产规模与流程在近期有没有发生重大变化。如有重大变化，以历史数据为基础的回归分析就不能比较准确地预测未来的成本。其次，分析可能影响制造费用变动的因素，如产量、直接人工小时、机器小时等。获取这些变量的历史数据，期限最好限定在最近三年内，如果产品的成本一直比较稳定，可以只选用近一年的数据。然后，将获取的数据录入Excel中，并检查这些数据中有没有异常点，如设备大修理、一次摊销低值易耗品等月份，并将其剔除。因为计算回归方程使用的是最小二乘法，异常点的数据会影响回归方程的准确性。

案例：海华纺织厂在近两年内生产规模与流程未发生重大变化，经过分析，该厂棉纱的制造费用受到产品产量、直接人工小时和机器小时的影响。近15个月的具体数据如表1。经检查与分析，在第4个月里一次性摊销生产领用的包装物6000元，为了使回归方程能更准确地模拟实际情况，将该月的数据剔除。

二、回归分析的应用

回归分析包括一元回归和多元回归。一元回归指回归模型中只有一个自变量，多元回归指回归模型中有多个自变量。如果通过上一步的分析，得出影响制造费用的因素是多个的，那么预测制造费用的回归方程也是多个。

如何评价回归方程的优劣，需要注意三个统计数字——判定系数(R2)、t统计量、F统计量，Excel回归结果中也提供这三个统计数字。判定系数(R2)的大小说明回归直线对数据的拟合程度，其值越接近1.0，则回归直线的拟合度越好，反之，越接近0，则回归直线的拟合度越差。与R2相对应的一个统计量是调整后的R2，它消除了R2中对模型没有解释力的新增变量，这在分析增加新的自变量后回归方程的优劣时有着非常重要的作用。如果在回归模型中增加自变量后，调整后的R2没有变大，则可以删除这个自变量。t统计量是检验自变量的系数是否显著为零，F统计量是检验所有自变量系数是否显著为零，前者对应的是具体的自变量，后者对应的是整个回归方程。在给定显著水平下，需要通过查阅t分布表和F分布表，才能判断这两个检验是否成立。但在使用Excel进行回归分析时，可以利用Excel输出的结果直接判断。假设给定的显著水平是0.05，那么只要检查输出结果中P值(P-value)和F显著水平(Significance F)下的数字是否小于等于0.05。若P-value下的数值大于0.05表明对应的自变量系数是显著为。的，该自变量可以从模型中剔除。若SignificanceF下的数值大于0.05，表明白变量与因变量之间的线性关系不显著。

通过这三个值可以选择拟合度最优、回归系数显著的回归方程，结合前面的案例讲解如何使用Excel来进行回归分析。打开Excel“工具”菜单中的“数据分析”选项，双击“回归”，即打开“回归”对话框。分别输入因变量Y和自变量X的数据区域。笔者建议输入的数据区域包括标志栏，使得输出的结果便于阅读。需要注意的是，在进行多元回归分析时，变量的数据在文档中必须按列存放，否则Excel无法执行回归分析。在回归分析制造费用时，不用选择“常数为零”的复选框，因为制造费用中部分成本是固定的。系统默认的置信度为95％，即显著水平为0.05，如果要更改置信度，则选择置信度复选框，在后面的文本框中输入更改后的置信度。输出回归结果有三种方式：在当前工作表、新工作表、新工作薄，可以选择其中一种。此外，可以根据需要选择输出结果中包含的其它信息，如残差、标准残差、残差图等(参图1)。在填好数据后，单击确定即可得出回归分析的结果(参图2)。

海华纺织厂制造费用的影响因素有三个，在回归分析时，先进行一元回归，然后再增加自变量个数。回归输出结果经整理后，列于表2。经分析自变量为直接人工小时的回归方程的调整后R2最大，并且F显著水平和p值都小于0.05。所以，选择该回归方程来-预测制造费用。

计算如下：y=105.689+5.824x(1)

三、制造费用的预测

预测分为点预测和区间预测。点预测就是将自变量的数据带人回归方程中，计算得到的y 值就是因变量的点预测值。区间预测是在求出因变量的点预测值y后，在一定的置信度下，求因变量落在以y为中心的一个区间。在预测制造费用时，不可能准确地预测出最终发生的费用，但通过区间预测能够知道在一定的概率下，制造费用的大致范围。在一元回归分析中，计算因变量预测区间的公式如下：点预测值±t临界值x标准误差

其中，t临界值是按给定的显著水平、自由度为样本量减2查t分布表得到，也可通过Excel中的TINV函数求解。标准误差可以从Excel回归结果中获取。

假定海华纺织厂预测下一个月的直接人工小时为36(千小时)，带人方程(1)得出制造费用的点预测值为103.975(万元)。回归结果给出的标准误差为4.902。打开Excel中插入函数对话框，在统计类别下找到TINV函数，双击即可打开该函数的对话框，输入显示水平0.05，自由度12(14-2)，回车确认，即可得到t临界值为2.179。算出给定x值与x平均值之差的平方为6.985，各个x值与x平均值之差的平方和为97.216。根据这些数据，在编辑栏中输入公式

=2.179*4.902*SQR/(1+1/14+6.985/97.216)，回车得出调节项的数值为11.421。从而计算得出制造费用的区间预测值为92.554到115.396万元，即当直接人工小时为36(千小时)时，制造费用有95％的概率落在92.554到115.396万元之间。