Excel盈亏平衡分析模型

一、模型描述 1、基本模式

Excel电子表格建立盈亏平衡分析模型的方法，可采用公式计算、单变量求解、规划求解等寻找盈亏平衡点的多种方法，分析各种管理参数的变化对盈亏平衡点的影响。

盈亏平衡分析问题描述：销售量Q，销售收益R，总成本C以及利润л之间的关系的模型：

销售收益R=销售量Q*销售单价p 总成本C=固定成本+变动成本V

变动成本V=单位变动成本v*销售量Q

总成本C=固定成本+单位变动成本v*销售量Q 利润л=销售收益R−总成本C

单位边际贡献=销售单价p − 单位变动成本v 边际贡献=销售收益R − 变动成本V 边际贡献率k=单位边际贡献/销售单价

2、盈亏平衡销量Q0和盈亏平衡销售收益R0

二、EXCEL中建立盈亏平衡分析模型的步骤

（1）在Excel中建立盈亏平衡分析的框架，输入产品的单价、单位变动成本、固定成本 （2）给定销售量的情况下，计算总成本、销售收益、利润等。

（3）可绘制利润随着销售量改变的XY散点图形（利用模型运算表）

（4）计算盈亏平衡点：可以使用A：单变量求解 B：规划求解 C：公式计算 （5）可绘制参数（例如单价）对盈亏平衡点的影响 （6）根据预期的利润确定实现该利润的产品销量 三、案例分析

富勒公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信息，公司最高生产能力为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散点图，注意：数据系列选择列。 调整图形为

1500001000005000000-50000500100015002000销售收益总成本利润

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：==\"销售量为: \"&B2&IF(B13>0,\"赢利\平衡\亏损\")) A20：==\"售价=\"&B5&\"元，盈亏平衡销量=\"&ROUND(B15,0)

E：在图表中创建微调器控件，反映销售量和单价与利润之间的关系。

在图表中创建标签：公式是：=$b$19 同样：创建单价和销售利润之间的关系：