EXCEL的中高级应用

1、EXCEL在财务分析中的几个中高级应用问题22一、前景分析-预测财务报表的编制l如何利用Excel等工具编制预测财务报表l希金斯财务管理分析第三章中的例子。l注意：例中的销售净额增长25%这是一个估计值，也可以由下列方法计算而出。33预测分析方法常用的预测模型分类：l线性预测方法移动平均方法（包括简单移动平均和加权移动平均等）；指数平滑方法（包括一次平滑、二次平滑、多次平滑方法）；回归预测方法（包括一元回归、多元回归预测方法）44预测分析方法（续）l非线性预测方法幂函数预测方法；指数函数预测方法；双曲函数预测方法；多项式函数预测方法等l非线性预测模型一般从数学上不能直接求解，需要先转换成线性模

2、型后再求解。55移动平均方法l移动平均（moving average）模型是时间序列（time serials）类预测方法中的一种，主要有简单移动平均法与趋势移动平均法。l数学公式：略l工具的操作过程：具体演示l注意：选取不同的间隔值n会得出不同的估计值和预测结果。66移动平均方法（续）l一般来说：当n=1时，估计值与原来数据值相同；当n=2时，估计值是一串平均值，无法形成递推效应；当n=t时，估计值只有一个平均值。因此，这三种情况都应该避免的。l在一般情况下，n应该取值：2nt-1l移动平均法存在两个不足之外。一是存贮数据量较大，二是对最近的N期数据等权看待，而对t-N期以前的数据则完全不考

3、虑。77指数平滑方法l指数平滑（exponential smoothing）模型也是时间序列类预测方法中的一种常用的预测分析方法。l数学公式：略l工具的操作过程：具体演示l注意：阻尼系数（）的选择原则88指数平滑方法（续）l通常，如果希望预测结果很敏感地反映最近的一些观测值的变化，则应取较大的值，如： =0.8，0.75等l若想要消除周期性波动，侧重于反映长期数据的发展趋势，则应取较小的 值，如 =0.1，0.05，0.01等。99多元线性回归方法l回归分析用于分析各种生产或经营要素之间相互影响和发展变化的规律，是一种因果关系分析法。lExcel工具的做法：具体演示1010二、 图解分析法l（

4、一）对比分析图解法演示l（二）结构分析图解法演示l（三）趋势分析图解法演示1111l（四）其他图解分析法l1、量本利图解分析法l2、雷达图法1212雷达图法l雷达图的编制方法是：先画三个同心圆，把图的度分为个区域，分别代表企业的收益性、生产性、流动性、安全性和成长性。l同心圆中最小的环，代表同行业平均水平的/值或最差情况； l中间的环代表同行业平均水平或特定比较对象的水平，称为标准区（线）；l最外边的环表示同行业增均水平的. 倍或最佳状态。个区域内，以圆心为起点，以放射线形式画出相应的经营比率线，然后，在相应的比率线刻度上标出本企业一个决算期的经营水平。1313l如果把本企业的各种比率值用线联

5、结起来，就形成一个形状不规则的闭环。 它能够清楚地反映出本企业的“经济姿态”，并便于与标准线（区）进行对照比较。l 雷达图告诉我们如果本企业的比率值位于标准线（区）以内，就表明这是本企业的弱点所在，应该认真分析产生弱点的原因，提出改进的方向；如果接近或低于最小的环，那就处于十分危险的境地，急需扭转局面；假若本企业的比率值超越了中间环即标准线（区），甚至接近最大的环，大都说明这是本企业的优势所在。1414l雷达图能够直观地显示企业经营的特长和薄弱环节，为改善企业经营指出方向。因此，如果说一个企业是一艘大轮船，那么，雷达图就象装在船上的雷达，为轮船导航，故称雷达图。 l在企业经营分析中，雷达图更重

