妙用动态散点图，精确计算盈亏平衡点[zz]

妙用动态散点图，精确计算盈亏平衡点

 

分类： 软件应用

    很多企业在经营过程中，经常需要核算某种产品的盈利情况，而计算产品盈利能力时，往往就涉及到盈亏平衡点点的问题，就是在现有的资源条件 下，某种产品的产量或者销量达到什么规模，才能够保住成本，也就是常说的盈亏平衡点的问题，以前大家往往是通过手工计算，十分麻烦，也不直观，而且当一些 企业生产经营环境变化时，也不容易调整，实际上利用Excel的散点图就可以制作出非常直观精确的散点图，并且结合Excel的其他功能，可以实现动态调 整，下面就以某公司甲产品的盈亏平衡点散点图制作为例，来介绍具体的操作方法。本文实例介绍表格可从 http://work.newhua.com/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中显示相应数据时，会自动通过文本符连接符号& amp;组合在一起，如显示“当产品销售价格=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）。

(图6)

 

    小提示：需要注意的是，如果在 Excel2007的菜单栏没有显示“开发工具”选项，可以单击左上

            角的Office图标按钮，从弹出的选项对话框中，将“常用”类别中的“在功能区显示

           ‘开发工具’选项卡”复选项选中，然后单击“确定“按钮，关闭此对话框即可。

 

    同时为进一步让图表查阅者更直观了解关于产品盈亏平衡的准确数据信息，可以在此图表上添加一个标签控件，此控件插入到图表后，单击此控件，然后在上面的编 辑栏中输入公式“=$D$13”。
    这样当企业经营环境变化时，比如销售价格下调为39千元/吨，单位变动成本上升为14千元/吨，需要测算这种情况下产品的盈亏平衡点时，只需要拖动销售价 格、变动成本后面的滚动条，调整到合适数值，就可以在图表中直接显示盈亏平衡点，并且在图表的标签控件中显示相应的文本信息，如“当产品销售价格=39千 元，单位变动成本=14千元时，盈亏平衡点销量=448吨,销售收入=17472千元”,使用这种动态图表也可以查看任意不同经营环境下的盈亏平衡点情 况。最后修改下图表的格式、给图表添加标题以及图例，就可以得出一份非常精致、实用、智能的动态盈亏平衡点计算图表了（见图7）。

(图7)

    实际上盈亏平衡点计算图不仅仅可以应用在生产销售计算过程中，还可以应用在其他经济活动中，如金融投资、项目预算等等业务中，充分利用盈亏平衡点测算工 具，对于控制成本、提高收益是非常有益的。