风险型量本利分析模型的建立(正式版)

1、风险型量本利分析模型的建立（蔡明杰 湖南商学院会计系 湖南长沙 410205 陈四清 湖南商学院会计系 湖南长沙 410205） 文章摘要： 在传统的量本利分析中，一般先假定销售量、销售单价、单位变动成本和固 定成本中的某几个参数保持不变， 然后再进行分析， 因此， 这种量本利分析为一种无风险的 确定型分析。 但在实际工作中， 这些因数受很多因素的影响， 企业外部市场环境和企业内部 因素的变化都会导致产品的销售量、 销售单价、 单位变动成本和固定成本发生变化， 在分析 某些因素时假设其他因素不发生变化是不现实的， 因此， 在这种情况下， 有必要进行风险型 量本利分析。关键词： 量本利分析；蒙特

2、卡罗模拟；风险型 在传统的量本利分析中，一般先假定销售量、销售单价、单位变 动成本和固定成本中的某几个参数保持不变， 然后再进行分析， 因此， 这种量本利分析为一种无风险的确定型分析。 但在实际工作中， 这些 因数受很多因素的影响， 企业外部市场环境和企业内部因素的变化都 会导致产品的销售量、 销售单价、单位变动成本和固定成本发生变化， 在分析某些因素时假设其他因素不发生变化是不现实的， 因此，在这 种情况下，有必要进行风险型量本利分析。风险型量本利分析的方法通常采用联合概率分析，即对销售量、 销售单价、单位变动成本和固定成本确定其可能发生的数值和概率， 然后根据其可能值及发生概率进行计算。

3、当只有一个参数 （比如销售 单价）是不确定的， 计算过程就比较简单， 但当多个参数都不确定时， 计算就非常繁琐。假如我们估计的销售量、销售单价、单位变动成本 和固定成本的发生可能概率次数均为5次，就需要计算5X 5X 5X5=625 个组合，计算工作量非常大。利用蒙特卡罗模拟方法可以在 EXCEL!子表格处理软件上很方便地进行这种分析和计算。蒙特卡罗（ Monte Carlo ）方法，又称随机抽样或统计试验方法， 属于计算数学的一个分支， 它是在本世纪四十年代中期为了适应当时 原子能事业的发展而发展起来的。 蒙特卡罗方法的基本原理及思想如 下：当所要求解的问题是某种事件出现的概率， 或者是某个

4、随机变量 的期望值时，它们可以通过某种“试验”的方法，得到这种事件出现 的频率，或者这个随机变数的平均值，并用它们作为问题的解。这就 是蒙特卡罗方法的基本思想。 蒙特卡罗方法通过抓住事物变化的数量 和特征，利用数学方法来加以模拟，即进行一种数字模拟实验。它是 以一个概率模型为基础， 按照这个模型所描绘的过程， 通过模拟实验 的结果，作为问题的近似解。 可以把蒙特卡罗解题归结为三个主要步 骤：构造或描述概率过程； 实现从已知概率分布抽样；建立各种估计 量。蒙特卡罗模拟法的理论基础是概率论与数理统计。 由于包含着随 机因素，因而所建立的数学模型中的目标变量也成了随机变量， 而通 过其他手段难以确定

5、随机变量的概率分布与统计特征。 应用蒙特卡罗 模拟技术可以获得较多、 较全面的统计信息， 从而可以使决策者在量 本利分析过程中取得更加全面的认识，以便有利于作出最佳决策。我们用EXCEI提供的RANDBETWEE函（数所生成的，在（0、99） 区间中均匀分布的随机数进行变换的方法， 来生成所需要的随机变量 的观测值。本文就以一个案例具体予以讨论。一、基本数据某公司甲产品的销售量、 销售单价、 单位变动成本和固定成本均 为非确定性因素，其可能达到的水平及概率情况如下表所示10单位变动成本固定戍本11粕率累计 槪率随机数对应的 随机数可能 值槪率累计 慨率随机数対应的 随机数可能值12045045

