在EXCEL中运用蒙特卡罗模拟方法例子 (Wilmott Varie)

水平上下波动。

本文简单介绍在折现现金流量模莲！！中采用蒙特卡洛模拟方法对那些高风险或发展前景存在不确定性因素的公司进行决策。

蒙特卡洛模拟的方法，对于财务数据都是通过随机取样来确定的，客观地反映了市场的变化。

而大量的数据模拟解释了一种概率的结果．这是人们对收益法中采用财务预测产生怀疑的一个很好解释。

二、蒙特卡洛方法简介蒙特卡洛模拟法Ｏ＇ｌｏｎｔｅＣａｒｌｏＳｉｍｕｌａｔｉｏｎ）３Ｌ称随机模拟法，其名７来源于摩纳哥著名赌城蒙特卡洛，它是计算机模拟的基础。

该理论最早起源于法国科学家普丰在１７７７年提出的一种计算圆周率的方法——随机投针法．即著名的普丰针实验。

蒙特卡洛模拟建立在中心极限定理的基础上，假设某个随机变量＇，的期望值Ｏ＝Ｅ『Ｙ１，那么我们假设可以产生与ｙ独立同分布的随机变量的值，每产生一次完成一次模拟。

假设进行了＆次模拟，产生了ｋ个值ｙ，，ｙ：，ｂ，…，ｙ＊，如果令ｙ＝乞Ｙ。

／ｋ是它们的代数平均值，那么Ｙ就可以看作０的一个估计值，并且我们可以证明在中心极限定理的假设下，＾越大，越接近正态分布，那么ｙ也就是０的一个较好的估计量。

这种估计期望值的方法就称为蒙特卡洛模拟。

三、风险投资决策的蒙特卡洛模拟法在企业价值评估中，常常采用收益法，把预测的盈利流折现得出企业现在的价值。

这种方法所用的增长率是平均值，但是这个增长率忽视了预计因素变动的不确定性。

还有一种模型是Ｄｅｌｐｈｉ法，通过反复的大量调查取值来计算一个参数的平均值，该方法在实际运用中将会耗用大量成本。

现实【吐界的情况是不确定因素很多并且服从不同的概率分布，所以。

把这种变化明确地表现到预测和模拟当中是一种可以考虑的选择。

本文介绍一种基于Ｅｘｃｅｌ加载宏的ＣｒｙｓｔａｌＢａｌｌ软件，来实现风险投资决策的仿真运算模型。

１．ＣｒｙｓｔａｌＢａｌｌ软件简介ＣｒｙｓｔａｌＢａｌｌ软件是由美国Ｄｅｃｉｓｉｏｎｅｅｒｉｎｇ公司开发的，为Ｅｘｃｅｌ电子表格提供的功能强大的加载宏。

当科学家们使用计算机来试图预测复杂的趋势和事件时, 他们通常应用一类需要长串的随机数的复杂计算。

设计这种用来预测复杂趋势和事件的数字模型越来越依赖于一种称为蒙特卡罗模似的统计手段, 而这种模拟进一步又要取决于可靠的无穷尽的随机数目来源。

蒙特卡罗模拟因摩纳哥著名的赌场而得名。

它能够帮助人们从数学上表述物理、化学、工程、经济学以及环境动力学中一些非常复杂的相互作用。

数学家们称这种表述为“模式”, 而当一种模式足够精确时, 他能产生与实际操作中对同一条件相同的反应。

但蒙特卡罗模拟有一个危险的缺陷: 如果必须输入一个模式中的随机数并不像设想的那样是随机数, 而却构成一些微妙的非随机模式, 那么整个的模拟(及其预测结果)都可能是错的。

最近, 由美国佐治亚大学的费伦博格博士作出的一分报告证明了最普遍用以产生随机数串的计算机程序中有5个在用于一个简单的模拟磁性晶体中原子行为的数学模型时出现错误。

科学家们发现, 出现这些错误的根源在于这5个程序产生的数串其实并不随机, 它们实际上隐藏了一些相互关系和样式, 这一点只是在这种微小的非随机性歪曲了晶体模型的已知特性时才表露出来。

