基于Excel的盈亏平衡分析决策模型创新研究

张霞

【摘 要】 为了加强对利润的管理，使企业能顺利实现财务管理目标，财务管理人员一般都要进行盈亏平衡分析。文章在介绍盈亏平衡分析理论的基础上，详细阐述了利用Excel工具建立盈亏平衡分析决策模型和创建可调动态盈亏平衡分析图形的过程和方法，以更好地为决策者和管理者服务，提高管理决策模型的有效性，实现财务管理向智能化发展。

【关键词】 Excel； 盈亏平衡分析； 模型

中图分类号：F235.19 文献标识码：A 文章编号：1004-5937（2015）19-0077-03

一、盈亏平衡分析的基础理论

盈亏平衡分析是在变动成本计算模型的基础上，研究企业在一定期间的成本、业务量和利润三者之间变量关系的一种专门方法。这一分析方法是在成本按性态进行划分的基础上发展起来的，主要用于确定公司达到盈亏平衡时的销售水平，即分析销量高于或低于这一平衡点时的盈利和亏损状况。在产品的生产或销售中，存在固定成本与变动成本这两种不同性质的成本。固定成本是不随销量变化的成本，包括行政管理人员的工资、办公费、固定资产折旧费、职工教育培训费等。变动成本是随销量变化的成本，一般包括直接材料费、人工费及设备的维修费等。产品的盈亏平衡点是指一种产品提供的边际贡献正好抵销固定成本时或产品的全部销售收入等于全部成本时的销量或销售收入。由于产品的销售收入与销量成正比，因此，有时人们也从销售收入出发而不是销量出发，将使利润等于零的销售收入称为盈亏平衡点。为明确起见，在可能发生混淆的时候，将使利润等于零的销量称为“盈亏平衡销量”，将使利润等于零的销售收入称为“盈亏平衡销售收入”。

如果将盈亏平衡销量表示为Q0，固定成本为F，销售单价为p，单位变动成本为v，盈亏平衡销售收入为R0，边际贡献为k，盈亏平衡销量和盈亏平衡销售收入可以使用如下公式计算。

二、在Excel中建立盈亏平衡分析决策模型的基本原则

在Excel中建立盈亏平衡分析决策模型的基本原则一般包括以下三个方面：

（一）正确性

模型的逻辑必须正确与完备。在一个单元格中输入的公式应该能够计算出正确值，尤其在使用IF（）函数进行嵌套计算时，应该能正确计算出各个条件下的结果数值，正确进行四舍五入操作。

（二）可读性

模型的基本含义与结论应该便于创建者或其他使用者正确理解。模型应提供多方面的配套分析数据和图形，以便决策者从各个方面去深入理解它所提供的含义与性质。

（三）易维护性

模型应该让使用者在问题发生变化时可以方便地进行修改。应将决策问题中的所有已知参数集中安排在模型工作边的一个单元格区域，并设定决策变量单元格、中间变量单元格和目标单元格。在中间变量和目标变量的计算公式中，应使用参数单元格、决策变量单元格、中间变量单元格和函数，不应直接使用参数值和中间计算值。

三、盈亏平衡分析决策模型的建立

运用Excel电子表格建立盈亏平衡分析决策模型的方法很多，如使用公式计算、单变量求解工具、规划求解工具和模拟运算表的查表加内插值法。下面介绍单变量求解工具和公式计算的方式建立盈亏平衡分析决策模型。

（一）利用单变量求解工具计算盈亏平衡销量

假设宏远公司生产某种零件用于智能手机的生产，固定成本为2 700元，单位变动成本为0.15元，销售单价为0.38元，当前销量为260件。建立盈亏平衡分析决策模型必须先计算出盈亏平衡销量，根据盈亏平衡分析的基础理论，在Excel中建立盈亏平衡分析基础数据，在D6单元格输入公式“=D5*D4”，在D7单元格输入公式“=D2+D3*D5”，在D8单元格输入公式“=D6-D7”，分别计算销售收入、总成本和利润。利用单变量求解工具，在打开的单变量求解对话框中，对参数进行设置，其中目标单元格为D8单元格，目标值为0，可变单元格为D5单元格，计算公司收支相等、利润为0时的销量，即盈亏平衡销量，如图1所示。

