运用EXCEL编制及跟踪预算讲义

《运用EXCEL编制及跟踪预算讲义》由会员分享，可在线阅读，更多相关《运用EXCEL编制及跟踪预算讲义（86页珍藏版）》请在金锄头文库上搜索。

1、山东理工大学管理学院 孙芳 运用EXCEL编制及跟踪预算 凡事预则立，不预则废。 夫未战而庙算胜者，得算多也；未战而 庙算不胜者，得算少也。多算胜少算， 而况于无算乎！ 中国企业预算管理的通病 为什么预算与现实总是差异很大？ 目标定得过高，完不成也无所谓 目标定了之后，每个月不控制它，最后导致了失 控 预算执行不好，没有相应的奖惩 上对下，层层加码，马到成功 下对上，层层兑水，水到渠成 心照不宣 全面预算是对企业未来经营状况和财务结果的事 前模拟，已经成为国内多数企业提升管理水平的 重要工具。 预算要发挥应有的作用，有两点非常重要： 预算应尽可能编制的比较详细，只有细致的预算 才有可能更符合企

2、业的实际。 全面预算要能够随经营环境的变化而进行动态的 调整。 全面预算是一个内部联系紧密的预算体系，既 包括业务预算，还包括财务预算，需要企业从 销售、生产、采购到人力资源、财务等部门共 同参与，预算编制过程中公司的上下级之间部 门之间的数据交换量和数据交换频率高。 另一方面，从全面预算管理的流程来看，它是 一个“自上而下和自下而上”相结合并多次往复 的过程。越是规模较大，组织层级较多、预算 管理精细、采用滚动预算的企业，这种数据共 享、交换和流程管理的工作量就越大，企业的 预算管理的效率就越会受到影响。 如何编制预算 预算宣导 第三次全体会议 第二次全体会议 第一次全体会议 协调会议 营销

3、部门会议 开始行动 报董事会 准备会议 9月底 10月底 10月中旬 10月初 11月初 12月初 11月底 11月中旬 12月中旬 此外，定期对各预算单位预算执行情况进行跟 踪分析和总结，是企业推行全面预算管理中的 一个重要环节，它不仅为公司的决策层提供决 策依据，而且还是公司绩效考评的基础。 一个完善的预算跟踪要从不同角度对预算进行 分析，如月度及累计月度预算与实际对比分析 、预算完成进度分析、预算完成情况的差异分 析等。 所有这些，在传统手工分析情况下，工作量大 ，很难达到全面、及时、准确的要求，因此可 以充分利用EXCEL强大的计算、汇总及图表功 能帮助财务管理人员编制及跟踪预算。 预

4、算编制的方式：销售起点模式、成本起点模 式、利润起点模式等； 不同的预算项目有不同的预算编制方法：固定 预算、弹性预算、滚动预算、零基预算等 企业不同阶段需要不同的预算模式： 创业期间-资本预算为基础 成长期间-销售预算为基础 成熟期间-成本控制为基础 衰退期间-现金流量为基础 此外还要处理好预算与企业经营战略目标之间 的关系。 以销售起点模式为例，在预测的基础上利用 EXCEL编制预算，综合利用EXCEL知识与技术动 态跟踪预算，及时分析差异，提出改进措施，形 成整体循环。 课程提纲 1. 如何运用EXCEL进行预测 2. 如何运用EXCEL编制预算 3. 如何利用EXCEL跟踪预算 财务预

5、测分析（一） 财务预测分析概述 销售预测 财务预测分析概述 预测分析就是用科学的方法预计、推断事物发展的必 然性或可能性的行为，即根据过去和现在预计未来， 由已知推断未知的过程。 管理会计中的预测分析是指按照一定的原则和程序， 运用专门的方法进行经营预测的过程。 财务预测分析主要包括：销售预测、利润预测、成本 预测、资金预测等。 预测分析的方法有很多，企业应根据不同的需要选择 不同的预测方法。可分两大类： 定量预测法 定性预测法 定量预测法 是指在掌握与预测对象有关的各种要素的定量资 料的基础上，运用现代数学方法进行数据处理， 从而建立起能够反映有关变量之间关系的各类预 测模型的方法。 财务预

