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

经营安全性Excel仿真模型设计【阅读案例】某制鞋公司准备上一条登山鞋生产线。每年固定性折旧费、固定性管理和销售费用合计20万元，每双变动成本（包括直接材料和直接人工）100元，售价300元。根据市场调查每年大约需求1500双。问题：（1）该产品保本点生产量是多少？是否值得生产？（2）该产品安全边际量是多少？安全边际率是多少？（3）市场需求均可能变化。分析这两个参数变化对安全边际率的敏感性分析。（4）若以上三种分布分别为：市场需求Q~N(1500,2002)、变动成本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()) (Excel2003版本无此函数)逆变换法产生的指数分布随机数：=-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 敏感性分析图转载自：/s/blog_4d69c7430100w98d.html