EXCLE动态模型在本量利中的应用分析

杨忠 夏利华

摘 要：通过一个实训案例的设计，运用EXCLE工具建立动态模型，使读者和学生通过绘制标准式本量利分析图，设置基本指标计算区、辅助计算区和本量利分析报告区，熟悉控件工具箱的运用，掌握调节按钮的使用、数值上下限超范围情况的处理，以及特殊步长要求的实现技巧，提升本量利分析的质量和效率。

关键词：EXCLE;动态模型;本量利分析

中图分类号：F23 文献标识码：A doi：10.19311/j.cnki.1672-3198.2019.35.062

本量利分析是成本-业务量-利润关系分析的简称，是指在變动成本计算模式的基础上，以数学化的会计模型与图式来揭示固定成本、变动成本、销售量、单价、销售量等变量之间的内在规律联系。本量利分析基本公式是（单价-单位变动成本）*销售量-固定成本=（p-b）*x-a。企业营业利润与产品的边际贡献有着密切的联系，可以将本量利分析法应用于全面预算、成本控制和责任会计中。本量利分析与经营风险分析相关联，可促使企业努力降低风险。运用EXCLE工具建立动态模型，掌握分析规则与技巧，对提升本量利分析的质量和效率有着重要的意义，下面通过实训案例的设计做以详细分析。

1 实训案例基础数据

ABC公司2018年营业周期内的预测相关数据如表1所示。

2 实训案例内容设计

（1）建立一个工作表，将其命名为“标准式本量利分析图”，在“标准式本量利分析图”工作表上，根据初始数据设置利润计算区，计算营业收入、变动成本、贡献边际和利润指标。

（2）在同一工作表上，设置辅助计算区，计算总成本、保本点、安全边际等指标，确定绘制标准式本量利分析图相关变量的各项参数;确定绘制传统式本量利分析图。

（3）利用视图下拉菜单中的工具栏，调出控件工具箱，利用上下按钮，根据相关参数的上下限范围和步长数据，建立动态数据（注意：上下限超范围和特殊步长的实现），将静态本量利分析图调整为动态本量利分析图。

（4）设置本量利分析报告区，反映在特定业务量条件下的保本点、利润水平和安全边际指标。

3 实训案例具体实施步骤

（1）建立一个工作表，将其命名为“标准式本量利分析图”。

（2）在A10：B14区域计算利润表。贡献式利润表计算步骤及公式如表2所示。

（3）在A16：B26区域输入辅助计算数据。其中单位贡献边际=单价-单位变动成本，即=B5-B6;贡献边际率=单位贡献边际/单价，即=B17/B5;变动成本率=单位变动成本/单价，即=B6/B5;总成本=固定成本+单位变动成本*销量，即=B7+B4*B6;保本量=固定成本/单价-单位变动成本，且保留两位小数，即ROUND（B7/（B5-B6），2）;保本额=固定成本/贡献边际率，且保留两位小数，即=ROUND（B7/B18，2）;安全边际量=保本量-销量，即=B4-B21;安全边际额=营业收入-保本额，即=B10-B22;安全边际率=安全边际量/本期销量，即=B23/B4;保本作业率=保本量/本期销量，即=B21/B4，如图1所示。

（4）绘制标准式本量利分析图.采用“高低点法”绘制本量利分析图，画出收入线。

①选中最低点（0，0），高点（1800，504000）。

②单击工具栏中“插入”选项，选中散点图-仅带数据标记的散点图，右击选择数据，弹出“设置数据区域”对话框，单击“添加”，输入“收入线”，选中x轴，y轴数据区域，单击“确定”，右击图中的点，添加趋势线，勾选“线性”和“显示公式”。

③固定成本线、总成本线等同理可得，如图2所示。

（5）将静态本量利分析图调整为动态本量利分析图。

①利用视图下拉菜单中的工具栏，调出控件工具箱。

打开文件-选项-自定义功能区-主选项卡-勾选“开发工具”-确定;点击“开发工具”-插入-表单控件-数值调节钮（窗体控件）。

②利用上下按钮，根据相关参数的上下限范围和步长数据，建立动态数据（注意：上下限超范围和特殊步长的实现）。

“本期销量（x）”：右击“数值调节钮”-设置控件格式-控制-依次输入相应的“当前值、最大值、最小值、步长、单元格链接$B$4”-确定。

“单价（p）”：右击“数值调节钮”-设置控件格式-控制-依次输入相应的“当前值1400、最大值1800、最小值0、步长100、单元格链接”-确定。

“单位变动成本（b）”：右击“数值调节钮”-设置控件格式-控制-依次输入相应的“当前值、最大值、最小值、步长、单元格链接”-确定。

“固定成本（a）”：右击“数值调节钮”-设置控件格式-控制-依次输入相应的“当前值、最大值、最小值、步长、单元格链接”-确定。

注意：在设置“固定成本”的数值时，由于最大值限制为“30000”，故另选一个单元格，将这个单元格内的数值按比例缩小10倍，“单元格链接”链接到其他单元格，当前单元格=链接的其他单元格*10，如图3所示。

③点击数值调节钮，即可看到折线图变动的过程。

（6）设置本量利分析报告区。

按照主要分析信息设置报告区内容，具体设计内容如表3所示。

当本期销售量为1400件时，营业利润为178000元，此时保本点为（352.94，98823.5）;安全边际绝对量为（1047.06，293176.47）;安全边际率为74.79%;保本作业率为25.21%。

4 实训小结及注意事项

本次实训涉及的计算量较多，从营业收入到安全边际率，这些指标之间是可以联系起来的，要掌握这些指标的计算;在计算完指标的基础上，选取各特殊点，通过插入散点图，绘制传统的本量利分析图;依据“数值调节钮”功能，可以将传统的本量利分析图变成动态的本量利分析图，在实际运用中更具有价值，具体注意事项如下：

（1）控件的取值介于0-30000，当遇到需要调节的范围大于数值调节钮规定的数值范围时，可以利用乘数，设置中间单元格，使控件链接于其他表格，同时使取值是该表格的倍数（例如10倍），可以通过控件调整该表格来调整取值，帮助实现自动化。

（2）无论是“利润表”，还是“辅助计算区”中的数值，要使用原始数据中的单元格利用链接和公式带入计算，为方便初始数据变化的情况下，两表数值，以及本量利分析图和报告区中的数值随当前数据的变化而变化，有利于建立动态本量利分析模型。

（3）设置本量利分析报告区，反映在特定业务量条件下的保本点、利润水平和安全边际指标。在表述报告区时也要用特殊的表达方式，从而使当前数值发生改变时，报告区的结果也能随之变动。

参考文献

[1]周丽媛，付艳.EXCLE在财务管理中的应用[M].哈尔滨：东北财经大学出版社，2014.

[2]韩良智.EXCLE在财务管理中的应用[M].北京：清华大学出版社，2015.