EXCEL盈亏平衡分析模型

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

盈亏平衡分析模型

一、模型描述

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散点图,注意:数据系列选择列。调整图形为

150000

100000

50000

0500100015002000 -50000

销售收益总成本利润

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:在图表中创建微调器控件,反映销售量和单价与利润之间的关系。

在图表中创建标签:公式是:=$b$19

同样:创建单价和销售利润之间的关系:

相关文档
最新文档