PCMA精选：如何用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)
- 发表于:
- 原文链接：https://kuaibao.qq.com/s/20201019A02ZA500?refer=cp_1026
- 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号（企鹅号）传播渠道之一，根据《腾讯内容开放平台服务协议》转载发布内容。
- 如有侵权，请联系 cloudcommunity@tencent.com 删除。
相关快讯
扫码
添加站长 进交流群
领取专属 10元无门槛券
私享最新 技术干货
腾讯云开发者
