【作　　者】
侯志才

【作者单位】
（东华理工大学经济与管理学院 江西抚州 344000）

【摘　　要】

【摘要】 本文针对企业多产品生产情况下本量利分析的复杂性，用EXCEL软件构建多产品本量利分析模型，借助规划求解等分析工具，可以快速直观地分析企业生产组合的盈亏平衡情况，设置盈亏平衡点，为企业调整生产进行决策提供帮助。
【关键词】 多产品   本量利   平衡点   规划求解

经济活动中企业的生产行为极为复杂，一般企业都生产多种产品，在产品成本构成不同且成本会随市场波动的情况下，手工方式对企业进行盈亏临界分析显得有点力不从心，且工作量巨大。建立多种产品本量利分析模型，利用EXCEL软件规划求解和微调工具，可以为企业的目标利润预算管理及其他工作提供帮助，提高企业财务人员的工作效率及企业决策的有效性。
一、企业多产品本量利分析的基本思路和方法
本量利分析（CVP分析）是针对企业利润盈亏平衡的一种分析方法，主要分析企业生产过程产品的数量、生产成本和销售利润三者之间的关系。
企业对单一产品利润盈亏平衡分析，主要考察在生产成品，生产数量为多少时可以达到盈亏平衡，相关公式如下：
利润=销售收入-总成本=销售收入-（变动成本+固定成本）=边际贡献-固定成本 （1）
由式（1）来看，企业要想实现盈利，那么产品的边际贡献至少要等于固定成本。
从另外一个角度来看，企业可以据此来推算应该达到什么样的业务量才能弥补所有的成本，即实现盈亏平衡，相关公式如下：
盈亏平衡点=固定成本/（产品单价-单位变动成本）
 （2）  
在多产品的情况下企业进行盈亏平衡分析的方法主要有销售金额配合法、联合单位法、分算法、顺序法和主要产品法等，这些方法的核心思想都基于CVP分析法，并可以运用式（1）、式（2）进行拓展分析。
本文主要采用销售金额配合法构建分析模型，该模型的基本思路为：计算销售比重→计算平均边际贡献率→计算保本销售总额→计算各种产品的保本销售额和保本销售量→计算利润。
二、多产品的EXCEL本量利模型构建分析
（一）背景资料
以某公司2013年生产资料为基础构建分析模型。该公司2013年在生产经营玩具汽车的同时，还生产经营玩具飞机和电动汽车，三种产品的有关资料见下表：

企业主要想分析各产品在处于一个什么样的水平下可以实现企业全年的盈亏平衡，即各产品在盈亏平衡点时的销售数量和销售金额。
（二）建模分析
1. 建立模型基础资料区。多种产品的本量利分析模型需要企业提供各产品的基础资料，包括产品的销售数量、销售单价、单位变动成本以及企业总的固定成本，根据原始资料构建模型的基础资料区，如图1所示。
2. 构建模型分析区。根据多种产品本量利分析的基本原理，需要构建出模型中的几个关键指标，包括销售比重、加权边际贡献率和综合保本销售额。各指标在模型中的勾稽关系可以用EXCEL公式描述。
（1）单位边际贡献=销售单价-单位变动成本，模型中玩具汽车的单位边际贡献B7单元格填入“=B4-B5”，单元格C7、D7可以用公式填充工具填入“=C4-C5”、“=D4-D5”。
（2）边际贡献率=单位边际贡献/销售单价，模型中玩具汽车的边际贡献率单元格B8填入“=B7/B4”，单元格C8、D8可以用公式填充工具填入“=C7-C4”、“=D7-D4”。
（3）边际贡献总额=单位边际贡献×销售数量，模型中玩具汽车的边际贡献总额单元格B9填入“=B7×B3”，单元格C9、D9可以用公式填充工具填入“=C7×C3”、“=D7×D3”。
（4）销售收入=销售单价×销售数量，模型中玩具汽车的销售收入单元格B10填入“=B4×B3”，单元格C10、D10分别填入“=C4×C3”、“=D4×D3”，全年的销售收入可以用求和公式计算得出，单元格E10填入“=SUM（B10：D10）”。
（5）销售比重=销售收入/全年销售收入，因此各产品的销售比重所在单元格公式分别为“=B10/E10”、“=C10/E10”、“=D10/E10”，建模过程中可以公式填充工具填充。
（6）加权边际贡献率=边际贡献率×销售比重，模型中各产品的加权边际贡献率所在单元格分别填入“=B8×B11”、“=C8×C11”、“=D8×D11”。全年所有产品的加权边际贡献率可以通过求和计算得出，单元格E12填入求和公式。
企业对产品实行本量利的分析目的是了解在盈亏临界点的产品销售组合情况，进而可以通过调整产品的组合以达到利润最大的目的。因此模型中的目标指标为综合保本销售额及各产品的保本销售额、保本销售数量和利润。
综合保本销售额=固定成本/加权边际贡献率，E6单元格填入公式“=E6/E12”，根据计算出来的综合保本销售额可以计算各产品的保本销售额及保本销售数量，保本销售额=综合保本销售额×销售比重，因此B14单元格填入“=E13×B11”，C14、D14单元格用公式填充工具填充；保本销售数量=保本销售额/销售单价，因此单元格B15填入公式“=B14/B4”，C15、D15单元格同理填入。
模型中另一个关键指标“利润”，企业通过模型可以测度利润的情况，设定利润的目标值来推测出产品的销售额和销售数量，可以用来年底预算的制定。根据多产品本量利的计算思路，企业多产品利润计算公式为：全年利润=综合保本销售额×全年加权边际贡献率-固定成本，因此模型中利润所在的单元格E16填入公式“=E13×E12-E6”。
通过单元格勾稽关系的构建，多产品本量利模型基本框架构建完成。但是该模型中单价、单位变动成本等因素无法变化调整，因此在模型中加入微调控制按钮，用来控制各产品的销售单价、变动成本及固定成本的变化。
由于篇幅所限，本文只列举产品销售单价变动的微调按钮设计，利用EXCEL的“窗体工具”在F3单元格中划出“微调按钮”。我们设置控件格式，在“单元格链接”栏中填入“F4”，F4单元格用存放“微调按钮”每次变动时所产生的数值，为了使变动的幅度在-50%至100%按照百分比的形式变化，把参数“最大值”设置为150，因此在单元格G4中填入“=F4/100-50%”，把G4单元格格式设置成百分比形式。在玩具汽车销售数量B4单元格中填入公式“=12.5×（1+G4）”，这样通过“微调按钮”的变动可以控制销售单价的变化。各产品的销售单价、单位变动成本的变化可以按照上述方法同理建立。构建好的模型如图1所示：