贝尔实验室的里德博士告诫人们记住伟大的诺伊曼的忠告:“任何人如果相信计算机能够产生出真正的随机的数序组都是疯子。

”蒙特卡罗方法（MC）蒙特卡罗（Monte Carlo）方法：蒙特卡罗（Monte Carlo）方法，又称随机抽样或统计试验方法，属于计算数学的一个分支，它是在本世纪四十年代中期为了适应当时原子能事业的发展而发展起来的。

传统的经验方法由于不能逼近真实的物理过程，很难得到满意的结果，而蒙特卡罗方法由于能够真实地模拟实际物理过程，故解决问题与实际非常符合，可以得到很圆满的结果。

这也是我们采用该方法的原因。

蒙特卡罗方法的基本原理及思想如下：当所要求解的问题是某种事件出现的概率，或者是某个随机变量的期望值时，它们可以通过某种“试验”的方法，得到这种事件出现的频率，或者这个随机变数的平均值，并用它们作为问题的解。

基于Ｅｘｃｅｌ的蒙特卡罗模拟在银行排队业务中的应用［摘要］针对目前银行排队难的问题，本文将蒙特卡罗模拟的方法运用到银行排队业务中。

在仿真模型的建立过程中大量使用了Excel的函数、公式编辑、加载宏Crystal Ball等工具；运用蒙特卡罗法处理系统运行指标随输入过程参数变化的不确定性；最后，得到模型的运行结果——顾客等待时间的频数图，并通过灵敏性分析确定到达率和服务率对顾客等待时间的影响程度。

［关键词］Excel；银行排队；蒙特卡罗模拟；Crystal Ball1 引言随着国内银行业全面市场化转型，不断增长的中间业务，如水费、电费、煤气费、工资、社保资金通过银行代收代发，加大了银行柜面的压力；而近期由于加息导致转存和提前还贷骤增；同时，随着股市和基金的升温，银行推出的基金和理财产品，也派生出了大量的柜面业务，而且这些业务相对复杂，耗时很长；多种因素促使银行排队矛盾集中爆发。

银行网点排队问题成为社会舆论和新闻媒体关注的焦点。

本文结合运筹学排队论问题的基本特点，利用蒙特卡罗模拟在刻画排队系统动态性方面的优势，重点介绍了利用Excel强大的函数、公式编辑功能以及加载宏Crystal Ball软件建立模型，实现蒙特卡罗模拟的方法在处理实际排队问题中的应用。

本文第3部分重点介绍应用Excel实现对某银行排队系统各运行指标的动态模拟，并对结果进行了敏感性分析，取得了良好的效果。

本文最后介绍了该方法在实际应用中的意义，并给出全文的总结。

2 排队问题的分析方法2. 1传统的分析方法解决排队问题的目的，是研究排队系统的运行效率，估计服务质量，确定系统参数的最优值，以确定系统结构是否合理、研究设计改进措施等。

所以必须确定用以判断系统运行优劣的数量指标，求出这些数量指标的概率分布。

这些数量指标通常是，队长L：系统中顾客数的期望值；排队长Lq：等待排队的顾客数期望值；逗留时间W：顾客在系统中的停留时间期望值；等待时间Wq：排队等待的期望值；空闲率I：服务台空闲的概率。

基于Excel的蒙特卡洛法及其在工程设计上的应用庞晓红【摘要】本文讨论蒙特卡洛(Monte Carlo)法的基本概念和应用.从正态概率分布等函数产生的随机数可以简易地在Excel工作表上产生.基于Excel的蒙特卡洛模拟可以避免编制复杂的Visual BASIC程序.工程设计方面蒙特卡洛法的实例应用表明,在Excel上通过蒙特卡洛模拟对设计参数作出概率分析,并计算PNC(Probability of Non-Compliance,"不符合"概率)简单易行.而愈简易的技术,其技术内涵不一定低,但生命力却较强.【期刊名称】《漳州职业技术学院学报》【年(卷),期】2006(008)003【总页数】5页(P20-23,58)【关键词】蒙特卡洛法;Excel;常用分布随机数;PNC计算;蒙特卡洛模拟实例应用【作者】庞晓红【作者单位】漳州职业技术学院,现代教育技术中心,福建,漳州,363000【正文语种】中文【中图分类】TP301.6用于运行概率分析的一种简单而强有力的技术称为蒙特卡洛（Monte Carlo）法。

