EXCEL蒙特卡洛正态分布模拟实例.docxVIP

EXCEL蒙特卡洛正态分布模拟实例

图2EXCEL模型输入区和生成区

2．仿真试验及其统计

在D4、E4、F4分别引用生成区所给出的随机数公式所在的单元格；

选择C4:D1003单元格区域/选择“数据”选择卡/模拟分析/模拟运算表。弹出模拟运算对话框，在“输入列单元格引用”中选择任一空单元格，此处选择了C2，单元确定，产生了1000个均值为1500，标准差为200的随机数；

选择D4:E1003单元格区域，执行如上操作，产生1000个价格随机数；

选择E4:F1003单元格区域，执行如上操作，产生1000个单位变动成本随机数；

在G4、H4、I4单元格输入图3所示公式，并复制到G5:I1003得模拟结果。

?

图3EXCEL模型仿真过程

对模拟结果，在L3:L6单元格输入图4所示函数，得1000个随机数的最大值、最小值、平均数和标准差，并由最大值和最小值，按10组计算组距，以便进行频数统计。

按图4中K11:K20输入公式，计算各组上限；

选择L11:L20输入图中所示公式，按住Ctrl+Shift键不放，再按Enter键，得频数统计结果；

选择M11:M20输入图中所示公式，按住Ctrl+Shift键不放，再按Enter键，计算出频率；

在N11:N20输入图中所示公式，得累积频率分布。

图4仿真结果统计（公式显示模式）

根据上述操作，并统计结果（图5）并插入散点图。

图5仿真统计结果

3．敏感性分析

由安全边际率判断企业经营安全性一般标准如图6所示。

图6经营安全判断一般标准

当相关参数变化时，安全边际率也会发生变化。我们选择了市场需求和价格两因素变化对安全边际率的影响。

在K27单元格输入：=B11，即引用安全边际率的计算公式；

在L17:L27输入销售单价的变化范围；

在K28:L38输入市场需求量的变化范围；

在L26输入：=”P=”L27，并复制到M26:S26；

选择K27:L38，从“数据”选项卡选择“模拟运算表”，“输入引用行单元格”输入：=B5，在“引用列单元格”输入：=B6，单击“确定”，得模拟运算结果；

选择L28:S38单元格区域，“开始/条件格式/图标集”在“等级”选择如图所示图标。再选择“条件格式/管理规则/编辑规则”设置如下：

?

图7编辑格式规则设置对话框

单击“确定”，得图标显示结果。

图8敏感性分析表

选择K26:S26，按住Ctrl不放，再选择K28：L38，插入/图表/光滑型散点图，确定，得如下统计图。

图9敏感性分析图

转载自：/s/blog_4d69c7430100w98d.html