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

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