快速风险审计：在Excel中生成随机事件和影响的简介

2024年6月17日14:00:20评论2 views字数 2759阅读9分11秒阅读模式

在Excel中生成随机事件和影响的简介

本书网站上准备好的电子表格将包含以下所有设置，因此您不必了解如何运行风险评估模拟的详细信息。但如果您最终想自己制作更复杂的模型，这将是一本有用的入门书。

蒙特卡罗可以涉及数千个随机生成的场景。这可以用读者可能熟悉的任何编程语言来完成，甚至可以在Excel电子表格中完成。首先，让我们为单个威胁生成一个随机场景。让我们首先考虑该事件是否在单一场景中因单一风险而发生。为了模拟特定事件是否发生，我们可以随机生成“1”（如果发生）和“0”（如果不发生），其中“1”的概率等于事件发生的概率。在Excel中，我们可以将其写为

=if(rand( )<event_probability,1,0)

例如，如果每年的事件概率为0.15，则该方程将在15%的场景中产生“1”（表示事件发生）。在Excel中，每次重新计算（按F9）时，您都会看到不同的结果。如果您执行此操作一千次，您会看到该事件发生大约150次。请注意，这适用于您在模拟中列出的每个单独风险。因此，如果您有100个风险，每个风险都有不同的概率，并且运行1,000个场景，那么这个小公式将被执行100,000次。

我们之前提到的一个注意事项是，我们在这里使用Excel“rand()”函数作为任何随机数生成器的简写。Excel中实际上有一个rand()函数，上面的方程将完全按照描述的方式工作。但无论何时我们提到rand()，我们的电子表格实际上都会使用不同的方法来生成随机数。有关更多详细信息，请参阅插图“HDRPRNG”。电子表格中对此进行了进一步解释。

现在，为了产生影响，我们需要生成的不仅仅是“0”或“1”，而是一个连续的值。我们可以使用Excel的“逆概率函数”之一来完成此操作。Excel中的一些概率函数会告诉您特定概率分布中给定结果的概率。例如，normdist(x,均值,标准差,1)将告诉您具有给定均值和标准差的正态分布产生x或更小的值的概率。另一方面，逆概率函数告诉您给定概率的x值。在Excel中，正态分布的逆概率函数为

=norminv(probability,mean,standard deviation)

（注意：最新版本的Excel也使用“norm.inv()”，但“norminv()”仍然有效。）如果您使用Excel rand()函数（或HDR PRNG）代替概率项，这将产生具有指定平均值和标准差的正态分布随机值。标准差是概率分布宽度的一种度量，但对于专家来说，它并不是一个非常直观的量来估计。如果我们像前面描述的那样直接向专家询问90% CI会更好。这可用于根据专家提供的描述一系列潜在损失的上限(UB)和下限(LB)来计算所需的参数，例如平均值和标准差。

我们将把这个范围转换成我们经常使用的特定类型的概率分布：“对数正态”分布。当我们在本章开头确定如何加权边界以计算简单快速审计中的期望值时，这就是我们假设的分布类型。

对数正态分布是更熟悉的钟形正态分布的变体。它只是我们想要模拟的值的对数的正态分布，并且通常是更好地代表现实的分布。图3.1举例说明了该分布与正态分布的比较。请注意，与正态分布不同，对数正态分布是如何不平衡或“倾斜”的。对数正态分布不能产生零或负数，但它有一个向右的尾部，允许产生极大结果的可能性。这就是为什么它通常是高得惊人的损失的现实表现。足以捕获某些极端事件的正态分布也可能在天平的另一端产生不合逻辑的负面结果（您不能有负数的记录被破坏或系统的负停机时间）。这就是为什么也使用对数正态对不能为负但可能（但很少）非常大的各种量进行建模。

快速风险审计：在Excel中生成随机事件和影响的简介

图对数正态分布与正态分布

为了生成对数正态分布，本书网站上提供的工具在Excel中使用以下公式：

=lognorm.inv(rand(),mean of ln(X),standard deviation of ln(X))

在哪里：

ln(X)的标准差	=	(ln(UB)–ln(LB))/3.29
ln(X)的平均值	=	(ln(UB)+ln(LB))/2

因此，如果我们对100,000美元到800万美元的影响有90% CI，那么我们需要用于lognorm.inv（原始分布对数的平均值和标准差）的平均值和标准差将为：

mean of ln(x)=(ln(8000000)+ln(100000))/2=13.7

Standard deviation of ln(x)=(ln(8000000)-ln(100000))/3.29=1.33

为了产生发生概率为5%、影响为100万到900万美元的事件的损失，我们可以这样写

快速风险审计：在Excel中生成随机事件和影响的简介

大多数时候（95%），这个函数会产生一个零。只有5%的情况下，它产生的价值有90%的可能性落在100万美元到900万美元之间。请注意，由于这是90% CI，因此有5%的机会低于下限（但高于零，因为对数正态分布只能产生正值），有5%的机会高于上限，有时远高于。如果发生上述示例中的事件，则损失有1%的可能超过1,420万美元。

这是使用对数正态分布时需要注意的一点。当上限是下限的许多倍时，给定90% CI的极端损失可能是不现实的。当估计该值的专家错误地认为上限代表最坏情况的极端情况（但事实并非如此）时，就会发生这种情况。90% CI的上限允许该值有5%的机会更高。极端结果也对下限敏感。如果90% CI为10,000美元到100万美元，则上限是下限的100倍。在这种情况下，损失有1%的可能性会超过规定上限（260万美元）的2.6倍。如果90% CI为1,000至1,000万美元，则损失有1%的可能性超过上限（6,700万美元）的6.7倍。如果这看起来太多了，然后重新考虑范围的宽度或简单地将生成的值截断为某个最大值。如果我们想说1000万美元是最大损失，那么我们可以使用Excel函数=min(loss,$10000000)取损失或1000万美元中的较小者。