（二）利用公式计算建立盈亏平衡分析决策模型

1.建立基本模型

基于以上案例，在Excel中建立盈亏平衡分析基础数据，在E7单元格输入公式“=E6*E5”，在E8单元格输入公式“=E3+E4*E6”，在E9单元格输入公式“=E7-E8”，在E11单元格输入公式“=E3/（E5-E4）”，在E12单元格输入公式“=E11*E5”，在C15单元格输入公式“=C11&"="&ROUND（E11，0）”。

2.采用模拟运算表准备绘图数据

以销量作为自变量，同时对销售收入、总成本和利润三个因变量函数进行一维模拟运算，注意，在单元格I3、I4中输入自变量的各个值，因为本案例是一个线性问题，所以只需输入自变量销量的起始值（0）和终止值（40 000），在单元格J2、K2、L2分别引用单元格E7、E8、E9。

进行灵敏度分析即求解模拟运算表的步骤是：选取单元格I2：L4，执行“数据”菜单下的“模拟运算表”命令，在“模拟运算表”对话框中的“输入引用列的单元格”文本框中，选择模型中的单元格E6，并将计算结果放置在单元格J3：L4区域。同理在单元格P3：Q14区域建立模拟运算表，即单价随盈亏平衡销量的灵敏度分析。

3.利用模拟运算表的数据创建图表

图形能够使人们更直观、更清晰地理解模型的信息。图形虽然可用折现图、饼图、柱形图，但在大多数情况下采用的是XY散点图而不是折线图。因为同样坐标值在XY散点图上的点是重叠的，而折线图则不会重叠。一般而言，经常使用XY散点图来反映自变量是数值的函数值图形。利用单元格I3：L4的数据绘制一个XY散点图的图形，然后编辑图形，对X轴和Y轴的刻度固定，以图形的方式反映销售收入、总成本和利润三者间的关系。图形的效果是图3所示的三条直线。

4.在图形上添加垂直参考线和参考点

利用单元格E11的数据在图形上添加盈亏平衡销量的垂直参考线；利用单元格E11：E12的数据添加盈亏平衡销量，销售收入等于总成本即利润为0时的参考点；利用单元格E6的数据添加反映当前销量时的销售收入、总成本和利润的垂直参考线，当前销量（E6）由小变大时，反映当前销量的销售收入、总成本和利润的垂直参考线由左向右移动，反映了由亏损变为盈利的过程；利用单元格E7、E8、E9的数据添加反映当前销售收入、总成本和利润的参考点；利用E11单元格的数据，添加盈亏平衡销量在X轴的参考点，反映盈亏平衡销量。构造垂直参考线和参考点的坐标数据时，尽量采用单元格的引用，因为垂直参考线和参考点要随着模型中销量和单价的变化而变化。

5.使用IF函数得到决策结论文字

IF函数是常用的分支函数，使用IF（）函数能够判断数据的不同情况，得到不同的决策结论。在单元格C14中输入公式：“=C6&"="&E6&"时，"&IF（E9>0，"盈利！盈利额="&E9，IF（E9<0，"亏损！亏损额="&E9"，盈亏平衡"）”，在C14单元格得到决策结论。最后，在图表区添加一个文本框，在该文本框中输入公式“=$C$14”，即可在图表区显示盈亏平衡分析的决策结果，如图2所示。

6.制作改变模型中参数值的控件按钮

在图表中添加两个微调项控件，使得销量单元格E6从0到40 000按步长1 000变化，单元格E5从0.25到0.4按步长0.01变化，这样就制作成了盈亏平衡销量和单价可调图形，且在文本框中显示“盈利”、“盈亏平衡”、“亏损”等决策结论的文字。需要注意的是销量的最大值是40 000，而微调项控件最大值只能是30 000，因此在对话框中的值缩小100倍，然后在模型工作表的单元格E6中输入公式“=F6*100”；同理，单价的最小值是0.25，而微调项控件最小值只能是0，因此在对话框中的值扩大100倍，在模型工作表的单元格E5输入公式“=F5/100”。

7.组合控件、文本框和图表

将控件、文本框、图表组合在一起制成盈亏平衡可调图形，结果如图3所示。

