盈亏平衡分析模型-EXCEL动态

销售单价80销售量边际贡献固定成本利润单位可变成本：350052000-52000直接人工101500675005200015500直接材料15可变制造费10盈亏点销量垂直参考线：单位边际贡献451155.55556150000固定成本：520001155.5555652000管理人员工资200001155.555560资产折旧100001155.55556-50000固定销售费用10000当前销量垂直参考线：销量700700150000边际贡献3150070031500销售收入5600070052000总成本76500700-20500利润-20500700-50000售价=80元，盈亏平衡销量=1155.6盈亏平衡销量1155.55556销量=700时,亏损-50000-30000-100001000030000500007000090000110000130000150000052000-20500200400600800100012001400盈亏平衡分析边际贡献固定成本利润系列4系列5售价=80元，盈亏平衡销量=1155.6销量=700时,亏损其中生产一件产品的人工费10元，材料费15元，制造过程中所耗费的水电费10元(当然可能会有其他随产量变动的制造费用，此处暂且不计)可做表如下：其中“单位可变成本”即为直接人工，直接材料，可变制造费相加之和我们暂且估计数据如下（本故事纯属虚构，如有雷同，纯属巧合）上表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-E2C3输入1500,假设这是企业最大销量(闲少？那您填多大我都不拦着)，D3输入公式:=C3*B7,E3输入公式:=B8,F3输入公式:=D3－E3再加两条参考线：当企业有销量时，单位边际贡献随之增加，只有所有售出产品的边际贡献总值超过企业的固定成本时，企业才会实现利润。在利润为零那一时刻，盈亏实现平衡，边际贡献总值等于固定成本。因为边际贡献总值=单位边际贡献乘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,最大值设成1500000f.再分别选择中，图例，x轴，y轴，在它们鼠标右键的格式设定中的字体选项里边，将它们的字体全设成11号字体请看下图是完成后样子，如果您的不是这样，请按上边步骤重新改过-60000-40000-200000200004000060000800001000001200000500100015002000边际贡献固定成本利润图表区绘图区图例y轴，x轴y轴主格线-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润5.选中绘图区-鼠标右键－〉数据源-在“系列”页上，按“添加”按钮－〉出现系列4(如下图)6.在x值处，去选择表格上的C7到C10这个区域。在y值处，先删除里边内容中，再去选择表格上的D7到D10这个区域，确定之后图表应该是下同这个样子-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列47.按上述方法再添加一个系列，系列5.x值选择C13到C17,y值处先删掉里边内容，再选择D13到D178.完成后，如下图所示。-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列4-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列4系列59.在绘图区选中系列4-鼠标右键-数据系列格式－〉左侧线形处颜色选紫色，粗细选倒数第二粗－〉右侧数据标记处，前景色选红色，背景色选白色，大小设成5磅－〉确定10.在绘图区选中系列5-鼠标右键-数据系列格式－〉左侧线形处颜色选天蓝色，粗细选倒数第二粗－〉右侧数据标记处，前景色选红色，背景色选白色，大小设成5磅－〉确定-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列4系列511.完成效果如下图：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.当销量增加，产品的边际贡献逐渐增加，利润的负值(亏损)逐渐减少2.当边际贡献数值增长到与固定费用持平时，这时利润刚好为零，此时即所谓的盈亏平衡3.当边际贡献再继续增加时，这时企业已开始盈利了17.最后一步，给图表区右键的表选项里给表加上标题。点编辑菜单下的-定位－〉定位条件－〉选对象－〉确定－〉在选中图形上按鼠标右键－〉组合－〉组合，使EXCEL图表，两个按钮，两个矩形框组合到一起。看一下成果吧其中生产一件产品的人工费10元，材料费15元，制造过程中所耗费的水电费10元(当然可能会有其他随产量变动的制造费用，此处暂且不计)假设有一个制衣厂，生产的裤子单位售价100元，上表B2=B3+B4+B5,请自己加公式。B7解释一下，B7等于销售单介B1减单位可变成本B2,这个值在会计上专用名词叫单元边际贡献，意思说卖出去一件产品对利润所做的贡献，注意单位两个字，是销售一件产品贡献。在实际企业生产过程中，还有部分费用是不随产量变动而变动的，我们称之为固定费用。象机器设备的折旧，管理层人员的工资，销售过程中的广告费等等，它们是不随销量变动，意思是说卖不卖东西，这部分费用依然产生。以下内容为EXCEL盈亏损模型所需要的建的辅助区域，至于为什么要建这个区域，等做好之后您再慢慢体会。继续做表其中：C1到F1标题手工填入，C2销量输入0,C2为0，D2肯定也为0,E2输入公式=B8,F2输入公式:=D2-E2C3输入1500,假设这是企业最大销量(闲少？那您填多大我都不拦着)，D3输入公式:=C3*B7,E3输入公式:=B8,F3输入公式:=D3－E3当企业有销量时，单位边际贡献随之增加，只有所有售出产品的边际贡献总值超过企业的固定成本时，企业才会实现利润。在利润为零那一时刻，盈亏实现平衡，边际贡献总值等于固定成本。因为边际贡献总值=单位边际贡献