Excel 中的蒙特卡洛模拟简介

• 谁使用蒙特卡洛模拟？

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

• 如何模拟离散随机变量的值？

• 如何模拟普通随机变量的值？

• 问候卡公司如何确定要生产多少张卡片？

我们希望准确估计不确定事件的概率。 例如，新产品的现金流 (NPV) 具有正净现值的概率是多少？ 我们的投资组合的风险因素是什么？ 蒙特卡洛模拟使我们能够对存在不确定性的情况进行建模，然后在计算机上进行数千次的模拟。

在接下来的五章中，你将看到如何使用 Excel 执行蒙特卡洛模拟的示例。

许多公司使用蒙特卡洛模拟作为其决策过程的重要组成部分。 下面是一些示例。

• 通用汽车、Proctor 和 Gamble、辉瑞、Bristol-Myers Squibb 和 Eli Lilly 使用模拟来估算新产品的平均回报和风险因素。 在通用汽车，首席执行官使用这些信息来确定哪些产品进入市场。

• 通用汽车对诸如预测公司净收入、预测结构和采购成本，以及确定其对各种风险 (（如利率变化和汇率波动) ）的易感性等活动进行模拟。

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

• Proctor 和 Gamble 使用模拟对外汇风险进行建模和优化对冲。

• 西尔斯使用模拟来确定应从供应商处订购每个产品系列的单位数，例如，今年应订购的 Dockers 裤子数量。

• 石油和制药公司使用模拟来计算“实际选项”，例如扩大、合同或推迟项目的选项的价值。

• 财务规划师使用蒙特卡洛模拟来确定客户退休的最佳投资策略。

在单元格中键入公式 =RAND () 时，将获得一个同样可能假定介于 0 和 1 之间的任何值的数字。 因此，大约 25% 的时间，应得到一个小于或等于 0.25 的数字;大约 10% 的时间应得到至少为 0.90 的数字，依此依序。 若要演示 RAND 函数的工作原理，请查看文件 Randdemo.xlsx，如图 60-1 所示。

首先，将单元格 C3 复制到 C4：C402 公式 =RAND () 。 然后将区域命名为 C3：C402 数据。 然后，在 F 列中，可以跟踪单元格 F2) (400 个随机数的平均值，并使用 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) ，则不会告诉我们生成的其他随机数的值。

假设对日历的需求受以下离散随机变量控制：

需求	Probability
10,000	0.10
20,000	0.35
40,000	0.3
60,000	0.25

我们如何让 Excel 多次播放或模拟对日历的需求？ 技巧是将 RAND 函数的每个可能值与日历的可能需求相关联。 以下分配可确保在 10% 的时间内出现 10,000 个需求，依此进行。

需求	分配的随机数
10,000	小于 0.10
20,000	大于或等于 0.10 且小于 0.45
40,000	大于或等于 0.45 且小于 0.75
60,000	大于或等于 0.75

若要演示需求模拟，请查看文件 Discretesim.xlsx，如下一页的图 60-2 所示。

模拟的关键是使用随机数从表范围 F2：G5 (名为 lookup) 启动 查找 。 大于或等于 0 且小于 0.10 的随机数将产生 10,000 个需求：大于或等于 0.10 且小于 0.45 的随机数将产生 20,000 个需求：大于或等于 0.45 且小于 0.75 的随机数将产生 40,000 个需求：大于或等于 0.75 的随机数将产生 60,000 个需求。 通过将公式 RAND () 从 C3 复制到 C4：C402，可以生成 400 个随机数。 然后，通过将公式 VLOOKUP (C3，lookup，2 ) 从 B3 复制到 B4：B402，生成日历需求的 400 次试用或迭代。 此公式可确保任何小于 0.10 的随机数生成 10,000 个需求，0.10 到 0.45 之间的任意随机数生成 20,000 个需求，依此类说。 在单元格范围 F8：F11 中，使用 COUNTIF 函数确定产生每个需求的 400 次迭代的分数。 当我们按 F9 重新计算随机数时，模拟概率接近假设的需求概率。

