基于Excel的多品种条件下本量利分析模型设计

梁馨匀+兰素英

摘要：本量利分析以数量化的会计模型或图式揭示企业的固定成本、变动成本、单价、销售量、销售额、利润等变量之间的关系。文章运用Excel设计多品种本量利分析模型，通过建立基础资料区、设置微调器、制作面积折线图求解，使管理层直观地了解企业的盈利情况，为预测、决策、规划、控制提供必要的财务信息，帮助管理层在产销规模、成本控制和产品结构等方面做出决策。

关键词：Excel；本量利分析；保本

本量利分析，是指研究成本—业务量—利润关系的一种数学分析方法。在现实的生产经营活动中，大部分企业的生产活动是复杂的，一般都是进行多品种生产经营，这就涉及多品种本量利分析。企业在生产、销售多种产品的情况下，运用Excel建立分析模型，借助微调器及面积折线图等功能，将数据变得动态可调，有利于提高企业管理层决策的效率。

一、基本分析思路

在多品种的条件下，只能用销售额来表示企业的盈亏临界点。通常计算多品种企业盈亏临界点的方法有综合边际贡献率法、联合单位法、分算法等几种方法。本文主要介绍综合边际贡献率法。综合边际贡献率法是假设产品结构不变的情况下，通过计算多品种的综合边际贡献率，从而确定企业综合保本点和每种产品的保本点的方法。

保本点也称盈亏临界点，是指企业达到收支平衡实现保本的销售水平。盈亏临界点分析是确定企业经营安全程度和进行保利分析的基础。多品种的盈亏临界点分析关键是企业综合保本销售额的确定。具体来说，

企业综合保本销售额=

综合边际贡献率法下保本点的具体计算步骤如下：

第一步，计算综合边际贡献率

综合边际贡献率=∑（各種产品的边际贡献率×该种产品的销售额比重）

第二步，计算企业综合保本销售额

企业综合保本销售额=

第三步，计算各种产品保本销售额

某种产品保本销售额=企业综合保本销售额×该种产品的销售额比重

二、模型设计与案例分析

（一）案例背景

以某公司2016年生产资料为例，构建本量利模型进行分析，主要分析该公司三种主要产品在盈亏平衡点时的销量和销售收入。建立多品种分析模型需要了解产品的销量、单价、单位变动成本、固定成本、边际贡献率、销售比重、综合保本销售额等。三种产品的基础资料如表1所示。

（二）模型设计

1. 建立基础资料区

第一步：打开Excel2010工作表，将案例中的基础资料输入。

第二步：设置计算公式。

（1）单价：在B5单元格中输入“=6100*H5”；C5单元格输入“=5500*H5”；D5单元格输入“=6050*H5”。

（2）单位变动成本：在B6单元格中输入“=4180.95*H7”；C6单元格输入“=4278.43*H7”；D6单元格输入“=2383.61*H7。

（3）固定成本：在E7单元格中输入“=26900000*H9”。

（以上公式中的H5、H7、H9涉及下文微调器的使用）。

（4）单位边际贡献=单价—单位变动成本，在B8单元格中输入“=B5-B6”，单击公式栏中的“√”确定，即可得出计算结果。C8、D8单元格可以通过公式填充工具复制B8单元格来计算。

（5）边际贡献率= ，在B9单元格中输入“=B8/B5”，单击公式栏中的“√”确定，即可得出计算结果。C9、D9单元格可以通过公式填充工具复制B9单元格来计算。

（6）边际贡献总额=销量×单位边际贡献，在B10单元格中输入“=B4*B8”，单击公式栏中的“√”确定，即可得出计算结果。C10、D10单元格可以通过公式填充工具复制B10单元格来计算。

（7）销售收入=销量×单价，在B11单元格中输入“=B4*B5”，单击公式栏中的“√”确定，即可得出计算结果。C11、D11单元格可以通过公式填充工具复制B11单元格来计算。E11单元格可以选定E11，然后单击选项卡中的“公式”，再单击“自动求和”，即可得出计算结果。

（8）销售比重= ，在B12单元格中输入“=B11/$E$11”，单击公式栏中的“√”确定，即可得出计算结果。C12、D12单元格可以通过公式填充工具复制B12单元格来计算。

（9）加权边际贡献率=边际贡献率×销售比重，在B13单元格中输入“=B9*B12”，单击公式栏中的“√”确定，即可得出计算结果。C13、D13单元格可以通过公式填充工具复制B13单元格来计算。E13单元格可以选定E13，然后单击选项卡中的“公式”，再单击“自动求和”，即可得出计算结果。

