在 Excel 中 Monte Carlo 模拟简介

注意:  我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的 英文版本 以便参考。

本文是从Microsoft Excel 的数据分析和建模业务通过 Wayne l。 Winston 改动。

  • 供谁使用 Monte Carlo 模拟?

  • 在单元格中键入=RAND()时,会发生什么情况?

  • 您可以如何模拟离散随机变量的值?

  • 您可以如何模拟一个普通的随机变量的值?

  • 贺卡公司如何确定多少卡片以生成?

我们希望准确地估计确定事件的概率。例如,正净现值 (NPV) 将新产品的现金流的概率是什么?什么是风险因素我们投资项目组合的?Monte Carlo 模拟,我们可以演示不确定性,然后将它们的次数千位计算机上播放模型情况。

注意: Monte Carlo 模拟来自 1930s年和年代来估计所需的以 detonate atom 炸弹链反应工作成功的概率过程中执行的计算机模拟名称。本文中所涉及物理学家已大风扇的以便他们所提供的模拟Monte Carlo代码名称。

在接下来的五个章节,您将看到如何使用 Excel 执行 Monte Carlo 模拟的示例。

许多公司使用 Monte Carlo 模拟作为其决策过程的重要部分。下面是一些示例。

  • 通用汽车公司、 Proctor 和场赌博、 Pfizer、 卡纸 Myers Squibb 和黎 Lilly 使用模拟估计平均收益和新产品的风险因素。在 GM、 此信息用于通过 CEO 确定哪些产品有市场。

  • GM 用于预测的公司的净收入、 预测结构和采购成本和确定其受到不同类型的风险 (如利率更改和汇率波动) 等活动的模拟。

  • Lilly 使用模拟来确定每个药物的最佳工厂容量。

  • Proctor 和场赌博使用模拟模型,并以最佳方式篱外来 exchange 风险。

  • Sears 使用模拟来确定应从供应商排序的每个产品线多少单位,例如,对今年应订购的 Dockers trousers 的数字。

  • Oil 和药物公司使用模拟值"实选项,"如展开、 合同,或推迟项目的选项的值。

  • 财务计划使用 Monte Carlo 模拟客户端的退休确定最佳投资策略。

在单元格中键入公式=RAND()时,您将获得一个数字,都可能假定 0 到 1 之间的任何值。因此,25%左右的时间,您应该收到数字小于或等于 0.25;您应该收到大量的时间的 10%的解决方法至少 0.90,等等。若要演示的 RAND 函数的工作原理,一下文件 Randdemo.xlsx,显示图 60-1。

书籍图像
图 60-1 演示 RAND 函数

注意: 当您打开的文件 Randdemo.xlsx 时,您看不显示图 60-1 中的同一个随机数字。RAND 函数始终自动重新计算时打开工作表或工作表中输入新的信息时生成的数字。

首先,从复制单元格 C3 到 C4:C402 公式=RAND()。然后您命名区域 C3:C402数据。然后,在 F 列中,您可以跟踪 400 的随机数字 (单元格 F2) 的平均值并使用 COUNTIF 函数来确定 0 和 0.25、 0.25 和 0.50、 0.50 和 0.75,和 0.75 和 1 之间的分数。当你按 F9 键时,重新计算的随机数字。400 数的平均值总是大约 0.5,并且大约 25%的结果为 0.25 间隔的通知。这些结果是与定义的一个随机数一致。另请注意,生成 RAND 不同单元格中的值无关。例如,如果在生成随机数字单元格 C3 大量 (例如,0.99),它可以告诉我们执行任何操作有关其他生成随机数字的值。

假设日历 demand 受下列离散的随机变量:

请求

概率

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

我们可以如何具有 Excel 播放,或模拟,此日历要求多次?技巧就是将 RAND 函数的可能值的每个日历的可能要求与相关联。以下分配确保 10000 请求将出现的时间,10%,依此类推。

请求

分配的随机数字

10,000

小于 0.10

20,000

大于或等于 0.10,且小于 0.45

40000

大于或等于 0.45,且小于 0.75

60,000

大于或等于 0.75

若要演示的需求的模拟,查看文件 Discretesim.xlsx,在下一页上显示图 60-2。

书籍图像
图 60-2 模拟离散随机变量