三、模型应用分析
根据该企业2013年度的资料，可以算出在当前的销售数量、销售单价等条件下，企业的保本销售额为8 783 132.53元，即各产品的销售额分别要达到4 518 072.29元、1 734 939.76元、2 530 120.48元才能实现企业的盈亏平衡，如图2所示：

在以目标利润为导向的企业预算管理模式下，管理者首先通过科学预测确定企业应实现的目标利润，其他预算都以目标利润为导向，围绕着目标利润的实现进行编制。目标利润是企业管理过程的主线，这就使企业各分支部门的目标更加明确，各项经济活动也都围绕实现目标利润而进行。预算目标的设定需要科学性和合理性，这就需要借助EXCEL规划求解来科学分析。假设该企业想要在2014年实现50万元的利润，利用“规划求解”工具可以测算出各产品的销售量。具体测算步骤如下：
以EXCEL2007为例，点击“数据”菜单找到“规划求解”分析工具，在“规划求解参数”对话框需要填入相关的参数。根据假设，该企业的2014年的目标利润为50万元，因此设置目标单元格“E16”，即利润所在的单元格，目标值为500 000元，根据管理需要还可以选择其他的目标方式，如“最大值”和“最小值”。“可变单元格”是指会对目标单元格数值产生影响的单元格，本例中选择“综合保本销售额”所在单元格“E13”作为可变单元格。根据企业的管理需要，假设每种产品的销售不能小于1 000，因此在“约束”条件框加入约束条件，如图3所示。参数设置好之后点击“求解”，计算结果详见图4。

通过以上分析，以最后的求解结果来看企业要想实现50万元的年利润，至少要达到10 246 987.95元的综合销售额，其中玩具汽车、玩具飞机和电动汽车分别至少要销售421 686件、33 735件、8 434件。不难看出，通过这个对目标利润的分解，生产销售部门会有更好的目标，可以对照这样的目标进行生产销售，同时有利于对各部门进行绩效考核。
但是如果在企业的目标利润测算不准的情况下，企业的生产部门按照这个目标有可能根本无法完成生产销售任务。因此需要针对目标利润进行调整，根据本量利的分析原理，销售单价、单位变动成本、固定成本三者之中的任何一个因素的变动对目标利润都可以产生影响。例如通过减低1%的产品单价，目标利润就变为431 848.61元，三种产品的销售数量及销售额也随之发生变化。通过对各影响因子的逐步测算，直到最合理的目标为止。如图5所示：

四、结语
企业不仅可以利用多种产品的EXCEL本量利模型进行目标利润预算，还可以将其作为相关部门绩效考核的工具，如果涉及更多产品的本量利测算，对该模型进行扩展即可。
由于篇幅的原因，该模型还不是非常地完善，例如变动因素微调做得不够细，没有细化到各产品的变动因素之中去。该模型还可以加入动态面积折线图，更直观地反映出企业在盈亏临界点位置时，产品销售数量、成本和利润的情况。
主要参考文献
张瑞君.计算机财务管理.北京：中国人民大学出版社，2001