早在二次世界大战期间，该法便用于解决在原子弹研制中出现的困难问题。

蒙特卡洛模拟的根源来自轮盘赌博，其中大部分是概率和随机现象。

二次世界大战以后蒙特卡洛法已经用来预测一切事物，包括简单纸牌游戏的结局到原子裂变。

蒙特卡洛法的目标是从输入参数分布中随机取样来预见（测量）输出响应，从而模拟已有的模型（可以是方程式，模拟，或实际设备）。

蒙特卡洛法的流程图表示于图1。

目前蒙特卡洛法已广泛应用于许多领域，诸如数学领域（不易求解的多变量数学积分等……），统计领域（工程和投资项目的风险评价等……），自然科学领域（化学反应动力学问题，量子力学能量和波函数计算，原子裂变等……）和工程设计领域等。

本文将探讨蒙特卡洛法在工程设计方面的实例应用。

多年来已开发出许多技术来产生随机数。

在计算机盛行前, Rand公司曾出版一本列有利用电子轮盘机产生的100万随机数的书。

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!［收稿日期］２００７－１１－２８中国管理信息化ＣｈｉｎａＭａｎａｇｅｍｅｎｔＩｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ２００８年４月第１１卷第７期Ａｐｒ．，２００８Ｖｏｌ．１１，Ｎｏ．７Ｅｘｃｅｌ在蒙特卡罗模拟分析中的应用刘清志，许学娜（中国石油大学（华东）经济管理学院，山东东营２５７０６１）［摘要］蒙特卡罗模拟法是进行项目不确定性分析的一种常用方法，Ｅｘｃｅｌ是具有强大数据分析功能的软件，可以很好地解决手工计算烦琐的问题。

基于此，本文结合案例研究了如何运用Ｅｘｃｅｌ进行蒙特卡罗模拟分析。

［关键词］Ｅｘｃｅｌ；蒙特卡罗模拟；数据分析［中图分类号］Ｆ２３２；Ｆ２７５［文献标识码］Ａ［文章编号］１６７３－０１９４（２００８）０７－００４４－０４表４相关最小总成本模拟运算表一、蒙特卡罗模拟分析概述在工程项目的经济评价中，通常假定有关数据都是不变的。

但项目分析中采用的数据大部分来自预测或估计，它们在很大程度上受到未来可变因素的影响；如果把它们作为固定值看待进行分析，计算结果将与实际情况不符，从而带来某种程度的风险。

近年来的经济评价中越来越注意分析研究这种风险的程度和可能性，蒙特卡罗模拟（９）通过规划求解功能，求得最佳现金持有量为３２４０３．７０，相应的最小总成本为３８８８．４４，该规划求解结果与利用控件优化结果一致。

四、利用模拟运算表进行动态模型设计与优化除了上述两种动态优化方法外，Ｅｘｃｅｌ软件还提供了另一种形式的动态分析表———模拟运算表，模拟运算表提供单因素与双因素运算表，该模拟运算表提供一种显示与比较多组不同数值的操作结果，笔者试图分析现金总量与每次交易成本同时变化对最佳现金持有量与相关总成本的影响，模拟运算表计算与分析步骤如下：（１）在模拟运算表行序列中录入现金总额，在模拟运算表列序列中录入每次交易成本；（２）在行与列序列交叉处输入目标函数，如在表３中的Ａ１６单元格中输入ＳＱＲＴ（２＊Ｂ４＊Ｂ５／Ｂ６），在表４中的Ａ２６单元格中输入ＳＱＲＴ（２＊Ｂ４＊Ｂ５＊Ｂ６）；（３）在表３中选择Ａ１６～Ｇ２２区域，并选择模拟运算表，在行单元格中输入Ｂ４，在列单元格中输入Ｂ５，Ｅｘｃｅｌ中立即出现如表３的计算结果，该计算结果反映在不同现金总额与每次交易成本变化时的最佳现金持有量；（４）在表４中选择Ａ２６～Ｇ３２区域，并选择模拟运算表，在行单元格中输入Ｂ４，在列单元格中输入Ｂ５，Ｅｘｃｅｌ中立即出现如表４的计算结果，该计算结果反映在不同现金总额与每次交易成本变化时的最小相关总成本。

