用EXCEL做盈亏平衡分析

1、企业管理也在行Excel做量本利分析（图）2001-12-27 17:20:41赛迪网一中国电脑教育报什么是量、本、利分析呢？就是运用数学计算和图像法研究产品销量、成本及售价的变 化，对目标利润影响的一种分析方法。它作为财务分析的有效的手段，在企业管理中得到广 泛应用。本文试用Excel对量、本、利关系做一解析。盈亏平衡点的测定盈亏平衡点指某种产品的销售收入恰好等于总成本时的销售量或销售额，在该点，既无 盈利，也无亏损。盈亏平衡点的测定，需要根据产品的有关数据资料，对量、本、利之间的 关系进行分析。例1：某产品固定成本为12000元，单位可变成本为100元，售价为210元，试确定盈 亏平衡点的

2、销量。计算方法如下：ABCD1互定卤本单位可蒙成本害价 著平街中能量2 ；12000100 210IW图1如图1所示，在A2、B2、C2中分别键入固定成本、单位可变成本、售价，在单元格D2 中键入公式“ 二A2/（C2-B2）”，则D2中显示的数据即为所求，可知盈亏平衡点销量为109 单位。盈亏平衡图作法如下： 取销售量为0、20. 40180，分别键入A5、A6A14， B5中键入公式 “=12000+100*A5”、C5中键入公式“ 二210*A5”。将B5、C5中的公式分别复制到B6:B14、C6:C14，得到数据如图2所示。选择A4:C14，单击“插入-图表”（或单击工具栏图表向导按钮

3、），显示“图表类 型”对话框。AB4第普员总成本总收入5012000n2014000420071016000S4008so13000l%009B0濒00响。1 10LOO22000210001112024m器知。12iio26000204001602E00033600ISO3000037E00图2选择“XY散点图”及其子图“无数据点平滑线散点图”，单击下一步,显示“图 表数据源”对话框。选择“系列产生在列”，单击完成，显示图表如图3所示。图3图中X轴为销量，Y轴为成本，两直线中较平缓的一条为总成本线，较陡峭的一条为总 收入线，两直线交点为盈亏平衡点，该点X轴的坐标为109，即当销量为109单位

4、时盈亏平 衡。位于平衡点左侧两直线所夹区域为亏损区，平衡点右侧两直线所夹区域为盈利区。目标利润下销量的测定当产品的固定成本、单位可变成本、售价已知时，如果要实现一定的目标利润，可通过 量、本、利分析确定相应的销量。例2：上例产品固定成本、单位可变成本、售价不变，试测定销量达到多少时可获目标 利润5000元。A B C D EW .固定成本单位可敦木目标销景目标耕肖1812000100 210-12000图4计算方法如下：建立计算表如图4所示，单元格E18中键入公式=（C18-B18）*D18-A18”。单击“ E18”。执行菜单命令“工具-单变量求解”，显示“单变量求解”对话框， 如图5所示。

5、图5激活“目标值”文本框，键入“5000”（即目标利润5000元）。激活“可变单元格”文本框，键入“D18”（或单击“D18”），单击确定按钮，显示 “单变量求解状态”对话框。单击确定按钮，显示计算结果如图6所示。|A_iB C D: i7IT.固定成本单位应费成术售论目标销量目标勃?同1012000100 2101555000图6由图可知，要实现5000元目标利润，目标销量需达到155单位。成本变化对利润影响的测定成本包括固定成本和可变成本。在产品售价不变的情况下，成本的变化将会引起利润做 反方向的运动，即成本上升，利润下降；反之，成本下降，利润上升。譬如，产品原材料提价，引起可变成本上升，

6、导致利润下降；广告可以促销产品，但广 告费的支出又引起固定成本的上升。如此，在总成本上升的情况下，销量达到多少才能维持 目标利润。通过量、本、利分析，可以确定目标销量的增量，进而分析能否达到该增量，以便作出 正确的经营决策。I ：A I 3 I CJ D I . 1 F 如固走成本单位可变成本 磁 目标销量销量增量目标利牝 _22|1700013fl 210-155-1?000例3：上例产品固定成本上升5000元，单位可变成本上升300元，售价不变，如果目 标利润维持5000元，则销量比原目标销量增加多少？计算方法如下:1.建立计算表如图7所示。单元格A22、B22、C22、E22及F22中，分别键入公式 “=A18+5000”、=B18+30”、=C18”、=D22-D18”、=(C22-B22)*D22-A22”