四、盈亏平衡分析决策模型的评价

（一）优点

首先，利用Excel建立的盈亏平衡分析决策模型能够准确、快捷地计算出盈亏平衡销量和盈亏平衡销售收入，并得出决策结论，即销量达到多少时，企业盈利，盈利额为多少，为公司决策者进行盈亏平衡分析提供了有力的依据和数据支持。其次，所建立的模型将所有已知的参数如固定成本、单位变动成本、单价和销量安排在一个固定的单元格区域，模型中所有可变单元格的计算公式都统一使用了参数单元格、中间变量单元格和函数，没有直接使用参数值和中间计算值。因此，当已知参数发生变化时，只需改变已知参数单元格中的数值即可，模型同样会第一时间作出反应，计算出盈亏平衡销量和盈亏平衡销售收入，并得出决策结论。最后，利用Excel中的图形工具为建立管理决策模型提供了一种极为有效的工具，特别是可调图形不但使模型的决策过程与结果能动态表现，而且制作极为简便，根本不需要任何编程。该模型中制作的XY散点图能直观地反映销售收入、成本和利润三者之间的关系，体现了模型分析能力与图形直观的人机界面的完美结合，决策者可以在图形上调节参数，观察反映决策结果的曲线及其特征的变化，从而提高管理决策模型的有效性，便于决策者从各个方面对盈亏平衡进行分析和决策。

（二）缺点

一是以上建立的盈亏平衡分析模型是建立在单一产品盈亏平衡分析基础上的，然而，现实生产过程中，大多数企业都同时进行着多种产品的生产和经营，由于各种产品的销售价格、单位变动成本、固定成本都不一样，进行盈亏平衡分析时不能借助于产品的实物数量指标，因其实物数量不能简单地相加，必须利用能够综合反映各种产品销售量的金额指标，即销售收入。因此，多品种盈亏平衡分析方法与单一品种盈亏平衡分析法不同，该模型的适用具有一定的局限性，只适用于单一产品的盈亏平衡分析。

二是该模型只对销量和单价进行了变动分析，当模型中的一系列因素发生相互关联的变化时，如公司外部环境条件发生变化，企业拟采取某些措施等都会使单价、固定成本、单位变动成本和销量发生变化，此时，以上建立的盈亏平衡分析模型就不能适用了。

（三）模型的改进

1.建立多品种盈亏平衡分析模型

在遵循单一产品的盈亏平衡分析模型的基础上对多种产品进行盈亏平衡分析，应根据不同情况采取相应的方法进行确定。目前，多品种的盈亏平衡分析主要方法是销售金额配合法，销售金额配合法是根据各种产品的销售额占全部销售额的比重，分别计算出全部产品的综合盈亏平衡销售收入和各种产品的盈亏平衡销售收入。

2.建立盈亏平衡分析多因素变动分析模型

第一，可以为固定成本、单位变动成本、单价和销量都添加窗体控件，即为各因素建立“滚动条”控件，利用用户图形接口工具建立多因素变动分析区，这样能够非常方便、直观地进行多因素变动分析，决策者只需调节模型中的滚动条即可得到盈亏平衡分析的结果。上述利用“滚动条”按钮工具建立的多因素变动分析模型，一个条件的变动需要有一个工作表来反映其结果，不便于分析。第二，利用Excel中提供的一种管理假设分析方案的工具——方案管理器就能很好地解决这个问题。此工具可以根据使用者或决策者输入的多种假设条件，建立多个方案，每个方案都可以假设一组条件，根据这些条件，系统自动产生相应的多种结果，并将输入值和结果作为方案的总结报告保存。通过查看方案总结报告，便可看到假设条件对目标值的影响。

【参考文献】

[1] 刘兰娟.财经管理中的计算机应用[M].上海：上海财经大学出版社，2014.

[2] 王舰.盈亏平衡分析的三种实现方法[J].会计之友，2009（9）：48-49.

[3] 杜茂宝.利用Excel建立项目投资动态盈亏平衡分析模型[J].财会通讯，2007（2）：69-70.

[4] 任红松.投资项目动态盈亏平衡分析及Excel计算方法[J].技术经济与管理研究，2007（6）：10-11.