（10）保本销量= ，在B14单元格中输入“=B15/B5”，单击公式栏中的“√”确定，即可得出计算结果。C14、D14单元格可以通过公式填充工具复制B14单元格来计算。

（11）保本销售额=综合保本销售额×销售比重，在B15单元格中输入“=$E$16*B12”，单击公式栏中的“√”确定，即可得出计算结果。C15、D15单元格可以通过公式填充工具复制B15单元格来计算。

（12）综合保本销售额=

，在E16单元格中输入“=E7/E13”， 单击公式栏中的“√”确定，即可得出结果。

（13）利润=全年综合保本销售额×全年加权边际贡献率—全年固定成本，在E17单元格中输入“=E16*E13—E7”， 单击公式栏中的“√”确定，即可得出计算结果。

第三步：设置结论性文字。在B19单元格中输入“=”盈亏平衡点：“&ROUND（E16，2）&”元”， 单击公式栏中的“√”确定即可。构建好的模型如图1所示。endprint

图2所示，在当前条件下，企业的盈亏平衡点为58557502.79元，三种产品的保本量分别是4751.21吨、66.67吨、4827.84吨，保本额分别是28982375.57元、366686.06元、29208441.16元。企业当前三种产品的实际销量均大于各自的保本量，共实现销售收入764135000.00元，大于综合保本销售额58557502.79元，说明该企业目前处于盈利状态。

2. 设置微调器

企业在生产经营过程中，往往需要根据市场情况调整单价等因素。使用微调器可以快速完成单价等因素的调整。本文主要列举了单价微调器的使用。依次点击：开发工具—插入—数值调节钮。

当鼠标变成“+”时，在G5单元格中画一个大小适中的微调器。G5单元格作为过渡单元格。右键单击数值调节钮，调出“设置控件格式”对话框。最小值设为“0”，在“单元格链接”中选中“G5单元格”。为了把变动幅度控制在“-50%～150%”，把“最大值”设为200，步长为1。如图3所示。

在H5單元格中输入“=G5/100-50%”。同理可得单位变动成本和固定成本的微调器。每上（下）按动一次微调器按钮，对应金额就会提高（降低）基础的10%。如图4所示。

至此，一个动态可调的多品种本量利分析模型就完成了。该模型可以对单价、单位变动成本、固定成本三个变量进行动态调节，通过观察三个变量的变动对利润、销量的影响，从而做出合理的决策。

3. 制作面积折线图

首先，按住Ctrl键，选择区域A3：D3、A5：D6，然后依次点击插入—面积图—二维面积图，双击网格线，选择无线条。

其次，仅选数据区域B5：D6，按Ctrl+C复制数据，单击绘图区，按Ctrl+V粘贴，右键单击颜色区域，把图片类型修改为“带数据标记的折线图”，双击折线节点，可以设置数据点格式。

最后，在绘图区添加5个文本框，输入相应的文字，选中单价对应的文本框，在公式编辑栏输入“=Sheet3！$H$5”；选中单位变动成本对应的文本框，在公式编辑栏输入“=Sheet3！$H$7”； 选中中间的文本框，在公式编辑栏输入“=Sheet3！$B$19”；把单价和变动成本的微调器复制粘贴到绘图区。至此，一个动态可调的盈亏平衡面积折线图就完成了。如图5所示。

图5可以直观反映三种产品随单价、单位变动成本变动的变化趋势。企业当前三种产品的单价趋势线均位于单位变动成本趋势线上方，两条趋势线之间的面积为边际贡献，只要边际贡献大于固定成本，企业就能盈利。每按一次微调器，企业的边际贡献就会随趋势线的变化而变化。

三、结语

在现实经济生活中，企业利润受到多因素的影响。本文运用Excel设计多品种本量利分析模型，通过建立基础资料区、设置微调器、制作面积折线图，为实际问题的解决提供定量分析和计算的方法，提高管理层的决策效率。基于Excel的本量利分析模型，可以将复杂的数据简单化，该模型的结论会随着参数的改变而呈现动态变化，非常直观形象，使管理者决策向科学化和合理化方向发展。

参考文献：

[1]温素彬.管理会计[M].机械工业出版社，2016.

[2]候志才.多产品的Excel本量利模型构建[J].财会月刊，2014（05）.

[3]王琦，温素彬.管理会计中Excel的高级应用[J].财务与会计，2014（05）.

基金项目：广西区级大学生创新创业训练计划项目资助（项目编号：201713638048）。

（作者单位：广西大学行健文理学院）endprint