Excel实战技巧：如何使用Excel数据表创建蒙特卡罗模型和预测

引言：本文学习整理自exceluser.com，非常好的一篇文章，特分享于此，供有兴趣的朋友参考。

大多数电子表格模型和预测都使用的是确定性方法。例如，如果要预测一段时间的利润，确定性模型将使用一个数字来预测销售额，使用另一个数字来预测运营费用，等等。然而，更有用的方法是使用概率方法，由蒙特卡罗方法支持。

蒙特卡罗方法

蒙特卡罗方法支持“随机”或“概率”系统。

据百度百科：蒙特·卡罗方法（MonteCarlo method），也称统计模拟方法，是二十世纪四十年代中期由于科学技术的发展和电子计算机的发明，而被提出的一种以概率统计理论为指导的一类非常重要的数值计算方法。是指使用随机数（或更常见的伪随机数）来解决很多计算问题的方法。与它对应的是确定性算法。蒙特·卡罗方法在金融工程学，宏观经济学，计算物理学（如粒子输运计算、量子热力学计算、空气动力学计算）等领域应用广泛。

下图1说明了正态概率分布，这可能是大多数业务使用的最佳方法。

图1

要使用此方法，首先要设置要由该曲线定义的关键假设。然后，工作簿中的模型从该正态分布中随机选择值，在计算中使用这些值，记录关键结果，多次重复该过程，然后汇总结果记录。这个过程被称为蒙特卡罗方法。

在本文中，将向你展示如何使用Excel模拟运算表来记录每次计算产生的结果。

然而，在详细讲解之前，需要事先声明两件事。

首先，无论何时打开使用模拟运算表的蒙特卡罗分析，请确保蒙特卡罗工作簿是唯一打开的工作簿。这是因为它需要多次重新计算，如果打开了其他工作簿，它们也会不必要地重新计算。这可能会使你的模拟非常慢。

其次，你可能会抱怨本文的示例。

如果你从事财务工作，可能会抱怨以下示例大大简化了通常复杂的财务建模过程。在这种情况下，我会回答说我让模型变得非常简单，这样你就可以理解我建议对你的标准建模过程进行的更改。

如果你不在金融领域工作，可能会抱怨我应该使用你自己专业的例子，而不是金融。在这种情况下，我会回答我使用简单的损益表作为示例，因为即使你从事工程、运营、营销或其他任何工作，也了解简单的损益表。因此，你将能够了解模型正在做什么，并且将能够将我的技术应用于你自己的模型和预测。

标准的预测方法

下图2演示了确定性预测，这是标准方法。它实际上是在问：“如果我们的销售额为100，销售成本为35%，运营费用为45，税率为25%，那么我们的利润是多少？”

图2

与大多数此类预测不同，该预测在列F中明确说明了其假设。当然，在现实生活中，每个假设都可能得到单独的分析和预测的支持。

同样，这种方法的问题在于我们知道预测将是不正确的，因为大多数预测都是不正确的，而且我们无法表达利润预测可能有多大的合理性。

Stats表

下图3演示了在新工作簿中设置的统计表。

图3

这张表将我们的四个关键假设转化为五个结果，我们可以在预测的每次迭代中使用这些结果。

要从潜在销售的正态曲线计算随机数，我们需要知道销售曲线的均值和标准差。如果可以直接计算这些值，则可以直接将它们输入到单元格E5和F5中。

然而，黄色单元格说明了一种不太严格的方法来找到这些数字，这种方法效果很好。

正如上图1底部的数字所示，与均值的一个标准偏差代表了来自正态曲线的大约68%的潜在结果，而两个标准差代表大约95%。

因此，如果我们估计最高可行的销售额，可以说该数字代表高于均值的第二个标准差，并将其输入到统计表的单元格C5中；可以说我们对最低可行销售额的估计代表低于均值的第二个标准差，并在表格的单元格D5中输入该数字。

为了再次验证我们的假设，我们说实际销售额有95%的可能性介于这两个数字之间。因此，最大值和最小值的平均值是均值，由以下公式计算：

E5：=AVERAGE(C5:D5)

并且标准偏差仅为最大值和最小值之间范围的四分之一，通过以下公式计算得出：

F5：=(C5-D5)/4

现在，我们需要Excel从正态分布中返回一个随机数，该正态分布由单元格E5中的均值和单元格F5中的标准差定义。为此，我们使用以下公式：

H5: =NORM.INV(RAND(),E5,F5)

现在，将上述公式向下复制至单元格区域E5:H5，如上图3表中所示。然后输入列I中显示的标签，将这些标签指定为列H中相邻单元格的名称。

设置随机模型

下图4演示了我们将使用的模型。数据列“假设”的四个公式中的每一个都引用刚刚在统计表中命名的四个值之一。

图4

在工作簿中添加一个新工作表，将其命名为“Model”，在列D中输入下面的公式：

D5:=c.Sales

D6:=c.PctCOGS

D9:=c.OpExp

D12:=c.TaxRate

数据列“数量”中的公式依赖列D中的假设：

G5:=D5

G6:=D6*G5

G7:=G5-G6

G9:=D9

G10:=G7-G9

G12:=D12*G10

G13:=G10-G12

最后，创建单元格名称，即将图4中文本所在列的值作为其左侧相邻单元格的名称。