投资考研成本预测中的应用院系：管理与经济学院专业：工业工程学号：200911002姓名：方俊强投资考研成本预测中的应用方俊强（华北水利水电学院，郑州，2012）【摘要】本文运用蒙特卡罗模拟和程序，结合自身实际上考研目的，以货币为主要评价标准，分心评估考研的主要成本，并借助Microsoft Excel软件对项目进行模拟和测试，给出了成本模拟的结果。

从结果可以看出，蒙特卡罗模拟方法应用于项目成本评估时占用的资源少、科学准确、操作性强，对于考研成本评估是行之有效的。

【关键词】蒙特卡罗模拟 Microsoft Excel 考研成本分析0前言随着时光的推移，我们三年级学生将不如大学最后的阶段——大四，在这里我们已经面临岔路口，有的选择考研，有的选择工作，也有选择考公务员。

每一位同学都是根据自己的兴趣爱好、特长、家庭经济能力等多个因素，经过综合判断选择一条属于自己的道路。

当然也有部分同学现在还有一点迷茫。

现在，我以自己的方向——考研，从我自身角度出发，主要以货币为主要评价标准，自己在2012年1-6月份的消费情况，运用蒙特卡罗模拟预测在2012年6月到2013年1这8个月中的投资考研的成本。

1蒙特卡罗模拟原理（1）模拟模拟是对真实事物或者过程的虚拟。

模拟要表现出选定的物理系统或抽象系统的关键特性。

模拟的关键问题包括有效信息的获取、关键特性和表现的选定、近似简化和假设的应用，以及模拟的重现度和有效性。

可以认为仿真是一种重现系统外在表现的特殊的模拟。

模拟的作用表现在：①能对高度复杂的内部交互作用的系统进行研究和实验；②能设想各种不同方案，观察这些方案对系统的结构和行为的影响；③能反映变量间的相互关系，说明哪些变量更重要，如何影响其他变量和整个系统；④能研究不同时期相互间的动态联系，反映系统行为随时间变化而变化的情况；⑤能检验模型的假设，改进模型的结构。

模拟已经和电子表格（Excel）联系起来，使管理者能够在多个领域进行分析，模拟主要分为蒙特卡罗模拟和系统模拟模型两大类。

摘要随着经济全球化的不断发展，我国的投资行业也进入蓬勃发展的时代，在投资决策分析过程中，风险分析必不可少。

传统的投资决策分析方法在现实中存在很多的局限，手工计算耗时耗力，且大型分析软件需要大量费用。

本文利用大众软件excel在折现现金流量模型中采用蒙特卡罗模拟方法对那些高风险或发展前景存在不确定性因素的公司进行决策，充分考虑各变量取值的随机性, 通过随机模拟技术,结合实际项目进行模拟分析，在考察投资决策变量概率分布规律的基础上, 给出了投资项目净现值可能取值的范围和不小于某一特定值的概率,对目标变量投资项目净现值的取值情况进行大量随机试验, 获取相关风险分析的统计信息, 为投资决策提供更为科学的决策依据。

运用Excel 所提供的数学、财务及其他函数, 以及分析工具和图表功能, 更好地解决投资决策过程中风险分析问题。

采用蒙特卡罗模拟法结合计算机编程语言,可以快速得到结果,只要适当把握好试验次数,就可以提高其精确度,是项目风险分析的一种有效工具。

该技术依靠随机抽样来预测项目未来盈利情况,主要考察项目的净现值,内部收益率等指标,让投资者根据结果进行权衡,能有效地预测风险的存在及风险发生的概率。