6、要的是考察经营姿态的推移情况，为此，要把企业过去几期（至少三期）的经营比率全部用雷达图表示出来，分析其发展趋势。1515如何画？1616背景资料：八种企业的经营姿态l稳定理想型 对一个企业来说，经营姿态处于稳定理想型，当然是很完美的。如果企业处于这个状态，可以根据社会需要，进行设备投资，扩大企业规模，并采取多种经营积极发展企业的政策，而且还可以积极进行研究开发，广告宣传等先行投资。当然，处于这种状态的企业是少数，而且在发展过程中，经营姿态还会发生变化。1717保守型l这种经营姿态的特征，是流动性、收益性、安全性都比较好，但生产性、成长性较差。l这种类型的企业，一般是经营多年的老企业。这类企业应

7、当注意市场调查研究，开拓新的生产门路，进行设备投资，探讨销售战略等。保守型企业如果能够发挥优势，可以争取向稳定型发展。如果有向消极安全性发展的趋势，那就要注意了。1818成长型l 某些新企业或处于恢复期的企业，成长性、收益性和流动性都处于良好状态，但是企业财政尚未能适应企业急速发展的需要，这就是成长型的经营姿态。这类企业，应该就意调度资金，争取筹措更多的资金。1919特殊型l某些具有特种技术的企业（特别是专业化企业），常常呈现这种特殊的经营姿态，企业有着较强的收益性和流动性，但是成长性、安全性很差，这时，企业应该着重致力增加销售额，积累资金，争取向成长型发展。2020积极扩大型l当企业开始扩大

8、经营范围或开发新产品时，常出现积极扩大型经营姿态。l这种类型的特点是，企业的成长性迅速增长，但收益性并不理想。这时，企业经营的关键是制定精确的利润计划，努力生产经济效益较高的产品，降低费用，尽量使企业增加盈利，不能使之走向活动型。2121消极安全型l一个企业如果财力雄厚但消极经营，就容易陷入这种经营姿态。l遇到这种情况 ,就应该充分利用财政方面的富余力量，努力开发新产品，扩大经营范围和内容，提高企业的成长性。 2222活动型l当企业处于恢复期，销售额急剧增长进，往往呈现活动型经营姿态。这时，企业必须高法充实资金，制定长期的利润计划，从根本上提高企业的素质，提高流动性和收益性。2323均衡缩小型

9、l这类企业的所有经营比率都低于标准张（区），企业处于可危的境地。这时，企业面临着全面整顿和改善的任务。对于这一类型的企业，要从经营环境、产品前途、经营方针及技术设备等方面进行全面的评价，探讨改善的途径。2424三、财务计划l编制预测财务报表仅仅是公司生产经营计划中的财务方面的内容。现在，公司的管理层准备编制财务计划。这包括复审前面做的预测，考虑是否需要修改生产经营计划。第一步是判断模拟财务报表是否适用，例如银行不愿意贷款给公司，或公司不想借款或是公司想降低对外部资金的依赖，管理层必须修改原有计划使之符合财务实际。在这一过程中，以下工具显得特别重要且实用。2525（一）敏感性分析l敏感分析也称作

10、“What-If分析”，倘若例子中的R&E公司的销售增长是15%而非25%会怎样？倘若销售成本是销售额的84%而不是86%会怎样？2626l希金斯认为该分析方法至少有两种用途。l首先，它提供有关可能结果范围的信息。l其次，敏感性分析鼓励管理层保留不同意见。l它能使管理者判断出哪些假设对预测的影响最大，哪些次之，从而促使管理者把它们收集数据和预测的努力都集中到那些最关键的假设上。随后，在财务计划的实施过程中，同样的信息使管理层将注意力集中于那些有利于计划成功的关键因素上。2727l再比如像长期投资项目，其偿还额与利率、付款期数、每期付款额度等参数密切相关。l又如固定资产的折旧，与固定资产