我们模拟的关键是要使用的随机数字以启动表区域 F2:G5 (称为查阅) 中的查找。大于或等于 0 且小于 0.10 随机数将产生 10000; 的要求大于或等于 0.10 且小于 0.45 随机数将产生 20000; 的要求大于或等于 0.45 且小于 0.75 随机数将产生 40000; 的要求和随机数字大于或等于 0.75 将产生 60000 的要求。您可以通过从复制 C3 到 C4:C402 公式rand ()生成 400 随机数字。生成 400 试用版或通过从复制 B3 到 B4:B402 公式VLOOKUP(C3,lookup,2)日历请求的小版本。此公式可确保任何随机数字小于 0.10 生成 10000 的要求,任何 0.10 和 0.45 之间的随机数字生成 demand 20000,依此类推。在单元格区域 F8:F11 中,使用 COUNTIF 函数来确定生成每个请求我们 400 迭代的分数。当我们按 F9 重新计算的随机数字时,模拟的概率是接近我们假设的 demand 概率。

如果您在任何单元格中键入公式NORMINV(rand(),mu,sigma),您将生成一个普通的随机变量遇到平均值mu和标准偏差sigma的模拟的值。在显示图 60-3 文件 Normalsim.xlsx,说明了此过程。

书籍图像
图 60-3 模拟一个普通的随机变量

假设我们要模拟 400 试用版或迭代 10000 的标准偏差的平均值为 40000 与普通随机变量。(您可以在单元格 E1 和 E2 中,键入以下值和名称这些单元格意味着sigma,分别。)将公式=RAND()从 C4 复制到 C5:C403 生成 400 不同的随机数字。将从复制 B4 到 B5:B403 公式NORMINV(C4,mean,sigma)生成不同试用值 400 从 10000 的标准偏差的平均值为 40000 与普通随机变量。当我们按 f9 重新计算的随机数字时,平均值保持接近 40000 和接近 10000 标准偏差。

实际上,为随机数字x,公式NORMINV(p,mu,sigma)生成一个普通的随机变量mu平均值和标准偏差sigmap个百分点值。例如,随机数字在单元格 C4 0.77 (请参阅图 60-3) 生成在单元格 B4 中大约 77th 的百分点普通的随机变量 40000 平均值和标准偏差为 10000。

在此部分中,您将看到如何蒙特卡罗模拟可用作决策工具。假设情人节贺卡的需求,受下列离散的随机变量:

请求

概率

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

贺卡销售 4.00 美元,并生成每张卡片的变量成本为 $1.50。必须在成本为 $0.20 每个卡的释放剩余的卡片。应打印多少卡片?

基本上,但我们多次 (例如,1000年多迭代次数) 模拟每个可能的生产数量 (10000、 20000、 40000,或 60000)。然后,我们确定哪些订单数量产生的最大的平均利润通过 1000年多迭代次数。您可以找到有关文件 Valentine.xlsx,显示图 60-4 的这一节的数据。您为单元格 C1:C11 分配中单元格 B1:B11 的区域名称。单元格区域 G3:H6 分配名称查找。在单元格 C4:C6 输入我们销售价格和成本参数。

书籍图像
图 60-4 情人节卡片模拟

您可以在单元格 C1 中输入试用生产数量 (在本例中 40000)。接下来,在单元格 C2 公式=RAND()中创建的随机数字。如前文所述,您可以模拟加大与VLOOKUP(rand,lookup,2)公式的单元格 C3 中的卡片。(在 VLOOKUP 公式中, rand是分配给单元格 C3 中,不 RAND 函数的单元格名称。)

销售数量是我们生产数量和请求的较小值。在单元格 C8,则计算我们包含公式的收入MIN (生成,需) * 单价。在单元格 C9,则计算包含公式的总生产成本产生 * unit_prod_cost

如果我们产生大于请求中的多个卡,单位数留下等于生产减去 demand;无单位中留下否则。我们在计算中包含公式的单元格 C10 我们处置成本unit_disp_cost * IF (产生 > demand,产生 – 要求,0)。最后,在单元格 C11,我们计算我们的利润作为收入-total_var_cost-total_disposing_cost

我们希望为每个生产数量按 F9 多次 (例如,1000年),并统计每个数量我们预期的利润的有效方法。这种情况下是一个双向模拟运算表进入我们修复。(请参阅第 15 章"敏感度分析与模拟运算表,"模拟运算表有关的详细信息)。此示例中使用模拟运算表显示了图 60-5。

书籍图像
图 60-5 贺卡模拟的双向模拟运算表

在单元格区域 A16:A1015 中,输入数字 1-1000 (对应于我们 1000年试用版)。若要创建这些值的一种简单方法是通过在单元格 A16 中输入1开始。选择该单元格,然后在开始选项卡在编辑组中,单击填充,并选择要显示系列对话框中的系列。在系列对话框,显示在图 60-6,输入 1 步骤值和 1000年停止值。在系列中区域中,选择选项,然后单击确定。将数字 1-1000年列中输入单元格 A16 中启动。