AbstractWith the continuous development of economic globalization, China's investment industry into the era of booming, also in the process of investment decision analysis, risk analysis is necessary. Traditional investment decision analysis method in reality there are a lot of limitations,time-consuming manual calculation, and the large analysis software requires a lot of cost. In this paper, using mass software excel in discounted cash flow model using montecarlo simulation method for those high risk or future uncertainty factors of company to make decisions, fully considering the randomness of each state variable through the stochastic simulation technology, simulated analysis, combined with actual project in examining investment decision variable, on the basis of probability distribution, gives the range of possible value of the net present value of investment projects and not less than one.Provided by use of Excel of mathematics, finance and other functions, as well as the analysistool and function of the chart, to better solve the problem of risk analysis of investment decision-making process. Using montecarlo simulation method in combination with computer programming language, can quickly get the results, as long as appropriate grasp good experiments, can improve the accuracy, is an effective tool of project risk analysis. The technology by random sampling predict project future earnings, the main project of the net present value, internal rate of return and other indicators, let investors to weigh according to the results, can effectively predict the existence of the risk and the probability of risk occurrence.关键词：蒙特卡罗模拟风险分析；excel ；投资决策Key words：Risk analysis of Monte Carlo simulation；Excel；Investment decisions目录一．引论 (4)（一）选题背景与意义 (4)（二）研究思路 (5)二．理论分析 (5)（一）投资决策分析方法类型 (5)（二）蒙特卡罗模拟 (7)（三）项目投资决策理论 (8)三．实证(仿真)分析 (10)（一）建立输入区 (10)（二）建立主体区 (11)（三）建立输出区 (11)（四）建立模拟实验区 (12)（五）建立统计区 (12)（六）建立图形区 (13)四．结语 (15)参考文献 (16)一．引论（一）选题背景与意义随着社会主义市场经济体制的逐步完善、经济水平的逐步提高,我国社会经济活动日趋复杂,越来越多变,其影响越来越广泛,越来越深远,不确定性逐渐成为企业决策时所面临的主要难题。

、.~①我们‖打〈败〉了敌人。

②我们‖〔把敌人〕打〈败〉了。

也许你已经在Excel中完成过上百张财务报表，也许你已利用Excel 函数实现过上千次的复杂运算，也许你认为Excel也不过如此，甚至了无新意。

但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一。

本专题从Excel中的一些鲜为人知的技巧入手，领略一下关于Excel的别样风情。

一、让不同类型数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。

1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式→条件格式”命令，打开“条件格式”对话框。

单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。

单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。

2.按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。

3.设置完成后，按下“确定”按钮。

看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。

二、建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。

1.在Sheet2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。

2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。

仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”……3.切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据→有效性”命令，打开“数据有效性”对话框。

海峡财经导报/2006年/11月/9日/第021版财税・财务管理Excel在蒙特卡罗模拟法中的应用钟爱军蒙特卡罗模拟法(MonteCarlo simulation)是一种以数理统计理论为指导的模拟技术，其实质是按概率分布产生随机数的方法来模拟可能出现的随机现象。

在投资项目不确定性风险分析中，我们可以将敏感性分析与各个不确定因素的概率分布进行结合来衡量投资项目风险，即根据随机数对影响因素的概率分布进行随机抽样，使模拟值涵盖各种可能出现的情况，再根据每次随机抽样值来计算投资项目的净现值期望值和标准差，以此衡量投资项目风险。

蒙特卡罗模拟方法的优点在于它是一种全值估计方法，它所产生的大量情景可以解决非线性、大幅波动问题；其缺点在于因依赖所确定的随机过程而有可能导致模型风险，当然如果模型做得正确，则此方法可能是衡量风险最全面的方法。

在Excel中，利用蒙特卡罗模拟法分析投资项目风险的基本思路和方法是：估计影响投资项目净现值的各关键因素各种可能结果的概率，列每个因素根据其各种可能出现结果的概率分配相应的随机数。

形成每个因素所服从的概率分布。

