本量利关系敏感性分析的Excel应用

本量利关系敏感性分析的Excel应用

珠海城市职业技术学院经济管理学院陈希

本量利分析（Cost-Volume-ProfitAnalysis，CVP）是指在变动成本法基础上，以数学化的会计模型与图文来揭示固定成本、单位变动成本、销售单价、保本点和保利点等变量之间的内在规律性联系及企业的经营安全性，所提供的原理、方法在管理会计中有着广泛的用途，同时又是企业进行决策、计划和控制的重要工具。在进行本量利分析时，主要变量有固定成本、单位变动成本、销售单价、目标利润、税后目标利润以及企业所得税率，供企业进行财务决策的主要指标包括保本点、保利点、保净利点和安全边际率等。

一、变动成本法下本-量-利关系

（一）本量利基本关系式营业利润（P）=（单价－单位变动成本）×销量－固定成本=（p－b）x–a；边际贡献（Tcm）=销售收入-变动成本=（p–b）x；单位边际贡献（cm）=销售单价-单位变动成本=p–b；边际贡献率（cmR）=单位边际贡献/销售单价= cm/p。

（二）保本点保本点又称为盈亏临界点（Break-evenPoint），是指企业的经营规模（产销量）刚好使企业达到不盈不亏的状态，即营业利润为零时的销售量或销售额。保本量=固定成本/（销售单价-单位变动成本）=a/（p-b），保本额=固定成本/边际贡献率=a/cmR。

（三）保利点保利点是指在单价和成本水平确定的情况下，为确保预先确定的目标利润（TP）能够实现而应达到的销售量或销售额。保利量=（固定成本+目标利润）/（销售单价-单位变动成本）=（a+TP）/（p-b），保利额=（固定成本+目标利润）/边际贡献率=（a+TP）/cmR。

（四）保净利点保净利点是指在单价和成本水平确定的情况下，为确保预先确定的税后目标利润能够实现应达到的销售量或销售额。

)/单位边际贡献

保净利量= (固定成本+目标利润

1−所得税率

)/单位边际贡献率

保净利额= (固定成本+目标利润

1−所得税率

（五）经营安全分析

安全边际量=计划（实际）产销数量-保本量

安全边际率=安全边际量/计划（实际）产销量

安全边际率越低，说明企业的计划产销量或实际产销量离保本量越近，随着企业经营状况的不确定性出现亏损的可能性越大；安全边际率越高，说明企业的计划产销量或实际产销量高出保本量越多，经营安全性越好，一般标准如表1

二、利用Excel2010建立本-量-利模型

（一）案例资料某企业只生产一种产品，单价5元，单位变动成本2.6元，2010年计划产销量60000件，目标利润240000元，固定成本108000元，企业所得税率25% ，求保本点、保利点、保净利点，并进行敏感性分析，以及对该企业的经营安全性做出判断。

（二）输入变量打开一个新的Excel2010文档，划分好相关变量和分析结果两个功能区域，输入案例资料中的变量，见图1。

（三）输入公式输入公式时必须用英文标点符号才有效。

C5=C2-C1；F5 =C5/C2；C6=F1/C5；F6=F1/F5；

C7=（F1+C3）/C5；F7=（F1+C3）/F5

C8=（F1+C3/（1-F2））/C5；F8=（F1+C3/（1-F2））/F5；

C9=（C4-C7）/C4；

F9单元格显示的是经营安全程度，在不同的数值下分别对应危险、值得注意、较安全、安全和非常安全等文字描述，要用到Excel中的if函数，在该单

元格中输入=IF （C11>=30% ，IF（C11>=40% ，“很安全”，“安全”），IF （C11>=20% ，“较安全”，IF（C11>=10% ，“值得注意”，“危险”）。全部输完后，见图2。

（四）设置数值调节钮（窗体控件）进行各变量的敏感性分析相对于早期版本，Excel2010的窗体控件的使用有较大变化。具体操作是：（1）单击“文件”菜单，在下拉菜单中选择“选项”。（2）在“选项”对话框中单击“自定义功能区”，在“主选项卡”中找到“开发工具”，单击“开发工具”对应的复选框，在□中打上√，点“确定”回到Excel主界面后，在菜单列表中已经有了“开发工具”菜单。（3）单击“开发工具”菜单，再单击“插入”打开“表单控件”，单击数值调节钮（窗体控件）图标后，光标变成“+”形状，在D1单元格中拖动鼠标，松开后即在该单元格中出现窗体控件图标。（4）右键单击D1单元格中的窗体控件图标，选择“设置控件格式”，进入设置控件格式对话框。见图3。（5）在“单元格链接”中输入$C$1，点确定。同样操作，在D2单元格建立窗体控件，链接到C2，出现如图4所示的界面。通过单击D1单元格的数值调节钮，可以上下调节C1单元格数值的大小，在分析结果区域相关单元格中的计算结果会随着单位变动成本和销售单价变量的变化而发生变化。（6）在现有的Excel版本中，控件设置格式对话框中的“步长”栏中，能输入的最小数值为1，即限制了数值调节钮上下调节的最小单位为1个单位，无法精确到小数位，而在企业的实际情况中，单位变动成本和售价可能到小数点后两位数字（1分钱），因此，对相关的链接我们还需要做一些技巧性的处理。（7）在G1中输入260，在C1中输入公式：C1=G1/100；在G2中输入500，C2中输入公式：C2=G2/100。（8）右键单击D1单元格中的数值调节钮，进入设置控件格式对话框，在“单元格链接”中输入$G$1；同样处理，把D2单元格中窗体控件链接到G2单元格。（9）右键单击G

列，选择“隐藏”，即可完成Excel2010下的本量利关系敏感性分析模型。现在，点击D1或D2单元格中的数值调节钮，就可以使C1单元格和C2单元格中数字的变化精确到0.01个单位，也就是说在本量利关系敏感性分析模型中，单位变动成本和售价的敏感性分析精确到了1分钱的变动。见图5。

用Excel软件可以大大减化本量利关系的计算，使各变量的敏感性分析更加简单和直观，甚至对于财务会计的初学者，也可以依样建立模型，并使用模型进