EXCEL盈亏平衡分析模型

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

盈亏平衡分析问题描述：销售量 Q ,销售收益R ，总成本C 以及利润JI 之间的关系的 模型： 销售收益R=销售量Q*销售单价p 总成本C=固定成本+变动成本V 变动成本V=单位变动成本v*销售量Q 总成本C=固定成本+单位变动成本v*销售量Q 利润J =销售收益R-总成本C
单位边际贡献=销售单价p -单位变动成本v 边际贡献=销售收益R -变动成本V 边际贡献率k=单位边际贡献/销售单价
7T — (p- v)O — F
yr = I — —)R — F 二 kR — F P
2、盈亏平衡销量Q0和盈亏平衡销售收益 R0
Q 严厶
入=+
P - V
k
二、 EXCEL 中建立盈亏平衡分析模型的步骤
(1 )在Excel 中建立盈亏平衡分析的框架，输入产品的单价、单位变动成本、固定成 本 (2) 给定销售量的情况下，计算总成本、销售收益、利润等。

(3)
可绘制利润随着销售量改变的
XY 散点图形(利用模型运算
表)
(4)
计算盈亏平衡点：可以使用 A :单变量求
解
B :规划求解
C :公式计算
(5) 可绘制参数(例如单价)对盈亏平衡点的影响 (6 )根据预期的利润确定实现该利润的产品销量 三、 案例分析
富勒公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信 息，公司最高生产能力为 1500。

一项销售调查显示明年的平均每双销售价格定为 90元；
公司的成本数据为：固定成本为 37800元，每双可变成本为 36元。

若当前的销量为 900,
要求：
1、 计算单位边际贡献及边际贡献率；
2、 计算销售收益、总成本及利润；
3、 盈亏平衡(保本点)销量及盈亏平衡销售收益；
R 二 PQ C =F 十 vO 开二
R — C
4、假若公司预算利润为24000元，计算为达到利润目标所需要的销量及销售收益；
5、根据公司的销售收益、总成本、利润等数据，绘制本-量-利图形；通过图形动态反映出销量从100按增量10变化到1500时利润的情况及“盈利”、“亏损”、“保本”的决策信息。

6、假定销售单价从80元按增量变化到100元时，计算出盈亏平衡销量和盈亏平衡销售收益的相应变化值？并且以图形方式动态反映。

操作步骤：
（1 ）在Excel中建立模型，计算单位边际贡献、单位边际贡献率、销售收益、总成本、利润、盈亏平衡销量、盈亏平衡销售收益
_ B C D
1
2销量900
|1 !
4单位I元-
5平均每双售价90
6每双可变成本36
7固定成本37800
8J
9单位边际贡献54
10边际贡献率60^
11销售收益81000
12总本70200
13利润10800
14
15盈亏平衡销量700
16盈亏平衡收益63000
17 ]
各个单位格公式如下：
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 :禾U用模拟运算表准备作图数据。

以销售量为变量，销售收益、总成本、禾U润进行单变量模拟。

公式如下：
G3: =B11
H3 : =B12
13: =B13
模拟运算参数：选F3: 15
输入引用行的单元格⑥：I V
输入引用列的单元格©:[m2~5]
A B C E F G H I
1
Z怕虽?<'0訐吿收益益成本
3皿网7C2QQ LQ0W 4单弘元0Q37000rSFBoa 5平均毎用售价901500L350QO91BCI432D0 :舟％可夹底慕36
7固定时37SOO
g
59
11边航BM率
H销雷收益R100：'
12 i^,TOSiC'O
13対用1020D I 14
15盈亏平像悟呈70Q
Ifi匡号平挙妝G3(iCiCi
A,—
B :选择F2: 12 F5: I6 ，绘制：XY散点图，注意：数据系列选择列。

调整图形为
—销售收益—总成本—利润
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,"赢利",IF(B13=0,"平衡","亏损")) A20 :=="售价="&B5&"元，盈亏平衡销量 ="&ROUND(B15,0) E :在图表中创建微调器控件，反映销售量和单价与利润之间的关系。

添加单无格为 盲新系列⑸ r 新数据点迥
数值仅】轴在
确定 取消
厂首行为系列名称⑩ ▽
胃列为芬类-茶值魁 r 簪换规有芬葵®
____________ !
选择性粘贴
r?
诗置悴怦格式
申丘格畫爰于而阪T；
▽三詢琥趨
锁疋」眾制
在图表中创建标签：公式是：=$b$19
同样：创建单价和销售利润之间的关系：
—销售量为：110亏损令售价用0元，盈专平衡销＞^359
140000
120000
100000
80000
60000
40000
20000
0 1
-20000
〔00
-40000 ----------------------------------------------。