利用Excel随机函数RANDBETWEEN(如果该函数不可用，并返回错误值#NAME?，请在Excel工作表中执行“工具”—“加载宏”—“分析工具库”)产生随机数。

然后利用VLOOKUP函数来查找对应随机数的变量数值，根据每个因素选定的数值。

应用评价模型计算投资项目的净现值指标，完成一次模拟过程，将结果储存起来，重复地进行模拟计算，进行足够的模拟计算次数后，得到投资项目净现值的期望值、标准差及变差系数，从而判断投资项目的风险。

案例：某公司计划投资一个项目用来生产A产品，A产品在未来3年的销售价不会发生变动，但销售量和单位变动成本有着很大的不确定性，其可能达到的水平值及发生的概率。

试用蒙特卡罗模拟法对该投资项目进行风险分析。

已经按计算要求对概率和变量值进行分区，如以第1年销售量为例，随机数为0、30、60、90时对应销售量分别为50000件、60000件、70000件、80000件，也就是说当产生的随机数分别为0－29、30－59、60－89、90－99时，对应的销售量分别为50000件、60000件、70000件、80000件，对应的累计概率分别为0.20、0.55、085和1.00。

上海会计!""#$"%!"5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555556666我们在进行投资项目决策时，一般假定现金流量是确定的。

实际上，个人或企业所从事的投资活动往往充满了不确定性。

由于项目投资中问题太复杂，采用数学分析来求解已变得很困难。

解决这类问题时，有关文献中的风险调整贴现率法和肯定当量这两种方法计算起来比较麻烦，也存在自身的缺陷。

在计算机技术日益普及的今天，笔者拟推荐一种新的风险分析方法———蒙特卡罗模拟法，借助于&’(&)中强大的数据分析、决策建模功能，帮助财务管理人员做出最佳决策。

一、原理蒙特卡罗模拟法的理论基础是概率论与数理统计。

由于包含着随机因素，因而所建立的数学模型中的目标变量也成了随机变量，而通过其他手段难以确定该随机变量的概率分布与统计特征。

应用蒙特卡罗模拟技术可以获得较多、较全面的统计信息，从而可以使决策者对于投资项目获得更加全面的认识，以便有利于作出最佳决策。

我们用&’(&)提供的*+,-./函数所生成的，在."、#/区间中均匀分布的随机数进行变换的方法，来生成所需要的随机变量的观测值。

在需要生成其他形式概率分布随机变量的观测值时，可以采用反函数变换技术.具体的数学定理推导过程略/，我们只需了解使用&’(&)中的内建查表参数0)11234./及*+,-（）函数，联合使用生成具有给定概率分布的随机变量的观测值即可。

