文档介绍：盈亏平衡分析模型
一、模型描述
1基本模式
Excel电子表格建立盈亏平衡分析模型的方法，可采用公式计算、单变量求解、规划求解等寻找盈亏平衡点的多种方法，分析各种管理参数的变化对盈亏平衡点的影响。
盈亏平衡分析问题描述：销售量 Q,销售收益R,总成本C以及利润沃之间的关系的模
型：
销售收益R=销售量Q*销售单价p
总成本。=固定成本+变动成本V
变动成本V=单位变动成本v*销售量Q
总成本。=固定成本+单位变动成本v*销售量Q
利润ji =销售收益R-总成本C
单位边际贡献二销售单价p •单位变动成本v
边际贡献=销售收益R -变动成本V
边际贡献率k=单位边际贡献/销售单价
R=PQ
C = FvO v
〜77= U .、R—F 二 kR—F
7T - R-C p
2、盈亏平衡销量Q0和盈亏平衡销售收益R0
F
二、EXCEL中建立盈亏平衡分析模型的步骤
(1 )在Excel中建立盈亏平衡分析的框架，输入产品的单价、单位变动成本、固定成本
(2)给定销售量的情况下，计算总成本、销售收益、利润等。
可绘制利润随着销售量改变的XY散点图形(利用模型
运算表)
计算盈亏平衡点：可以使用 A :单变量求解B:规
划求解 C:公式计算
(5)可绘制参数(例如单价)对盈亏平衡点的影响
)根据预期的利润确定实现该利润的产品销量
三、案例分析
富勒公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信息，公司最高生产能力为1500。一项销售调查显示明年的平均每双销售价格定为 90元；公
司的成本数据为：固定成本为37800元，每双可变成本为36元。若当前的销量为900,要求：
1、计算单位边际贡献及边际贡献率；
2、计算销售收益、总成本及利润；
3、盈亏平衡(保本点)销量及盈亏平衡销售收益；
4、假若公司预算利润为24000元，计算为达到利润目标所需要的销量及销售收益；
5、根据公司的销售收益、总成本、利润等数据，绘制本~~•量■利图形；通过图形动态反映出销量从100按增量10变化到1500时利润的情况及“盈利”、“亏损”、“保本”的决策信息。
6、
假定销售单价从80元按增量变化到100元时，计算出盈亏平衡销量和盈亏平衡销售收益的
相应变化值？并且以图形方式动态反映。操作步骤：
单位边际贡献率、销售收益、总成本、
C9: =C5— C6
C12: =C6*C2+C7
C10： =C9/C5
C13 : =C11 — C12
C11 ： =C2*C5
C15 : =C7/C9
C16: =C15*C5
(2)
(3)绘制公司的销售收益、
总成本、利润等数据,
绘制本一量一利。
A :禾U用模拟运算表准备作图数据。以销售量为变量，销售收益、总成本、禾U润进行单变量模拟。
公式如下：
G3: =B11
H3: =B12
13: =B13
模拟运算参数：选F3: 15
模拟运算表迂]区I
输入引用行的单元格®: I V
输入引用列的单元格©: [SS~V 取消
1
2
3
4
5
销量
单位:
900
平均每双善价
6每双可变成本
7固定咸本
元
9Q
56
37S00
F
G
H
I
销量
销■售收益总成本 81000 70200
利涧
10800
0
0
37300
-37800
1500
135000
91S00
43200
C D E
8 ~9
10
11
12
13
14
15
单位边拓贡献边际贡献率销售收益总甘和涓
54
60% 81000 70200 1080()
盈亏平衡销呈
1G盈号平衡收益
700
63000
-n
B :选择 F2: 12
F5: 16,绘制XY散点图，注意：数据系列选择列。
调整图形为
销售收益一一总成本禾I」润
C:构造垂直参考线的数据
增加销售量垂直线参考•：
公式：F8: =B15 F9: =B15 G8: -40000 G8: 140000
选择F8: G9,选择“编辑”的“选择性粘贴”
110
销」
单位:
5
&
7
8
9
10
11
12
13
14
15
16
17 1E
19
20
21
平均每冢售价每双可变成本固定成本
单位边际贡献
销售收益总咸本
盈亏平衡销］
盈亏平衡收益