计算机财务建模教学案例——本量利分析

吕志明

（天津财经大学 会计学院，天津 300222）

0 引言

本量利分析是管理会计课程当中非常重要的一项内容，传统教学方法无法直观揭示本、量、利之间的动态变化过程，也无法进行动态盈亏平衡点的分析。本文利用Excel 构建动态本量利分析相关模型，能够动态揭示本、量、利之间的变化关系以及盈亏平衡点的动态分析，能够弥补传统本量利分析教学手段的不足，丰富本量利分析教学内容，提高教学效率、改善教学效果。企业可参照本文模型，结合企业实际资料和数据，设计出适合企业自身情况的决策分析模型，从而为改善企业决策手段、提高决策质量提供保障。具体包括动态本量利分析模型和动态盈亏平衡点模型。

1 动态本量利分析模型

1.1 模型功能

动态本量利分析基本模型如图1 所示。利用滚动条调节单价、单位变动成本、当前销量、固定成本等数值时，本量利分析图会相应地进行动态调整，其中当前销量以红色垂线显示，并标注其与成本线、收入线和利润线的交点，即当前销量对应的总成本、销售收入和利润总额。

图1 动态本量利分析模型

1.2 模型构建方法与步骤

1.2.1 设置基本数据

利用滚动条设置单价、单位变动成本、当前销量和固定成本。

1.2.2 准备图表数据

第一，本量利数据。按照图2 输入数据和公式，然后利用数据表功能生成数据。

图2 本量利数据公式设置

第二，当前销量数据。因为要在图表中显示当前销量直线及其对应的总成本、销售收入和利润，所以需要按照图3 准备数据。

图3 当前销量数据

1.2.3 制作图表

第一，同时选中区域“N1：Q1”和“N3：Q4”，制作“带平滑线的散点图”，并对图表进行必要的格式设置。

第二，添加当前销量直线。选中本量利图表，添加数据系列，将“X 轴系列值”设置为“N6：N7”，将“Y 轴系列值”设置为“O6：O7”，并对图表进行必要的格式设置。

第三，添加当前销量直线与总成本线、销售收入线和利润总额线的交点系列。参照以上步骤，依次添加3 个系列，第1 个系列的X 轴系列值为“N9”，Y 轴系列值为“O9”，第2 个系列的X 轴系列值为“N10”，Y 轴系列值为“O10”，第3 个系列的X 轴系列值为“N11”，Y 轴系列值为“O11”。根据需要设置这三个系列点的格式。

1.2.4 保护工作表

在该模型中，只需用滚动条设置单价、单位变动成本、销量和固定成本数据，为此，选中“C2：C4”和“D5”，取消其单元格锁定状态，然后保护工作表。

2 动态盈亏平衡点模型

2.1 模型功能

模型样式如图4 所示。在给定的单价、单位变动成本和固定成本下，根据一系列销量制作成本线、收入线和利润线，并以红色垂直线显示当前条件下的盈亏平衡销量及其与以上三条曲线的交点。当通过微调钮调整单价、单位变动成本和固定成本时，各曲线将随之动态调整。此外，还可通过微调钮设置当前销量，并在图中显示当前销量垂线及其与利润线的交点，随着销量的变化，该垂线也将动态调整。显然，当前销量垂线与盈亏平衡垂线间的水平距离即为安全边际。该模型可以直观地动态反映当前给定条件下的盈亏平衡点以及安全边际。

图4 动态盈亏平衡点模型

2.2 模型设计方法与步骤

2.2.1 设置基本数据

在该模型中，通过微调钮设置变动成本、单价、固定成本和当前销量。为此，需要进行如下设置：

第一，变动成本。利用文本框与微调钮，将变动成本设置为5～10 的整数，对以上各对象进行必要的格式设置，并组合为一个对象。

第二，单价。利用文本框与微调钮，将单价设置为15～20 的整数，对以上各对象进行必要的格式设置，并组合为一个对象。

