【管理会计】如何用Excel建立预测模型，进行财务预测

引言

很多企业做经营计划、财务预算都是“三拍”：

年初拍脑袋：不懂科学的预测方法，也没有使用预测工具的意识，对来年的经营指标全靠领导拍脑袋决定。

年中拍胸脯：半年度对预算执行情况进行检视汇报时，哪怕经营指标完成得很不好，但还是拍胸脯“保证完成年度预算”。

年底拍屁股：年度预算严重脱离实际，根本不可能完成，到了年底完成不了既定预算，只好拍屁股走人。

那怎样才能预测工具进行科学地预测呢？《管理会计应用指引第802号--管理会计信息系统》第二十一条告诉了我们相关方法：

企业应借助适用的预测方法（如：趋势预测、平滑预测、回归预测等）建立预测模型，辅助企业制定预算目标，依据预算管理体系，自动分解预算目标，辅助预算的审批流程，自动汇总预算。最终输出结果应为各个责任中心的预算方案等。

本文介绍如何利用Excel相关功能来科学地预测经营指标。

一、按时间序列进行趋势预测

企业的经营指标都是按时间的先后顺序排列组成的序列。该序列体现了该经营指标的发展方向和发展趋势，只要经营情况没有发生重大变化，该指标仍将保持原来的的发展趋势和速度，这样我们就可以利用历史数据来估算该指标的未来某时间段的值。

案例1：

逸凡咨询有限公司2011年到2019年的营业收入如下表：

假定其他条件不变，公司保持现有的增长速度，请根据这些历史数据，估计2020-2022年的收入，以做为确定三年经营计划的参考数据。

在Excel偷懒的技术微信公众号2月3日的文章中已经介绍过使用折线图的趋势线来确定：

好消息，新型肺炎疫情确诊人数这一天开始将逐渐减少！！

按照文章中介绍的方法，可以得到趋势线公式。

其趋势线的公式为：

y=17.333x+372.78

利用此公式可以计算未来三年的营业收入。

注意：

1、公式中的X是时间序列1、2、3……，而不能使用年份数字2011、2012、2013。

2、之所以使用折线图来取得趋势线公式，是因为折线图默认将年份的2011、2012、2013分别视同为1、2、3。更规范的做法是使用散点图，并添加趋势线公式。

插入图表并添加趋势线，以取得趋势线公式，这种方法相对比较麻烦一点。我们可以直接使用TREND函数来计算预测值，公式：

=TREND(D3:D11,B3:B11,[@时间序列])

上图中D12单元格的公式相当于

=TREND(D3:D11,B3:B11,B12)

TREND函数语法：

=TREND（已知的因变量Y值列表，已知的自变量X值列表，新的自变量X值，是否强制截距等于0）

肯定有朋友会问趋势线公式中的17.333和372.78是怎么计算得来的呢？

y=17.333x+372.78

实际上，该趋势线就是回归直线，用回归方程来计算的

如果用上面的公式计算过程很复杂很麻烦，但在Excel中一切都变得很简单，用一个公式就搞定了，比如计算回归系数b：

=INDEX(LINEST(D3:D11,B3:B11,,0),1)

LINEST函数的详细用法，我们下篇文章再介绍。敬请关注。

二、根据相关指标来预测（因果分析法）

在日常财务管理中，除了根据经营指标的历史数据来预测未来的值，有时候还需要研究几个指标的关联关系，这样就可以根据已知指标的值来预测另一个指标的估算值。比如，车间设备的修理费用与机器工作时间存在定的关联关系，使用时间越多，修理费越多。那么，我们在编制预算时就可以根据机器的开机时间来预测机器的修理费。

案例2：

逸凡精工设备公司2019年车间的机器工时及修理费如下表如示，根据2020年的经营计划，2020年4月、5月、6月机器生产工时预计为450、420、512小时，请使用线性回归法估算各月的修理费。

与时间序列一样，我们使用散点图添加趋势线，得到趋势线公式：

当然，也可以使用折线图添加趋势线，显示趋势公式，来得到趋势线的公式，只是需要注意的是，前面已经介绍过，折线图默认将横坐标的数值视同为文本标签，等同于1、2、3，所以，这里的图表需要将横坐标设置为日期坐标轴。否则，图表默认将横轴的分类标签视为文本，计算出的趋势线公式是错的。

一样的，也可以TREND函数以及LINEST来计算回归系数。

前面介绍的二个案例都是使用的一元线性回归模式，在实际工作中还有更复杂的情况，比如一个指标跟多个指标相关联，要根据多个指标的已知值来估算另一指标的值。

案例3：

比如一个产品的销量主要受两个因素影响：居民的购买力、广告费。已知近五年的居民的购买力、广告费、销量情况，2020年预计要投入广告费和居民购买力为已知数，现要估算2020年的销量。

可以使用TREND函数编制下面的公式：

=TREND(E3:E7,C3:D7,C8:D8)

从上面的公式可以看出，TREND函数第二个参数“已知的自变量X值列表”可以是多列数据。

TREND函数还可以用于更复杂的预测和估算。

案例4：

房产的价值受多个因素影响，假设某地的房产主要受附近的商业配套、学校、使用年限等因素影响，现收集了该市类似楼盘的一些数据，如下图表A2：F12单元格区域，B15：E30为本楼盘已知的数据，现要根据这些数据来测算某楼盘的价值：

在F15单元格输入公式：

=TREND(F2:F12,B2:E12,B15:E15)

《偷懒2》不是《偷懒1》的改版，两者内容是完全不同的，《偷懒2》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式，要用的时候，直接比照套用就是了。