Excel 简介 Monte Carlo 模拟

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

本文通过堡 Winston 与Microsoft Excel 数据分析和业务建模进行了改编。

  • 谁使用 Monte Carlo 模拟?

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

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

  • 如何模拟正常随机变量的值?

  • 贺卡公司如何确定要出具多少张卡?

我们希望准确估计不确定事件的概率。 例如, 新产品的现金流将具有正的净现值 (NPV) 的概率是多少? 我们的投资阵容有哪些风险因素? Monte Carlo 模拟使我们能够模拟展示不确定因素的情况, 然后在计算机上进行上千次播放。

注意: Monte Carlo 模拟的名称来自1930s 和1940s 期间执行的计算机模拟, 以估计 atom 炸弹到 detonate 所需的链反应将成功运行的概率。 此工作中涉及的 physicists 是很大的赌博风扇, 因此它们将模拟代码名称Monte Carlo

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

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

  • 常规汽车、Proctor 和 Gamble、Pfizer、Bristol-Myers Squibb 和黎黎国 Lilly 使用模拟来估计新产品的平均回报和风险系数。 在 GM, CEO 将使用此信息来确定哪些产品走向市场。

  • GM 对活动 (如预测公司净收益) 使用模拟, 预测结构性和购买成本, 并确定其对不同类型风险 (如利率更改和汇率波动) 的敏感程度。

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

  • Proctor 和 Gamble 使用模拟来建模和优化树外部交换风险。

  • Sears 使用模拟来确定应从供应商订购每个产品行的多少个单位, 例如, 本年度应订购的 Dockers trousers 的数量。

  • 石油和药品公司使用模拟值 "真正选项", 例如用于扩展、收缩或推迟项目的选项值。

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

当您在单元格中键入公式= RAND ()时, 将获得一个数字, 该数字可能会采用介于0和1之间的任何值。 因此, 大约 25% 的时间, 你应该获得一个小于或等于0.25 的数字;大约 10% 的时间应获得一个至少0.90 的数字, 依此类推。 若要演示 RAND 函数的工作方式, 请参阅图60-1 中所示的文件 Randdemo。

书籍图像

注意:  当您打开文件 Randdemo 时, 您将看不到图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), 它不会告诉我们其他随机数字的值无关。

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

需求

Probability

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, 如图60-2 中的 "下一页" 所示。

书籍图像

我们的模拟的关键是使用随机号码从表范围 F2: G5 (已命名的查阅) 启动查找。 大于或等于0且小于0.10 的随机数字将产生10000的要求;大于或等于0.10 且小于0.45 的随机数字将产生20000的要求;大于或等于0.45 且小于0.75 的随机数字将产生40000的要求;而大于或等于0.75 的随机数字将产生60000的要求。 通过从 C3 复制到 C4 来生成400随机数: C402 公式RAND ()。 然后, 通过从 B3 复制到 B4, 生成400试验 (或迭代) 日历需求: B402 公式VLOOKUP (C3、lookup、2)。 此公式确保任何小于0.10 的随机数都会生成10000的请求, 0.10 和0.45 之间的任何随机数都会产生20000的要求等。 在单元格区域 F8: F11 中, 使用 COUNTIF 函数确定我们的400迭代的分数是每个需求。 按 F9 重新计算随机数字时, 模拟的概率接近于假定的需求概率。

如果你在任何单元格中键入公式NORMINV (rand (), mu, sigma), 你将生成一个模拟值, 表示具有平均值mu和标准偏差sigma的标准随机变量的模拟值。 此过程将在 Normalsim (如图60-3 所示) 的文件中阐释。

书籍图像

假设我们要模拟400试验或迭代, 对于平均值为40000的常规随机变量, 标准偏差为10000。 (可以在单元格 E1 和 E2 中键入这些值, 并分别命名这些单元格的含义sigma。) 将公式= RAND ()从 C4 复制到 C5: C403 生成400个不同的随机数字。 从 B4 复制到 B5: B403 公式NORMINV (C4、均值、sigma)通过平均值为40000的标准随机变量和10000的标准偏差生成400不同的试用值。 当我们按 F9 键重新计算随机数字时, 平均值保持接近 40000, 标准偏差接近10000。

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

在本部分中, 你将看到 Monte Carlo 模拟如何用作决策制定工具。 假设情人节卡片的要求由以下离散随机变量控制:

需求

Probability

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

该贺卡销售 $4.00, 每张卡的可变成本为 $1.50。 剩余卡必须以每张卡的 $0.20 成本进行处置。 应打印多少张卡片?

