财务预测、回归分析与Excel建模

钟爱军，邹 丹

（1.武汉商学院 工商管理学院， 武汉 430056；2.襄阳职业技术学院， 湖北 襄阳 441050）

经过多年的改革和发展，我国的会计准则体系基本完备，基本解决了会计信息披露的规范问题，但服务内部管理、提供决策支持的财务管理工作仍然滞后。随着市场竞争的加剧和管理要求的不断提高，企业逐步意识到加强财务管理工作的重要性。在国际上一直有着“通用财务软件”美誉的Excel软件，有着强大的数据动态链接能力、数据分析工具和丰富的函数，使用灵活，操作方便，能够满足个性化分析需要和复杂多变的动态分析需要。财务管理所涉及的计算与分析，几乎都可以借助于Excel来解决，Excel软件在不同规模和各种类型的经济组织里都能发挥作用，掌握Excel软件的使用技术是现代财务工作者必备要求。

一、财务管理与回归分析

（一）财务管理工作中的回归分析法

回归分析法是指在观测历史数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式，并据此进行预测的方法。回归分析法按照数学上最小平方法的原理，确定能够正确反映自变量和因变量之间具有最小误差平方和的回归方程，作为预测模型进行预测的方法。

回归分析包括一元回归分析、多元回归分析以及线性回归分析、非线性回归分析这几种不同的情况，线性回归分析法是最基本的分析方法。遇到非线性回归问题时，可以通过数学变换将其转化为线性方程后再进行回归分析。

在财务管理工作中，常常要利用事物发展的因果和相关关系来预测发展趋势，因果关系分析中又经常使用回归分析法，诸如资金需要量预测、销售预测、混合成本分解、资本资产定价模型相关分析等。回归分析在财务管理中有着丰富的应用，本文只介绍其中简便易用的一元线性回归分析法，有了一定的基础，可以进行更为复杂的应用。[1]

（二）一元线性回归分析的基本原理

一元线性回归分析法的关键在于确定回归直线方程中的参数a和b，主要步骤如下。

（1）分析两组数据的关系，因变量y与自变量x之间是否存在线性相关。

（2）确定因变量y与自变量x之间的线性相关程度——相关系数。

r数值越大，两个变量之间的相关程度越密切；反之，相关程度越小。根据所取观测值确定的r值，如果两组数据之间线性相关，则可进行下一步，否则不能采用任何线性分解方法。

（3）若存在线性相关，则建立数学模型y=a+bx，利用各观测值，求解回归直线方程y=a+bx中a和b的值。a和b的计算公式如下：

（4）根据目标自变量x值，求因变量y值。将a、b、x的值全部代入y=a+bx中，求出y的值。

二、回归分析法的人工计算和Excel建模

（一）实例资料

以Excel在资金需要量预测中的应用为例，资金需要量与业务量存在如下的一元线性关系，即y=a+bx，其中因变量y代表资金需要量；a为直线的截距，代表固定资金；b为直线的斜率，代表单位变动资金；自变量x代表业务量。

某企业A产品的产销量（产销一致）和资金占用的历史资料如表1所示，如果2018年计划产销量为80 000，根据产销量与资金占用量之间的内在关系，预计2018年资金需要量。

表1 某公司A产品的产销量和资金占用的历史数据

（二）人工计算过程

（1）整理中间计算过程数据，见表2。（2）计算相关系数的值

（3）计算回归直线方程中斜率b和截距a的值

表2 中间计算结果数据

（4）确定回归直线方程

（5）预测资金需要量。如果2018产销量为80000，那么2018年的资金需要量=2629+0.0124×80000=3621。

（三）Excel建模应用

在Excel工作表中，利用INTERCEPT函数和SLOPE函数分别测算回归直线的截距和斜率，然后再利用回归直线方程预测未来时期的资金需要量。还可以利用TREND函数，直接预测未来时期的资金需要量，更为快捷。[2]具体步骤与方法如下。

（1）将数据录入到Excel工作表中，建立数据清单。

（2）确定历史数据中的因变量和自变量。

（3）定义相关系数公式，判定数据之间的相关性。

（4）定义计算斜率、截距和资金预测结果的公式，完成模型建立。

（5）直接计算资金需要量结果的公式

B13=TREND（C3:C7,B3:B7，B11）

如果使用REEND函数，可以直接出结果，更为便捷高效，就不需要定义B9、B10和B12这三个单元格里的公式。

在Excel工作表中建立的具有自动计算功能的分析模型见图1所示。

该模型中，相关公式中的函数功能及其语法格式说明如下：

CORREL函数的功能：返回两组数值的相关系数

图1 A产品资金需要量预测分析模型

ORREL 函 数 的 语 法 ：CORREL（array1，array2），即：CORREL（第一组数值单元格区域，第二组数值单元格区域）

INTERCEPT函数的功能：求线性回归拟合线方程的截距

INTERCEPT函数的语法：INTERCEPT（known_y’s，known_x’s)

即：INTERCEPT（因变的观察值或数据的集合,自变的观察值或数据的集合）

SLOPE函数的功能：返回经过给定数据点的线性回归线的斜率，即回归线的变化率。

SLOPE 函 数 的 语 法 ：SLOPE（known_y’s,known_x’s）

即：SLOPE（数字型因变量数据点数组或单元格区域，自变量数据点集合）

TREND函数的功能：返回线性趋势值，即找到适合已知数组known_y’s和known_x’s的直线（用最小二乘法），并返回指定数组new_x’s在直线上对应的y值。

TREND 函 数 的 语 法 ：TREND(known_y’s,known_x’s，new_x’s，const)

即：TREND（已知因变量y值集合,已知自变量x值集合，新x值，逻辑值）

逻辑值为TRUE或省略，截距按正常计算；为FALSE，截距被设为0。

三、启示

依靠传统手工模式开展财务管理工作，劳动强度大，工作效率低，同时也制约着财务管理工作的质量和水平的提高。本文介绍的利用Excel软件进行回归分析的财务建模方法，能够帮助有志于追求提高工作质量和水平的财务管理专业人士拓展工作思路。信息技术的不断发展，为财务管理功能的发挥提供了更为广阔的空间，要想更好地发挥财务管理的作用，促进财务管理工作的转型升级，必须将信息技术与财务管理工作进行深度融合。

参考文献：

[1]（瑞士）凯勒.统计学在经济和管理中的应用[M].北京：中国人民大学出版社，2006.

[2]Excel Home.Excel应用大全[M].北京：人民邮电出版社，2008.