11、原值、估计残值、固定资产的生命周期、折旧计算的期次以及余额递减速率等密切相关。l而作为决策者往往需要定量地了解，当这些参数变动时对有关指标的影响。总之，敏感性分析在财务、会计、管理、统计等应用领域是不可缺少的工具。28281、利用模拟运算表（1）单变量模拟运算表主要用来分析当其他因素不变时，一个参数的变化对目标值的影响。l例：假设某公司要贷款1000万元，年限为10年，目前的年利率为5，分月偿还。则利用PMT函数可以计算出每月的偿还额。其具体操作步骤如下： 在工作表中输入有关参数，如下图所示。 在B5单元格输入计算月偿还额的公式：“=PMT(B3/12,B4*12,B2)” 29293030l

12、具体演示，结果如下3131（2）双变量模拟运算表l当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表。例如上例，如果不仅要考虑利率利率的变化的变化，还可以选择贷款年限贷款年限。 3232l在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。l选定整个模拟运算表区域。如下图所示。3333l具体演示，结果如下：34342、方案分析l对于一些更复杂的问题，常常需要考查更多的因素。l例如为了达到公司的预算目标，可以从多种途径入手。如通过增加广告促销、提高价格增收、降低包装费、材料费，减少非生产开支等等。3535l利用Excel提供的方案管理器，可以模拟为达到目标而选择

13、的不同方式。对于每个变量改变的结果都被称之为一个方案，根据多个方案的对比分析，可以考查不同方案的优劣，从中选择最合适公司目标的方案。3636步骤：l创建方案l浏览、编辑方案l方案总结具体演示3737（二）目标反推搜索方法l“What-If”分析方法主要采用模拟计算的方法解决不同因素或不同方案对目标的影响。这对于计划人员、决策人员都是常用的工具。但是对于生产的组织和实施人员来说，经常遇到的是相反的问题。3838l例如，根据上级部门制定的某个目标，分析要实现该目标，需要实现的具体指标，再逐一落实。当然也可以根据每个具体指标，进一步分析要达到的更详细的指标。在进行这样的分析时，往往由于计算方法较为复

14、杂或是许多因素交织在一起而很难进行。这可以利用Excel的目标搜索技术实现。3939典型的应用问题l案例1：通过多元回归分析，我们得到预测销售量的方程如下：l其中：X1是指产品单价；X2是指产品外包装费用；X3是指广告投入；X4是指时间。l代入数据：X1=0.42;X2=105;X3=1000;X4=99l计算得出预测值：Y=528.519万元l现将问题反过来，如果企业决策者提出2011年要将销售额提高到600万元，请问广告部经理：在其他经营要素不变的情况下，2011年广告部的预算要达到多少才能实现这一销售目标？Y=-1209.03-169.495X1-1.213X2+0.089X3+18.6

15、6X4资料来源：经营分析方法与IT工具，姜旭平著清华大学出版社2002、3（改编）4040l案例2：假设某公司销售总额与诸多影响销售因素的函数关系如下式：总销售额C=市场信誉CF*环境因素H*（广告投入G/2）+生产能力S）1/2现在问：如果希望明年总销售额达到某个希望值，在其他各经营要素都不变的情况下，相应的广告投入应该增加至多少才有可能实现这个目标？因此，目标搜索方法是部门管理者向老板或上目标搜索方法是部门管理者向老板或上级索要资源的一种有力武器。级索要资源的一种有力武器。4141例 题l如上面案例24242l如果C=80，请问G应为多少？4343结果如下单变量求解还可以直接求各方程,特别

16、是非线性方程的根,例如求2x3-5x2+7x=104444（三）规划分析l规划分析它可以帮助管理者找到在确定经营管理目标下的最佳资源配置。例如：如何在库存容量、使用需求、储备资金、运输条件等限制情况下，实现库存费用最低；如何在满足市场需求、生产能力制约、原材料制约等限制下达到产量或利润最大等等。l虽然规划问题种类繁多，但是其所要解决的问题可分成两类：一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使它们获得最大的收益。l举一个简单的例子来源为姜旭平书4545假设：某企业有三个生产车间，生产X1，X2，X3三种商品，各车间的生

