|
2015-02-07
EXCEL梦想之家
EXCEL图表 - 动态 - 盈亏平衡分析 “赵钱孙李，周吴郑王，冯陈楚魏，切糕沾白糖” 定场诗一句，跟今天所讲内容一点关系也没有！
今天周末，我们来讲个大的 - 本利分析法。
假设下图，是一家制鞋企业的量本利数据。可变成本会包括象制鞋的原料，生产工人的工资等等，它们是随生产量的变化而变化的。不变成本（固定成本）如管理人员工资，设备折旧费，这些通常与产量无关，换句话说即使没有产量这些费用都会产生。
上图中引入了一个概念 - 边际贡献。 单元位边际贡献是产品的销售单价减去单位可变成本，也可以简单地将它理解为，它是生产一个产品对利润的“贡献”能力。边际贡献是收入减去可变成本之后的数值，如果它再减去固定成本的话就是利润。
盈亏平衡是指总收入等于总成本，此时利润为零。盈亏平衡点时的销量在本利分析中有着重要意义，它的简算方法是固定成本除以单位边际贡献。
了解以上基本概念之后，我们可以开始作图了。
当销售量为2000时，边际贡献为12万，固定成本48000，利润72000
由于默认行列顺序不对，选中图表，点设计选项卡下的切换行列。
图表上选中边际贡献的系列，鼠标右键，设置数据系列格式。
在标记选项中，设置标记类型为无。
在“标记填充”选项中，设置其为无填充。
在线条颜色选项上，设置其为橙色实线。
以上操作目的是在数据点间加连接线，同时去除数据点格式，使其看起来是直线（如下图）。
重复以上操作，把固定成本系列加红色实线，利润系列加黄色实线。
选中X轴，鼠标右键，设置坐标轴格式。
坐标轴选项中，将最大值设成固定 2000。
设置完成后，如下图所示。由下图可以看出，销量（X轴）为0时，边际贡献（橙色线）也是为0，利润是负的4万+; 随着销量增加，橙色线在增加，利润的负值在变小，值到销量等于800时，橙色线（边际贡献）与红色线（固定成本）Y值相等，此刻利润刚好为0，这里就是盈亏平衡点。
准备垂直参考线的作图数据：如果需要在平衡点的位置加垂直线，最少需要两个点，本例会使用四个点（为什么是需要四个，随着作图的继续，您就会明白用意）。由于这条线垂直于X轴，所以四个点的X值全部是800，由图上可以看出，Y值分别应该是14000,48000,0,-6000， 以此为依据，在表格上输入四个点的作图数据（见下图左侧）。
图表上按鼠标右键，选择数据。
点添加来添加刚输入的垂直线的作图数据
此刻垂直线已经添加到图表中。修改Y轴的最大，最小值以使图表看起来美观些。 选中Y轴，鼠标右键，设置坐标轴格式
坐标轴最小值设成-60000，最大值设成140000。
选中平衡点参考线，鼠标右键，添加数据标签。
选中数据标签，鼠标右键，设置数据标签格式。
设置标签中显示，X值和Y值。
选中数据标签，设置标签的显示在数据点的上方。
单独选中最上，最上两个数据点标签，鼠标右键，选删除。
注：“单独选中”数据点是指左键点标签两次，但不是双击左键。
为数据点标签添加边框，加填充色。以便其显示得更清楚。
单独选中中间的固定成本线的交叉点，鼠标右键，设置数据点格式。
标记类型选，内置的方形。
填充色设置为白色纯色填充。
重复上述数据点的格式设置，为另一外与利润的交叉点设置同样格式。
设置完成后，平衡点的参考线的样子，如下图所示：
这条垂直参考线的作图数据，如是下图中左侧的表中所示：
在图表上按鼠标右键，在选择数据中添加数据如下。
按第七步的方法，为当前销量的参考线添加标签和格式，此处不再赘述。
|
|
|
本利分析法：它的全称是产量成本利润分析，也叫保本分析或盈亏平衡分析，是通过分析生产成本、销售利润和产品数量这三者的关系，掌握盈亏变化的规律，指导出企业选择能够以最小的成本生产最多产品并可使企业获得最大利润的经营方案（此解释来自百度）。
