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

1、假设有一个制衣厂,生产的裤子单位售价100元,其中生产一件产品的人工费10元, 材料费15元, 制造过程中所耗费的水电费10元(当然可能会有其他随产量变动可做表如下: 其中“单位可变成本” 即为 直接人工,直接材料,可变制造费用相加之和 这个值在会计上专用名词叫单元边际贡献,意思说卖出去一件产品对利润所做的贡献,注意在实际企业生产过程中,还有部分费用是不随产量变动而变动的,我们称之为固定费用。象机部分费用依然产生。我们暂且估计数据如下,假设销量1000条,继续做表 当企业有销量时,单位边际贡献随之增加,只有所有售出产品的边际贡献总值超过企业的固定为边际贡献总值=单位边际贡献乘 X 销量,所以盈

2、亏平衡时的销量=固定成本/单位边际贡献, 即B19=B8/B7以下内容为EXCEL盈亏损模型所需要的建的辅助区域 3直接人工104直接材料155可变制造费用1067单位边际贡献658固定成本:520009管理人员工资2200010资产折旧2000011固定销售费用100001213销量100014销售收入10000015边际贡献6500016总成本8700017利润130001819盈亏平衡销量800再加两条参考线:A B C D E项目金额销售量边际贡献固定成本1销售单价10000520002单位可变成本:35150097500520003直接人工104直接材料155可变制造费用106盈亏点

3、销售垂直参考线:7单位边际贡献65800150000手工输入值8固定成本:52000800520009管理人员工资220008000手工输入值10资产折旧20000800-50000手工输入值11固定销售费用1000012当前销售垂直参考线:13销量10001000150000手工输入值14销售收入10000010006500015边际贡献6500010005200016总成本8700010001300017利润130001000-50000手工输入值1819盈亏平衡销量800其中:C7到C10的公式都是: =B19, D8公式:=B8, C13到C17的公式都是: =B13, D14公式:=

4、B14, D15公式:=B8,至于为什么输入这些数字, 下边解释。 至于为什么要做参考线也下边解释。 现在只需要明白一点:拿“盈亏点销量垂直参考线”来说,如果把C7到C10的值放图表上的X轴,D7到D10为Y轴的话, 因为X值相同,这“当前销量垂直参考线”中的数据就是垂直于X轴的直线上的五个点。 明白这些就足够了,下面开始做图表:1. 选择C1到F3,然后按工具栏上的 图表向导。 选择XY散点图,子图表类型选择 无数据点折线散点图, 然后按 2. 系列产生在处选择 列,然后按完成 4. 简单修饰一下。a. 选中图例-鼠标右键-图例格式-位置-底部-确定b. 选中绘图区- 鼠标右键-绘图区格式-

5、区域 处选择 白色 -确定c. 选中绘图区-再选中 y轴主格线 -鼠标右键 - 网格线格式 颜色处 选择 浅灰色 -确定d. 选中 图表上的 黄色“利润”系列线型-鼠标右键-数据系列格式-左边 线型 下的颜色选择 黄色,同理按上述方法,将“边际贡献”系列改成蓝色,加粗线。 将“固定成本”系列改成桔红色,加粗线型e. 选中x坐标轴-鼠标右键-坐标轴格式-刻度-最小值输入0, 最大值输入1500-确定同样方法,将y轴刻度最小值设成 -50000, 最大值设成1500000f. 再分别选择中,图例,x轴,y轴,在它们鼠标右键的格式设定中的字体选项里边,将它们的字体全设成11号字请看下图是完成后样子,

6、如果您的不是这样,请按上边步骤重新改过5. 选中绘图区- 鼠标右键-数据源 - 在“系列”页上,按 “添加”按钮-出现系列4(如下图-60000-40000-200000200004000060000800001000001200000500100015002000边际贡献固定成本利润-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润 7. 按上述方法再添加一个系列,系列5. x值选择C13到C17, y值处先删掉里边内容,再选择 D13到D17 8.完

7、成后,如下图所示。9. 在绘图区选中 系列4 - 鼠标右键 - 数据系列格式-左侧 线形 处颜色 选紫色, 粗细选倒数第二粗 -50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列4系列5 10. 在绘图区选中 系列5 - 鼠标右键 - 数据系列格式-左侧 线形 处颜色 选天蓝色, 12. 分别清除系列4和系列5的最上边和最上边的数据点格式。操作如下:a. 先鼠标左键选中系列4,再鼠标左键单击系列4最上边的数据点,选中这个数据点(切记不是 b. 在右侧的

8、 数据标记 处选 “无” -确定 重复以上操作,将系列4最下边数据点,以及系列5最上和最下数据点格式去除掉。完成后效果 13. 关键数据点加 数据标志。 选中系列4, 再选中系列4与固定费用和边际贡献交叉的那个点-鼠标右键-同样操作,选中系列5,再选中系列5与利润交叉的那个点,加数据标志 Y值。确定后,效果如下图所示 啥? 跟我的不一样?字体大,没边框?您自己在这个数据标志的右键格式设定里改改嘛,到此为一个静态的盈亏模型就建好了。 虽然叫静态的,但如果您去修改表格上的数值,如价格,销量,固定成本有没有更好的办法,不直接去修改表值,能不能在图表这里直接变动价格,变动销量来实现动态的盈亏分析呢?答

9、案是能的,下面我们就在此图的基础之上,来做一个动态图表。这里我们需要用到 “窗体”工具栏上的微调按钮,如图。如果你的工具栏上没有窗体工具栏,请去视图菜单下的 14. 鼠标单击窗体工具栏上的微调按钮,然后在表格上拖拽出这个按钮,然后在其上按鼠标右键-选择 设置控当前值输入100,最小值输入50, 最大值300,步长5, 单元格链接处 选择B1,确定 (我的目的是用这个按钮控制 15. 再次鼠标单击窗体工具栏上的微调按钮,然后在表格上拖拽出第二个按钮,然后在其上按鼠标右键-选择当前值输入1000, 最小值输入0, 最大值1500,步长50, 单元格链接处 选择B13,确定 (我的目的是用这个按钮

10、完成之后,当单击这两个按钮的向上或向下键头的时候,你会发现图表是动态的了 注:如果你的按钮上没有“控制”这个选项,请你注意一下我用的窗体工具栏上的微调按钮,不是控制工具栏的16. 给这两个按钮添加一些文字说明a. 在单元格C18中输入公式:=售价=&B1&元,盈亏平衡销量=&ROUND(B19,1b. 在单元格C19中输入公式:=销量=&B13&时,&IF(B170,盈利,IF(B17=0,保本,亏损c. 在绘图工具栏上 选择矩形框,在图表上边拉出一个条形矩形框。然后选中这个矩形框,上边公式栏中输入 =注: 绘图工具栏默认在EXCEL窗口的下边,如果你找不到请去视图菜单下的工具栏里将其钩选c.

11、 再建一个矩形框。选中这个矩形框,上边公式栏中输入 =C19, 回车. 完成之后矩形框上就会出现C19里的文字17. 最后一步,给图表区右键的表选项里给表加上标题。点编辑菜单下的-定位-定位条件组合到一起。 看一下成果吧分析:由以上图形可以看出1.当销量增加,产品的边际贡献逐渐增加,利润的负值(亏损逐渐减少2.当边际贡献数值增长到与固定费用持平时,这时利润刚好为零,此时即所谓的盈亏平衡3.当边际贡献再继续增加时,这时企业已开始盈利了10元(当然可能会有其他随产量变动的制造费用,此处暂且不计费用相加之和,注意单位两个字,是销售一件产品贡献。象机器设备的折旧,管理层人员的工资,销售过程中的广告费等

12、等,它们是不随销量变动,意思是的固定成本时,企业才会实现利润。 在利润为零那一时刻,盈亏实现平衡,边际贡献总值等于固定成单位边际贡献, 即B19=B8/B7 , D14公式:=B14, D15公式:=B8, D16公式: =B17, 其他位置的数字直接手工输入现在只需要明白一点:D10为Y轴的话, 因为X值相同,这些实际上是垂直于X轴方向上的一条直线上的四个点 些就足够了,下面开始做图表:选择 无数据点折线散点图, 然后按一步,图表区 绘图区y轴,x轴浅灰色 -确定左边 线型 下的颜色选择 黄色,粗细处选择倒数第二粗的线粗系列改成桔红色,加粗线型大值输入1500-确定项里边,将它们的字体全设成

13、11号字体钮-出现系列4(如下图 图例y轴,x轴y 轴主格线 内容,再选择 D13到D17 , 粗细选倒数第二粗 -右侧 数据标记 处,前景色选红色,背景色选白色,大小设成 5磅 -确色 选天蓝色, 粗细选倒数第二粗 -右侧 数据标记 处,前景色选红色,背景色选白色,大小设成 5磅 -确定 记不是双击那个点,是两次单击才可选中,选中之后,按鼠标右键会出现 数据点格式 掉。完成后效果如下: 贡献交叉的那个点-鼠标右键-数据点格式-数据标志-选中Y值-确定确定后,效果如下图所示 定里改改嘛,上的数值,如价格,销量,固定成本,这个模型就随之变化了动销量来实现动态的盈亏分析呢?没有窗体工具栏,请去视图菜单下的工具栏里钩选窗体工具栏在其上按鼠标右键-选择 设置控件格式确定 (我的目的是用这个按钮控制售价变化,然后在其上按鼠标右键-选择 设置控件格式3