建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态.xls
《建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态.xls》由会员分享,可在线阅读,更多相关《建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态.xls(29页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、销售单价100销售量边际贡献固定成本利润单位可变成本:350052000-52000直接人工101500975005200045500直接材料15可变制造费10盈亏点销量垂直参考线:单位边际贡献65800150000固定成本:5200080052000管理人员工资200008000资产折旧10000800-50000固定销售费用10000当前销量垂直参考线:销量10001000150000边际贡献65000100065000销售收入100000100052000总成本87000100013000利润130001000-50000售价=100元,盈亏平衡销量=800盈亏平衡销量800 销量=10
2、00时,盈利EXCELEXCEL梦梦想想之之家家http:/ 52001300盈盈 亏亏 平平 衡衡 分分 1401201008006004002000-50000-30000-100001000030000500007000090000110000130000150000边际贡献固定成本利润系列4系列5售价=100元,盈亏平衡销量=800销量=1000时,盈利52001300盈盈 亏亏 平平 衡衡 分分 1401201008006004002000-50000-30000-100001000030000500007000090000110000130000150000边际贡献固定成本利润系列
3、4系列5售价=100元,盈亏平衡销量=800销量=1000时,盈利EXCELEXCEL梦梦想想之之家家http:/ 直接人工,直接材料,可变制造费 相加之和上表B2=B3+B4+B5,请自己加公式。B7解释一下,B7等于销售单介B1减单位可变成本B2,这个值在会计上专用名词叫单元边际贡献,意思说卖出去一件产品对利润所做的贡献,注意单位两个字,是销售一件产品贡献。在实际企业生产过程中,还有部分费用是不随产量变动而变动的,我们称之为固定费用。象机器设备的折旧,管理层人员的工资,销售过程中的广告费等等,它们是不随销量变动,意思是说卖不卖东西,这部分费用依然产生。我们暂且估计数据如下(本故事纯属虚构,
4、如有雷同,纯属巧合)1400上表B8=B9+B10+B11假设销量1000条,继续做表上表,B14=B13*B7,B15=B13*B1,B16=B8+B13*B2,解释一下B19盈亏平衡销量当企业有销量时,单位边际贡献随之增加,只有所有售出产品的边际贡献总值超过企业的固定成本时,企业才会实现利润。在利润为零那一时刻,盈亏实现平衡,边际贡献总值等于固定成本。因为边际贡献总值=单位边际贡献乘 X 销量,所以盈亏平衡时的销量=固定成本/单位边际贡献,即B19=B8/B7以下内容为EXCEL盈亏损模型所需要的建的辅助区域,至于为什么要建这个区域,等做好之后您再慢慢体会。继续做表其中:C1到F1标题手工
5、填入,C2销量输入0,C2为0,D2肯定也为0,E2输入公式=B8,F2输入公式:=D2-E2C3输入1500,假设这是企业最大销量(闲少?那您填多大我都不拦着),D3输入公式:=C3*B7,E3输入公式:=B8,F3输入公式:=D3E3再加两条参考线:其中:C7到C10的公式都是:=B19,D8公式:=B8,C13到C17的公式都是:=B13,D14公式:=B14,D15公式:=B8,D16公式:=B17,其他位置的数字直接手工输入至于为什么输入这些数字,下边解释。至于为什么要做参考线也下边解释。现在只需要明白一点:拿“盈亏点销量垂直参考线”来说,如果把C7到C10的值放图表上的X轴,D7到
6、D10为Y轴的话,因为X值相同,这些实际上是垂直于X轴方向上的一条直线上的四个点“当前销量垂直参考线”中的数据就是垂直于X轴的直线上的五个点。明白这些就足够了,下面开始做图表:1.选择C1到F3,然后按工具栏上的 图表向导。选择XY散点图,子图表类型选择 无数据点折线散点图,然后按一步,2.系列产生在处选择 列,然后按完成3.完成之后,如下图所示:蓝色文字指出图表上各部分名字,下文会用到这些地方,别找不到4.简单修饰一下。a.选中图例-鼠标右键-图例格式位置底部确定 b.选中绘图区-鼠标右键绘图区格式区域 处选择 白色 确定c.选中绘图区再选中 y轴主格线 鼠标右键-网格线格式 颜色处 选择
7、浅灰色 确定d.选中 图表上的 黄色“利润”系列线型鼠标右键数据系列格式左边 线型 下的颜色选择 黄色,粗细处选择倒数第二粗的线粗同理按上述方法,将“边际贡献”系列改成蓝色,加粗线。将“固定成本”系列改成桔红色,加粗线型e.选中x坐标轴鼠标右键坐标轴格式刻度最小值输入0,最大值输入1500确定同样方法,将y轴刻度最小值设成-50000,最大值设成1500000f.再分别选择中,图例,x轴,y轴,在它们鼠标右键的格式设定中的字体选项里边,将它们的字体全设成11号字体请看下图是完成后样子,如果您的不是这样,请按上边步骤重新改过-60000-40000-20000020000400006000080
8、0001000001200000500100015002000边际贡献固定成本利润图图表表区区绘绘图图区区图图例例y y轴轴,x x轴轴y y轴轴主主格格线线-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润5.选中绘图区-鼠标右键数据源-在“系列”页上,按“添加”按钮出现系列4(如下图)6.在x值处,去选择表格上的C7到C10这个区域。在y值处,先删除里边内容中,再去选择表格上的D7到D10这个区域,确定之后图表应该是下同这个样子-50000-3000
9、0-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列4-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润7.按上述方法再添加一个系列,系列5.x值选择C13到C17,y值处先删掉里边内容,再选择 D13到D178.完成后,如下图所示。-50000-30000-10000100003000050000700009000011000013
10、00001500000200400600800100012001400边际贡献固定成本利润-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润系列49.在绘图区选中 系列4-鼠标右键-数据系列格式左侧 线形 处颜色 选紫色,粗细选倒数第二粗 右侧 数据标记 处,前景色选红色,背景色选白色,大小设成 5磅 确定10.在绘图区选中 系列5-鼠标右键-数据系列格式左侧 线形 处颜色 选天蓝色,粗细选倒数第二粗 右侧 数据标记 处,前景色选红色,背景色选白色,大小
11、设成 5磅 确定-50000-30000-1000010000300005000070000900001100001300001500000200400600800100012001400边际贡献固定成本利润11.完成效果如下图:12.分别清除系列4和系列5的最上边和最上边的数据点格式。操作如下:a.先鼠标左键选中系列4,再鼠标左键单击系列4最上边的数据点,选中这个数据点(切记不是双击那个点,是两次单击才可选中),选中之后,按鼠标右键会出现 数据点格式b.在右侧的 数据标记 处选“无”确定重复以上操作,将系列4最下边数据点,以及系列5最上和最下数据点格式去除掉。完成后效果如下:13.关键数据点
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 建立 企业 盈亏 平衡 分析 模型 本利 EXCEL 动态
限制150内