如果在任意单元格中键入 公式 NORMINV (rand () ，mu，sigma) ，将生成具有平均值 mu 和标准偏差 sigma 的正常随机变量的模拟值。 此过程在图 60-3 所示的文件 Normalsim.xlsx 中进行了说明。

假设我们想要模拟一个平均值为 40,000，标准偏差为 10,000 的正常随机变量的 400 次试验或迭代。 (可以在单元格 E1 和 E2 中键入这些值，并将这些单元格分别命名 为 mean 和 sigma。) 将公式 =RAND () 从 C4 复制到 C5：C403 会生成 400 个不同的随机数。 将 公式 NORMINV (C4，mean，sigma) 从 B4 复制到 B5：B4：B40：B403 ，从普通随机变量生成 400 个不同的试验值，平均值为 40,000，标准偏差为 10,000。 当我们按 F9 键重新计算随机数时，平均值仍然接近 40,000，标准偏差接近 10,000。

实质上，对于随机数 x，公式 NORMINV (p，mu，sigma) 生成具有平均值 mu 和标准偏差 sigma 的正常随机变量的 p第百分位数。 例如，单元格 C4 中的随机数 0.77 (见图 60-3) 在单元格 B4 中生成的大约是正常随机变量的第 77 个百分位，平均值为 40,000，标准偏差为 10,000。

在本部分中，你将了解如何将蒙特卡洛模拟用作决策工具。 假设对情人节卡的需求受以下离散随机变量控制：

需求	Probability
10,000	0.10
20,000	0.35
40,000	0.3
60,000	0.25

问候语卡售价为 4.00 美元，每个卡的可变成本为 1.50 美元。 剩余卡必须按每卡 0.20 美元的价格处理。 应打印多少张卡片？

基本上，我们模拟每个可能的生产数量 (10,000、20,000、40,000 或 60,000) 多次 (例如 1000 次迭代) 。 然后，确定哪个订单数量在 1000 次迭代中产生最大平均利润。 可以在文件 Valentine.xlsx 中找到此部分的数据，如图 60-4 所示。 将单元格 B1：B11 中的区域名称分配给单元格 C1：C11。 为单元格区域 G3：H6 分配名称 查找。 我们的销售价格和成本参数在单元格 C4：C6 中输入。

在此示例中，可以在单元格 C1 中输入 (40,000) 的试用版生产数量。 接下来，在单元格 C2 中创建一个随机数，公式 为 =RAND () 。 如前所述，使用公式 VLOOKUP (rand，lookup，2) 来模拟单元格 C3 中卡的需求。 (在 VLOOKUP 公式中， rand 是分配给单元格 C3 的单元格名称，而不是 RAND 函数。)

销售的单位数量在我们的生产数量和需求中较小。 在单元格 C8 中，使用 公式 MIN (生成、需求) *unit_price来计算我们的收入。 在单元格 C9 中，使用 生成的公式*unit_prod_cost计算总生产成本。

如果我们生产的卡片比需求多，剩余的单位数等于生产减去需求：否则不会留下任何单位。 我们使用公式 unit_disp_cost*IF (产生>需求、生产-需求、0) 计算单元格 C10 中的 处置成本。 最后，在单元格 C11 中，我们将利润计算为 收入- total_var_cost-total_disposing_cost。

我们希望一种有效的方法来多次按 F9 (例如，每个生产量的 1000) ，并为每个数量算出预期利润。 这种情况是双向数据表来拯救我们的情况。 (有关数据表的详细信息，请参阅“使用数据表进行敏感度分析”第 15 章。) 本示例中使用的数据表如图 60-5 所示。

