妙用动态散点图,精确计算盈亏平衡点..docVIP

很多企业在经营过程中，经常需要核算某种产品的盈利情况，而计算产品盈利能力时，往往就涉及到盈亏平衡点点的问题，就是在现有的资源条件下，某种产品的产量或者销量达到什么规模，才能够保住成本，也就是常说的盈亏平衡点的问题，以前大家往往是通过手工计算，十分麻烦，也不直观，而且当一些企业生产经营环境变化时，也不容易调整，实际上利用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）。这样一个盈亏平衡点计算图表雏形就做出来了，在图中盈亏平