经营(jīngyíng)安全性Excel仿真模型(móxíng)设计
【阅读案例】某制鞋公司准备(zhǔnbèi)上一条登山鞋生产线。每年固定性折旧费、固定性管理和销售费用合计20万元，每双变动(biàndòng)成本（包括直接材料和直接人工）100元，售价300元。根据市场调查每年(měinián)大约需求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模型(móxíng)输入区和生成区
2．仿真试验(shìyàn)及其统计
在D4、E4、F4分别引用(yǐnyòng)生成区所给出的随机数公式所在的单元格；
选择(xuǎnzé)excel的随机数函数C4:D1003单元格区域(qūyù)/选择“数据”选择卡/模拟分析/模拟运算表。弹出模拟运算对话框，在“输入列单元格引用”中选择任一空单元格，此处选择了C2，单元确定，产生了1000个均值为1500，标准差为200的随机数；
选择D4:E1003单元格区域，执行如上操作，产生1000个价格随机数；
选择E4:F1003单元格区域，执行如上操作，产生1000个单位变动成本随机数；
在G4、H4、I4单元格输入图3所示公式，并复制到G5:I1003得模拟结果。
 

图3 EXCEL模型仿真(fǎnɡ zhēn)过程
对模拟(mónǐ)结果，在L3:L6单元格输入(shūrù)图4所示函数(hánshù)，得1000个随机数的最大值、最小值、平均数和标准差，并由最大值和最小值，按10组计算(jì suàn)组距，以便进行频数统计。
按图4中K11:K20输入公式，计算各组上限；
选择L11:L20输入图中所示公式，按住Ctrl+Shift键不放，再按Enter键，得频数统计结果；
选择M11:M20输入图中所示公式，按住Ctrl+Shift键不放，再按Enter键，计算出频率；
在N11:N20输入图中所示公式，得累积频率分布。

图4 仿真结果统计（公式显示模式）
根据上述操作，并统计结果（图5）并插入散点图。
图5 仿真统计(tǒngjì)结果
3．敏感性分析(fēnxī)
由安全边际率判断企业(qǐyè)经营安全性一般标准如图6所示。

图6 经营(jīngyíng)安全判断一般标准
当相关参数变化(biànhuà)时，安全边际率也会发生变化。我们选择了市场需求和价格两因素变化对安全边际率的影响。
在K27单元格输入：=B11，即引用安全边际率的计算公式；
在L17:L27输入销售单价的变化范围；
在K28:L38输入市场需求量的变化范围；
在L26输入：=”P=”&L27，并复制到M26:S26；
选择K27:L38，从“数据”选项卡选择“模拟运算表”，“输入引用行单元格”输入：=B5，在“引用列单元格”输入：=B6，单击“确定”，得模拟运算结果；
选择L28:S38单元格区域，“开始/条件格式/图标集”在“等级”选择如图所示图标。再选择“条
件格式/管理规则/编辑规则”设置如下：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。