6、测中常用的定量预测法主要有： 移动平均法 指数平滑法 回归分析预测法 模拟法 移动平均法 它根据近期数据对预测值影响较大，远期数据对 预测值影响较小的事实，把平均数逐期移动。 移动期数的大小视具体情况而定，移动期数少， 能快速反映变化，但不能反映变化趋势；移动期 数多，能反映变化趋势，但预测值带有明显的滞 后偏差。 常用的移动平均法主要有一次移动平均法和二次 移动平均法。 一次移动平均法 根据时间序列逐期移动，依次计算包含一定项数的时 间序列平均数，形成一个新的平均数时间序列，并据 此进行预测。 预测模型: 第t+1期的预测值=最后N期的实际观测值之和/N 在实际预测中，可以多取几个N，并将得

7、到的预测值 与实际值进行比较，选用误差较小的N值。 当数列有季节周期时，只要移动平均的项数和季节波 动的周期长度一致（例如3），则移动平均值可以消 除季节周期造成的误差影响，从而揭示出数列的长期 趋势。 二次移动平均法 是对时间序列计算一次移动牢固均数后，再对一 次移动平均数序列进行一次移动平均运算。 预测模型: 第t+1期的预测值=最后N个的一次移动平均数之 和/N 二次移动平均法可以进行近期、短期的预测，不 能解决中长期的预测问题。 指数平滑法 也是一种改良的加权平均法。 YT+1=a*YT+(1-a)*Y T 在指数平滑法中，确定合适的a值非常重要 。a越大，实际值对新预测值的贡献越大。

8、 一般情况下，可取几个不同的a值进行预测 比较，选择误差最小的a值。 回归分析预测法 回归分析预测法是通过研究两组或两组以上的变量之 间的关系，建立相应的回归预测模型，根据假定的自 变量的值来预测因变量的一种预测方法。 回归分析预测法的基本程序： 收集资料：将各种可能影响因素的有关数据尽可能多 的收集起来。 判断趋势：根据收集到的数据，判断其变化趋势，从 而为建立相应的数学模型作准备。对于变量不多的问 题，可以通过绘制散点图来判断变化趋势。 建立预测模型。 相关检验。对于建立的预测数学模型，必须进行有关 的检验，主要是通过计算预测模型的相关系数、方差 以及显著性等指标，来判断预测模型的准确性、

9、是否 需要修正等。 财务预测中常用的几种回归模型： 一元线性回归模型 Y=aX+b 特点：只有两个变量，并且它们直接存在线性关系 可以通过数学变换化成一元线性模型的一元非线性模型：对数模型、 指数模型、乘幂模型、双曲线模型等 多元线性回归模型 Y=a1X1+a2X2+anXn+b 特点：有两个或两个以上自变量，且因变量与这些变量之间是线性组 合关系。 多元非线性回归模型 典型例子：柯柏道格拉斯生产函数 Y=aXbKc (L和K分别为劳动力和固定成本，a b c 为系数。) 模拟法 在企业的实际经济活动中，各种经济参数往往 并不是确定的，而是随机变化的，比如，产品 的销售量往往随市场的变化而变化

10、，在这种情 况下，就需要对这些参数的不确定性进行分析 ，而对其进行预测也就需要采用与传统的确定 性分析不同的方法来进行。一般情况下，可以 采用模拟法来解决不确定性情况下的财务预测 问题，概率法、蒙特卡罗模拟方法就是较实用 的方法。 例：不确定条件下投资利润预测 EXCEL数据分析工具提供了三种财务预测 计算方法：常用的移动平均法、指数平滑 法、回归法，利用这些工具可以计算出估 计值、标准差、残差和拟合图。同时如果 配合使用EXCEL数据分析某些宏与某些函 数，还可以完成数学曲线拟合法。 定性预测法 定性预测法是由有关方面的专业人员或专 家根据自己的经验和知识，结合预测对象 的特点进行的综合分析

