关键字：动态图表；盈亏分析；量本利
栏目：图表
哈喽，小伙伴们，你们好呀！
每天学点Excel，工作效率up,up~
美好的周末已经过去了，又开始了新的一周！
开始之前，先问大家一个问题：你知道什么是量本利分析吗？
乍一看，似乎一头雾水。
量本利分析，简称CVP分析。其实就是根据业务量（指产量、销售量、销售额等）、成本和利润三者之间的互相依存关系，进行综合分析。
这样子一解释，似乎好像更加难懂了。
站在老板的角度，其实就是用以预测利润，是否保本，盈亏如何的分析。
在企业经营决策、利润规划、成本目标的确定等方面应用很广，其中心内容是盈亏临界点分析。
如下图所示：
从图示中，我们可以根据调整“单价”的滚动条表单控件，得到一个动态图表，从中可以直观的看出“盈亏平衡点”以及“平衡点上的参数”。这样交给领导做定价决策就很方便了。
下面一起来看看它是如何通过Excel实现的吧。
STEP1：建立数据区域
单元格地址
内容摘要
函数
B1
单价
=C1/4
B2
销量
=C2*1000
B3
销售收入
=B2*B1
B4
固定成本
固定值
B5
单位变动成本
固定值
B6
总变动成本
=B5*B2
B7
总成本
=B4+B6
B8
销售收入
=B2*B1
B9
利润
=B8-B7
B10
盈亏平衡销量
=IFERROR(ROUNDUP(B4/(B1-B5),0),0)
B11
盈亏平衡销售收入
=B10*B1
B12
盈亏平衡制表参数
=A1&"："&B1&CHAR(10)&A10&"："&B10&CHAR(10)&A11&"："&B11
按上方的内容，建立测算数据区域如下图所示：
STEP2：建立滚动条，控制单价和销量
在工具栏——开发工具——插入——表单控件：滚动条，插入控件。鼠标选中控件，点击鼠标右键，弹出命令菜单，选择“设置控件格式”；在弹出的窗口中，按下图配置参数，如下：
按“确定”按钮，此时，我们使用滚动条的时候，就会发现【单价】字段会随着滚动条的调整而变化；同理制作【销量】的滚动条，如下设置：
STEP3：建立图表数据区域
单元格地址
摘要
函数
B16
最小销量
销量滚动条最小值*1000
B17
最小销量-销售收入
=B16*B1
B18
最小销量-总成本
=B4+B5*B16
B19
最小销量-利润
=B17-B18
C16
最大销量
销量滚动条最大值*1000
C17
最大销量-销售收入
=C16*B1
C18
最大销量-总成本
=B4+B5*C16
C19
最大销量-利润
=C17-C18
制作完后，如下图所示：
STEP4：制作图表
选中A16:C19单元格区域，插入图表——带直线的散点图；
得到下面的图表：
鼠标对图表，点击右键，在弹出的命令菜单中选择“选择数据”，按下图箭头的地方，点击“切换行/列”，如下所示：
STEP5：插入“盈亏平衡点”
依然对图表，点击鼠标右键——选择“选择数据”，点击“添加”新的系列；
在弹出的窗口中，按下图设置参数引用，如下：
及此，图表部分制作完毕。具体的美工部分这里就不展开讲了。
知识拓展：制作并使用一次“量本利分析”后，我们应该从中学到一些关于数据应用的知识。
1、盈亏平衡和什么有关系？
通过图表滚动条的变化，我们发现，图表内容和“单价”有关，和“销量”无关！这是因为，销量的增加，会导致“总变动成本”的增加，而且是成正比增加，这个比例是定值；而真正影响盈亏平衡的因素，是“单价”和“单位变动成本”。
2、销量在经营中的意义何在？
如果说盈亏平衡与销量无关，那么销量在经营数据中的意义到底是什么？作者认为是“体量”。一个公司的销售渠道不可能无限扩展的，一定是在市场上有着自己的比例，即：市场份额。我们通过上面的图表工具，测算出“盈亏平衡销量”，那么经营者就要考虑：
a、产能允许生产出这个数量的产品吗？
b、我们是否可以卖掉这个数量的产品？
如果不能满足，那么如何调整策略来满足这个条件，那么销量的作用就是这个了，它不是决定因素，而是决策因素。
好啦!
以上就是对“量本利分析”的一个基本总结和应用分享，感兴趣的同学们可以自己试着做一做。