在单元格范围 A16：A1015 中，输入与 1000 个试验) 对应的数字 1-1000 (。 创建这些值的一种简单方法是首先在单元格 A16 中输入 1。 选择单元格，然后在“编辑”组中的“开始”选项卡上，单击“填充”，然后选择“序列”以显示“序列”对话框。 在“ 序列 ”对话框中（如图 60-6 所示）中，输入“步骤值 1”和“停止值”1000。 在 “系列输入 ”区域中，选择“ 列” 选项，然后单击“ 确定”。 数字 1-1000 将在 A 列中输入，从单元格 A16 开始。

接下来，在单元格 B15：E15 中输入可能的生产数量 (10,000、20,000、40,000、60,000) 。 我们希望计算每个试用编号 (1 到 1000) 和每个生产数量的利润。 我们通过输入 =C11 引用在数据表的左上角单元格 C11) 计算的利润 (公式， (A15) 。

现在，我们已准备好欺骗 Excel 来模拟每个生产数量的 1000 次需求迭代。 (A15：E1014) 选择表范围，然后在“数据”选项卡上的“数据工具”组中，单击“What If Analysis”，然后选择“数据表”。 若要设置双向数据表，请选择生产数量 (单元格 C1) 作为行输入单元格，然后选择任何空白单元格， (选择单元格 I14) 作为列输入单元格。 单击“确定”后，Excel 将模拟每个订单数量的 1000 个需求值。

若要了解此工作原理，请考虑单元格区域 C16：C1015 中数据表放置的值。 对于每个单元格，Excel 将在单元格 C1 中使用值 20,000。 在 C16 中，列输入单元格值 1 放置在空白单元格中，单元格 C2 中的随机数将重新计算。 然后，相应的利润记录在单元格 C16 中。 然后，将列单元格输入值 2 置于空白单元格中，C2 中的随机数再次重新计算。 在单元格 C17 中输入相应的利润。

通过将单元格 B13 复制到 C13：E13 公式 AVERAGE (B16：B1015) ，我们计算每个生产数量的平均模拟利润。 通过将单元格 B14 复制到 C14：E14 公式 STDEV (B16：B1015) ，我们计算每个订单数量的模拟利润的标准偏差。 每次按 F9 时，都会针对每个订单量模拟 1000 次需求迭代。 生产 40,000 张卡总是产生最大的预期利润。 因此，生产 40,000 张卡片似乎是适当的决定。

风险对我们决策的影响      如果我们生产了 20,000 张而不是 40,000 张卡，我们的预期利润将下降约 22%，但根据利润) 的标准偏差衡量，我们的风险 (下降近 73%。 因此，如果我们非常厌恶风险，生产20,000张卡可能是正确的决定。 顺便说一句，生产 10,000 张卡片的标准偏差总是为 0 张，因为如果我们生产 10,000 张卡，我们将始终出售所有这些卡片，没有任何剩余纸。

平均利润的置信区间      在这种情况下，一个自然要问的问题是，我们 95% 确定真正的平均利润会下降， 此间隔称为 平均利润的 95% 置信区间。 任何模拟输出平均值的 95% 置信区间由以下公式计算：

在单元格 J11 中，当使用公式 D13–1.96*D14/SQRT (1000) 生成 40,000 个日历时，计算平均利润 95% 置信区间的下限。 在单元格 J12 中，使用公式 D13+1.96*D14/SQRT (1000) 计算 95 % 置信区间的上限。 图 60-7 显示了这些计算。

我们 95% 确信订购 40,000 个日历时的平均利润在 56,687 美元到 62,589 美元之间。

1. GMC经销商认为，2005年特使的需求将正常分配，平均为200，标准偏差为30。 他接待特使的费用是25,000美元，他以40,000美元的价格卖了一个特使。 没有全价出售的特使中一半可以以30,000美元的价格出售。 他正在考虑订购200、220、240、260、280或300名特使。 他应该点多少？

2. 一家小超市正试图确定他们每周应该订购多少本人员杂志。 他们认为他们对人员的需求受以下离散随机变量的约束：

   需求	Probability
   15	0.10
   20	0.20
   25	0.30
   30	0.25
   35	0.15

3. 超市为每份人员支付1.00美元，然后以1.95美元出售。 每个未售出的副本可以返回 0.50 美元。 存储应订购多少份人员？