浅析Excel在建立盈亏临界分析决策模型中的应用

王晓俊 WANG Xiao-jun

（深圳市第一职业技术学校，深圳 518026）

摘要： 本文主要以现代经济管理理论为依据，从技术层面介绍用Excel建立盈亏临界分析决策模型和可调动态图表的基本方法。

教育期刊网 http://www.jyqkw.com
关键词 ： EXCEL；管理决策；模型

中图分类号：F275 文献标识码：A 文章编号：1006-4311（2015）23-0038-03

作者简介：王晓俊（1976-），女，浙江丽水人，深圳市第一职业技术学校教师，中学一级，学士学位，主要从事计算机教学工作。

0 引言

现代企业在做盈亏临界分析时，很多大中型企业一般采用委托软件公司开发专业软件，但由于软件开发需要投入较大的资金、专业技术人才及开发时间等，对于小微企业来讲，并不切合实际。微软公司推出的Excel、VBA等软件，早已被国内外经济管理人员公认为强有力的信息分析与决策支持软件工具。为了节约小微企业的有限资金，缩短软件开发时间，笔者试图在EXCEL电子表格平台上开发设计出一套盈亏临界分析决策模型。下面，就此问题展开如下分析。

1 本文涉及的基本概念

①决策：是基于一定的目标，运用科学地方法、手段，从两个或两个以上的方案中筛选出最优方案分析判断过程。决策问题有结构化、半结构化和非结构化之分。

②结构化决策：是指用确定的模型或语言描述某一决策过程的环境及规则，形成决策方案，对多套方案进行比选之后确定最优决策方案。对结构化决策问题而言，只要建立了模型就可在此基础上找到最优解或满意的解，因此完全可以运用计算机完成结构化决策。这就是本文的研究课题。

③模型：是所研究的系统、过程、事物或概念的一种表达形式。模型可分为物理模型、模拟模型和数学模型三大类。数学模型即通常所说的定量模型，是在现实系统中通过数学公式来量化分析各种本质属性，并描述各种变量之间的依赖关系。数学模型能够以量化的形式对各个特征量的变化规律进行描述。

④决策模型：是为辅助决策而构建的数学模型，主要用于管理决策。近年来，运筹学的内涵不断延伸，专家学者通过研究提出了许多决策分析法，如对策论、排队论、调度模型、存贷模型、线性规则、动态规则等等。计算机系统是实施这些分析法的必备载体，它使决策方法数学化和模型化。可以利用计算机来编制重复性的数学模型，并形成管理决策，然后用Excel电子表格实现，提高效率。企业经营管理决策常用的模型有很多，下面笔者以介绍建立盈亏临界分析模型为例，从建模分析工具、所需函数、具体步骤等三方面讲解如何用Excel电子表格建立决策模型。

⑤盈亏临界点：也称损益平衡点、保本点，它是指企业当期销售收入与当期成本刚好相等、不亏不盈即达到盈亏平衡的状态。

⑥盈亏临界分析：是财务管理中的基础性分析方法，其最基本的应用领域是“本—量—利”分析，它通过成本、销量和利润三者关系的分析，找出三者之间联系的规律，从而有效地制定经营决策，为目标控制提供非常有用的方法。

2 EXCEL建模分析工具

2.1 “单变量求解”

单变量求解是通过对另一个单元格中的值进行调整，并计算指定单元格中的特定值的方法。单变量求解，需要在Excel中调整指定单元格中的值，直至与该单元格关联的公式返回符合要求的值。“单变量求解”是组成一组命令的关键部分，通常将这些命令视为工具。假设单个公式的预期结果为已知数，用来确定此公式结果的输入值为未知数，就能运用“工具”菜单中的“单变量求解”功能进行求解。在单变量求解的过程中，Excel中特定单元格中的值会不断做出调整，直至与该单元格相关联的公式返回符合要求的结果。

例如，表1中使用“单变量求解”逐渐增加单元格 B3 中的利率，直到 B4 中的付款额等于 900。

2.2 “模拟运算表”

模拟运算表实际是一个单元格区域。该表包括单/双输入模拟运算表，可以显示一个或多个公式中替换不同值时的结果。单输入模拟运算表中，可以针对某一变量输入不同的数值，观察其数值变化对公式产生了什么影响。而双输入模拟运算，则需要键入两个变量的不同值。

3 盈亏临界分析模型设计中使用的函数

IF函数在逻辑运算中比较常见。该函数执行真假值判断，即基于逻辑运算的真假值返回不同结果。通常用此函数来检测数值和公式。

3.1 函数语法

IF(logical_test,value_if_true,value_if_false)

Logical_test 是计算结果为TRUE或FALSE的任意值或表达式。譬如，“A10=100”是一个逻辑表达式，若单元格A10=100，其表达式即为TRUE，反之则为FALSE。在实际运算中，可以通过任何比较运算符得出本参数。

Value_if_true logical_test为TRUE 时返回的值。假设本参数是文本字符串“预算内”，且logical_test参数值是TRUE，那么IF函数就会相应的显示“预算内”。假设logical_test 为TRUE，而value_if_true为空，则本参数返回0（零）。若要显示TRUE，就应为本参数使用逻辑值TRUE。Value_if_true也可以是其它公式。