书籍图像
使用系列对话框中填写试用数字 1 至 1000年图 60-6

下一步,我们在单元格 B15:E15 中输入 10000,20000、 40000 (60000) 我们可能生产数量。我们要计算每个试用号码 (1 到 1000年) 利润和每个生产数量。我们输入= C11引用利润 (在计算单元格 C11) 我们模拟运算表 (A15) 的左上角单元格中的公式。

我们已准备就绪到技巧 Excel 到模拟 1000年多迭代次数对每个生产数量的需求。选择表区域 (A15:E1014),然后在数据选项卡上的数据工具组中,单击 What If 分析,然后选择模拟运算表。要设置双向模拟运算表,请选择行输入单元格为我们生产数量 (单元格 C1) 并选择作为列输入单元格 (我们选择了单元格 I14) 任何空白单元格。单击确定后, Excel 模拟 1000年要求值的每个订单数量。

若要了解为什么这适用,请考虑模拟运算表中的单元格区域 C16:C1015 所加盖的值。对于每个这些单元格,Excel 将在单元格 C1 中使用 20000 的值。在 C16,列输入单元格的值为 1 位于在空白单元格和单元格 C2 重新计算中的随机数字。在单元格中 C16 然后记录对应的利润。然后 2 列单元格中输入的值放在一个空白单元格,然后再次重新计算单元格 C2 中的随机数字。在单元格 C17 中输入相应的利润。

通过从单元格 B13 复制到 C13:E13 AVERAGE(B16:B1015)的公式,我们将计算每个生产数量的平均模拟的利润。通过将复制单元格 B14 到 C14:E14 STDEV(B16:B1015)的公式,我们计算标准偏差的我们模拟利润的每个订单数量。我们按 F9,每次为每个订单数量模拟 1000年迭代的要求。始终生成 40000 卡片会产生的最大的预期的利润。因此,它将显示,产生 40000 卡片是正确的决策。

在我们的决策的风险的影响    如果我们产生 20000,而不是 40000 卡片,我们预期的利润降低约 22%,但我们风险 (如测量的利润的标准偏差) 降低几乎 73%。因此,如果我们非常不到风险,产生 20000 卡可能是正确的决策。如果我们产生 10000 卡片,我们将始终销售所有这些无任何 leftovers 因为,始终产生 10000 卡具有顺便,标准偏差为 0 的卡片。

注意: 此工作簿中的计算选项设置为自动除表。(使用公式选项卡上的计算组中的计算命令)。此设置确保我们模拟运算表除非按 F9,这是一个好主意,因为大型模拟运算表会慢工作,如果重新计算每次在工作表中键入内容时不会重新计算。请注意,在此示例中,按 F9,时的平均值的利润将更改。因为不同的 1000年随机数序列用于生成对每个订单数量要求按 F9,每次发生这种情况。

置信区间意味着利润    这种情况下问自然问题是,到哪些间隔是我们的 95%确保处于真实平均值利润?此时间间隔称为95%利润的平均值的置信区间。以下公式计算 95%置信区间任何模拟输出的平均值:

书籍图像

在单元格 J11,则计算 95%置信区间平均值利润上的下限时 40000 日历所生成的公式D13–1.96*D14/SQRT(1000)。在单元格 J12,则计算公式D13+1.96*D14/SQRT(1000)与我们 95%置信区间内的上限值。这些计算显示图 60-7。

书籍图像
图 60-7 95%置信区间平均值利润时 40000 日历中的排序

我们正在确保我们平均值的利润时 40000 日历中的排序 $56,687 和 $62,589 之间的 95%。

  1. GMC 经销商认为 2005年代表的请求将以正常方式分发平均值为 200 与 30 的标准偏差。接收代表其成本为 $25000,然后他 40000 美元的销售代表。所有不完整的价格销售代表的一半可以出售 30000 美元。他正在考虑排序 200、 220、 240、 260、 280 或 300 代表。他订购多少?

  2. 小型超市尝试确定多少份人员杂志应订购每周。他们认为他们的人员的要求受下列离散的随机变量:

    请求

    概率

    15

    0.10

    20

    0.20

    25

    0.30

    30

    0.25

    35

    0.15

  3. 超市为每个副本的人员的支付 $1.00 和 $1.95 销售它。可以为 $0.50 返回每个未售出的副本。多少份人员应商店顺序?

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×