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