赵文丽

本文运用Excel的规划求解工具和微调器分别设计了联合单位法和综合边际贡献率法下的多品种本量利分析模型，为企业的经营决策提供一种快捷高效的途径。利用Excel进行多产品条件下的本量利分析，可以将经营数据模型化，而且可以动态观察各因素变化对盈亏平衡点的影响，使管理者的决策更加科学化、合理化。

本量利分析概念

本量利分析是研究成本―产量/销售量―利润依存关系的一种分析方法，又称为CVP分析（Cost―Volume―Profit Analysis），它是一种基于成本性态分析和变动成本计算模式的分析方法。CVP分析以数学化的会计模型来揭示固定成本、变动成本、单價、销售量、销售额、利润等多个变量之间的内在规律性，其中所蕴含的原理和方法为企业预测决策和规划提供了一种必要的财务信息，是企业计划和控制的重要工具。

在产品生产和销售过程中，存在着两种不同性质的成本：固定成本与变动成本。固定成本是在一定范围内保持不变的成本，它是不随销量发生变化的成本，包括固定资产折旧费、办公费用、管理人员工资、职工培训费等。而变动成本指支付给各种变动生产要素的费用，如工人工资成本、原材料成本等。变动成本随产量变化而变化，常常在实际生产过程开始后才需支付。

此外，在本量利分析中，贡献毛益（又称为边际贡献、边际利润）指的是销售收入与变动成本的差额，也就是对企业盈利所作的贡献。用贡献毛益补偿固定成本之后，若有剩余即为企业营业利润。若贡献毛益不足以补偿固定成本，两者差额为企业营业亏损。贡献毛益率是指贡献毛益总额占销售收入总额的百分比，或单位贡献毛益占销售单价的百分比。

多品种下本量利分析的基本方法

在多品种条件下，由于每种产品的贡献毛益不尽相同，因此，企业的保本点（盈亏平衡点）或保利点（实现目标利润的销售额）不能由各产品的销售量直接相加，而只能以总的销售收入金额来表示保本点。多品种条件下，确定保本点或保利点销售额常用方法有联合单位法、综合边际贡献率法。

当企业的多种产品之间存在着相对稳定的产销量比例关系，且预计的销售量都能实现，那么就可以把这一比例关系的组合看作一个联合单位，通过确定联合单位的联合单价及联合单位变动成本，然后根据单一产品的本量利分析法计算出联合保本或保利量，这就是联合单位法。联合单位法下保本点的关键计算步骤如下：首先计算联合单价，联合单价=Σ（各产品销量比×该产品单价）；然后计算联合单位变动成本，联合单位变动成本=Σ（各产品销量比×该产品的单位变动成本）；再计算联合保本量，联合保本量=固定成本/（联合单价―联合单位变动成本）；最后计算各产品的保本量和保本额，各产品的保本量=各产品的销量比×联合保本量、各产品的保本额=各产品的保本量×单价。

假定产品的品种结构保持不变，通过计算各产品的加权平均边际贡献率，来确定企业的综合保本/保利点和每种产品的保本/保利点，这就是综合边际贡献率法。综合边际贡献率法下保本点的关键计算步骤如下：首先计算综合边际贡献率，综合边际贡献率=Σ（各种产品的边际贡献率×该种产品的销售额比重）；然后计算企业综合保本销售额，企业综合保本销售额=企业固定成本总额/综合边际贡献率；最后得出某种产品保本销售额，某种产品保本销售额=企业综合保本销售额×该种产品的销售额比重。

联合单位法在Excel中的设计与实现

例如，利达材料公司生产A、B、C三种产品，全厂固定成本为300000元。A、B、C三种产品：预计销售量分别为100000 件、25000 件、10000件，预计销售单价分别为10元、20元、50元，预计单位变动成本分别为8.5元、16元、25元。

在确定该公司的综合及各产品的保本或保利销售收入及销售量之前，首先，按表1所示来设计多产品条件下的本量利分析模型。表1中，单元格C7为固定成本总额，A、B、C三种产品的单价分布在单元格D4至D6中，A、B、C三种产品的单位变动成本分布在单元格F4至F6中，A、B、C三种产品的销售量分布在单元格C4至C6中；A、B、C三种产品的销售收入分布在单元格E4至E6中，且其值为相对应的单价与销售数量的乘积；单元格C9用来存放综合保本或保利销售额，单元格C8为预计的目标利润总额，总利润的计算公式见下文。

然后，按照Excel中规划求解步骤来逐步求解。

一、多产品品类下本量利分析模型的目标单元格设计

显然，本案例的决策目标就是多产品条件下的总利润，即单元格C8。总利润的计算公式为=SUMPRODUCT（C4 ： C6，D4 ： D6）―SUMPRODUCT（C4 ： C6，F4 ： F6―C7。规划求解时，可以根据情况确定总利润目标的数值为零（保本） 或预期的计划利润 （保利）。

二、多产品品类下本量利分析模型的可变单元格（即未知数）设计

当总利润目标C8为零或预期利润值时，规划求解后A、B、C三种产品的保本或保利销售量出现的具体位置，本例中把可变单元格设置为C4至C6（未知数）。在规划求解前，C4至C6内的数据分别表示A、B、C三种产品的预计销售量。

三、多产品条件下本量利分析模型的约束条件设计

