建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态图表-折线散点图

如价格，销量，固定成本，这个模型就随之变化了 实现动态的盈亏分析呢？
具栏，请去视图菜单下的工具栏里钩选窗体工具栏
鼠标右键－〉选择 设置控件格式 的目的是用这个按钮控制售价变化）
其上按鼠标右键－〉选择 设置控件格式
（我的目的是用这个按钮控制销量变化）
按钮，不是控制工具栏的。这两个有区别的
框，上边公式栏中输入 =C18, 回车. 完成之后矩形框上就会出现C18里的文字，如图所示
据点折线散点图， 然后按一步，
图表区 绘图区
轴，x轴
轴主格线 图例
献 本
浅灰色 －〉确定 型 下的颜色选择 黄色，粗细处选择倒数第二粗的线粗 红色，加粗线型 500－〉确定
它们的字体全设成11号字体
ቤተ መጻሕፍቲ ባይዱ
"系列4"(如下图)
择表格上的D7到D10这个区域，确定之后图表应该是下同这个样子
选择 D13到D17
， 粗细选倒数第二粗 －〉右侧 数据标记 处，前景色选红色，背景色选白色，大小设成 5磅 －〉确定
色， 粗细选倒数第二粗 －〉右侧 数据标记 处，前景色选红色，背景色选白色，大小设成 5磅 －〉确定
记不是双击那个点，是两次单击才可选中)，选中之后，按鼠标右键会出现 数据点格式
后效果如下：
那个点－〉鼠标右键－〉数据点格式－〉数据标志－〉选中Y值－〉确定 效果如下图所示
啥？ 跟我的不一样？字体大，没边框？您自己在这个数据标志的右键格式设定里改改嘛，
到此为一个静态的盈亏模型就建好了。 虽然叫静态的，但如果您去修改表格上的数值，如价格，销量，固定成本 有没有更好的办法，不直接去修改表值，能不能在图表这里直接变动价格，变动销量来实现动态的盈亏分析呢？ 答案是能的，下面我们就在此图的基础之上，来做一个动态图表。
8.完成后，如下图所示。
150000 130000 110000 90000 70000 50000 30000 10000 -10000 -30000 -50000 0 200 400 600 800 1000 1200 1400
90000 70000 50000 30000 10000 -10000 -30000 -50000 边际贡献 系列4 固定成本 系列5 利润 0 200 400 600 800 1000 1200 1400
好之后您再慢慢体会。继续做表
F2输入公式: =D2-E2 3*B7, E3输入公式: =B8, F3输入公式: =D3－E3
：=B14, D15公式：=B8， D16公式: =B17, 其他位置的数字直接手工输入 要明白一点： 的话， 因为X值相同，这些实际上是垂直于X轴方向上的一条直线上的四个点 了，下面开始做图表：
150000 130000 110000 90000 70000 50000 30000 10000
150000 130000 110000 90000 70000 50000 30000 10000 -10000 -30000 -50000 边际贡献 固定成本 利润 0 200 400 600 800 1000 1200 1400
c. 再建一个矩形框。选中这个矩形框，上边公式栏中输入 =C19, 回车. 完成之后矩形框上就会出现C19里的文字
17. 最后一步，给图表区右键的表选项里给表加上标题。点编辑菜单下的->定位－〉定位条件－〉选 对象－〉确 表，两个按钮，两个矩形框组合到一起。 看一下成果吧
分析：由以上图形可以看出 1.当销量增加，产品的边际贡献逐渐增加，利润的负值(亏损)逐渐减少 2.当边际贡献数值增长到与固定费用持平时，这时利润刚好为零，此时即所谓的盈亏平衡 3.当边际贡献再继续增加时，这时企业已开始盈利了
9. 在绘图区选中 系列4 -> 鼠标右键 -> 数据系列格式－〉左侧 线形 处颜色 选紫色， 粗细选倒数第二粗 －
10. 在绘图区选中 系列5 -> 鼠标右键 -> 数据系列格式－〉左侧 线形 处颜色 选天蓝色， 粗细选倒数第二粗
11. 完成效果如下图：
12. 分别清除系列4和系列5的最上边和最上边的数据点格式。操作如下： a. 先鼠标左键选中系列4，再鼠标左键单击系列4最上边的数据点，选中这个数据点(切记不是双击那个点，是两
1400
上表B8=B9+B10+B11 假设销量1000条，继续做表
上表，B14=B13*B7, B15=B13*B1, B16=B8+B13*B2, 解释一下B19盈亏平衡销量
当企业有销量时，单位边际贡献随之增加，只有所有售出产品的边际贡献总值超过企业的固定成本时，企业才会 际贡献总值等于固定成本。因为边际贡献总值=单位边际贡献乘 X 销量，所以盈亏平衡时的销量=固定成本/单位
y轴主格线 图例
4. 简单修饰一下。 a. 选中图例-〉鼠标右键-〉图例格式－〉位置－〉底部－〉确定 b. 选中绘图区-> 鼠标右键－〉绘图区格式－〉区域 处选择 白色 －〉确定 c. 选中绘图区－〉再选中 y轴主格线 －〉鼠标右键 -> 网格线格式 —〉颜色处 选择 浅灰色 －〉确定 d. 选中 图表上的 黄色“利润”系列线型－〉鼠标右键－〉数据系列格式－〉左边 线型 下的颜色选择 黄色， 同理按上述方法，将“边际贡献”系列改成蓝色，加粗线。 将“固定成本”系列改成桔红色，加粗线型 e. 选中x坐标轴－〉鼠标右键－〉坐标轴格式－〉刻度－〉最小值输入0， 最大值输入1500－〉确定 同样方法，将y轴刻度最小值设成 -50000, 最大值设成1500000 f. 再分别选择中，图例，x轴，y轴，在它们鼠标右键的格式设定中的字体选项里边，将它们的字体全设成11号字 请看下图是完成后样子，如果您的不是这样，请按上边步骤重新改过
这里我们需要用到 “窗体”工具栏上的微调按钮，如图。如果你的工具栏上没有窗体工具栏，请去视图菜单下的
14. 鼠标单击窗体工具栏上的微调按钮，然后在表格上拖拽出这个按钮，然后在其上按鼠标右键－〉选择 设置控 当前值输入100，最小值输入50, 最大值300，步长5, 单元格链接处 选择B1，确定 （我的目的是用这个按钮控制
EXCEL梦想之家 http://hi.baidu.com/lxlzmh2002/home
转载请保留出处
假设有一个制衣厂，生产的裤子单位售价100元， 其中生产一件产品的人工费10元， 材料费15元， 制造过程中所耗费的水电费10元(当然可能会有其他随产量变动 可做表如下： 其中“单位可变成本” 即为 直接人工，直接材料，可变制造费 相加之和
其中：C7到C10的公式都是: =B19, D8公式：=B8, C13到C17的公式都是: =B13, D14公式：=B14, D15公式：=B8， 至于为什么输入这些数字, 下边解释。 至于为什么要做参考线也下边解释。 现在只需要明白一点： 拿“盈亏点销量垂直参考线”来说，如果把C7到C10的值放图表上的X轴，D7到D10为Y轴的话， 因为X值相同，这 “当前销量垂直参考线”中的数据就是垂直于X轴的直线上的五个点。 明白这些就足够了，下面开始做图表：
框上就会出现C19里的文字，如图所示
位条件－〉选 对象－〉确定 －〉在选中图形上按鼠标右键－〉组合－〉组合，使EXCEL图
上表B2=B3+B4+B5, 请自己加公式。 B7解释一下，B7等于销售单介B1减单位可变成本B2, 这个值在会计上专用名 注意"单位"两个字，是销售一件产品贡献。
在实际企业生产过程中，还有部分费用是不随产量变动而变动的，我们称之为固定费用。象机器设备的折旧，管 动，意思是说卖不卖东西，这部分费用依然产生。 我们暂且估计数据如下（本故事纯属虚构，如有雷同，纯属巧合）
以下内容为EXCEL盈亏损模型所需要的建的辅助区域， 至于为什么要建这个区域，等做好之后您再慢慢体会。继
÷
其中： C1到F1标题手工填入，C2销量输入0, C2为0，D2肯定也为0, E2输入公式 =B8, F2输入公式: =D2-E2 C3输入1500, 假设这是企业最大销量(闲少？那您填多大我都不拦着)， D3输入公式: =C3*B7, E3输入公式: =B8 再加两条参考线：
b. 在右侧的 数据标记 处选 “无” －〉确定
重复以上操作，将系列4最下边数据点，以及系列5最上和最下数据点格式去除掉。完成后效果如下：
13. 关键数据点加 数据标志。 选中系列4， 再选中系列4与固定费用和边际贡献交叉的那个点－〉鼠标右键－〉 同样操作，选中系列5，再选中系列5与利润交叉的那个点，加数据标志 Y值。确定后，效果如下图所示
5. 选中绘图区-> 鼠标右键－〉数据源 -> 在“系列”页上，按 “添加”按钮－〉出现"系列4"(如下图)
6. 在x值处，去选择表格上的C7到C10这个区域。在y值处，先删除里边内容中，再去选择表格上的D7到D10这个区
150000 130000 110000 90000 70000 50000 30000 10000 -10000 -30000 0 200 400 600 800 1000 1200 1400
b. 在单元格C19中输入公式： ="销量="&B13&"时,"&IF(B17>0,"盈利",IF(B17=0,"保本","亏损"))
c. 在绘图工具栏上 选择矩形框，在图表上边拉出一个条形矩形框。然后选中这个矩形框，上边公式栏中输入 =
注: 绘图工具栏默认在EXCEL窗口的下边，如果你找不到请去视图菜单下的工具栏里将其钩选
可能会有其他随产量变动的制造费用，此处暂且不计)
这个值在会计上专用名词叫单元边际贡献，意思说卖出去一件产品对利润所做的贡献，
。象机器设备的折旧，管理层人员的工资，销售过程中的广告费等等，它们是不随销量变
的固定成本时，企业才会实现利润。 在利润为零那一时刻，盈亏实现平衡，边 时的销量=固定成本/单位边际贡献， 即B19=B8/B7
15. 再次鼠标单击窗体工具栏上的微调按钮，然后在表格上拖拽出第二个按钮，然后在其上按鼠标右键－〉选择
当前值输入1000, 最小值输入0, 最大值1500，步长50, 单元格链接处 选择B13，确定 （我的目的是用这个按钮
完成之后，当单击这两个按钮的向上或向下键头的时候，你会发现图表是动态的了
注：如果你的按钮上没有“控制”这个选项，请你注意一下我用的窗体工具栏上的微调按钮，不是控制工具栏的 16. 给这两个按钮添加一些文字说明 a. 在单元格C18中输入公式： ="售价="&B1&"元，盈亏平衡销量="&ROUND(B19,1)
130000 110000 90000 70000 50000 30000 10000 -10000 -30000 -50000 边际贡献 固定成本 利润 系列4 0 200 400 600 800 1000 1200 1400
7. 按上述方法再添加一个系列，系列5. x值选择C13到C17, y值处先删掉里边内容，再选择 D13到D17
1. 选择C1到F3，然后按工具栏上的 图表向导。 选择XY散点图，子图表类型选择 无数据点折线散点图， 然后按
2. 系列产生在处选择 列，然后按完成
3.完成之后，如下图所示：蓝色文字指出图表上各部分名字，下文会用到这些地方，别找不到 图表区 绘图区
y轴，x
120000 100000 80000 60000 40000 20000 0 -20000 -40000 -60000 0 500 1000 1500 2000 边际贡献 固定成本 利润