用excel怎么做盈亏平衡分析

如图所示，表格中红色数字都要通过公式输入。公式已标出。

这样处理是为了下面通过控件来使Excel盈亏平衡图进行动态分析。

2.选中表格数据区域——点击插入——推荐的图表——带平滑线和数据标记的散点图。

3.如图所示，创建盈亏点销量垂直参考线。

参考线可以看做是X（销量）=4000时的直线。

4.点击图表工具——设计——选择数据——添加。

5.点击开发工具——插入——窗体控件。

6.右击设置控件格式——单元格链接——B3（单价所在单元格）；

当前值——单价=10；

最小值——单价不能小于单位变动成本。

最大值——根据实际情况设定；

步长——变速每点击一下变动为1。

7.如图所示输入公式="单价="&B3&"元，盈亏平衡销量="&ROUND(B6,1)。

显示当单价为多少时相应的盈亏平衡销量。

8.如图所示，点击窗体控件，显示相应的单价和盈亏平衡销量。

盈亏平衡分析

例2：假定生产产品a时，年固定成本为50000元，年产销量

，单位售价，单位变动成本同上。试分析生产该产品的盈亏平衡情况。

盈亏平衡分析

1.

制工作表2：

盈亏平衡分析的公式：

销售收益

=

产销量

*

单位售价

总成本

=

固定成本

+

产销量

*

单位变动成本

利润

=

销售收益

–

总成本

产销量盈亏平衡点

=

固定成本

/

（单位售价

–

单位变动成本）

在b2:

c6单元格：输入已知的条件：b2:c2格

用乙机器生产产品a

b3格固定成本、b4格年产销量、b5格单位售价、b6格单位变动成本、c3:c6格输入已知数据：c3

50000

c49000

c510

c6

6

在b8:c10单元格输入：

b8格

销售收益、b9格

总成本、b10格

利润

在

c8输入

=c4*c5

c9输入

=c3+c4*c6

c10输入

=c8-c9

选定b2:c10

套框同上

在b12:c15输入：b12格盈亏平衡时产销量、b13格销售收益、b14

格总成本、b15格利润

在

c12输入

=c3

/（c5-c6）

c13输入

=c12*c5

c14输入

=c3+c12*c6

c15输入

=c13-c14

选定b12:c15

套框同上

作一维模拟运算表，分析在不同产销量的情况下，销售收益、总成本、

利润的变化。

在e2:h16输入：e2格年产销量、f2格销售收益、g2格总成本、h2格

利润。

在

e4格输入

8000

工具栏选编辑

\

填充

\

序列

\

选

列

、等差序列、步长值

1000

、终止值20000

确定

f3格输入

=c8

、g3格输入

=c9

、h3格输入

=c10

选定e3:h16

工具栏选择

数据

\

模拟运算表

\

输入引入列单元格

$c$4

\

确定

会自动填充数据反映和利润随产销量的变化情况

选定

e2:h16

套框同上

数字换位：

选定数字区域

工具栏选格式

\

单元格

\

数字

\

数值

\

小数点后0位数、使用千分位分隔符、确定

2.

制图表2：

用模拟运算表中

销售收益、总成本数据作xy散点图。

选工作表2、

选定

e2:g16

工具栏选

图表向导

\

标准类型

\

xy

散点图

\

无数据平滑线散点图

\

下一步

\

列

下一步

\

选标题

图表标题

输入

盈亏平衡分析

数值x轴a输入

产销量

数值y轴v输入

金额

\

下一步

\

选

新工作表

完成

在图表上调整xy轴数据，右击x轴

\

坐标轴格式

\

选刻度

最小值

8000、最大值20000、主要刻度2000、次要刻度200、数值y轴、交叉于0、确定

右击y轴

\

坐标轴格式

\

选刻度

最小值0、最大值250000、主要刻度50000、次要刻度10000、数值x轴、交叉于0、

工作表2作经过盈亏平衡点的直线数据：i3格输入经过盈亏平衡的直线数据：

在j4:k6画盈亏平衡线的x数据y数据

在j4输入公式

=

$c$12然后将j4复制到j5:j6

在k4输入y轴的最小值0

在k6输入y轴的最大值250000

在k5输入销售收益和总成本交叉时的y值，

=

c13或=c14

选定j4：k6，套框同上。

选定j4:k6

单击工具栏

复制

\

选图表2

工具栏选

编辑

\

选择性粘贴

选新系列、列、首列为分类x值、确定

图中加上一条经过盈亏平衡点的垂直线

选垂线

右击

选

数据系列格式

\

图案

\线形

自动

\数据标记

无

样式

前景颜色

无

、背景颜色

无

、大小

6磅

\

数据标志

显示值

确定

鼠标箭头指向图表背景墙显示

图表区域

右击

\

数据源

\

选定系列

1

在名称栏输入

销售收益、选定系列

2

在名称栏输入

总成本、选定系列3

在名称栏输入

盈亏平衡线

确定

。

3.

设置滚动条：

在工具栏

选择绘图

\

自选图形

\

方框拖入合适位置

\

视图

\

工具栏

\

窗体

\

滚动条

\

拖到方框中

\再选

绘图

\

自选图形

\

方框两个至滚动条上方

\右击大框

\

添加文字

输入单位售价

=

选表2

d5格输入

100

回图表2

选定小方框

在编辑栏上输入

=

并单击表2

选定

d5

回车

滚动条参数设置

右击滚动条

\

设置控件格式

\

控制

\

当前值100、最

小值85、最大值120、步长5、页步长10、单元格链接

sheet2!$d$5

确定

\

选

表2

c5单元格改为

=

d5/10

回车

返回图表2

---