17、产能力如下：l第一车间：每班可生产X1产品2件，X2产品1件，X3产品1件；l第二车间：每班可生产X1产品1件，X2产品2件，X3产品1件；l第三车间：每班可生产X1产品1件，X2产品1件，X3产品2件；各车间月生产能力为：l第一车间总数每月不超过240件；l第二车间总数每月不超过360件；l第三车间总数每月不超过300件。各产品的盈利状况为：X1每件16元；X2每件10元，X3每件9元。4646l问题：在这种情况下，怎样安排月生产计划，才能保证企业能充分利用现有的生产资源，获取最大利润？l可建立如下模型求解：l目标函数：maxZ=16X1+10X2+9X3 l满足约束条件：l具体演示2X1+

18、X2+X3240X1+2X2+X3360X1+X2+2X3300Xi0，I=1,2,34747l如果约束条件和目标函数都是线性函数，则称作线性规划，否则为非线性规划。如果要求决策变量的值为整数，则称为整数规划。其中线性规划的应用最广。根据美国财富杂志对全美前500家大公司的调查表明，线性规划的应用程度名列前矛，有85%的公司频繁地使用线性规划，并取得了显著提高经济效益的效果。4848l利用Excel提供的规划求解法可以解运筹学中的许多问题，譬如线性规划、指派问题、运输问题、机器分配问题、人事安排 等，只要是对生产、制造、投资、财务、工程等求最大利润、最小成本等问题，就基本上可以用规划求解法快速

19、得到答案。4949模糊综合评判l模糊数学是研究和处理模糊现象的数学。生活中的许多复杂现金带有模糊性，难以明辨是非，应用模糊数学的方法进行综合评判，会取得更好的实际效果。l案例：文献-高等学校人力资源管理的模糊综合评判5050数组公式及其运用l数组公式的一般应用l数组公式的中高级应用贝叶斯决策法例题5151贝叶斯决策l运用贝叶斯公司对状态参数进行修正，再利用经过修正的更准确的状态参数按期望损益标准进行决策，以提高决策的可靠性。这是贝叶斯决策法的基本思想。5252niniAiPAiBPAiPAiBPAiPBAiP111上式为贝叶斯公式，它用在决策中的意义是求造成B事件发生的原因Ai的概率，然后根据

20、原因概率预测经济结果，以便进行决策。5353决策的基本步骤l分析决策问题，确定备选方案，收集各方案状态先验信息（未得到新信息以前的原信息），估计先验概率P(i)（据先验信息估计出的状态概率）；l通过“实验”，取得与状态相关的另一个随机变量X（预报状态）关于给定的状态的条件概率P（Xk|i）;l根据贝叶斯公式，计算后验概率P （i|Xk）;l利用后验概率，计算在预报状态X下，各方案的期望损益值l按期望标准进行优选，期望收益大者或期望损失小者为优。5454一个案例l某公司的销售收入受市场销售情况的影响，存在3种状态：畅销（1）、一般（2）和滞销（3）。3种状态发生的概率分别是0.5、0.3和0.2

21、.公司制定的3种销售方案相应的收益情况如下表所示：P（i）a1a2a310.5200150 18020.3501005030.2-100-50 -105555l该公司经过深入的市场调查，对市场销售前景进行了预报，记预报为畅销、一般、滞销的状态分别为X1、X2、X3，条件概率如下表所示P（i）P=(x1i）P=(x2i）P=(x3i）10.50.60.20.220.30.10.70.230.20.30.10.6那么针对这3种预报状态应分别采用哪种销售方案？5656电子表格建模程序计划设想一下你的目标手工进行一些计算建立一个电子表格建模 先建一个小模型将模型扩展为完整的模型测试 利用不同的测试数据分析模型的逻辑关系分析 评估建议和解和/或利用SOLVER优化如果得出的解显示模型不完善，回到计划或建模步骤5757案例研究：大沼泽地金色年代公司的现金流问题5858建立一个好的电子表格模型的几个原则l首先输入数据l组织和清楚地标识数据l每个数据输入惟一的一