Value_if_false logical_test是FALSE时返回的值。假设本参数是文本字符串“超出预算”，且logical_test参数值是FALSE，那么IF函数就会显示文本“超出预算”。假设logical_test 为FALSE且Value_if_false忽略不计（也就是说value_if_true 后没有逗号），那么系统就会返回逻辑值FALSE。假设logical_test 为 FALSE且Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回0（零）。当然，Value_if_false 也可以是其它公式。

3.2 函数说明

函数IF可以嵌套七层，通过value_if_false及value_if_true 参数能构造出复杂的检测条件。

在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。假设IF的参数包含数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。），则执行IF语句时，必须逐一计算数组中的所有元素。

4 建立盈亏临界分析模型

盈亏临界分析主要用于确定企业达到盈亏平衡时的销售水平，即分析销量高于或低于这一平衡点时的盈利和亏损状况。盈亏平衡点的基本算法：假定利润为零和利润为目标利润时，先分别测算原材料保本采购价格和保利采购价格；再分别测算产品保本销售价格和保利销售价格。基本公式如下：

①按产品销售量计算：

盈亏平衡点=固定成本/（产品销售单价－单位产品变动成本）

②按产品销售额计算：

盈亏平衡点=固定成本/（1－变动成本/产品销售收入）=固定成本/(1-变动成本率)

4.1 案例分析

YH公司制造一种高质量运动鞋。公司最大生产能力为1500双，固定成本为38800元，每双可变成本为38元，当前的销量为900双，平均销售价格为92元，公司管理层需要建立一个决策模型用于盈亏平衡分析，模型应包含以下功能：

①需要计算的项目：1）单位边际贡献及边际贡献率，2）销售收入、总成本及利润，3）盈亏平衡销量及盈亏平衡销售收入；

②假定公司希望获得24000元利润，计算为达到利润目标所需要的销量及销售收入；

③提供反映公司的销售收益、总成本、利润等数据的本-量-利图形，基于图形动态反映出销量从100按增量10变化到2000时利润的调整情况以及“盈利”、“亏损”、“保本”的决策信息；

④考虑到销售价格受市场影响可能有波动，用图形模型反映销售价格从70元按增量1变化到100元时，盈亏平衡销量和盈亏平衡销售收入的相应变化。

4.2 模型设计界面

4.3 建模步骤

①新建表，计算相关指标。

在“成本管理.xls”工作簿中新建一“盈亏临界分析”工作表，分别输入相关数据，并按公式法计算出盈亏平衡销量与销售额。如图2所示。

其中：B8=B4-B5，B9=B8/B4，B10=B1*B4，B11= B5*B1+B6，B12=B10-B11，B14=B6/B8，B15=B14*B4。

②利用“单变量求解”工具计算目标利润对应的目标销量和目标销售额。

单击B12单元格→选择菜单栏中的“工具”→“单变量求解”→在弹出的“单变量求解”对话框中做如图3所示的设置。这样即可得到目标利润为24000元时的销量为1163双，销售额为106996元。如图4所示。

③建立模拟运算表。

在单元格C2：F5单元格区域中建立收入、成本和利润的模拟运算表，具体做法是：在单元格D2、E2、F2分别输入公式“=B10”、“=B11”、“=B12”，选中单元格区域C2：F5，单击菜单“数据”→选择“模拟运算表”→在弹出的“模拟运算表”对话框中做如图5所示的设置。得到的结果如图6所示。

这样即可得到不同销量时的销售收入、总成本以及利润等。修改不同的销量，其三个指标也自动调整。

④使用If函数得到决策结论。

单击A22单元格→在编辑栏中输入“="销量="&ROUND(B1,0)&"时,"&IF(B12>0,"盈利",IF(B12=0,"保本","亏损"))”→按“回车”键确认。该公式的含义是判断B12单元格的利润，如果>0，显示“盈利”，如果<0，显示“亏损”，如果=0，显示“保本”。

⑤添加微调控件，建立模型。

打开窗体控件，添加两个微调控件，分别调控销量与价格。右击微调控件，在弹出的“设置控件格式”对话框中做如图7所示的设置。

这样即可动态显示出不同销量、不同价格的收入、成本与利润的变化情况。

⑥建立动态图表。

选择C2：F5单元格区域，利用图表向导建立收入、成本、利润的XY散点图，每个曲线分别代表收入、成本、利润，可以清楚地看到三条曲线随价格或销量的变化情况，并添加如前所述的控件按钮。结果如图8所示。

其中：垂直线是盈亏平衡销量垂直参考线和当前销量垂直参考线。采用垂直参考点的图形十分有助于决策者了解利润随销售单价变化的全貌，它反映出当固定成本与单位可变成本不变而销售单价由小变大时，盈亏平衡销量由大变小，垂直参考线向左移动。

综上，通过Excel成功建立了一个盈亏临界分析决策模型和可调动态图表，决策者可以在图形上边调节参数，边观察反映决策结果的曲线及其特征的变化。该方法可以帮助小微企业提高决策分析效率、节约企业资金、缩短开发时间、降低开发风险，帮助小微企业实现降低成本的目的。管理人员甚至可以举一反三，无须专职程序员帮助，利用Excel自行建立成本决策模型、最佳产品组合分析模型、设备更新改造的投资决策模型等企业经营管理决策常用模型。

教育期刊网 http://www.jyqkw.com
参考文献：

[1]刘继伟，杨桦.EXCEL在财务管理中的应用[M].清华大学出版社.

[2]杨兵，肖燕松.EXCEL财务管理高级应用[M].中国电力出版社.

[3]陈浩.EXCEL在公司管理中的应用[M].中国青年出版社.