Excel在利润敏感性分析与预测模型中的应用(含摸版)

Excel在利润敏感性分析与预测模型中的应用

2010-07-01 来源: 作者: 参与讨论：会计论坛

【4Fang,最简单实用的免费财务软件】【2011年注册会计师考试辅导班报名中】

【摘要】利润是企业的综合性经济指标，它反映企业一个时期的经营成果。在利润的形成过程中，会存在许多不确定因素对利润产生影响，各因素对利润的影响程度和敏感程度不同。本文阐述了各因素对利润的敏感系数的计算及如何运用敏感系数进行利润预测，在此基础上设计出利润敏感性分析与预测模型，可以方便地进行敏感性分析和利润预测。

【关键词】敏感系数；敏感分析；利润预测

敏感性分析是在投资项目评价和企业其他经营决策中常用的一种不确定性分析方法。它是在确定性分析的基础上，重复分析不确定因素（如销售量、价格、成本、投资等）变化时，将对项目经济效益评价指标（如利润、投资收益率、投资回收期等）的影响程度。利润敏感性分析的主要作用之一是进行利润的预测和规划。根据不确定性因素每次变动数目的多少，敏感性分析可以分为单因素敏感性分析和多因素敏感性分析。本文拟分析当影响利润的一个或几个因素同时发生变动时，如何运用敏感系数进行利润预测，并建立Excel利润敏感性分析与预测模型。

一、单因素条件下敏感系数的计算

一定时期内，企业可实现的营业利润是由产品的产销量、单价、单位变动成本及固定成本总额等因素决定的，这些因素中的任何一个发生变化，都会对利润产生不同程度的影响。因此，常规条件下的敏感系数就是计量上述某一因素单独发生变化时对利润影响程度的指标，一般其计算公式为：

某因素的敏感系数=营业利润变动率/该因素的变动率

某因素的敏感系数越大，说明其变化对利润的影响程度越大，管理中应对其重点关注。实务中，为事先了解各因素的变化对利润的影响情况，便于利润预测和管理，各因素的敏感系数必须根据基期的有关数据直接测算。设基期营业利润为I，单价为p，单位变动成本为b，固定成本总额为a，产销量为Q，根据量本利分析法的计算公式，则有：

I=Qp-Qb-a

在计算常规条件下的敏感系数时，假设其他因素不变，计划期间只有价格发生变化，其变化率为k，则：利润增长额为：

当影响利润的某一因素单独发生变化时，按常规的敏感系数可直接预测计划期的营业利润，计算公式为：

预测期营业利润=基期利润×（1+某因素的敏感系数×该因素变化率）

二、多个因素同时发生变化时敏感系数在利润预测中的应用

现实经济生活中，计划期与基期相比，虽然影响利润的某个因素单独变动的情况时有发生，但更经常出现的是几个因素同时发生变化的情况。本文运用常规的敏感系数进行利润预测，作出如下分析：设基期营业利润I为（Q p- Q b- a），与基期相比，计划期的销售量、单价、单位变动成本、固定成本总额同时发生变化，其变化率分别为k1、k2、k3、k4，则计划期营业利润I1为：

运用上式，无论影响利润的因素是单独变动还是几个同时变动，都可以根据事先测定的常规条件下的敏感系数进行利润预测。

三、利润敏感性分析与预测模型的设计与应用

（一）设计表样

新建一个Excel工作簿，参照表1，定义合适的行高、列宽，合并相应单元格，并在相应单元格录入文字，在B3、B4、B5、B6、B7单元格录入相应数据。

（二）定义单元公式，建立分析与预测的数学关系式

在定义公式之前，需将录入状态设置为英文（半角）状态。其中：C3=D3/100-100%；

C4=D4/100-100%；C5=D5/100-100%；C6= D6/100-100%；C7= （E7-B7）/B7；E3=B3*（1+C3）；E4=B4*（1+C4）；E5=B5*（1+C5）；E6=B6*（1+C6）；E7= （E3-E4）*E6-E5；F3= B6*B3/B7；F4= -B6*B4/B7；F5= -B5/B7；F6=B6*（B3-B4）/B7；G3= F3*C3；G4= F4*C4；G5=F5*C5；G6=F6*C6；H3= $B$7*（1+G3）；B9= B5/（B3-B4）；E9= （B3-B4）*B6-B7；B10= B3-（B5+B7）/B6；E10= （B5+B7）/B6+B4；G7=F6*C6+F3*C3+F4*C4+F5*C5+F3*C6*C3+F4*C6*C4；

选中H3单元格，拖动H3单元格右下角的填充柄到H7。

（三）设计滚动条按钮，实现因素变动的灵活调节及如意组合

1．如果窗体工具按钮没有出现在工具栏上，可依次通过“视图”菜单、“工具栏”菜单和“窗体”菜单来调出。

2．单击窗体工具按钮中的“滚动条”按钮，在D3单元格中画出大小合适的滚动条按钮，设置控件格式的控制参数为：最小值设为0，最大值设为200，步长设为1，单元格链接到D3。

3．按照上述方法，在D4、D5 和D6 单元格中画出大小合适的滚动条按钮，分别设置控件格式的控制参数，单元格链接分别指定为D4、D5、D6。

以上最大值和最小值的设定依据是：考虑单价、单位变动成本、固定成本的最小允许值（最小可能值）和最大允许值（最大可能值），并结合C3、C4、C5、C6单元格的公式对D3、D4、D5和D6单元格中数据所进行的转换。

（四）利润敏感性分析与预测模型的应用

1．为决策者提供各因素的最低值及最大值。由表1中的有关数据可以得出以下经济指标：

（1）销售量最低值。销售量最低值是指利润为零时的销售量，即盈亏临界点的销量。

（2）单位变动成本的最大值。单位变动成本上升会使利润下降，并逐渐趋于零，相对于利润来说是一个逆指标，此时是企业可以忍受的单位变动成本最大值。

（3）固定成本最大值。固定成本也是利润的逆指标，其上升也会使利润下降并趋于零。

（4）单价最小值。单价相对于利润来说是一个正指标，它的下降会使利润下降，下降到一定程度，利润将变为零，它是企业能忍受的单价最小值。

2．敏感分析。单击分析模型中的变动按钮，使单价、单位变动成本、销售量和固定成本等因素中的一个因素值发生变化，其他因素不变（变动百分比为0），即可自动显示出变化因素对利润影响的敏感程度（位于F3～F6 单元）。

3．利用敏感系数进行利润预测。在该模型中，用鼠标单击某一个或多个因素的变动按钮，可以观察到某一个因素或多个因素变动的百分比（C3～C6单元格）及其变动后数值（E3～E6单元格）以及所预测的相应的目标利润（单因素变动是每一行对应的预期营业利润单元格，多因素变动是H7单元格）的变化结果。

主要参考文献

【1】钟爱军. 用Excel进行利润的敏感性分析【J】. 中国管理信息化，2006，（2）.

【2】林丰岩. 敏感性分析在利润预测中的应用【J】. 财会通讯：综合版，2006，（2）.