6、g 口013:0580.3Sg苏0?5W0130.210361535151J0.260.64386338100000140J510.67346636150.200.S464<Sj64105000150.140.S1犷$07160.1203S-W9534103000160.100.91S1170.041.00旳96110000170.060.991959】1S18003kOO9"対971919目标利润丫囂 CU|图表1 :基本数据2慨率累计 槪率 DM 0.1730.060.110.220390.tf70.90602S70.2S8O.OS0.9S90.021.00单价随机数范回对

7、应的 随机数可能 值槪率按率随机数 菸围对应的 随机数可能道050SOOOP 0.0S0.0$0" 70206166150000.190,g筑S21173817：D(XW0.250.52M刃27rr 空39300000 220J452735224即対67400000.14O.SS7487742690300000.110.99S3-93S32S爼対58500000011.0030二、模型建立步骤利用蒙特卡罗模拟在 EXCEL上进行风险型量本利分析的方法和步骤如下：第一步：根据图中各参数的累计概率确定随机数范围及对应的随机数，以便于在EXCELh进行模拟计算；第二步：在单元格 A26、C

8、26、E26、G26中输入随机数公式RANDBETWEEN 99），产生0-99的随机数；如图示。-* 齿瓷云b咸丰杠1柱Ji利徊愧利fit电肖皆 a4餅陡机 散单位曲本戡*39300009830711呑305000325OCK>1035S240000JS工卫X142鼻80002J53OOO11S731S123430000一頁9'368103000ZKSOOOSO77119034WOO32% -% 亠*-1-41 5950001 -307 1 445<XKK>30221838lOCXMMJ1(XXXK>2 5OOO375005&30000G7225S15

9、>115000LU71207141ABC I DEFGHIT56782222901502130Poooo7r2069 r165i：1(XMXM>30003 73005022903<xxm)92r2S64 r134910000055000076921153S502332*2000015r21S3 r171$P50Q0-I5QW5C25O502450它 oooo1321io君1324&5000145000L1S518125S0254*"800044r0 ”1366105000-330004166717222第三步：利用 VLOOKUI®数寻找符合随机数

10、的销售量、单价、单位变动成本和固定成本总额，公式分别为：单元格B26 “二VLOOKUP（A26, $D$3:$E$9,2） ”；单元格 D26二VLOOKUC26, $I$3:$E$9,2）； 单元格 F26二VLOOKUPE26, $D$12:$E$18,2）；单元格 H26 “二VLOOKUP（G26 $I$12:$J$16，2）；第四步：在单元格I26中输入利润计算公式“二B26* （D26-F26）-H26”；在单元格J26中输入保本量计算公式“二H26/（ D26-F26）”；在单元格K26中输入保利量计算公式“二（H26+$B$19 /（ D26-F26）；第五步：选中单元区域

11、A26: K26,拖动单元格区域右下角的填充柄向下一直拖动到5025行，即进行5000次模拟计算；（如图表2） 第六步：在单元格N3:R9中存放模拟计算结果，如下图：LMN0pQR1模拟计算结果2期望值标准差最小值最大值变异丟数3利润148553141907*102000SOOOOO0.9554保本星11627786ilooMTn0.5505保利量1121311621S5291600000.54562965612166S0006000004107单价2320300.1108单位变动成本15190.1019固疋成本1004595061950001loooo0.05010利润为负的欖率0.122图

12、表3:模拟计算结果各参数期望平均值的计算公式分别为：N3二AVERAGI26 : I5025）； 单元格 N4二AVERAGE26: J5025）；单元格 N5二AVERAGES6: K5025）； 单元格 N6二AVERAGE526: B5025）；单元格 N7二AVERA（E）26: D5025）； 单元格 N8二AVERAGERS: F5025）;单元格 N9=AVERA（EH26: H5025）；各参数的标准差的计算公式： 03二STDEVI26 : I5025 ）； 04二STDEV（J26：J5025）；O5=STDE（VK26：K5025）；O6=STDE（VB26：B5025）