A、B、C三种产品销售量分别为100000 件、25000件、10000 件，它们的销量比为10 ： 2.5 ： 1。联合单位法下A、B、C三种产品的销售量单元格C4至C6必须满足：$C$4 ： $C$6≧0，$C$4 ： $C$6=整数，$C$4=10×$C$6，$C$5=2.5×$C$6。

在Excel 2010主菜单下，单击“数据”选项卡下的“规划求解”功能按钮，启动规划求解功能，可变单元格、目标单元格和约束条件的设置都在“规划求解参数”对话框中，然后单击求解。

四、多产品条件下本量利分析模型的规划求解结果解读

如表2所示，求解前，若目标单元格C8的值设置为0，那么单击“规划求解”就可以立即看到A、B、C三种产品条件下的盈亏平衡分析的综合运算结果。由此可以看出，计划期A、B、C三种产品的保本销售量分别为60000件、15000件、6000件，综合保本销售额为1200000 元。若求解前，目标单元格C8的值设置为170000，则可得出多产品条件下保利分析的最终运算结果。

综合边际贡献率法在Excel中的设计与实现

下面利用Excel来实现综合边际贡献率法下的多產品本量利分析模型，并且考虑固定成本、销售单价、单位变动成本等因素变化对盈亏平衡点的影响。

首先，打开Excel工作表，建立数据区并录入已知数据，如表3所示。

然后，在数据区按顺序依次设置计算公式，如表3所示。分别在单价行的B5、C5、D5单元格中输入“=10×H5”“=20×H5”“=50×H5”。分别在单位

位变动成本行的B6、C6、D6单元格中输入“=8.5×H7”“=16×H7”“=25×H7”。在单元格E7中输入固定成本的计算公式“=300000×H9”。为方便观察销售单价、单位变动成本和固定成本因素的变化对盈亏平衡点的影响，以上公式计算中用到的H5、H7、H9，会放置微调器来动态调整三个因素的数值。单位边际贡献=单价—单位变动成本，分别在单位边际贡献行的B8、C8、D8单元格中输入“=B5―B6”“=C5―C6”“=D5―D6”。边际贡献率=单位边际贡献/单价，分别在边际贡献率行的B9、C9、D9单元格中输入“=B8/B5”“=C8/C5”“=D8/D5”。边际贡献总额=销量×单位边际贡献，分别在边际贡献总额行的B10、C10、D10单元格中输入“=B4×B8”“=C4×C8”“=D4×D8”。销售收入=销量×单价，分别在销售收入行的B11、C11、D11单元格中输入“=B4×B5”“=C4×C5”“=D4×D5”，对E11单元格进行单击“自动求和”计算出全年销售收入。销售比重=销售收入/全年销售收入，分别在销售比重行的B12、C12、D12单元格中输入“=B11/$E$11”“=C11/$E$11”“=D11/$E$11”。加权边际贡献率=边际贡献率×销售比重，分别在加权边际贡献率行的B13、C13、D13单元格中输入“=B9×B12”“=C9×C12”“=D9×D12”，对E13 单元格进行单击“自动求和”，计算出全年加权边际贡献率。保本销量=保本销售额/单价，分别在保本销量行的B14、C14、D14单元格中输入“=B15/B5”“=C15/C5”“=D15/D5”。保本销售额=综合保本销售额×销售比重，分别在保本销售额行的B15、C15、D15单元格中输入“=$E$16×B12”“=$E$16×C12”“=$E$16×D12”。依据公式“综合保本销售金额=全年固定成本/全年加权边际贡献率”，在E16单元格中输入公式“=E7/E13”。依据“利润=综合保本销售金额×全年加权边际贡献率—全年固定成本”，在E17单元格中输入公式“=E16×E13―E7”。

最后，设置输出结果。在B18单元格中输入=“盈亏平衡点：”&ROUND（E16，1）&“元”。

在表3的基础上，在单元格G5、G7、G9中继续添加3个微调器，目的是为了动态观察固定成本、单位变动成本和销售单价对综合盈亏平衡点的影响，使决策更快捷。右击数值调节钮设置控件格式，最小值设为0，最大值设为200，步长为1， 这样能把变动幅度控制在―50%至150%，“单元格链接”分别设置为G5、G7、G9。在H5单元格中输入“=G5/100 ―50%”，H7、H9复制H5公式即可。

至此，一个可以对单价、单位变动成本、固定成本三个变量进行动态调整的多品种本量利分析模型就完成了。通过调整变量，帮助管理者做出合理的决策。

在当前状态下，企业盈亏平衡点的综合保本销售额为1200000 元，三种产品的保本销售量分别是60000件、15000件、6000件，保本销售额分别是600000元、300000元、300000元，如表4所示。当前三种产品的实际销量均大于各自的保本量，共实现销售收入2000000元，大于综合保本销售额1200000元，表明企业处于盈利状态。

本文利用Excel对多品种下的本量利分析，分别进行了联合单位法和综合边际贡献率法模型的设计与实现，从计算结果可以看出，两种计算模型的计算结果是一样的。第二种方法中还加入了微调器，便于动态观察各因素对盈亏平衡点的影响，能够极大地提高工作的效率与效果。本文案例只有三种产品，稍加改进就可推广到更多品种的盈亏平衡分析中，可见，熟练使用Excel进行本量利分析，能在经营中提高决策的效率和质量。（作者单位：洛阳理工学院会计学院）