基本上, 我们会多次模拟每个可能的生产数量 (10000、20000、40000或 60000) (例如, 1000 迭代)。 然后, 我们确定哪种订单数量将在1000迭代上产生最大的平均利润。 你可以在文件情人 (如图60-4 所示) 中查找此部分的数据。 将单元格 B1: B11 中的区域名称分配给单元格 C1: C11。 单元格区域 G3: H6 被分配名称lookup。 我们的销售价和成本参数在单元格 C4: C6 中输入。

书籍图像

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

售出的单位数是生产数量和需求的较小者。 在单元格 C8 中, 你可以通过公式MIN (已生产, 需求) * unit_price计算收入。 在 C9 单元格中, 计算生产成本为* unit_prod_cost的公式的总生产成本。

如果我们生成的卡数量超过了需求, 则按等于生产减去需求的单位数量;否则, 不会留下任何单元。 我们在单元格 C10 中计算我们的处置成本, 公式unit_disp_cost * IF (produced>demand, 0)。 最后, 在单元格 C11 中, 我们计算收益-total_var_cost-total_disposing_cost

我们希望一种有效的方式为每个生产数量按 F9 (例如 1000), 并对每个数量的预期利润进行计数。 这种情况下, 一个双向数据表可用于我们的修复。 (有关数据表的详细信息, 请参阅第15章 "对数据表的敏感性分析"。) 此示例中使用的数据表如图60-5 所示。

书籍图像

在单元格区域 A16: A1015 中, 输入1到1000的数字 (对应于我们的1000试用版)。 创建这些值的一种简单方法是首先在单元格 A16 中输入1 。 选择单元格, 然后在 "开始" 选项卡上的 "编辑" 组中, 单击 "填充", 然后选择 "系列" 以显示 "系列" 对话框。 在 "系列" 对话框 (如图60-6 所示) 中, 输入1和2的 "Stop" 值。 在 "系列位于" 区域中, 选择 "" 选项, 然后单击"确定"。 将在列 A 中以单元格 A16 开始输入数字1–1000。

书籍图像

接下来, 我们在单元格 B15: E15 中输入可能的生产数量 (10000、20000、40000、60000)。 我们想要计算每个试用期 (从1到 1000) 和每个生产数量的利润。 通过输入= C11, 我们将在数据表的左上角单元格 (A15) 中引用用于利润的公式 (在单元格 C11 中计算)。

现在, 我们可以诱骗 Excel 模拟每个生产数量的1000迭代需求。 选择表区域 (A15: E1014), 然后在 "数据" 选项卡上的 "数据工具" 组中, 单击 "如果分析", 然后选择 "数据表"。 若要设置双向模拟运算表, 请选择生产数量 (单元格 C1) 作为行输入单元格, 然后选择任何空白单元格 (我们选择单元格 I14) 作为列输入单元格。 单击 "确定" 后, Excel 将为每个订单数量模拟1000需求值。

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

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

风险对决策的影响     如果我们制作20000而不是40000卡, 我们预计的利润大约为 22%, 但我们的风险 (由利润标准偏差衡量) 将降低约 73%。 因此, 如果我们非常 averse 风险, 则生产20000卡可能是正确的决策。 顺便说一下, 生产10000卡的标准偏差始终为0个卡, 因为如果我们制作10000卡, 我们将始终销售所有这些卡, 而不是任何 leftovers。

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

平均利润的置信区间     在这种情况下, 要问的一个自然问题是, 我们 95% 的时间间隔是多少? 此间隔称为平均利润的 95% 置信区间。 按以下公式计算任何模拟输出的平均值的 95% 的置信区间:

书籍图像

在单元格 J11 中, 当40000日历是用公式D13-1.96 * D14/SQRT (1000)生成时, 将计算平均利润的 95% 置信区间的下限。 在单元格 J12 中, 通过公式D13 + 1.96 * D14/SQRT (1000)计算 95% 置信区间的上限。 这些计算如图60-7 所示。

书籍图像

我们的 95%, 确保在订购40000日历时, 我们的平均利润介于 $56687 和 $62589 之间。

  1. GMC 庄家认为, 对 2005 Envoys 的要求通常以200和标准偏差30的平均值进行分布。 他收到 Envoy 的费用是 $25000, 他销售 $40000 的 Envoy。 对于 $30000, 您可以销售的所有不是以全价售出的 Envoys 的一半。 他正在考虑订购200、220、240、260、280或 300 Envoys。 他应订购多少?

  2. 一次小超市正在尝试确定每周应订购的用户杂志的份数。 他们认为他们对用户的需求由以下离散随机变量控制:

    需求

    Probability

    15

    0.10

    20

    0.20

    二十五

    0.30

    大约

    0.25

    35

    0.15

  3. 超市为每个人员的副本支付 $1.00, 并销售 $1.95。 可为 $0.50 返回每个 unsold 副本。 应用商店订单应具有多少个副本?

需要更多帮助吗?

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

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

此信息是否有帮助?

谢谢您的反馈!

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

×