编者按

经济复杂性的不断提高使得管理会计在经济决策中的基础性作用和战略支撑作用日益突显。与财务会计相比，管理会计涉及更多的计算和分析，更加具有多样性、复杂性和灵活性，需要会计人员根据具体问题进行具体分析。随着大数据时代的来临，这种计算和分析的复杂性更加明显。“工欲善其事，必先利其器”。Excel具有强大的计算功能和可再开发的特点，是解决管理会计问题的有效工具，然而它所具备的高级运算功能及其设计方法在管理会计中的推广应用还非常有限。为此，从本期开始，本刊将陆续刊发“管理会计中Excel的高级应用”这一专题文章，以期推动企业管理会计工作效率的提升。

成本——数量——利润分析（简称本量利分析）用来研究企业成本、业务量和利润之间的关系，是企业进行利润规划的基本方法。传统的本量利分析都是基于线性、确定性假设条件下的，而未来的现实经济变量多数是不确定的、随机的。因此，设计随机条件下的本量利分析模型更接近现实。而随机条件下的本量利分析涉及复杂的数学计算，难以通过手工计算来实现。Excel强大的计算分析功能为开展随机条件下的本量利分析提供了方便，笔者将分（Ⅰ）（Ⅱ）两篇文章分别详细讲解。

一、随机条件下的本量利分析模型

基本的本量利分析模型都假设成本与产量总是处于线性关系，即单价不变、单位变动成本不变、固定成本不变。同时，假设产量、单价、单位变动成本、固定成本等都是确定性变量，决策时这些变量是已知的。基本的本量利分析模型直观明了，在帮助管理者理解生产经营和利润规划时起到了较好的作用。但是，由于基本模型是对本量利关系的理想表达，因此在进行现实决策时存在许多缺陷，最重要的两点体现在：

一是线性假设经常不符合现实。本量利分析模型中的相关变量经常是非线性的，例如，单价会随着市场供需关系的不同而发生变化，也会随着购销合同的不同而不同，如批量折扣；单位变动成本和固定成本也并非始终不变，它会随着技术水平、生产效率、材料采购价格、工资率水平等因素的变化而变化。

二是确定性假设不符合现实。例如，生产和销售经常存在季节性；未来的市场存在随机性，进而单价也具有随机波动性；销量会随着市场供求的变化而存在不确定性；市场的不确定性经常导致单位变动成本的不确定性；等等。由于现实经济变量存在着大量的不确定性，基本的本量利分析模型显然难以适应管理决策的需要，从而很大程度上限制了其有效性。

基于上述分析，随机条件下的本量利模型为：利润=（单价-单位变动成本）×销量-固定成本，其中，销量、单价、单位变动成本、固定成本都可能是不确定的或随机的。当单价、单位变动成本、固定成本也是随机变量时，管理者很难通过确定的计算方法直接计算其确定的保本点和保利点，只能运用概率分析法计算保本点的数学期望、保利点的数学期望、保本的概率、保利的概率等，从而进行相关决策。

二、基于Excel的随机本量利分析模型（Ⅰ）

随机本量利分析模型涉及复杂的概率计算，必须借助计算机工具方能实现，而Excel所具备的动态计算功能和可再开发的特性，为设计随机本量利模型提供了方便。下面笔者通过实例来说明运用Excel的高级功能设计随机本量利分析模型的方法和技巧。

（一）模型概要

1.主要变量。本文假设单价、单位变动成本为随机变量（当销售量同时也是随机变量时，问题将变得更加复杂，该模型的设计方法将在下一篇文章中详细讲解）。

2.决策变量包括保本点的数学期望、盈利的概率等。

3.决策方法包括随机分析法、蒙特卡罗模拟法、盈亏平衡分析、图示法。

4.关键技术包括连续型随机数的产生方法、离散型随机数的产生方法、模拟运算表的使用方法、微调器的使用方法、动态可调图形的绘制方法等。

（二）问题描述

某公司生产A产品，该产品的单位变动成本服从均值为40元/件、标准差为5元/件的正态分布，在相关范围内的固定成本为40000元，假设该产品的最高生产能力为2000件。该产品单价的概率分布为：

（三）建模的技巧与步骤

1.相关函数。

①随机数函数RAND（），可均匀产生0到1的随机数。

②正态分布的反函数NORMINV（a，b，c），其中，a为正态分布的概率，b为数学期望，c为标准差。该函数返回给定概率时相应的临界点（即相应随机变量的值）。

例如，给出正态分布N（10，2），累计概率为0.95对应的随机数，即P（X≤A）=0.95，求A值，则某单元格=NORMINV（0.95，10，20）。

③纵向查找函数VLOOKUP（a，b，c，d），其中：a为需要在数据表第一列中查找的数值；b为数据表区域；c为数据表区域中希望给出的匹配值的列序号，c为1时，返回数据表区域第1列的数值，c为2时，返回数据表区域第2列的数值，以此类推；d为一逻辑值，指明函数VLOOKUP查找时是精确匹配还是近似匹配。如果为false或0，则返回精确匹配；如果为TRUE或1，将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于a的最大数值。该函数可搜索数据区中满足条件的元素，确定行序号，然后得出该行给定列的数值。

例如，在区（A1：C5）的A列查找数值“2”，然后给出该行第3列的数据，则某单元格=VLOOKUP（2，A1：C5，3）。

