EXCEL盈亏平衡分析模型.docx

盈亏平衡分析模型 盈亏平衡分析模型 一、模型描述 1、基本模式 Excel 电子表格建立盈亏平衡分析模型的方法，可采用公式计算、单变量求解、规划求解等寻找盈亏平衡点的多种方法，分析各种管理参数的变化对盈亏平衡点的影响。 盈亏平衡分析问题描述：销售量 型： Q，销售收益 R，总成本 C 以及利润 л之间的关系的模 销售收益 R= 销售量 Q* 销售单价 p 总成本 C=固定成本 +变动成本 V 变动成本 V= 单位变动成本 v* 销售量 Q 总成本 C=固定成本 +单位变动成本 v* 销售量 Q 利润 л =销售收益 R- 总成本 C 单位边际贡献 =销售单价 边际贡献 =销售收益 R - p - 单位变动成本 v 变动成本 V 边际贡献率 k= 单位边际贡献 /销售单价 2、盈亏平衡销量 Q0 和盈亏平衡销售收益 R0 二、 EXCEL 中建立盈亏平衡分析模型的步骤 在 Excel 中建立盈亏平衡分析的框架，输入产品的单价、单位变动成本、固定成本 给定销售量的情况下，计算总成本、销售收益、利润等。 可绘制利润随着销售量改变的 XY 散点图形（利用模型运算表） 计算盈亏平衡点：可以使用 A ：单变量求解 B：规划求解 C：公式计算 可绘制参数（例如单价）对盈亏平衡点的影响 根据预期的利润确定实现该利润的产品销量三、案例分析 富勒公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信 息，公司最高生产能力为 1500。一项销售调查显示明年的平均每双销售价格定为 90 元；公司的成本数据为：固定成本为 37800 元，每双可变成本为 36 元。若当前的销量为 900，要求： 1、 计算单位边际贡献及边际贡献率； 2、 计算销售收益、总成本及利润； 3、 盈亏平衡（保本点）销量及盈亏平衡销售收益； 4、 假若公司预算利润为 24000 元，计算为达到利润目标所需要的销量及销售收益； 5、根据公司的销售收益、总成本、利润等数据，绘制本 -量-利图形；通过图形动态反映 出销量从 100 按增量 10 变化到 1500 时利润的情况及 “盈利”、“亏损”、“保本” 的决策信息。 6、假定销售单价从 80 元按增量变化到 100 元时，计算出盈亏平衡销量和盈亏平衡销售收益的相应变化值？并且以图形方式动态反映。 操作步骤： （1）在 Excel 中建立模型，计算单位边际贡献、单位边际贡献率、销售收益、总成本、利润、盈亏平衡销量、盈亏平衡销售收益 各个单位格公式如下： C9： =C5— C6 C10： =C9/C5 C11：=C2*C5 C12： =C6*C2+C7 C13 ：=C11— C12 C15 ：=C7/C9 C16： =C15*C5 （2） （3）绘制公司的销售收益、总成本、利润等数据，绘制本—量—利。 A ：利用模拟运算表准备作图数据。以销售量为变量，销售收益、总成本、利润进行单变量模拟。 公式如下： G3： =B11 H3 ： =B12 I3： =B13 模拟运算参数：选 F3： I5 B：选择 F2： I2 F5： I6 ，绘制 XY 散点图，注意：数据系列选择列。调整图形为 150000 100000 50000 0 -50000  0 500 1000 1500 2000 销售收益 总成本 利润 C：构造垂直参考线的数据增加销售量垂直线参考： 公式： F8： =B15 F9： =B15 G8： -40000 G8： 140000 选择 F8：G9 ，选择“编辑”的“选择性粘贴” 增加销售量垂直参考线： 增加销售量垂直参考线： 公式： F12： =B2 F13： =B2 G12： -40000 G13： 140000 选择 F12： G13，选择图形，选择“编辑”的“选择性粘贴” D：在表格的 A19 和 A20 建立公式，反映建模型的结果： A19 ： == 销售量为 : B2IF(B130, 赢利 ,IF(B13=0, 平衡 , 亏损)) A20 ： == 售价=B5 元，盈亏平衡销量 =ROUND(B15,0) E：在图表中创建微调器控件，反映销售量和单价与利润之间的关系。 在图表中创建标签：公式是： =$b$19 同样：创建单价和销售利润之间的关系： 七 夕，只因有你， 总有一些人牵肠挂肚难以忘记， 总有一些日子温暖甜蜜最为珍惜 从春夏到秋冬，从陌生到熟悉， 虽不能时时联系，却总在特别的日子想起你， 七夕快乐，我的朋友。 七夕，只因有你， 因为有你，再苦生活也不觉得累， 再大的险阻也无所畏， 再大的波折也不担忧， 再痛的经历也会忘记， 因为有你，我就拥有了整个世界， 谢谢你出现在我的生命里。 七夕快乐，我的朋友。 七夕，只因有你 ， 相识，是最珍贵的缘