1、市场竞争模拟讲义北京科技大学经济管理学院胡 枫二八年九月目 录一、计算机模拟及其在管理决策中的应用 11.1 模拟与计算机模拟 .11.2 计算机模拟在经济管理中的应用举例 .7二、用 Excel 进行决策模拟 102.1 掷硬币游戏 .102.2 随机需求的存储问题 .23三、企业竞争模拟 293.1 企业竞争模拟及软件简介 293.2 模拟规则 313.3 模拟向导 31四、用 Excel 进行预测 324.1 时间序列预测 .324.2 回归分析预测 .41五、用 Excel 进行优化 585.1 Excel 自带的“规划求解 ”功能 585.2 WhatsBest 的“规划求解”功能
2、63习 题 67参考文献 69市场竞争模拟讲义1一、计算机模拟及其在管理决策中的应用1.1 模拟与计算机模拟1. 模拟在给模拟下一个定义以前，先让我们一起看以下有关模拟的例子。比如，要设计一个新型的飞机，设计师一般不是按图纸制造出飞机，直接由飞行员驾驶飞上兰天，因为那样做很容易造成人员伤亡和财产损失，风险太大。通常，设计师会先制造一个形状一样但体积很小的飞机模型，在风洞（可以调节出不同的风速和气流的复杂变化）实验室里观察飞机模型的状态，发现问题后再进行改进。设计师通过多次类似的实验和改进，感觉没有什么问题了，才比较放心地设计真正的飞机。当然，开始制造的真正的飞机也是要经过有经验的驾驶员试飞的，
3、但它与在实验室进行试验的“试飞”不同。人们通常将实验室里进行的飞机模型试验叫做“模拟”试验。模拟的例子很多，比如建造一个水库大坝也要经过模拟试验，大家还可以想出其它的例子。从上面的例子可以看出“模拟”的几个特点：（1）模拟对象的复杂性在上例中，飞机在高空飞行遇到各种气流变化时，飞机的状态和性能会产什么样的变化，这是设计师说不清楚的，有些情况甚至是想象不到的。假如设计师能通过某一个公式把飞机的各种状况准确地计算出来，而且计算也不复杂，他也就不会再费力做模拟试验了。（2）模拟系统与模拟对象的相象性在风洞里做试验的飞机模型与实际设计的飞机原型要尽量相象，飞机的机身与机翼的尺寸比例、飞机的比重等要与飞
4、机原型相同。风洞中的气流变化也要与实际的高空气流变化尽可能相象。甚至包括飞机模型的表面材料也要尽量一致。但是，飞机模型里面，不一定要有机舱座位等，因为它们不与外界气流直接接触。因此，模拟系统是模拟对象那个复杂系统的近似或抽象，它要能反映原系统中重要的本质性的特征。当然，这里所说的“本质” 依赖于模拟的目的。比如，同样是一个新型的飞机，要试验旅客座位的舒适性和安全性，就必须制造出几排座位，用机械产生升空、降落和颠簸的状况，市场竞争模拟讲义2以观测旅客的感觉。如果没有人身安全问题，可能直接由志愿者参加试验，因为“飞机”根本不升空，甚至可能连翅膀都没有。（3）模拟系统的可控性模拟系统比原系统要简单，
5、这是显而易见的。但是，模拟系统不仅是简单，它应该具有可控性。比如，风洞实验室应该能按试验人员的意愿产生不同方向和大小的气流，这可以让设计师观察到各种复杂的情况下飞机的性能。有些情况可能是在实际中很难遇到的，比如，观察飞机在一个机翼失灵时的状况。（4）模拟试验的可重复性模拟试验是科学的，其观察结果是稳定的，虽然可能存在微小的随机误差。基于此性质，人们可以从多次试验结果分析出客观事物存在的规律性。在以上所说的飞机试验模拟中，人们也可以通过不断调整设计的参数，改进飞机的性能。2. 计算机模拟在了解了什么是“模拟” 以后，让我们一起来看什么是计算机模拟。与用风洞实验室进行飞机的试验设计不同，计算机模拟
6、所用的模拟系统是利用计算机程序实现的。它不是用有形的风洞和飞机模型，也不是用有形的计算机。如果要模拟计算机在电压不稳时的工作性能，那时模拟系统就是计算机硬件。但现在人们通常说的计算机模拟是用计算机软件实现的。这里所说的“软件”包括商用软件和自编的计算机程序。商用软件不过是别人编的有特定功能的比较完善的计算机程序而已。在具体讨论计算机模拟以前，先介绍一个掷硬币的游戏（参见 Frederick S. Hillier int num_test=100;float v, earnings=10;for(i=0;i=0.5,1,0)”，然后将此单元格复制、粘贴到 “是否正面”列的其它市场竞争模拟讲义14
7、999 个单元格中。该 IF 函数的作用是：如果刚才产生的随机数大于或等于 0.5，那么该单元格的值就为 1，即本次投掷的硬币为正面；如果随机数小于 0.5，那么该单元格的值就为 0，即本次投掷的硬币为反面。这样，我们就实现了随机投掷硬币 1000 次的正反面结果。接下来，我们需要根据题目所给出的条件“直到累计出现的正面与反面次数之差的绝对值等于 3 为止”来判断“ 是否结束 ”。因此，我们需要计算出累计正面的次数和累计反面的次数，并计算它们的差值，从而判断“是否结束”，类似地，我们也是利用 IF()函数和求和函数 SUM()来实现上述过程。我们分别在单元格 D4 和 E4 内输入公式“=SU
8、M(C$4:C4)”和“=A4-D4”，然后分别将此两单元格复制、粘贴到“累积正面”列和“ 累积反面”列的其它 999 个单元格中。接下来，我们利用 IF()函数来判断 “是否结束”，以“1”表示“结束”， “0”表示“不结束”。因为第 1 次不可能结束，所以我们可以直接在单元格 F4 中填入“0”。我们从第 2 次投掷硬币起开始判断，于是我们在单元格 F5 中输入公式“=IF(D5-E5=B$2,1,0)”，并将此单元格复制、粘贴到“是否结束 ”列的其它 998 个单元格中。市场竞争模拟讲义15不过，这样做存在一定的问题，即可能会出现游戏已经结束但对游戏是否结束的判断仍然继续的情况！如上图中
9、本来第 5 次投掷硬币后游戏已经结束，但程序仍然对第 6 次及以后游戏是否结束做出判断。因此，我们需要加入一个约束条件来消除上述情形的发生，也就是说，采用两层嵌套 IF 函数的方法。具体的做法是：我们在单元格 F5 中输入公式“=IF(F4=1,1,IF(D5-E5=B$2,1,0)”，即“如果上次已经结束 ，则本次结束，否则对本次是否结束 进行判断” 。我们将此单元格复制、粘贴到“是否结束 ”列的其它 998 个单元格中。Excel 中的相对引用、绝对引用和混合引用随着公式的位置变化，所引用单元格位置也是在变化的是相对引用；而随着公式位置的变化所引用单元格位置不变化的就是绝对引用。混合引用具
10、有绝对列和相对行，或是绝对行和相对列。当选中公式中的单元格地址时，使用 F4 键能简单地对单元格的相对引用、绝对引用和混合引用进行切换。上面单元格 F5 中所输入公式中的 B$2即为混合引用，具有绝对行和相对列。市场竞争模拟讲义16最后，我们需要计算玩此游戏的最终收益，也就是最后所得报酬减去每次投币所花的钱。因为第 1 次不可能结束，所以我们可以直接在单元格 G4 中填入“-1”（因为每次投币需要花 1 元钱） 。类似“ 是否结束”列的做法，我们从第 2 次投掷硬币起开始判断，在单元格 G5 中输入公式“=IF(F4=1,G4, IF(F5=1,D$2-A5,-A5)”，即“如果 上次已经结束
11、，则本次赢得等于上次赢得，否则根据本次是否结束 来计算赢得如果结束，则得到 最终报酬减去 已经投币的花费 ；否则即为已经投币的花费”。我们将此单元格复制、粘贴到“赢得”列的其它 998个单元格中。然后，在单元格 F2 和 H2 内填入公式“ =MATCH(1,F4:F1003,0)”和“=G1003”，即得到本轮游戏的“实际投掷次数” 和“最终赢得”。市场竞争模拟讲义17至此，我们对参与一轮投硬币游戏的模拟已经完成。（6）运行并调试模型我们按一下键 F9，就会看到上述过程会重新计算一遍。不断地按 F9，就会得到不同的结果。但到底哪个结果是可信的呢？因此，我们需要进行全面的模拟分析，不要急于下结
12、论。（7）进行全面的模拟分析如果我们的模拟次数足够多（当然要考虑模拟的成本） ，使模拟系统进入平稳状态，那么这样我们就能获得比较稳定的结果。也就是说，如果我们能够得到多次的模拟结果，那么我们就可以对这些结果进行分析，比如求其最大值、最小值、平均值以及代表风险的方差等等，进而在此基础上做出自己的判断。实际上，我们可以通过“模拟运算表”来实现上述要求。模拟运算表是一种假设分析的方法，即当某一个或几个变量变动时，最终结果值会产生怎么样的变动。比如：我们可以给变量赋不同的值，那么结果也会相应地发生变化。我们当然可以把变量的值一次又一次的代入进行计算，但是更方便的方法就是用模拟运算表。在此我先给大家举一
13、个简单的例子对“模拟运算表”的应用加以说明，然后再继续对投硬币游戏例子进行模拟分析。Match 函数该函数返回目标值的位置，而不是数值本身。比如 F2 单元格中的MATCH(1,F4:F1003,0)： 第一个参数 1 为我们要找的目标值； 第二个参数 F4:F1003 为我们的搜索范围； 第三个参数 0 表示查找等于目标值 1 的第一个数值。其实，Excel 函数的功能非常强大，而我们不可能在课堂上学到所有的 Excel 函数。因此，需要同学们自己查阅 Excel 自带的帮助文件或利用 google 搜索来找到 Excel 函数的相关说明。市场竞争模拟讲义18模拟运算表假设某位同学的月生活费
14、为 1000 元，月固定开销（包括吃饭、买零食、买衣服、通讯费用等）为 800 元，请问该同学月结余多少钱？大家看到上面这个例子可能会觉得太简单了，小学一年级的小孩都会做。说得没错！不过，我给大家举这个例子的用意在于说明“模拟运算表”的作用，更复杂的工作我们会在投掷硬币游戏的模拟过程中进行说明。好，我们先在 Excel 中实现上述计算过程：非常简单，我们只需输入相应数据，并在单元格 B5 中输入公式“=B2 - B3”即可。但接下来的问题是：如果该同学的月生活费在 500 至 2000 之间变动时，他/她的月结余又是多少呢？当然我们可以直接利用公式一个个进行计算，但我们下面将利用“模拟运算表”
15、来更方便地解决上述问题。首先，我们在 B8 单元格中输入公式 “=B5”，即将单元格 B5 中“B2 - B3”（月生活费-月固定开销）这样一个关系引入到 B8 中来。接下来选中包含数据的两列，即区域“A8:B24”，然后点击菜单“ 数据”“模拟运算表”：市场竞争模拟讲义19现在回到我们的“投掷硬币游戏”问题。类似地，我们可以将更复杂的投掷硬币游戏的模拟过程重复进行 100 次，也就是说，进行 100 轮投掷硬币。请注意：我们这里只是将投硬币过程重复 100 遍，并没有涉及到任何参数的变化。我们先采用“编辑”“ 填充”“序列”的模拟运算表（续）接下来在弹出的对话框中的“输入引用列的单元格” （
16、因为数据 500 至 2000 是列的形式，而不是行的形式）中选择变量的位置，即 B2。也就是说，数据列 500 至 2000 中每一个数值都被放到 B2 那个位置计算一遍。最后点击确定后，即得到我们想要的结果（该结果与我们手动计算的结果完全一致）：市场竞争模拟讲义20方法在单元格 J5 至 J104 内填入 1 至 100，然后在单元格 K4 内填入公式“=H2”。接下来，我们将单元格 J4 至 K104 的区域选中，点击菜单“数据” “模拟运算表”。在弹出的对话选择框中，在“输入引用列的单元格（C ） ”一栏，我们选择单元格 M5，然后点击“确定”。注意：因为刚才我们所输入的 1 至 10
17、0 为一列数据，而不是一行数据，所以我们在“输入引用列的单元格（C ） ”中输入，而不是在“输入引用行的单元格（R） ”中输入。另外，因为刚才我们所输入的 1 至 100 没有任何实际意义，不是模型参数，所以我们在“输入引用列的单元格（C） ”一栏中选择任意的空白单元格即可，大家也可以试着选择其它空白单元格。如果为模型参数的话，则需要像上述那个简单例子一样选择相应参数所在的单元格。市场竞争模拟讲义21于是，我们就得到 100 轮投掷硬币的结果：我们需要对这些结果进行一些必要的简单统计分析，如它们的平均值和波动程度，来获得对结果的整体概念以及发现重要趋势，从而辅助我们做出是否参与此游戏的决策。上
18、述统计分析我们可以通过 Excel 自带的 “数据分析”功能来实现，当然也可以分别利用 Excel 函数来实现。简单的描述性统计分析所谓“描述性统计分析”就是通过所分析数据的均值、最大值、最小值、中位数、众数、全距、方差、标准差等，从而获得对数据的一个整体印象。其中，均值、中位数、众数反映的是一组数据的集中趋势；而全距、方差、标准差反映的则是离差程度。这里，我们先简单介绍一下概念（“均值” 、 “最大值” 、 “最小值”不用说了）：中位数：一组排好序的数据中，处于中间位置的数，如果是该组有偶数个数据，则取二个中间数的平均值为中位数。众数：一组数据中出现频率最高的数，一组数据可以有多个众数也可以
19、没有众数。全距：就是一组数据中的最大值减去最小值，反映了数据波动的范围。方差：是一组数据中各数据与该组平均数之差的平方的平均数，也就是描述各数据与中心（平均值）偏离的程度。标准差：方差的平方根。这些指标均可以通过以下 Excel 函数来得到，具体用法大家可以查帮助文件：指标 均值 最大值 最小值 中位数 众数 方差Excel 函数 AVERAGE MAX MIN MEDIAN MODE VAR市场竞争模拟讲义22简单的描述性统计分析（续）不过，下面我们要演示的是 Excel 自带的“数据分析”功能。点击菜单“工具” “数据分析”。如果发现“ 工具”菜单中没有“数据分析”，则需要加载该功能。具体
20、做法是：点击菜单“工具” “加载宏”，在弹出的对话框中选中 “分析工具库”，然后点“确定”即可。此时弹出“数据分析” 对话框，在“ 分析工具”列表框中选择 “描述统计” ，然后点“确定”即可。市场竞争模拟讲义23上述求得的均值、中位数、众数反映了玩此游戏的平均收益，而全距、方差、标准差则反映了玩此游戏的风险程度。均值或中位数越大，则玩此游戏的平均期望收益越高；而全距、方差、标准差越大，则玩此游戏的风险越大。另外，我们还可以利用函数 countif()来求得100 轮投掷硬币的结果中赚钱的次数，即获得参与此游戏的赢率。该指标也可以在一定程度上反映出玩此游戏的风险程度。（8）写出模拟报告我们可以根
21、据上面的模拟分析结果做出决策，并写出模拟报告（如果需要的话） 。类似地，我们也可以用模拟的方法解决随机需求的存储问题。下面再给大家举一个更为复杂的例子。2.2 随机需求的存储问题某商店每天的啤酒销售量服从如下离散型随机分布：需求量 100 120 150 200 250概率 0.2 0.25 0.35 0.15 0.05商店每次定货的定货费为 100 元；每瓶啤酒每天的存储费为 0.1 元；若顾客来买啤酒而商店缺货，每瓶的缺货费为 0.5 元（这是机会损失） 。假设定货期为 12 小时，即今天晚上定货，明天一早就到货。商店经理每天晚上检查存货情况，在存货少于一定数量（即：定货点）时就打电话定货
22、，并且要说明定多少（定货量） 。经理的目标是在一段较长的时间里平均成本最小。请你帮经理解决这一存储问题。下面我们在 Excel 环境下用模拟的方法解决这一问题：（1）输入基本参数我们先把存储问题的基本参数输入到电子表格。在 E 行计算出随机需求的累计分布，从公式显示条可以看到 E5 单元格的计算公式。注意：E7 的值不是 1，而是 0.95，意思是当随机数大于 0.95 时需求量为 250。市场竞争模拟讲义24（2）设定初始值和决策变量的取值假设该商店在模拟开始有 200 瓶啤酒，并给决策变量定货点和定货量赋值。为决策变量赋的值不一定是最优的，我们可以变化这些值观察目标函数的变化。（3）模拟一
23、天的需求、供货和定货为了产生第一天的需求，要调用 rand()函数产生一个介于（0,1）的均匀分布的随机数，根据该随机数的大小查看需求的累计分布，然后决定需求量。请注意下图中需求、存量、定货量以及各种费用是如何决定的。相应单元格的计算公式是用插入批注的办法在图中表示出来的。市场竞争模拟讲义25在确定需求量时我们用了函数 VLOOKUP()，它的功能是在一个数据列表中寻找相应的值。它的第一个参数是 C10，即产生的随机数的数值；第二个参数是 E3:F7 区域的数组，即累计分布与相应的需求量；第三个参数是 2，表示寻找的数值在该数组的第二列。（4）模拟多天的情形在模拟了第一天以后，我们可以用同样的
24、方法模拟第二、第三、.，一直到你选择的模拟天数。我们假设模拟 10 天，如下图所示。我们得出 10 天的总费用是 603.5 元。在实际模拟中，你会发现一个很奇怪的现象：当你做某种操作时，C 列的随机数会变化。因为 Excel 在做某些操作时，要对每个单元格进行计算，当碰到 rand（）函数时，它会产生一个新的随机数，所以表中的 C 列会刷新。为了对结市场竞争模拟讲义26果进行分析，我们可以将该列用选择性粘贴将它的值拷贝到同一列。以下，我们产生 100 天的存储情况，并把随机数的值用上面的办法固定下来。为了节省版面，我们只把开头和结尾列出来。于是，我们可以知道，100 天的总费用是 5986
25、元。请注意，该值是依赖于随机数的，所以你们在模拟时得出的值可能不是这个数，甚至可以说很难得到与本书完全同样的数。如果模拟的天数充分多，结果的误差是比较小的。（5）利用“模拟运算表” 进行分析为了求出最优的定货点和定货量，我们可以先“冻结”C 列的随机数，再改变决策变量的值。因为我们有两个决策变量，为了把解法叙述清楚，我们先固定一个决策变量定货点，仅变化定货量。为了更方便地观看决策变量和结果，我们让单元格的值等于单元格 J110（总费用）的值。市场竞争模拟讲义27如果我们要让定货量在更大范围变化，一个个地实验未免太麻烦了。下面，我们可以像上例一样，使用 Excel 中“数据”栏里“模拟运算表”的
26、功能，以达到模拟多种定货量的目的。首先，我们设定定货量取值的范围，假设为 300 到 800，每个间隔为 50。我们在 Excel表中选择合适的空位，用“编辑”里的“ 填充”功能输入 300，350，.,800 这 11 个数值。大家一定注意到，在 K2 到 U2 是预设的模拟参数定货量，但在 J3 设定为等于 B8（即总费用）的值。此后，要为进行模拟运算选择好数组。注意：这里必须包括 J2 和 J3 两个单元格。市场竞争模拟讲义28接着，我们可以选择“数据” 中的“ 模拟运算表”，得到一个对话选择框。在“ 输入引用行的单元格（R ） ”一栏，我们选择了 B7，这是定货量所在的单元格，其作用是告诉程序，上面的K2 到 U2 一行的数值要逐个替换 B7 中的定货量。至于下面一行的数值是什么，要由 J3 单元格来确定。因为我们已经令 J3 格等于 B8 格，即总费用，所以，从 K3 到 U3 的单元格将是与上面的定货量相应的总费用。我们不需要选“输入引用列的单元格（C） ”，因为所设定的数组是以横行的方式排列的。如果你想用竖列的方式排列数组，你就应选择下面的而不是上面的选择框。在选择完后，按“确定” 按钮，可以得到下面的结果。大家可以猜想，最优定货量大约在600 左右。