2.建模步骤。本文的计算模型见：http：//wensubin.ys168.com之文件夹“《财务与会计》论文”之“随机本量利模型1.xlsx”。建议读者先下载该文件，边阅读边操作。

第一步：构思模型界面。根据问题描述，模型界面应包括数据区、计算区、模拟试验区、结论区、绘图辅助区。

第二步：输入原始数据。在数据区输入相关条件，见下图中单元格区域（B2：C7）。

第三步：输入价格的概率分布数据。为了便于产生价格的随机数，在下图中区域（B9：D14）输入价格的概率分布（注意：在第一列输入累计概率分布），见下图中区域（B9：D14）。

第四步：设计计算区。在计算区（B15：C23）中设计计算公式，用于计算销售收入、变动成本、总成本、利润等指标（图略）。其中，价格和单位变动成本为产生的随机数。

单位变动成本的产生方法是根据单位变动成本服从N（40，5）的正态分布，使用正态分布的反函数产生单位变动成本的随机数，令单元格B19=NORMINV（RAND（），C3，C4）。

单价服从离散型分布，无法直接通过函数产生随机数，笔者介绍一种离散型随机数的单价产生方法，即输入离散型随机变量的概率分布，列出概率和累计概率（注意：第1列应为累计概率），见（B10：D14），令单元格B17=VLOOKUP（RAND（），B10：D14，3）。

第五步：进行保本点和利润的随机模拟试验。为了估计保本点和利润的数学期望，需要进行随机试验。Excel中的“模拟运算表”工具可以完成此项任务。方法如下：在模拟运算区（F5：G1004）的F列中输入一列数据（从1到1000），代表试验次数。令G4=C22，H4=C23，即希望计算的变量。选中（F4：H1004）的区域，选择菜单“数据——模拟分析——模拟运算表”，输入引用行的单元格、引用列的单元格。“输入引用行的单元格”是指在进行模拟运算时行变量所在的单元格，即在计算单元格G4、H4时需要引用的行变量所在的位置。“输入引用列的单元格”是指在进行模拟运算时列变量所在的单元格，即在计算单元格G4、H4时需要引用的列变量所在的位置。在本例中，由于只有列变量值，没有行变量值，且列变量值1-1000为试验次数，与保本点和利润的计算无关，因而“输入引用行的单元格”为空，“输入引用列的单元格”中输入表中的任一空的单元格即可（见下图）。点击“确定”，计算出试验1000次的保本点和利润的1000个样本。

第六步：估算保本点和利润的数学期望。令L3=AVERAGE（G5：G1004），O3=AVERAGE（H5：H1004），计算出保本点和利润的数学期望的估计值。

第七步：模拟运算不同销售量条件下的各项指标。在模拟运算区域（I7：N48）的I列输入销售量数据。令J7=C18，K7=C21，L7==MIN（J7：K7），M7=MAX（J7-K7，0），N7=MAX（K7-J7，0）。选中（I7：N48）的区域，选择菜单“数据——模拟分析——模拟运算表”，在“输入引用列的单元格”中输入$C$16。确定后，计算出不同销售量条件下的各项指标。

第八步：绘制盈亏平衡动态分析图。选中数据区域（I8：N48），插入折线图，点击“确定”后，绘制出相关指标的动态图形（绘图方法较简单，此处略），然后依次选中“成本收入最小值”、“盈利”、“亏损”3个序列，进行“点右键-更改系列图表类型-选择面积堆积图”的操作，根据喜好，更改序列颜色，即可得到所想要的模型（见下图）。

三、微调器和动态文本框的制作方法

上图中固定成本、销售量可利用微调器进行调节，通过调节能观察不同的固定成本和销售量条件下的保本点和期望利润的变化状态，从而形成一个动态可调的图形。以当前销售量为例，微调器和动态可调的文本框的制作方法如下：

1.打开菜单“开发工具”，插入“表单控件”中的“数值调节钮（窗体控件）”。

2.用鼠标单击“数值调节钮（窗体控件）”，鼠标变为“+”字，拖动鼠标，在图形中画一个大小适中的微调器；

3.设置微调器。右键单击微调器，选择“设置控件格式”，设置该控件（见下图）。单击“确定”，至此，该微调器制作完成。该微调器与工作表“盈亏平衡决策模型”的单元格C16建立了链接。通过控制微调器可控制工作表“盈亏平衡决策模型”的单元格C16的值，该单元格的值界于100-2000内，且随着微调器的调节，按步长100变动；

4.在图形中添加相关文本框。

5.添加“动态文本框”。在图形中绘制一个大小合适的文本框，单击该文本框的边缘，使其处于编辑状态，在Excel的编辑栏中输入“=盈亏平衡决策模型!C16”，然后“回车”。这时，该文本框与“盈亏平衡决策模型”的单元格C16建立了动态链接，文本框内显示的是单元格C16的内容。当微调器动态调节销售量（单元格C16）时，该文本框也会随之改变。其他动态文本框的方法相同。

至此，一个动态可调的本量利分析模型就完成了。该模型可对销售量、固定成本两个变量进行动态调节，还可动态地显示单价、单位变动成本、期望保本点、期望利润等信息，非常直观、形象。另外，在键盘上，每按一次“F9”键，Excel就重新进行一次随机模拟运算，可用于观察模拟运算结果的稳定性。■

（本文受国家自然科学基金资助项目71372008、教育部人文社会科学研究一般项目10YJA630164、江苏省高校哲学社会科学研究项目2011SJD630017的资助）

责任编辑 李卓