1.实训条件

某公司生产一种产品B，该产品的每件售价为50元。该产品的成本构成如下：直接材料10元/件，直接人工3元/件，变动性制造费用2元/件，变动性销售及管理费用3元/件。企业每年发生固定性成本为40000元，假定该产品目前的最高生产能力为5000件。

2.实训要求

（1）建立盈亏平衡模型（如图74所示）。

图74　线性本量利分析模型

（2）对本量利关系进行动态展示。

3.实训步骤

第一步，构建基本的线性本量利分析模型。

在数据区输入相关条件。

①在A12：B15中，计算销售收入、贡献边际、总成本和利润指标。

②设计结论性文字。在相关单元格内输入相关公式：

E2=B9/B8

D5=“当前销量为”＆B11＆“，”＆“利润为”＆B15＆“元”

D7=“当前售价=”＆B2＆“元”

D9=“盈亏平衡点：”＆ROUND（E2，0）＆“件”

第二步，为绘制盈亏平衡动态图示作准备。

①计算不同销售量下的贡献边际、固定成本和利润，如图75所示。

图75　不同销售量下的贡献边际、固定成本和利润

②列出盈亏平衡销售量的垂线绘图点，如图76所示。(www.daowen.com)

图76　盈亏平衡销售量的垂线绘图点

③列出当前销售量下的垂线绘图点，如图77所示。

图77　当前销售量下的垂线绘图点

第三步，绘制图形。

①选择菜单“插入”→“图表”→“XY散点图”→“带平滑线和数据标记散点图”，如图78所示。

图78　添加散点图

②单击鼠标右键，选择“数据选择”，首先添加名称为“贡献边际”的系列，其中X轴代表销售量，Y轴代表利润，如图79所示。

图79　添加系列

③依次添加固定成本、利润、盈亏平衡线、当前销售线的系列，如图710所示。

图710　盈亏损平衡决策图

第四步，添加控件以建立动态调整模型。

①打开菜单“视图”→“工具栏”→“窗体”。

②用鼠标单击微调器按钮，鼠标变为“+”字，在图形中画一个大小适中的微调器。

③设置微调器。右键单击微调器，选择“设置控件格式”，设置该控件，如图711所示。

图711　设置控件格式

④添加当前销售量和利润的说明框。在图形中绘制一个大小合适的文本框，单击文本框，使其处于编辑状态，在Excel的编辑栏中输入“=盈亏平衡决策模型！D6”。此时，该文本框与单元格D6之间建立了动态链接。该文本框显示的内容为单元格D6的内容。

⑤用同样的方式在表格中添加当前售价、单位变动成本、固定成本的控件，并据此绘制利润敏感性分析图，如图712所示。

图712　利润敏感性分析图