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 (生产>需求,已生成-要求,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" 1000 值。 在 "系列位于" 区域中,选择 "" 选项,然后单击"确定"。 将在列 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 支持专员。

×