第12卷　第2期1999年6月　聊城师院学报(自然科学版)Journal of L iaocheng T eachers U niversity(N at.Sci)V o l.12N o.2Jun.1999基于Excel的蒙特卡罗模拟方法的实现α姜庆华　李国锋(山东经济学院基础部,济南250014)摘　要　以M icro soft Excel电子表格软件为基础,探讨了Excel在开发决策支持系统方面的功能,在研究给出了M on te Carlo模拟算法之后,通过一案例实现了基于Excel的M on te Carlo摸拟系统1工作表明,Excel软件在管理决策中具有较好的应用前景1关键词　决策支持,Excel电子表格软件,蒙特卡罗模拟分类号　T P3蒙特卡罗模拟是利用计算机进行数值计算的一类特殊风格的方法,它是把某一现实或抽象系统的某种特征或部分状态,用模拟模型的系统来代替或模仿,使所求问题的解正好是模拟模型的参数或特征量,再通过统计实验,求出模型参数或特征量的估计值,得出所求问题的近似解1该方法应用面广,适应性强.惠斯通(W eston)对美国1000家大公司所作的统计表明:在公司管理决策中,采用随机模拟方法的频率占29%以上,远大于其他数学方法的使用频率1特别,该方法算法简单,但计算量大,在模拟实际问题时,要求所建模型必须反复验证1这就离不开计算机技术的帮助,自然可利用任何一门高级语言来实现这种方法1本文首先探讨Excel电子表格软件在开发决策支持系统方面所具有的功能特点,给出蒙特卡罗模拟算法之后,通过一案例具体实现了基于Excel的M on te Carlo模拟系统,由于M icro soft Excel电子表格软件强大的数据分析功能和友好的界面设计能力,使系统实现起来颇感轻松自如1本文的努力或许对管理决策自动化有所启示11　Excel开发决策系统的支持能力通常一个决策支持系统一般应包含便于用户与系统进行对话从而使他们在运用自己的经验、直觉和判断来解决所面临的半结构化问题时可以得到充分支持的友好界面,又应包含适合解决用户所面临的决策问题中的结构化部分的决策模型,同时还应包含便于保存作为分析对象和分析结果的那些数据的数据库及其管理系统1Excel之所以被认为是一种建立决策支持系统的方便工具,就是因为它在上述三个方面所具有的强大支持能力. 111　Excel具有灵活的建模能力和对所建模型进行W hat2if分析的功能αExcel 可以方便地利用公式、大量内建的财务统计函数、以及VBA 语言建立案例数值模型,并利用模拟运算表、方案管理器等功能模型进行“如果决策变量如此变化的话,目标变量将如何变化”的分析1利用单变量求解功能求解“为了使目标变量达到某个特定的目标值,决策变量应该如何变化”的问题1Excel 通过其规划求解功能将最优化算法的能力加入到对工作表数据进行操纵的能力之中,这使得Excel 特别适合建立线性规划、非线性规划等各种优化模型1112　Excel 可以在其工作表中存贮所分析的数据并具有对数据进行分析管理的能力Excel 数据文件以工作簿作为基本形式,一个工作簿可包含多达255张工作表,一张工作表是一具有65536行×256列的二维表格,允许用户将数据保存在其工作表中1因此,用户在Excel 数据文件中具有保存数据的更大的灵活性1借助于ODBC (开放数据库联接)工具,用户可以直接访问各种由dB ase 、FoxP ro 、A ccess 或者SQL Server 等生成的外部数据库1利用Excel 的数据透视表功能,可以十分方便地从大量数据中提取有用的综合统计信息1113　Excel 敞开的工作表屏幕具有直接的输入、输出功能,它的图形处理能力提供了友好的用户界面Excel 充分利用了W indow s 图形用户界面的优点,可以让用户利用W indow s 与Ex 2cel 本身的菜单、工具条、对话框等手段在建立与维护决策支持系统时方便地进行操作1还允许用户定义菜单、工具条和对话框来建立自己的系统界面1Excel 的图形不但质量好而且效率高1用漂亮的图形来向用户表现决策分析的结果显然会明显提高所建立的决策支持系统的功能1总之,Excel 在建立模型与模型分析、数据访问与管理以及用户界面三方面具有强大功能1在这些功能基础上,借助于VBA 宏,我们就能够开发出界面友好美观、数据存取容易、决策分析方便易行的各种决策支持系统来12　蒙特卡罗案例、算法研究及系统实现案例取材于一个库存控制决策支持系统的示范性试验1泰丰加油站对过往船只销售汽油1汽油需求依赖于天气条件而按下列分布波动1由于加油站位置偏僻,必须有一固定库存5500gallon ,且每周定货一次1经理面临的决策问题是:若定货太小,缺货损失12Y gallon ;若定货太多,他不得不支付5Y gallon 的退货费1每加仑销售利润为10Y ,现存货300gallon ,经理的最优定货量是多少? 问题可以通过一段时间的实际实验求解,而模拟则可以在几秒钟后给出答案,且成本很小1为此可以指定方案:定货3400、3500、3600、3750、4000、4500gallon 等模拟计算,比较几个方案的利润,求得一个满意解1周需求量(gallon )概率分布20000.1230000.2340000.4850000.17模拟的一般过程是:调查、收集数据并设定问题,构造模拟模型,测试模型,构造方案,模拟实验,结果评价,结果执行1蒙特卡罗模拟特点是依赖于一个特定的概率分布,模拟结果服从概率规律性1针对案例,构造模拟模型的算法步骤如下(1)　描述问题并确定一个概率分布1案例中是一个给定的分布;()72第2期姜庆华等:基于Excel 的蒙特卡罗模拟方法的实现　82聊城师院学报(自然科学版)第12卷(3)　计算累积概率并确定目标代表数区间1案例中利用Excel的直接输入功能,输入步骤1的概率分布,系统利用VBA宏自动计算累积概率和目标代表数区间;(4)　产生随机数,计算系统目标1这里的重点在于要求系统产生两位数的随机数,以便根据步骤3给出的目标代表数区间确定随机需求1系统目标不仅给出了周平均利润,还给出了周平均销售量、周平均缺货量和退货量等;(5)　模拟计算过程,统计评价并寻求满意解1案例模拟计算过程算法如图1.设I b为期初库存,I e为期末库存,I m为库存容量,D为随机需求,S为销售量,B为退货量,U为缺货量,O rs为定货量1则周期望利润=10S-12U-5B1系统实现有以下几个问题需要考虑1211　界面的设计充分利用了Excel在开发决策支持系统方面所具有的功能,利用Excel工作薄文件中多工作表现功能,将系统界面、模拟运算表、灵敏度分析、模拟方案结果表等各部分各自放在不同的工作表中;利用了Excel的建模能力、W hat2if分析能力、控件建立能力和VBA宏开发能力,在一个宏表中写了一些必要的宏以便对系统进行控制1给出了如下操作界面:启动系统后,首先进入系统初始化,要求给出最大库存容量、初始库存量、系统状态数,之后要求给出方案设计、模拟周期1系统初始化后会生成一个累积概率计算状态表以及相应的代表随机需求的随机数代表区间1在此基础上,点按一下“模拟计算”按钮,系统自动生成表格并进行模拟计算1值得说明的是,状态表和模拟计算表均是动态,他们随系统状态个数与模拟周期数而定1212　动态区域的实现在Excel中实现动态区域是方便的,利用了Excel的R ange对象、Selecti on对象的O ffset属性、R esize属性和N am e属性及Select方法1这比利用其他程序设计语言实现作者认为要容易得多1下面是状态表动态区域实现的VBA程序R ange(″B12″).SelectSelecti on.O ffset(0,0).R esize(Row Size:=S N um,Co lum nSize:=4).SelectSelecti on.N am e=″R ep resen ts N um bers R ange″其中变量S N um存储输入的状态个数1213　系统目标的统计与评价考虑到系统决策支持功能,算法设计为由专家设计方案,采用人机交互,这样可以发挥专家的经验判断;系统实现模拟运算——系统对每一个设定的定货方案在给出的模拟周期内计算出相应的平均利润,记忆模拟结果并与已模拟实现过的方案比较给出较好的方案选择1算法充分体现了人机交互追求问题的满意解的现代决策思想1案例的最终模拟结果显示4100gallon是最满意的1我们所开发的是一个示范性系统,但是这一系统的性能充分说明了本文中所指出的Excel作为一种决策支持系统开发工具或决策支持系统产生软件所具有的功能特点1模拟方法是管理决策的重要方法之一,本文的工作说明了Excel软件工具在管理决策上的广阔的应用前景,抛砖引玉,愿同行指教1图1案例模拟流程图参考文献1　Efrai m T urban .D ecisi on suppo rt and Expert system s :M anagem ent suppo rt system s ,M ac m illan Publish ing Compa 2ny ,19902　E ric W ells .M icro soft Excel 510应用开发技术精解1王华译1北京:清华大学出版社,1995The M onte C a rlo S i m ula tion M e thods on Exce lJ iang Q inghua L i Guofeng(Shandong Econom ic Co llege ,J inan 250014)Abstract T h is essay covers the m icro soft excel’s functi on s on decisi on suppo rt sys 2tem s and also ,th rough a case ,studies the M on te Carlo si m u laiton system s on excel after discu ssing it’s algo rithm .T herefo re ,it show s that m icro soft excel has a better app lica 2ti on p ro sp ect in m anagem en t decisi on .Key words D ecisi on suppo rt ,M icro soft excel ,M on te Carlo si m u lati on92第2期姜庆华等:基于Excel 的蒙特卡罗模拟方法的实现　。

经营(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é)C4:D1003单元格区域(qūyù)/选择“数据”选择卡/模拟分析/模拟运算表。