《Excel 2010财务应用案例教程》第9章 财务预测分析

销售预测与分析利润预测分析第9章财务预测分析成本预测分析资金需求量预测分析财务的预测分析就是用科学的方法预测、推断事物发展的必然性或可能性的行为。即根据过去预测未来，由已知推断未知的过程。本章介绍销售量、利润、成本和资金需求量的预测分析。一、销售预测与分析（一）使用函数预测产品销售量（二）使用分析工具预测产品销售量（三）使用趋势线分析产品销售量销售预测是指根据已知的某一时间内发生的实际销量，预测未来一段时间可能发生的销量。常见的预测方法有线性预测法和指数预测法。在Excel中，对于这两种不同的预测方法都有对应的函数和分析工具。例如，已知中国汽车2014年上半年的销售量如下表所示，下面我们利用函数和分析工具预测销售量。接下来我们以该表数据为例进行相关分析操作。（一）使用函数预测产品销售量Excel提供的预测函数主要有LINEST函数、TREND函数、LOGEST函数和GROWTH函数等（前两种属于线性预测函数，后两种属于指数预测函数），它们所使用的参数都基本相同。其中LINEST函数和LOGEST函数可返回系数和其他统计值，而TREND函数和GROWTH函数只返回预测结果。1．使用线性函数因为线性预测法更接近真实，所以下面我们根据上表所示的数据使用TREND函数进行线性预测，直接返回2014年下半年的预测结果。输入产品已知销售量数据输入数组公式得到下半年的预测值2．直线回归分析法直线回归分析法就是运用直线回归方程来进行预测。利用Excel中的FORECAST函数能很快地计算出预测数。我们还是用上面的例子来预测下半年的销售量。复制公式预测出其他月份的销售量。可看到使用线性函数和直线回归分析法的计算结果是一样的利用FORECAST函数计算出预测数（二）使用分析工具预测产品销售量要使用分析工具对销售量进行预测分析，方法有多种，常用的有移动平均法、指数平滑法。1．移动平均法移动平均法是用一组最近的实际数据值来预测未来一期或几期内公司产品的需求量、公司产能等的一种常用方法。移动平均法适用于即期预测。当产品需求既不快速增长也不快速下降，且不存在季节性因素时，移动平均法能有效地消除预测中的随机波动。移动平均法根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。下面我们使用移动平均法对中国汽车2014年上半年的销售量按2期和3期预测4~7月的销售量。在工作表中输入数据在“输入区域”编辑框中单击，然后在工作表中选择待分析数据单元格区域C6:C10在“间隔”编辑框中输入观察期数“2”；在“输出区域”编辑框单击，然后在工作表中选择D7：D11单元格区域输出的预测值用同样的方法按3期预测销售量，“输入区域”为C5：C10单元格区域，在“间隔”编辑框中输入“3”，“输出区域”为单元格区域E6：E11如果在“移动平均”对话框中选中“图表输出”复选框，然后在弹出的提示对话框中单击“确定”按钮，可分别在工作表中插入嵌入式图表。从左到右依次为按2期和3期的图表2．指数平滑法指数平滑法指以某种指标的本期实际数和本期预测数为基础，引入一个简化的加权因子，即平滑系数，以求得平均数的一种指数平滑预测法。它是加权移动平均预测法的一种变化。平滑系数必须呈大于0、小于1，如0.1、0.4、0.6等。其计算公式为：下期预测数＝本期实际数×平滑系数＋本期预测数×（1－平滑系数）一般说来，下期预测数常介乎本期实际数与本期预测数之间。平滑系数（即阻尼系数）的大小，可根据过去的预测数与实际数比较而定。差额大，则平滑系数应取大一些；反之，则取小一些。平滑系数越大，则近期倾向性变动影响越大；反之，则近期的倾向性变动影响越小，越平滑。据平滑次数不同，指数平滑法分为：一次指数平滑法、二次指数平滑法和三次指数平滑法等。下面我们仍以上表所示的数据为例，利用一次指数平滑法分别将阻尼系数设置为0.2和0.4时，来预期测产品2~7月的销售量。制作预测表格在“输入区域”编辑框中单击，然后在工作表中选择C5：C11单元格区域；在“阻尼系数”编辑框中输入0.2；在“输出区域”编辑框中单击，然后在工作表中选择D5：D11单元格区域用同样的方法将阻尼系数设置为0.4，输入区域保持不变，输出区域为E5：E11单元格区域。可看到：平滑系数越大，则近期倾向性变动影响越大；反之，则近期的倾向性变动影响越小，越平滑（三）使用趋势线分析产品销售量下面我们仍以上表所示数据为例，首先创建产品销量折线图，然后分别利用线性趋势线和指数趋势线预测下半年的销售量。1．创建产品销量折线图制作预测表格设置图表标题及图表的主要横、纵坐标轴标题2．创建线性趋势线并计算预测结果线性趋势预测法也称“直线趋势预测法”，是对观察期的时间序列资料表现为接近于一条直线，或表现为近似直线的上升和下降时采用的一种预测方法。关键是求得趋势直线，然后利用趋势直线的延伸求得预测值。下面我们在创建的销售量预测分析折线图表中添加线性趋势线，并计算下半年的预测结果。右击图表中的“实际销售量”数据系列图表中除了显示线性趋线外，还显示线性公式和R的平方值根据图表中的线性公式，在单元格E5输入公式计算7月份的预测值复制公式计算8~12月的预测值，可以看到与前面使用线性函数预测的结果差不多是一样的线性公式的实质就是根据直线方程y=m+bx计算y值。其中的x就是进行预测的月份数。3．创建指数趋势线并计算预测结果创建指数趋势线并计算预测结果的操作与创建线性趋势线的操作类似。右击图表中的“实际销售量”数据系列复制指数公式计算8~12月的预测值。从图表中的公式得知，线性预测的R平方值为0.6996，指数预测的R平方值为0.6899，这两个判定系数均接近1，说明这两种预测方法的准确程度还算可靠。二、利润预测分析（一）目标利润预测（二）利润敏感性分析利润预测是对公司未来某一时期可实现的利润的预计和测算。它是按影响公司利润变动的各种因素，预测公司将来所能达到的利润水平，或按实现目标利润的要求，预测需要达到的销售量或销售额。利润预测的方法主要有：本量利分析法、比例计算法和因素测算法。本量利法本量利法是一种利用产品销售量、销售额、固定成本、变动成本与利润之间的变动规律，对目标利润进行预测的方法。变动成本是指随业务量增长而成正比例增长的成本；固定成本是指在一定的业务量范围内，不受业务量影响的成本；混合成本介于变动成本和固定成本之间，是指随业务量的增长而增长，但不成正比例增长的成本，可将其分解成变动成本和固定成本两部分。比例计算法比例计算法是根据利润与有关财务指标的比例关系来测算 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 期目标利润数额的方法。常用的比例关系有销售利润率、成本利润率等。因素分析法因素分析法是指以上期的主营业务利润为基础，通过分析计算计划期影响主营业务目标利润的各项因素的变动，来测算计划期间利润数额的方法。下面主要介绍单一品种情况下的确定性本量利分析的具体方法和步骤。（一）目标利润预测目标利润就是指公司计划期内要求达到的利润水平。它既是公司生产经营的一项重要目标，又是确定公司计划期销售收入和目标成本的主要依据。正确的目标利润预测，可促使公司为实现目标利润而有效地进行生产经营活动，并根据目标利润对公司经营效果进行考核。要用Excel进行目标利润预测，首先要建立预测模型，然后利用公式进行计算。例如，某公司生产单一产品A，年销售量10000个，产品的市场单价为每个200元，生产的单位变动成本为每个150元，固定成本为400000元，假设要达到目标利润400000元，应采取哪些措施？下面我们首先根据上述已知条件建立目标利润预测模型，然后利用公式进行计算。建立目标利润预测模型根据“利润＝销售收入（销售量*单价）－变动成本（销售量*单位变动成本）－固定成本＝销售量（单价－单位变动成本）－固定成本”关系式，计算目前利润将计算公式进行变形求目标利润要达到400000元时的预测销售量、单价、变动成本价和固定成本（1）将产品销售量由目前的10000个提高到16000个。（2）将产品单价由目前的200元/个提高到230元/个。（3）将产品单位变动成本由目前的150元/个降低到120元/个。（3）将产品固定成本由目前的400000元降低到100000元。企业通过目标利润预测模型，可以快速找出实现目标利润的努力方向，并对生产各要素进行调整。在实际生产经营活动中，企业往往需要采取综合措施来实现目标利润，进行综合分析和平衡。从计算结果进行分析，若要将目前利润额从100000元提高到400000元，公司应采取如下相应措施：（二）利润敏感性分析所谓利润敏感性分析是研究与制约利润的有关因素发生某种变化时，利润变化程度的一种分析方法。利润敏感性分析的主要作用之一是进行利润的预测和规划。根据不确定性因素每次变动数目的多少，敏感性分析可以分为单因素敏感性分析和多因素敏感性分析。企业中，生产单品种产品，在产销平衡的条件下，本量利之间的关系为：利润＝销售收入（销售量*单价）－变动成本（销售量*单位变动成本）－固定成本＝销售量（单价－单位变动成本）－固定成本在进行利润敏感性分析时，有如下的假设条件：利润只受销售量、单价、单位变动成本和固定成本的影响，并且上述各种因素的变动均不会影响其他因素的变动。例如，已知产品的销售量为5000个，产品单价为42元/个，单位变动成本为26元/个，固定成本为50000元。下面我们分析当影响利润的一个因素发生变动时，如何运用敏感系数进行利润预测，并建立Excel利润敏感性分析与预测模型。建立利润敏感性分析模型和单因素利润敏感性模型表格在“利润敏感性分析模型”表格中绘制滚动条控件，以实现因素变动的灵活调节及如意组合设置其最小值、最大值和页步长分别为0、100和10，在“单元格链接”编辑框中输入G4，表示将其链接到G4单元格用同样的方法在F5、F6和F7单元格绘制滚动条按钮，其设置与上述相同，只是将其单元格链接分别设置为G5、G6和G7计算变化前的利润输入变化后计算公式，然后向下复制公式输入变化后利润的计算公式设置各因素变化率计算公式，然后向下复制公式设置变化后利润的变化率公式各因素变动为0时的显示值设置变化率计算公式，然后向下复制公式设置利润、利润变化量和利润变化率计算公式，至此，利润敏感性分析模型就建立起来了，通过调节各个影响因素滚动条的箭头，改变其变动幅度，可以方便地了解各因素对利润的单独影响程度以及综合影响程度调节“销售量”滚动条进行数值变化调节“单价”滚动条进行数值变化调节“单位变动成本”滚动条进行数值变化调节“固定成本”滚动条进行数值变化由图中可看出，当销售量单独增加10%时，利润增加约0.27%；当产品单价单独增加10%时，利润增加约0.7%；当单位变动成本增加10%时，利润减少约0.43%；当固定成本减少10%时，利润增加约0.17%。由此得出结论：产品单价对利润的影响最大，其次是单位变动成本，而固定成本对利润的影响最小。从中还可得知：销售量和单价相对于利润来说，是一个正指标，而单位变动成本和固定成本相对于利润来说是一个逆指标。在该模型中，若单击多个因素滚动按钮，可以观察到多个因素变动的百分比及其变动后数值以及所预测的相应的目标利润的变化结果。例如，当上述4个因素同时朝着使利润增加的方向变动2%时，则利润率增加辐度为14.1%。三、成本预测分析（一）成本预测的主要方法（二）利用指数平滑法和高低点法预测成本成本是指企业为生产和销售产品所需的全部费用。成本预测是根据企业未来的发展目标和现实条件，参考其他资料，利用专门的方法对企业未来成本水平及其变动趋势进行的估算和预测。成本预测是成本决策、成本计划和成本控制的必要前提。只有做好成本预测，才能加强成本管理；只有加强成本管理，才能提高企业效益。（一）成本预测的主要方法1．定性预测法成本的定性预测是成本预测人员根据专业知识和实践经验，对产品成本的发展趋势性质，以及可能达到的水平所做的分析和推断。由于定性预测主要依靠预测人员的素质和判断能力，因而，这种方法必须建立在对企业成本耗费历史资料、现状及影响因素深刻了解的基础之上。常用的定性预测方法有调查研究判断法和分析判断法。2．定量预测法定量预测法是指根据历史资料以及成本与影响因素之间的数量关系，通过建立数学模型来预计推断未来成本的各种预测方法的统称，它可以充分揭示有关变量之间规律性的联系，以此作为预测的依据。它主要包括如下几种方法。趋势预测法：是按时间顺序排列有关的历史成本资料，运用一定的数学模型和方法进行加工计算并预测的各类方法，也即建立成本y与时间变量t之间的某种函数关系y=f(x)，通过趋势的外推预测成本。常用的趋势预测法包括简单平均法、平均法和指数平滑法等。因果预测法：是根据成本与其相关之间的内在联系，建立数学模型并进行分析预测的各种方法。因果预测法包括本量利分析法、投入产出分析法和回归分析法等。成本预测的高低点法：是指根据企业一定期间产品成本的历史资料，按照成本习性原理和y=a+bx直线方程式，选用最高产量和最低产量的总成本之差（△y），同两种业务量之差（△x-）进行对比，先求b的值，然后再代入原直线方程，求出a的值，从而估计推测成本发展趋势。（二）利用指数平滑法和高低点法预测成本假设某企业生产一种机床，其中2014年最近5个月的产量和历史成本资料如表9-2所示。现需要根据已有的资料预测2015年1~3月份的生产产量和生产成本。1．利用指数函数预测根据资料制作成本表格和预测模型并格式化表格选中单元格区域C9：E9，然后输入数组公式“=GROWTH(C4:G4,C3:G3,C8:E8)”，按【Ctrl+Shift+Enter】组合键，得到2011年1~3月份产量的预测结果；选中单元格区域C10：E10，然后输入数组公式“=GROWTH(C5:G5,C3:G3,C8:E8)”，按【Ctrl+Shift+Enter】组合键，得到2015年1~3月份产品成本的预测结果2015年1~3月的预测表格2．利用指数趋势线预测下面我们利用指数趋势线来预测2015年1~3月份的产量和生成成本。新建“产量和产品成本预测（趋势线法）”表格右击“产量”数据系列右击“产品成本”数据系列，在弹出的快捷菜单中选择“添加趋势线”项，在打开的对话框进行与“产量”趋势线相同的设置，只是将趋势线名称改为“产品成本（预测）”，线条颜色改为绿色下面利用图表中显示的指数公式计算预测值。在C14单元格输入公式“=0.694*EXP(0.3624*C13)”，然后将该公式复制到D14和E14单元格，计算出产量的预测值在C15单元格输入公式“=974.3*EXP(-0.08*C13)”，然后将该公式复制到D15和E15单元格，计算出产品成本的预测值利用指数趋势线法预测产量和产品成本3．高低点法预测高低点法是指以历史成本资料中的产量的最高和最低两个时期的成本数据为依据，借以推算成本的固定和变动部分，用来预测计划期内产量变化条件下的总成本水平。它的数学模型是：y=a+bxb=yh-y1/xh-x1a=yh-bxh=y1-bx1其中，y为总成本，x为总产量，a为固定成本总额，b为单位变动成本，yh为高点产量的成本，y1为低点产量的成本，xh为高点产量，x1为低点产量。仍以上表所示的数据为例，假设2011年1月份的预测产量为75台，下面利用高低点法来预测2011年1月份的产品成本。新建“产量和产品成本预测（高低点法）”表格计算出最高和最低产量计算出相对应的最高和最低产品成本根据“单位变动成本＝(最高业务量成本－最低业务量成本)/(最高业务量－最低业务量)”关系式，在单元格C20中输入公式“=(E18-E19)/(C18-C19)”，按【Enter】键计算出单位变动成本计算出固定成本计算出单位变动成本b和固定成本a后，就可以利用总成本公式y=a+bx预测下一期的混合成本额了。在E21单元格输入公式“=E20+C20*C21”，按【Enter】键即可计算出预测产量为75台时1月份的预测成本四、资金需求量预测分析（一）资金需求量预测的主要方法（二）利用销售百分比法预测（三）利用资金习性法预测资金需求量是指企业为达到生产经营的预期目标所需要的资金数额。资金需求量预测是指企业根据生产经营的需求，对未来所需资金的估计和推测。企业筹集资金，首先要对资金需求量进行预测，即对企业未来组织生产经营活动的资金需求量进行估计、分析和判断，它是企业制定融资计划的基础。（一）资金需求量预测的主要方法企业资金需求量的预测方法主要有：定性预测法和定量预测法两种。定性预测法是根据调查研究所掌握的情况和数据资料，凭借预测人员的知识和经验，对资金需要量所做的判断。这种方法一般不能提供有关事件确切的定量概念，而主要是定性的估计某一事件的发展趋势、优劣程度和发生的概率。定性预测是否正确，完全取决于预测者的知识和经验。在进行定性预测时，虽然要汇总各方面人士的意见和综合地说明财务问题，但也需将定性的财务资料进行量化，这并不改变这种方法的性质。定性预测主要是根据经济理论和实际情况进行理性地、逻辑地分析和论证，以定量方法作为辅助；一般在缺乏完整、准确的历史资料时采用。定量预测法是指以资金需要量与有关因素的关系为依据，在掌握大量历史资料的基础上选用一定的数学方法加以计算，并将计算结果作为预测的一种方法。定量预测方法很多，如趋势分析法、相关分析法、线性规划法等，下面主要介绍两种预测方法。（1）销售百分比法销售百分比法是一种在分析报告年度资产负债表有关项目与销售额关系的基础上，根据市场调查和销售预测取得的资料，确定资产、负债和所有者权益的有关项目占销售额的百分比，然后依据计划期销售额及假定不变的百分比关系预测计划期资金需要量的一种方法。（2）资金习性法资金习性预测法是指根据资金习性预测未来资金需要量的方法。所谓资金习性，是指资金占用量与产品产销量之间的依存关系。按照这种关系，可将占用资金区分为不变资金、变动资金和半变动资金。不变资金是指在一定的产销规模内不随产量（或销量）变动的资金，主要包括为维持经营活动展开而占用的最低数额的现金、原材料的保险储备、必要的成品储备和厂房、机器设备等固定资产占用的资金。变动资金是指随产销量变动而同比例变动的资金，一般包括在最低储备以外的现金、存货、应收账款等所占用资金。半变动资金是指虽受产销量变动的影响，但不成同比例变动的资金，如一些辅助材料上占用的资金等，半变动资金可采用一定的方法划分为不变资金和变动资金两部分。（二）利用销售百分比法预测销售百分比法是指假定收入、费用、资产、负债与销售收入存在稳定的百分比关系，并根据预计销售额和相应的百分比预计资产、负债和所有者权益变动，然后利用会计等式确定融资需求的一种方法。销售百分比法预测步骤为：（1）整理历史报表，分析报表项目哪些与销售成比例变化，并计算出相关项目与销售额的百分比。（2）用一定方法预测销售额。（3）根据预测的销售额和报表项目与销售的百分比，预测出各报表项目。（4）根据会计恒等公式估计预测期的资金需要量。某公司资产负债表和其他已知数据如下所示，下面我们利用销售百分比法预测2015年的资金需求量。制作“2015年资金需求量计算”表格根据“敏感项目”判断条件，在“敏感项目”中填写“是”与“否”计算属于“敏感项目”的资产或负债项目的金额占基期“销售收入”的百分比，即为该项目的数值除以基期的销售收入。在单元格D14输入公式“=C5/$G$5”，然后将该公式复制到D17单元格计算计算“敏感项目”的资产项目的金额占基期“销售收入”的百分比、资产“敏感项目”占基期销售收入百分比合计数和负债“敏感项目”占基期销售收入百分比合计数根据“外部融资额＝（经营资产销售百分比*新增销售额）－（经营负债销售百分比*新增销售额）－（可动用的金融资产）－（销售利润率*计划销售额*（1－股利支付率））”关系式，预测2011年的外部资金需求量（三）利用资金习性法预测资金习性预测法有两种形式：一种是根据资金占用总额同产销量的关系来预测资金需要量；另一种是采用先分项后汇总的方式预测资金需要量。设产销量为自变量x，资金占用量为因变量y，它们之间的关系可用下式表示：y=a+bx（a为不变资金，b为单位产销量所需变动资金）b值可以用高低点法或回归直线法求得。（1）高低点法根据企业一定期间资金占用的历史资料，选用最高收入期和最低收入期的资金占用量之差，同这两个收入期的销售额之差进行对比，先求b的值，然后代入原直线方程求出a的值，从而估计推测资金发展趋势。计算公式为：b＝（最高产销量资金占用量－最低产销量资金占用量）/（最大产销量－最小产销量）这与9.3.2节中的第三种方法的数学模型是一致的a＝最大产销量对应的资金占用量－b×最大产销量＝最小产销量对应的资金占用量－b×最小产销量高低点法在企业的资金变动趋势比较稳定的情况下较适宜。（2）回归直线法根据若干期业务量和资金占用的历史资料，运用最小平方法原理计算不变资金（a）和单位销售额的变动资金（b）。例如，某企业的产销量与资金占用情况下所示，假设2015年的预计产销量为180万套，试计算2015年的资金需求量。下面分别使用高低点和回归线法进行预测。1．高低点法预测分析根据已知数据制作“高低点预测资金需求量”表格计算产销量最高点和最低点值计算产销量最高、最低点相对应的资金占用量计算预测方程系数a和b根据预测方程“y=a+bx”计算预测产销量为180万套时所需的资金2．回归线法预测分析新建“回归线法预测资金需求量”表格设置“回归”对话框中的参数工作表会自动显示计算的回归结果。从计算结果可得到回归方程y=a+bx中的系数a和b值。a对应回归结果中的“Intercept”值，b对应回归结果中的“XVariable1”值，即分别在D47和D48单元格输入公式“=C67”和“=C68”在单元格D48输入回归方程公式“=50+0.5*C48”，按【Enter】键得到2015年的预测值140万元。从回归结果可以看出：销售量与资金占用为正向的变动关系，销售量每变动一个单位，资金占用就会同向变动为0.5个单位