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

向前

很多公司做商业计划和财务预算都是“三拍”:

年初拍脑袋:不知道科学的预测方法，没有使用预测工具的意识。未来一年的业务指标由领导决定。

年中拍拍手:半年度预算执行情况审核上报时，即使业务指标没有很好完成，你还是要拍拍手“保证年度预算完成”。

年底拍屁股:年度预算严重脱离实际，不可能完成。如果年底不能完成既定预算，那就只能一巴掌拍屁股走人。

那么预测工具如何进行科学预测呢？《管理会计应用指南第802号——管理会计信息系统》第21条告诉我们相关方法:

企业应借助适用的预测方法(如趋势预测、平滑预测、回归预测等)建立预测模型。)，协助企业制定预算目标，根据预算管理系统自动分解预算目标，协助预算审批流程，自动汇总预算。最终输出的应该是各责任中心的预算计划等。

链接(点击下载):

管理会计应用指南及案例汇编免费下载

介绍了如何利用Excel的相关功能，科学预测业务指标。

首先，根据时间序列预测趋势

企业的业务指标都是按时间顺序排列的。这个系列反映了这个业务指标的发展方向和趋势。只要经营情况没有发生明显变化，这个指标还是会保持原来的发展趋势和速度，这样我们就可以用历史数据来估计这个指标在未来某个时间段的价值。

案例1:

一帆咨询有限公司2011年至2019年的经营收入如下:

假设其他条件不变，公司维持目前的增长率。请根据这些历史数据估算2020年至2022年的收入，作为确定三年经营计划的参考数据。

在2月3日Excel懒人科技微信微信官方账号的文章中，已经介绍了使用折线图的趋势线来确定:

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

根据文中介绍的方法，可以得到趋势线公式。

其趋势线的公式为:

y=17.333x+372.78

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

插入图表，加上趋势线，得到趋势线公式，比较麻烦。我们可以直接使用TREND函数来计算预测值，公式如下:

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

当然也可以用折线图添加趋势线，显示趋势公式，得到趋势线的公式。但是需要注意的是，折线图默认将横坐标的值视为文本标签，相当于1、2、3。所以这里的图表需要设置横坐标为日期坐标轴。否则，横轴分类标签默认视为文本，计算出的趋势线公式有误。

同样，回归系数也可以通过TREND函数和LINEST来计算。

上面提到的两个案例都是线性回归模型，但是实际工作中的情况比较复杂。例如，一个指标与多个指标相关联，另一个指标的值应该根据多个指标的已知值来估计。

案例3:

比如一个产品的销量主要受两个因素的影响:居民的购买力和广告成本。了解了近五年居民的购买力、广告成本和销量，估计2020年居民的广告成本和购买力就知道了，现在估计2020年的销量。

您可以使用TREND函数来编译以下公式:

=趋势(E3:E7，C3:D7，C8:D8)

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

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

案例4:

房地产的价值受多种因素影响，假设某个地方的房地产主要受附近的商业设施、学校、使用年限等因素影响，收集该城市类似房地产的一些数据，如下图A2: F12单元面积，B15:E30是该房地产的已知数据，一个房地产的价值应该根据这些数据来计算:

在单元格F15中输入公式:

=趋势(F2:F12，B2:E12，B15:E15)

扩展阅读:

本文的知识点

懒人2不是懒人1的修订版，但内容完全不同。Lazy 2主要介绍如何运用函数式和函数技巧进行专业的表格设计。并将日常工作中常用的公式设计成模型公式。想用的时候直接涂就行了。