注意，每次重新计算工作簿时，模型都会生成不同的结果，现在需要为许多重新计算自动捕获这些结果。

设置Data表

我们现在要建立一个数据表。此表将自动重新计算 Excel，返回下图5第2行中命名的项目的值，在第4行中记录这些值，重新计算，在第5行中记录当前项目，依此类推……直到表的最后一行。

图5

在工作簿中添加一个新工作表，将其命名为“Data”。在列Seq中，输入1至5000的连续序号。在下面单元格中输入公式：

C3:=m.NetProfit

D3:=m.Sales

E3:=m.PctCOGS

F3:=m.OpExp

G3:=m.TaxRate

现在是设置数据表的时候了。执行此操作后，Excel将计算工作簿5000次，因为数据表将包含5000行，完整的表的每一行都将包含每次计算后返回到上图5的第3行的值。

建议在开始数据表之前，将计算选项设置为手动。

选择单元格区域B3:G5003，单击功能区“数据”选项卡“预测”组中的“模拟分析——模拟运算表”。在“模拟运算表”对话框中，单击“输入引用列的单元格”中的输入框，将光标置于该框中，然后在工作表中数据表外单击任意空白单元格，单击“确定”，完成数据表。

注意，由于计算选项设置为手动，你可能会看到表中的数据都与第3行数据相同，可以按F9键重新计算，这将显示新数据。

然后，将数据表列C至列G中的数据区域部分分别命名为第2行对应的标题名。

汇总数据表的结果

我们将在数据表右侧的部分中汇总结果，显示的区域如下图6所示。

图6

使用单元格区域I1:I3的值作为相邻的单元格区域J1:J3中每个对应单元格的名称，使用单元格区域M1:M2的值作为相邻的单元格区域N1:N2中每个对应单元格的名称。

列Seq开始于单元格I7和M7，在其下方单元格输入1至21的连续数字，作为汇总数据的计数区间间隔。

在相应的单元格中输入下面的公式：

J1:=ROUNDUP(MAX(Profits),0)

J2:=ROUNDDOWN(MIN(Profits),0)

J3:=COUNT(I:I)

N1:=ROUNDUP(MAX(Sales),0)

N2:=ROUNDDOWN(MIN(Sales),0)

列ProfBins包含定义利润的开始值和结束值的数据，此列中的前两个公式：

J7:=MinProfits

J8:=(MaxProfits-MinProfits)/(NumBins-1)+J7

将单元格J8中的公式复制至单元格J27，J27中的值应该与MaxProfits值相等。前面已提到过，如果复制后单元格中的值不变，这是由于计算选项设置成手动的缘故，可以按F9键重新计算，从而刷新数据。

选择J6:K27，使用标题值为相应列的数据区域命名。

同样，在列SalesBins中的公式：

N7:=MinSales

N8:=(MaxSales-MinSales)/(NumBins-1)+N7

将单元格N7中的公式复制至单元格N27，N27中的值应该与MaxSales值相等。

选择N6:O27，使用标题值为相应列的数据区域命名。

现在可以使用FREQUENCY函数返回在每个区间中找到的项目数。

选择单元格区域K7:K27，输入数组公式：

=FREQUENCY(Profits,ProfBins)

同样，选择单元格区域O7:O27，输入数组公式：

=FREQUENCY(Sales,SalesBins)

完整的Stats表

现在可以完成统计表的K列和L列，如下图7所示。

图7

在相应单元格中输入公式：

K5:=AVERAGE(Sales)

K6:=AVERAGE(PctCOGS)

K7:=AVERAGE(OpExp)

K8:=AVERAGE(TaxRate)

K9:=AVERAGE(Profits)

将单元格区域K5:K9中的单元格命名为其右侧列L中相邻单元格中的值。

蒙特卡罗预测

下图8在工作表“Reports”中，显示了我们迄今为止所做的工作所产生的预测。每次重新计算工作簿时，它可能会略有变化，但不应有明显变化。

图8

预测分为4个部分：

• 预计净利润显示简单的损益表，它使用显示的每个项目的均值。
• 关键百分位数部分显示了销售额和利润的可能值。它表明销售额和利润等于或小于25%行中显示的值的可能性为25%，或者更少；表明销售额和利润有50%的可能性等于50%行中显示的值，或者更少……等等。此外，损失百分比值显示发生损失的可能性为15%。
• 销售直方图显示了我们的模型执行的5000个销售预测中销售结果的分布。
• 利润直方图显示了5000个利润预测的分布。

在图8所示的表中，相应单元格输入的公式：

D4:=s.Sales

D5:=s.PctCOGS*D4

D6:=D4-D5

D8:=s.OpExp

D9:=D6-D8

D10:=s.TaxRate*D9

D11:=D9-D10

每当按下F9 键时，Excel都会重新计算5000次并得到新的均值，此预测将显示该均值。注意，每次重新计算时，这个简单的预测变化很小。

在单元格区域B15:B18中输入图8中的百分比，相应单元格输入公式：

C15:=PERCENTILE.INC(Sales,$B15)

D15:=PERCENTILE.INC(Profits,$B15)

然后，将公式向下复制至第18行。

在单元格D21中输入公式：

D21:=COUNTIFS(Profits,”<0″)/COUNT(Profits)

使用Data表中单元格区域N6:O27和单元格区域J6:K27中的数据分别创建销售直方图和利润直方图。

最终的结果如上图8所示。