11、，对事物的未来状 况和发展趋势作出推测的预测方法。 定性预测法由于带有较多的个人主观性， 因而在实践中最好作为一种补充的预测方 法。 第二课 销售预测 在企业的预测系统中，销售预测处于先导地位，对 于指导利润预测、成本预测和资金预测，进行长短 期决策，安排经营计划，组织生产都起到重要的作 用。 销售预测是一项比较复杂的工作，需要考虑的因素 很多。通常可利用企业过去的数据进行统计分析， 并结合经济环境对未来市场的影响以及企业内外部 的各种条件的限制，做出预测。 常用的方法有： 时间序列预测法 因果关系预测法 通过生产能力或订货合同进行销售量（销售额预测 ） 时间序列预测法 是指将观测记录的历史数

12、据，按时间的先 后排列成数据系列进行统计分析，找出过 去长期的销售量或销售额的增减变化趋势 ，再根据变化趋势分析结果，预测未来时 期的销售量或销售额。 常见的时间序列的预测方法有：简单平均 法、移动平均法、指数平滑法或以时间为 自变量的回归分析法等。 移动平均法进行销售预测的步骤 打开各月销售额工作表 工具|数据分析|移动平均 输入区域:输入待分析数据区域的单元格引用 间隔：输入移动平均的期数 移动期数少，能快速反映变化，但不能反映变化趋势； 移动期数多，能反映变化趋势，但预测值带有明显的滞 后偏差。 当数列有季节周期时，只要移动平均的项数和季节波动 的周期长度一致（例如3），则移动平均值可以

13、消除季节 周期造成的误差影响，从而揭示出数列的长期趋势。 输出区域的设置：运算结果显示区域的起始单元格 确定输出 结果说明： 预测销售额即移动平均值，由于移动平均 法是以移动平均值作为趋势估计值，所以 也称其为趋势值。 移动平均值的位置不是在被平均的N项数值 的中间位置，而是直接排放在12时期的最 后一期，做为下一期的预测值。 指数平滑法 是用过去时间序列值的加权平均数作为趋势值。 优点：只需一个T期的实际值和趋势值，所用的数 据量和计算量都很少。 指数平滑法是在企业整体资产评估中常用的预测 未来收益的方法之一。它适用于预测呈长期趋势 变动和季节变动的评估对象。 指数平滑法可分为一次指数平滑法

14、和多次指数平 滑法。 用一次指数平滑法进行预测的具体步骤 打开各月销售额工作表 工具|数据分析|指数平滑 输入区域：必须包含4个以上单元格的一行或一列 阻尼系数：用来将总体中数据的不稳定性最小化 的修正因子，默认为0.3 输出区域 结果输出 数学曲线拟合法 EXCEL中没有提供数学曲线拟合的直接计算工具。 以直线趋势的拟合为例，介绍拟合步骤及思路。 打开各月销售额工作表 插入图表（如折线图）分类X轴：月份 分类Y轴：实际销售额 选中生成的实际销售额曲线，添加趋势线 类型及选项选项卡设置（类型：线性，选项：前推、后推 ，分别表示前后预测的月份） 结果输出 数学曲线拟合存在不足 拟合时应选择合适的

15、趋势线类型 回归法 回归是预测最常用的方法之一。 LINEST预测函数的应用 EXCEL的数据分析功能提供的是最小二乘 法 最小二乘回归法步骤 打开各月销售额工作表 工具|数据分析|回归 输入区域、标志、输出区域 结果输出及分析 与趋势线（线性）得出的结论近似，其原因就在于趋势线拟合本身也 是一种回归分析方法。 因果关系预测法 因果关系预测法通常可以通过回归分析的方法 检验出哪些因素与销售水平之间具有因果关系 ，在此基础上可建立回归方程，进行销售预测 。即利用有关因素与产品销售量或销售额之间 的固有因果关系，通过建立一定的数学模型来 预测企业未来的产品销售水平的一种方法。 例：因果关系预测法 LINEST( ) 自上而下，自左而右依次为X1,X2 结果输出区最右边是b,往左依次是a1,a2 通过生产能力或订货合同进行销售预测 企业生产的产品如果在市场上占有稳定 的份额或供不应求，则可按本企业的生 产能力预测产品的销售量 计划期销售量=计划期初库存量+计划期 预计生产量-计划期末预计库存量 实际预测中， 销售部要自下而上报明年的销售计划（每个业 务代表、业务经理上报后汇总） 市场部会根据明年市场的预测、销售市场容量 扩大 情况以及企业将采取的推