盈亏平衡分析图的画法

很多企业在经营过程中，经常需要核算某种产品的盈利情况，而计算产品盈利能力时，往往就涉及到盈亏平衡点点的问题，就是在现有的资源条件下，某种产品的产量或者销量达到什么规模，才能够保住成本，也就是常说的盈亏平衡点的问题，以前大家往往是通过手工计算，十分麻烦，也不直观，而且当一些企业生产经营环境变化时，也不容易调整，实际上利用Excel的散点图就可以制作出非常直观精确的散点图，并且结合Excel的其他功能，可以实现动态调整，下面就以某公司甲产品的盈亏平衡点散点图制作为例，来介绍具体的操作方法。本文实例介绍表格可从/cfan/200923/ykphd.rar下载到。(山东朱晓燕)

设计盈亏测算表

由于每个企业的生产经营状况均不同，而测算盈亏平衡点，就必须根据企业的具体实际情况来编辑一份盈亏测算表，主要涉及到企业的固定成本、变动成本、销量、销售单价等因素，比如甲产品的固定成本为11200千元，每吨产品的变动成本为12千元，平均销售单价为40千元/吨，通过以上这些资料，我们可以得到如下关于盈亏的计算公式，即：销售收入＝销量×销售单价、总成本＝固定成本＋单位变动成本×销量、盈亏平衡点销量＝固定成本÷（销售单价－单位变动成本）、盈亏平衡点销售收入＝盈亏平衡点销量×平均销售单价。

从以上公式可知，盈亏平衡点实际上就是销售收入与总成本相等时的数值，在图表上就表现为两者的交叉点，因此要设计盈亏平衡点动态图，就必须确定总成本、销售收入以及盈亏平衡点坐标值，而依据以上公式，就可以通过计算得到这些数据。

启动Excel 2007，新建一个工作表，将其命名为“盈亏平衡点测算表”，在此表的B9:K9

区域内输入序号、固定成本、单位变动成本、月销量、销售单价、销售收入、变动成本、总成本、盈亏平衡点x轴值、盈亏平衡点y轴值等内容，然后在此表的第一行的C10中输入固定成本数值，D10中输入单位变动成本值，E10中输入月销量初始值为0，F10中输入销售单价，G10中输入计算销售输入的公式“=E10*F10” ，H10中输入计算变动成本的公式“=E10*D10”，I10中输入计算总成本的公式“=C10+H10” ，并将这三个公式复制对应的下面G11、H11、I11单元格中，J10中输入盈亏平衡点X轴第一个值0、K10中输入计算盈亏平衡点Y轴第一个值公式“=F11*J11”；在第二行的C11、D11、F11中输入上面的固定成本、单位变动成本以及销售单价值，E11中输入公司最大月销量值，如2000，J11中输入计算

盈亏平衡点X轴第二个值公式“=C11/(F11-D11)”，K11中盈亏平衡点Y轴第二个值公式“=K10”；在第三行的J12中输入盈亏平衡点X轴第三个值公式“=J11”，K12中输入盈亏平衡点Y轴第三个值0（见图1）。

(图1)

通过以上计算，我们就可以得出销售收入的两个点坐标值，如（0、0）、（2000、80000），总成本的两个坐标点值，如（0、11200）、（2000、35200），而盈亏平衡点则由三点确定，如（0、16000）、（400、16000）、（400、0）。

同时为了便于直观地说明盈亏平衡的计算结果，在B13中输入文字“测算结果说明：”，在其后的D13中输入公式“="当产品销售价格="&F11&"千元，单位变动成本="&D11&"千元时，盈亏平衡点销量="&J11&"吨"&",销售收入="&K11&"千元"”，此公式的作用是当在F11、D11、J11、K11中显示相应数据时，会自动通过文本符连接符号&组合在一起，如显示“当产品销售价格=40千元，单位变动成本=12千元时，盈亏平衡点销量=400吨,销售收入=16000千元”。

利用计算数据，制作盈亏平衡点图

需要制作盈亏平衡点图时，单击“插入”菜单，从弹出的功能区中选择“图表”中的散点图，然后选择其中的“带直线的散点图”类型（见图2）。当插入一个图表后，单击弹出的“图表

工具”菜单下面的“设计”子菜单中的“选择数据”选项，在弹出的“选择数据源”对话框中，单击“添加”按钮，在随之弹出的“编辑数据系列”对话框中，进行如下设置：“系列名称”为“总成本”，“X轴系列值”为“=盈亏平衡点测算表!$E$10:$E$11”，“Y轴系列值”为“=盈亏平衡点测算表!$I$10:$I$11”。

(图2)

采用同样的方法，添加另外两个系列，分别为：销售收入，其X值为“=盈亏平衡点测算表!$E$10:$E$11”，Y值为“=盈亏平衡点测算表!$G$10:$G$11”；盈亏平衡点，其X值为“=盈亏平衡点测算表!$J$10:$J$12”，Y值为“=盈亏平衡点测算表!$K$10:$K$12”，最后关闭此对话框（见图3）。这样一个盈亏平衡点计算图表雏形就做出来了，在图中盈亏平衡点就为总成本与销售收入的交叉点，其坐标值为（0、400），即销量为400吨时达到盈亏平衡（见图4）。

(图3)

(图4)

妙用控件，将盈亏平衡点图动态化

以上制作的盈亏平衡点图仅仅是在已知条件下编辑的，如果企业的生产经营环境变化了

呢？比如企业经营过程中，销售价格以及变动成本上下浮动是非常正常的，虽然通过修改源数据计算表可以达到此目的，但是比较麻烦，实际上利用几个控件就可以非常方便地解决此问题，这样就可以根据实际生产经营情况，调整相应数据，并及时得到盈亏平衡点。

具体操作方法如下：单击“插入”菜单，从“文本”功能区中选择“文本框”选项，然后选择其中的“横排文本框”选项，在图表上划出一个文本框，并在其中输入“销售价格”。接着单击“开发工具”菜单，从弹出的“控件”功能区中，选择“插入”选项，在随之弹出的“表单控件”选项列表中选择“滚动条”控件，将其拖放到“销售价格”文本框的后面（见图5）。

(图5)

右击插入的滚动条控件，从弹出的快捷菜单中选择“设置控件格式”命令，然后在弹出对话框的“控制”选项卡中，将“最大值”、“最小值”设置为销售价格的合理范围，如0～100，“单元格链接”设置为上面计算表中销售价格所在的$F$11，最后关闭此对话框。采用同样的方法，再添加一个控制变动成本的滚动条控件，其单元格链接设置为$D$11（见图6）。