Excel数据分析技巧：如何用动态图表进行“量本利分析”

编按：“量本利分析”也叫本量利分析、盈亏平衡分析、保本分析，简称为CVP分析。通过它的一系列叫法，我们就可以看出，这个分析法是对于控制成本的一种数学分析方法。使用Excel进行量本利分析，最好的方式就是图表。

量本利分析是根据业务量（指产量、销售量、销售额等）、成本和利润三者之间的互相依存关系，进行综合分析的。用以预测利润，在企业经营决策、利润规划、成本目标的确定与控制等方面应用很广，其中心内容是盈亏临界点分析。

如下图所示：

从图示中，我们可以根据调整“单价”的滚动条表单控件，得到一个动态图表，从中可以直观的看出“盈亏平衡点”以及“平衡点上的参数”。这样交给领导做定价决策就很方便了。

下面一起来看看它是如何通过Excel实现的吧。

按上方的内容，建立测算数据区域如下图所示：

STEP2：建立滚动条，控制单价和销量

在工具栏——开发工具——插入——表单控件：滚动条，插入控件。鼠标选中控件，点击鼠标右键，弹出命令菜单，选择“设置控件格式”；在弹出的窗口中，按下图配置参数，如下：

按“确定”按钮，此时，我们使用滚动条的时候，就会发现【单价】字段会随着滚动条的调整而变化；同理制作【销量】的滚动条，如下设置：

STEP3：建立图表数据区域

制作完后，如下图所示：

STEP4：制作图表

选中A16:C19单元格区域，插入图表——带直线的散点图；

得到下面的图表：

鼠标对图表，点击右键，在弹出的命令菜单中选择“选择数据”，按下图箭头的地方，点击“切换行/列”，如下所示：

STEP5：插入“盈亏平衡点”

依然对图表，点击鼠标右键——选择“选择数据”，点击“添加”新的系列；

在弹出的窗口中，按下图设置参数引用，如下：

及此，图表部分制作完毕。具体的美工部分这里就不展开讲了。

写在最后：

制作并使用一次“量本利分析”后，我们应该从中学到一些关于数据应用的知识，这也是个人成长的一个很重要阶段，思考后才会有所得。

1、盈亏平衡和什么有关系？

通过图表滚动条的变化，我们发现，图表内容和“单价”有关，和“销量”无关！这是因为，销量的增加，会导致“总变动成本”的增加，而且是成正比增加，这个比例是定值；而真正影响盈亏平衡的因素，是“单价”和“单位变动成本”。

2、销量在经营中的意义何在？

如果说盈亏平衡与销量无关，那么销量在经营数据中的意义到底是什么，笔者认为是“体量”。一个公司的销售渠道不可能无限扩展的，一定是在市场上有着自己的比例，即：市场份额。我们通过上面的图表工具，测算出“盈亏平衡销量”，那么经营者就要考虑：

a、产能允许生产出这个数量的产品吗？

b、我们是否可以卖掉这个数量的产品？

如果不能满足，那么如何调整策略来满足这个条件，那么销量的作用就是这个了，它不是决定因素，而是决策因素。

编后语

以上就是对“量本利分析”的一个基本总结和应用分享，感兴趣的同学们可以自己试着做一做，分析一下当前自己公司的销售单价，再结合公司的历史数据、市场行情、原材料成本等等一些列数据做一份盈亏平衡分析报告，相信那时你就是领导眼中——那个最靓的仔。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

相关推荐：

将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招，速收藏

Power Query的数据替换技巧比Excel函数更万能！

版权申明：

本文作者E图表述；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。