13、；O7=STDE（VD26：D5025）；O7=STDE（VF26：F5025）；O8=STDE（VH26：H5025）；各参数最小值的计算公式： P3=MIN（I26 ：I5025 ）；P4=MIN（ J26：J5025）；P5=MIN（K26：K5025）；P6=MIN（B26：B5025）；P7=MIN（D26：D5025）；P8=MIN（F26：F5025）；P9=MIN（H26：H5025）；各参数最大值的计算公式： Q3=MA（XI26 ：I5025 ）；Q4=MA（XJ26：J5025）；Q5=MA（XK26：K5025）；Q6=MA（XB26：B5025）；Q7=MA（XD26

14、：D5025）；Q8=MA（XF26：F5025）；Q9=MA（XH26：H5025）；单元格R3: R9中为各参数的变异系数，首先选定 R3: R9输入 计算公式 “二（03: O9/N3: N9”，同时按下“ CTRL+SHIFT+ENT”R 键即可（数组公式的输入方法） ；单元格 N10 利润为负数概率的计算公式“=C0UNTI（F I26 :I5025， ” <0” ）/5000 ”；第七步：利用EXCEL勺FREQUENCES# （返回一组数据的频率分布）计算利润、保本量、保利量的概率，其计算过程如下首先对利润和保本量进行分组， 其中利润分组存放在单元格 L13:L25,保本量

15、和保利量分组存放在单元格 013: 024单元格，并分别在 单元格M13 M25和单元格P13: P24输入概率分布图的X轴坐标数据 （区间）。然后在单元格 N13：N25 中输入利润概率计算公式“ =FREQUENCY（I26 : I5025 , L13: 125） /5000 ”，在单元格 Q13: Q24 中输入保本量概率计算公式，“二FREQUENGJ26: J5025, 013 O24 /5000 ”, 在单元格R13: R24中输入保本量概率计算公式，“二FREQUENCK26:K5025, 013: 024 /5000 ”，上述公式均需采用数组公式输入方法, 输入完成后，同时按下

16、“ CTRL+SHIFT+ENT”组合键；就可以得到利润、保本量和保利量的概率分布结果如图所示LN0FQR11利润損率分布分析保本量及保利量撅率分析12莱统分粗区间槻率丟銳分组区间煤本星槪率保利星損率1300000小以下0,0005000了以下0.0000,00014-500000.027l(MX>0予L00.2960.02215(K0.10111500010150.3990,27416500000.14420000M二00.1950.2721?100000于10C.159F 2500020250.0550.2261815000010150.1593000025300.0170.0971

17、920000015200.1133500050350.020O.D252025000020250,0764000035400,0040.047213000000.0794500040舶0.0000.00022350000303500525000045500.00*001623400000站400.02S$500050-550.QCJC.00S24450000屯斗咅0.024 16000060以上0.0000.0002550000045kl_t0.016图表4:利润、保本量和保利量的概率分布最后利用EXCELS绘图工具可以绘制利润、保本量和保利量的概率分布图，分别如下图所示图表5:利润概率分布图

18、表6:保本量和保利量的概率分布三、结果分析这样，通过模拟计算结果即可对 A产品的利润及风险进行全面 分析。由模拟计算结果可以看出，利润期望值为148553元，利润主要分布在-50000200000 （概率约为0.7 ），亏损的概率约为0.14。 保本量期望值为14162件，保本量主要分布在5000件-20000件之间 （概率约为0.89 ）,若A产品的销售量大于20000件，那么盈利的概 率大于89%而且通过模拟计算可知，销售量的期望值为29656件，因此该企业生产A产品的风险较低。当A产品的目标销售利润为50000元时，为达到此目标利润的保 利量期望值为21213件，最小销售量为 8529件，而最大销售量为 160000件，而由图可知，保利量主要分布在10000-25000件之间，概率为76.8%,也就是说，当销售量大于25000件时，企业就有76.8% 的可能性实