第三，固定成本。利用文本框与微调钮，将固定成本设置为10 万～15 万的整数，对以上各对象进行必要的格式设置，并组合为一个对象。

第四，当前销量。利用文本框与微调钮，将当前销量设置为0～4 万的整数，对以上各对象进行必要的格式设置，并组合为一个对象。

2.2.2 准备图表数据

第一，成本线、收入线和利润线数据。按照图5 所示，准备计算成本、收入和利润的计算公式，并利用数据表功能求解各销量对应的成本、收入和利润。

图5 成本、收入和利润公式

第二，盈亏平衡销量直线数据。按照图6 所示，准备制作盈亏平衡销量垂线数据和公式。

图6 盈亏平衡销量垂线数据

第三，盈亏平衡线与收入线交点坐标。按照图7 所示，准备制作盈亏平衡线与收入线交点坐标数据。

图7 盈亏平衡线与收入线交点坐标

第四，当前销量直线数据。按照图8 所示，准备制作当前销量直线的数据。

图8 当前销量直线数据

第五，当前销量直线与利润线交点坐标。按照图9 所示，准备当前销量直线与利润线交点坐标。

图9 当前销量直线与利润线交点

2.2.3 制作图表

第一，根据图5 所示数据，制作散点图，以显示不同销量下的收入线、成本线和利润线。生成图表后，可对图表刻度、格式等进行必要的设置。

第二，为图表添加一个系列，用以显示盈亏平衡线，X 轴系列值为“D16：D17”，Y 轴系列值为“E16：E17”。对系列格式进行必要的设置。

第三，为图表添加一个系列，用以显示盈亏平衡线与收入线的交点，X 轴系列值为“D19”，Y 轴系列值为“E19”。对数据点格式进行必要的设置。

第四，为图表添加一个系列，用以显示当前销量线，X 轴系列值为“D13：D14”，Y 轴系列值为“E13：E14”。对系列格式进行必要的设置。

第五，为图表添加一个系列，用以显示当前销量线与利润线的交点，系列X 值坐标为“D21”，Y 值坐标为“E21”。对数据点格式进行必要的设置。

第六，在图表中添加两个文本框，分别动态显示盈亏平衡销量和当前利润。在“D23”单元格输入公式”＝IF（A1＞A3，＂盈亏平衡销量＂＆＂＝＂＆ROUND（D19，0），＂＂）”，然后将某个文本框与“D23”进行链接。在“D24”单元格输入公式“＝＂当前利润＂＆＂＝＂＆ROUND（E21，0）”，然后将另一文本框与“D24”进行链接。其中round（）为四舍五入函数，利用该函数将结果保留为整数。可对单元格进行必要的格式设置，并放置于图表之上。

可以设置图表的叠放次序，将其置于最底层，以显示其上的文本框和各组合对象。

2.2.4 保护工作表

各微调钮链接单元格均位于A 列，可将A 列隐藏，然后保护工作表。

3 模型的使用方法及效果

以上分别构建了动态本量利分析模型和动态盈亏平衡点模型。在动态本量利分析模型中，用户可通过滚动条分别设置单价、销量、单位变动成本和固定成本的数值，图形将根据设置结果动态显示本量利分析图，并以醒目的红色垂线显示当前销量，以及销量垂线与收入线、成本线和利润线的交点，并动态标注显示当前的收入、成本和利润金额，从而更加直观地揭示出本、量、利三者之间的关系。在动态盈亏平衡点模型中，用户可通过滚动条设置单价、单位变动成本和固定成本三个外生变量，以及当前销量这一内生变量，图形中将以不同颜色垂线分别表示当前销量和盈亏平衡点销量，及其与收入线、成本线和利润线的交点，从而可以动态显示盈亏平衡点，并与当前实际本量利情况进行直观对比。以上两个模型，极大地弥补了传统本量利教学的不足，丰富了本量利教学的内容，能够有效提高教学质量、改善教学效果。企业可参照该模型，结合企业实际资料和数据，设计出适合企业自身情况的决策分析模型，从而为改善企业决策手段、提高决策质量提供保障。