本量利分析是管理会计的常用分析方法，用来研究企业成本、业务量和利润之间关系，传统的本量利分析都是基于线性、确定性假设条件下的，而未来的现实经济变量多数是不确定、随机的，管理会计人员经常需要运用随机性思维进行分析和决策，因此，管理会计人员熟练掌握Excel公式和Excel随机模拟运算法极为重要。本文重点研究当销量为随机变量时的本量利分析模型。

一、当销量为随机变量时的本量利模型

在《管理会计中Excel的高级应用——随机条件下的本量利分析模型设计与应用（Ⅰ）》（见本刊2014年第3期，下称文章一）中，已假定单价、单位变动成本为随机变量，当销量也是随机变量时，问题将变得更加复杂。国内外已有学者对不确定条件下的本量利分析模型进行了一些有益的探索，运用概率统计方法构建随机仿真模型。为分析方便，记：T=（P-V）X-F，其中，T为利润，P为单价，V为单位变动成本，X为销量，F为固定成本。

当只有销量为随机变量时，该模型为一维随机模型。由于未来的销量是随机的，所以无法计算出准确的盈利额，但是可以根据销量的概率分布计算出保本的概率和达到目标利润的概率。决策者可以据此进行科学决策。

例如，若销量X是一个满足以μ为数学期望、σ为标准差的正态分布，即X～N（μ，σ），则保本的概率为：P（X≥盈亏平衡点），保利的概率为：P（X≥目标利润销量）。运用概率统计方法，通过查表则可得到相应的概率。

然而，当销量、单价、单位变量成本都是随机变量时，问题变化为多元随机概率问题，解决方法非常复杂。如果使用Excel工具，则此类问题可迎刃而解。

运用Excel解决上述随机本量利问题的方法可归纳为公式法和模拟运算法两类。对于一般性问题运用公式法可解决，对于多元随机问题则需运用模拟运算法加以解决。

二、运用公式法解决一维随机本量利问题

（一）模型概要

1.变量特征：此处假设销量为随机变量，单价、单位变动成本、固定成本为确定性变量。

2.决策变量：保本点、保利点、保本的概率、目标利润的概率等。

3.决策方法：概率分析法。

4.关键技术：随机概率分布、微调器的使用方法。

（二）问题描述

[例1]某公司生产A产品，该产品的单价为100元/件，单位变动成本为40元/件，固定成本为40000元，销量服从均值为1000件、标准差为100件的正态分布。要求：计算盈利的概率和实现目标利润的概率。

（三）建模的技巧与步骤

1.相关函数。

①正态分布函数NORMDIST（a，b，c，d），其中，a为需要计算其分布的数值，b为均值，c为标准差，d为函数形式的逻辑值，如果d为TRUE，返回累积分布函数，如果d为FALSE，则返回概率密度函数。该函数是返回指定平均值和标准差的正态分布函数。

例如，给出正态分布N（10，2），计算X≤8的累积概率即P（X≤8），则计算公式为NORMDIST（8，10，2，TRUE）。

②正态分布函数的反函数NORMINV是正态分布函数NORMDIST的反函数，即计算给定累积概率对应的临界点（即相应随机变量的值）。该函数的使用方法在文章一中已经进行过介绍，此处从略。

2.建模步骤。本文的模型见：http：//wensubin.ys168.com之文件夹“《财务与会计》论文”之“随机条件下的本量利模型（2）.xlsx”。该模型具有以下功能：计算保本的概率；计算达到目标利润的概率；进行人机对话，给出动态可调的结论。

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

第二步：输入原始数据。在数据区（B2：C7）输入相关条件，见模型文件中“例1公式法模型”工作表中单元格区域（B2：C7）（图略）。

第三步：设计计算区。在计算区（E2：F7）输入相关公式或函数，见图中区域（E2：F7）。

第四步：设计结论区。在单元格（B10：C11）中输入结论区说明文字和相关公式，令C10=1-NORMDIST（F3，C4，C5，TRUE），C11=1-NORMDIST（F4，C4，C5，TRUE）。则C10为保本的概率，C11为达到目标利润的概率。

第五步：填加微调器。在单价、单位变动成本、固定成本、目标利润单元格填加相应的控件“微调器”，形成动态可调的随机本量利分析模型。给定目标利润，则可计算出相应的概率。

本模型的不足之处是，除销量外其他变量均为确定性变量，而现实中的单价、单位变动成本、固定成本都可能具有随机性。此时，Excel所具有的“模拟运算表”工具可帮助解决这一复杂问题。

三、运用模拟法解决多维随机本量利问题

（一）模型概要

1.变量特征：单价、单位变动成本、销量为随机变量。

