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

经营安全性Excel仿真模型设计【阅读案例】某制鞋公司准备上一条登山鞋生产线每年固定性折旧费、固定性管理和销售费用合计20万元，每双变动成本（涉及直接材料和直接人工）100元，售价300元根据市场调查每年大概需求1500双问题：（1）该产品保本点生产量是多少？与否值得生产？（2）该产品安全边际量是多少？安全边际率是多少？（3）市场需求均也许变化分析这两个参数变化对安全边际率的敏感性分析4）若以上三种分布分别为：市场需求Q~N(1500,)、变动成本V~N(100,102)、销售价格P~N(300,302)建立安全边际率的Excel仿真模型模型设计基本思路：图1 模型设计流程1．建立数据输入区和生成区输入区与生成区所形成的成果，可用于模拟实验和敏感性分析（图2）图中安全边际率（B11单元格）是进行模拟和敏感性分析所要考察的指标；需求G16）、价格（G21）、变动成本（G26）随机数公式用于模拟实验其中ROUND是四舍五入函数NORMINV用于产生正态随机数若需求、价格和单位变动成本分布仍是正态分布，如果参数变化，只变化参数即可；若分布律发生变化，修改这三个单元格公式即可常用的随机数函数在如下：正态分布：=NORMINV(Rand(),均值，原则差)均匀分布：=a+(b-a)*RADN()二项分布：=BINOM.INV(实验次数,成功概率,RAND()) (Excel本无此函数)逆变换法产生的指数分布随机数：=-LN(1-rand())/lambda图2 EXCEL模型输入区和生成区2．仿真实验及其记录在D4、E4、F4分别引用生成区所给出的随机数公式所在的单元格；选择C4:D1003单元格区域/选择“数据”选择卡/模拟分析/模拟运算表。

弹出模拟运算对话框，在“输入列单元格引用”中选择任一空单元格，此处选择了C2，单元拟定，产生了1000个均值为1500，原则差为200的随机数；选择D4:E1003单元格区域，执行如上操作，产生1000个价格随机数；选择E4:F1003单元格区域，执行如上操作，产生1000个单位变动成本随机数；在G4、H4、I4单元格输入图3所示公式，并复制到G5:I1003得模拟成果 图3 EXCEL模型仿真过程对模拟成果，在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 敏感性分析图转载自：。