【管理会计】如何用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》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式,要用的时候,直接比照套用就是了。

(0)

相关推荐