2.决策变量：盈利的概率、目标利润的概率等。

3.决策方法：随机分析法、蒙特卡罗模拟法、图示法。

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

（二）问题描述

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

（三）建模的技巧与步骤

1.相关函数。本例需要运用的函数包括随机函数的反函数NORMINV、均匀分布函数RAND、纵向查找函数VLOOKUP、频率统计函数FREQUENCY等。频率统计函数表达式为FREQUENCY（A，B），其中，A为要进行统计分组的原始数据所在区域，B为分组区间。该函数计算数值在某个区域内的出现频率，然后返回一个垂直数组。由于函数FREQUENCY是一个数组函数，所以它必须以数组公式的形式输入。因此，在输入公式后，不可直接点击确定或回车键，而应同时按“Ctrl+Shift+Enter”键。

2.模型功能。本模型具有以下功能：计算盈亏平衡的概率；动态调整，观察不同盈利水平的概率；绘制利润的概率分布图，动态观察调节相关变量，给出动态可调的结论。

3.建模步骤。

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

第二步：输入原始数据，见模型文件中“例2模拟法模型”工作表中单元格区域（B2：C9）（图略）。

第三步：输入价格的概率分布数据（方法同文章一，图略）。

第四步：设计计算区。在计算区（B17：C25）中，设计计算公式，用于计算销售收入、变动成本、总成本、利润等指标（图略）。其中，销量、价格和单位变动成本为产生的随机数。销量的公式为C18=NORMINV（RAND（），C5，C6），其他公式与文章一相同。

第五步：进行保本点和利润的随机模拟试验。为了估计保本点和利润的数学期望，测算目标利润的概率，需要进行随机试验。Excel的“模拟运算表”工具可以完成此项任务。具体方法见文章一。

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

第七步：设计分组统计计算区。通过随机实验，获得了1000个随机样本，为了直观地观察利润的概率分布状况，需要进行分组统计，本文将1000个随机数分为40组进行统计，见工作表中区域（J6：K9）。输入公式为K6=MAX（H5：H1004），K7=MIN（H5：H1004），K8=40，K9=INT（（K6-K7）/K8）。

第八步：进行分组统计。在工作表中区域（J11：N54）设计分组统计区，进行分组统计。首先输入组数，在（J13：J54）输入1至42。然后输入分组的接收区间，令K13=K7，K14=K13+$K$9（注意为绝对引用），将K14复制粘贴到K15：K54，形成分组区间。然后进行统计分组，选中区域（L13：L54），在公式编辑栏输入公式：=FREQUENCY（H5：H1004，K13：K54），同时按“Ctrl+Shift+Enter”，得到统计分组结果。令M13=L13/1000，N13=N14+M14，分别复制其到M14：M54，N14：N54，得到分组统计的频率和累积频率。

第九步：设计利润的概率判断区。此处的基本原理是根据统计分组的累积频率，动态观察不同利润水平的概率。

首先填加一个微调器，用以控制组序（单元格O6），即控制器参数，其值设定为1至42。方法如图。

然后在区域（M6：O8）中输入：O7=INT（VLOOKUP（O6，J13：K54，2））；O8=VLOOKUP（O6，J12：N54，5），得到利润临界值及利润超过该数值的概率。具体见工作表中单元格区域（M6：O8）。

第十步：绘制统计图。在新工作表“例2模拟法图示”中，插入图表-柱形图，其中数据序列为$M$13：$M$53，横坐标序列为$K$13：$K$54。对图形进行美观修饰，得到利润的概率统计图。

在该工作表中，插入图表-XY散点图，其中X数据序列为$K$14：$K$54，Y数据序列为$N$14：$N$54。对图形进行美观修饰。

在图中填加两个动态文本框，单击框边，使其处于编辑状态，在编辑栏输入：='例2模拟法模型'!$M$8，则该文本框动态显示“利润大于X的概率”，其中X为动态调节的利润临界值。选中另一文本框，使其处于编辑状态，在编辑栏输入：='例2模拟法模型'!O8，则该文本框动态显示不同利润水平的概率。

在图中填加一个与第九步相同的微调器，用于动态调节利润水平。由此，得到利润的累积概率分布图。通过动态调节微调器，还可观察不同利润水平的概率（如图）。

至此，一个动态可调的多维随机本量利分析模型就完成了。该模型可以计算保本点和利润和数学期望，通过调节利润水平，动态地观察不同利润水平的概率，获得多种决策信息，非常直观形象。另外，在键盘上，每按一次“F9”键，Excel就重新进行一次随机模拟运算，可用于观察模拟运算结果的稳定性。■

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

责任编辑 李卓