运筹学实验指导手册

《运筹学》实验指导 1 运筹学实验指导手册 配套教材：《Introduction to Operations Research》 Frederick S.Hillier and Gerald J.Lieberman Copyright 1999 by the McGraw-Hill ,sixth edition 江西财经大学信息管理学院 《运筹学》课程组 2005 年 9 月 《运筹学》实验指导手册 2 目 录 实验一：EXCEL 电子表格应用初步 ......................................................3 实验二：应用 EXCEL 电子表格求解线性规划 .....................................5 实验三：EXCEL 电子表格建模艺术 ......................................................9 实验四：用 EXCEL 电子表格建立更复杂的 线性规划模型 .............14 实验五：用 EXCEL 电子表格做敏感性 .......................................17 实验六：用 EXCEL 电子表格解决物流中的网络配送问题 ...............21 实验七：用 EXCEL 电子表格解决物流中的网络配送问题（续） ...23 实验八：用 EXCEL 电子表格解决最优指派问题 ...............................27 实验九：用 EXCEL 电子表格求解网络优化问题 ...............................30 实验十：用 EXCEL 电子表格求解非线性规划问题 ...........................34 实验十一：用可分离规划逼近非线性规划问题 ..................................36 实验十二：用 EXCEL 电子表格求解风险决策问题 ...........................39 实验十三：风险决策问题的敏感性分析 ..............................................42 实验十四：用 EXCEL 中的相关分析做回归预测 ...............................45 《运筹学》实验指导手册 3 实验一：EXCEL 电子表格应用初步 一、实验目的 1、了解 EXCEL 电子表格的结构； 2、掌握 EXCEL 电子表格中单元格的表示方法； 3、掌握 EXCEL 电子表格中对单元格的各种操作； 4、掌握 EXCEL 电子表格中公式的输入方法。 二、实验内容 对某种产品进行盈亏平衡分析。 例题：特殊产品公司生产在商店销售的昂贵而不常见的礼品，礼品是为那些 已经几乎什么都有的富人生产的。公司研发部最新的产品计划是有限版落地摆钟 （limited edition grandfather clock）。公司管理部门需要决定是否生产这个新产 品，生产量为多少时才能盈利？ •Data: –If they go ahead with this product, a fixed cost of $50,000 is incurred. –The variable cost is $400 per clock produced. –Each clock sold would generate $900 in revenue. –A sales forecast will be obtained. 通过编制如下的电子表格模型来解决此问题 三、实验步骤 1、首先在草纸上对这个问题进行比较细致的分析 DDecision variable: Q = Number of grandfather clocks to produce Costs: Fixed Cost = $50,000 (if Q > 0) Variable Cost = $400 QTotal Cost = z0, if Q = 0 Data Results Unit Revenue $900 Total Revenue $270,000 Fixed Cost $50,000 Total Fixed Cost $50,000 Marginal Cost $400 Total Variable Cost $120,000 Sales Forecast 300 Profit (Loss) $100,000 Production Quantity 300 Break-Even Point 100 《运筹学》实验指导手册 4 z$50,000 + $400 Q, if Q > 0 Profit: Profit = Total revenue – Total cost zProfit = 0, if Q = 0 zProfit = $900Q – ($50,000 + $400Q) = –$50,000 + $500Q, if Q > 0 2、在数据单元格输入：单位收入$900，固定成本$50000，变动成本$400， 销售量预测值 300。 3、在输出单元格输入公式。在输入公式之前，首先对有关单元格定义名称， 如 UnitRevene, SalesForcast, ProductionQuantity, FixedCost, MarginalCost 等。 公式按如下表格输入： 4、通过改变黄色单元格的生产量，来求得盈亏平衡点。即利润为零时的产 量，Q=100。 《运筹学》实验指导手册 5 实验二：应用 EXCEL 电子表格求解线性规划 一、实验目的 1、了解 EXCEL 规划求解宏模块的功能； 2、掌握 EXCEL 规划求解宏模块的加载； 3、掌握在 EXCEL 电子表格中建立线性规划模型； 4、掌握用 EXCEL 规划求解宏模块求解线性规划。 二、实验内容 用 EXCEL 规划求解确定伟恩德玻璃制品公司产品组合问题 公司有三个工厂： 工厂 1：生产铝框和五金件 工厂 2：生产木框 工厂 3：生产玻璃和组装窗与门 公司打算生产的新产品 8 英尺玻璃门 4 英尺×6 英尺双层窗 基本生产信息如下表： 单位产品的生产时间 工厂 门 窗 每周可得时间 1 2 3 1 小时 0 3 小时 0 2 小时 2 小时 4 小时 12 小时 18 小时 单位利润（美元） 300 500 理论模型为： ⎪⎪⎩ ⎪⎪⎨ ⎧ ≥ ≤+ ≤ ≤ += 0, 1823 122 4 .. 500300max 21 21 2 1 21 xx xx x x ts xxZ 三、实验步骤 1、选择决策变量单元格，决策变量的初始值一般赋 0，并用较醒目的颜色（黄 色）表示。 《运筹学》实验指导手册 6 2、确定目标单元格，用函数公式表示，并用较醒目的颜色（桔黄色）表示。 3、用公式输入每一个约束条件左边项 4、Excel Solver 的安装。Excel 工具菜单中选择加载宏 《运筹学》实验指导手册 7 5、调用规划求解，确定可变单元格和目标单元格 6、增加约束条件 7、成求解对话框 《运筹学》实验指导手册 8 8、求解结果 《运筹学》实验指导手册 9 实验三：EXCEL 电子表格建模艺术 一、实验目的 1、了解 EXCEL 电子表格建模程序； 2、掌握建立一个好的 EXCEL 电子表格模型的原则和技巧； 3、了解如何调试和检测电子表格模型。 二、实验内容 大沼泽地金色年代公司目前仅有 100 万美元的现金余额，公司看来需要获得 一些贷款来满足其财务负担。而且，为了预防一些不确定因素，公司政策规定现 金储备在任何时候必须保持最少 50 万美元的余额。 公司出现的问题：目前仅有 100 万美元的现金余额，大沼泽地公司需要获得 一些贷款来满足其财务负担。 公司的要求：为了预防一些不确定因素，公司政策规定现金储备在任何时候 必须保持最小 50 万美元的余额。 解决问题的途径：第一种选择是十年期限的长期贷款，每年支付利息，全部 的本金在十年后一次性付清，贷款利率是 7%。 另一种选择是一系列的一年期贷款。这些贷款可以在每年需要时获得，但必 须在下一年将本金付清。这些贷款的利率是 10% 《运筹学》实验指导手册 10 管理者需要解决的问题：应该选择哪一种贷款方式，贷款多少，才能保持 公司的偿还能力，并使 2013 年的现金余额在支付完贷款之后最大化？ 三、实验步骤 1、首先认真研讨下面关于建立一个复杂电子表格模型的程序 2、对问题进行更为深入的分析 每年期末余额的计算: 期末余额=期初余额+现金流+贷款-利息支出-贷款偿还 决策变量:2003 年的长期贷款数,每年的短期贷款数 约束:每年期末余额≥0.5 万美元 目标是:2013 年的现金余额在支付完贷款之后期末余额最大化 3、手工进行 一些简单的计算，这是建立电子表格模型中重要公式的方法 《运筹学》实验指导手册 11 4、草拟电子表格 5、建立一个小的电子表格模型 6、从小的模型中获得模型的合理逻辑关系，然后再将其扩充成一个大的、 比较复杂、完善的模型 《运筹学》实验指导手册 12 7、建立一个好的电子表格模型的原则 •首先输入数据 –所有的电子表格都是受数据驱动的； –整个模型的形式是围绕数据结构建立的。 –在开始建立模型的其余部分之前，先输入和编排数据 •组织和清楚地标识数据 –相关的数据应当用方便的格式组合在一起，并用标识确定这些数据； –以表格形式展示的数据，应当给表格一个标题； –数据单位也应当标识。 •每个数据输入唯一的一个单元格 –如果一个数据需要在多个公式中使用，都指向最初的数据单元格，不要在 另外的地方重复。 –这可以使模型更容易修改，如果该数据发生了变化，你只要修改一处，不 需要修改多处。 •将数据与公式分离 –避免在公式中直接使用数据； –应当将所有需要的数据输入单元格，然后在公式中代入所需的单元格； –这可以使数据更明显而容易修改。 •保持简单化 –避免使用复杂的函数和采用功能强大的 Excel 函数； –将复杂的函数分解成简单的函数。 •使用区域名称 –指向单元格或区域中的数据应当尽可能使用名称； –使用名称会使公式和求解报告更容易读懂； –Care must be taken not to overuse range names and to make sure they remain correctly defined.•用相对引用和绝对引用来简化公式 –当需要重复输入公式时，只需要输入一次公式，然后用 Excel 的填充命令 复制公式. –这使模型建立非常容易，还能养活出错的可能. 《运筹学》实验指导手册 13 •使用边框、阴影、和颜色来区分单元格类型 –For example: •数据用兰色（不用边框） •变动单元格用黄色(带边框) •目标单元格用桔黄(粗边框) •在电子表格中显示整个模型 –所有的数据必须是显而易见的. –约束条件的所有内容应当列在电子表格上，对于每一个约束条件，需要使 用三个相邻的单元格. 8、调试电子表格模型 用 ctrl+~键在数据与公式之间切换。 用审核工具进行调试。使用左起第三个键可以追踪从属单元格，即哪些单元的公 式引用了这个单元格的数据。使用最左边第一个键可以追踪引用单元格，即这个 单元中的公式引用了哪些单元格数据。 实验四：用 EXCEL 电子表格建立更复杂的 线性规划模型 一、实验目的 1、掌握资源分配型线性规划模型的 EXCEL 电子表格建模程序； 2、掌握网络配送型线性规划模型的 EXCEL 电子表格建模程序； 3、掌握混合型线性规划模型的 EXCEL 电子表格建模程序。 《运筹学》实验指导手册 14 二、实验内容 维特公司经营一家回收中心，该中心专门回收 4 种固体废弃物，并将废弃物 进行处理混合成可以销售的小产品，根据混合时废弃物的不同比例，可生产三种 不同等级的产品：A, B,和 C。 问题：每种废弃物各使用多少，三种产品各生产多少使利润最大？该问题的 有关数据如下表示。要求建立线性规划模型，并用 EXCEL 规划求解求出最优方 案 。 三、实验步骤 1、首先对问题进行细致的分析，确定决策变量、目标函数、约束条件，然 后建立其理论模型。 《运筹学》实验指导手册 15 2、按如下形式建立其电子表格模型。注意电子表格模型中公式的输入及约 束条件的表达方式，然后调用规划求解求出最优。 《运筹学》实验指导手册 16 《运筹学》实验指导手册 17 实验五：用 EXCEL 电子表格做敏感性分析 一、实验目的 1、了解敏感性分析的内容； 2、掌握应用敏感性报告做敏感性分析； 3、掌握应用 Solver Table 做敏感性分析； 4、掌握应用百分百原则做敏感性分析。 二、实验内容 分配学生和学校 有 6 个地区的学生需要分配到 3 所学校去就读，有关信息如下表： 并且每一所学校各年级就读的人数应在总人数的 30%到 36%之间。请建立 该问题的电子表格模型，并就下列情况做敏感性分析。 《运筹学》实验指导手册 18 （1）假设其他地区的公交成本不变,运用灵敏度报告,检验在最优解不变的情 况下,6 区到学校 1 的公交可以增加的成本.如果求出可以增加的成本不能超过 10%,用规划求解器求一个允许增加 10%的最优解. （2）现在假设 6 区到所有的学校的公交成本都以同等百分比增加,运用灵敏 度报告分析最优解不变的条件下,增加的百分比允许多大.如果求出可以增加的成 本不能超过 10%,用规划求解器求一个允许增加 10%的最优解. （3）对于每一学校,使用灵敏度报告中的影子价格,确定是否需要租用简易教 室. （4）于租用的简易教室的学校,运用灵敏度分析影子价格有效的范围(假设只 有一个学校租用简易教室)。 （5）对于有几所学校需要租用简易教室,确定在影子价格有效的前提下,每个 学校租用教室的数目的组合.然后用影子价格确定其中哪种组合的公交和租费总 成本最小.使用规划求解器找到该问题对应的最优解。 （6）如果（5）中的结果可行,在影子价格最大的学校增加一个简易教室,使 用规划求解器求得最优解,并生成相应的灵敏度报告.利用这些信息,判断（5）中 的结果是否最优,如果不是,找出最优解. 三、实验步骤 1、仔细地分析问题，确定决策变量、目标函数、约束条件，建立其电子表 格模型。 问题分析:决策变量是每个地区分配到每个学校的学生人数 限制条件有：各学校人数限制、各地区人数限制 各学校每人数比例限制：某个学校某个年级人数比例 30%≤ =来自各地区的某一年级人数/学校总人数 ≤36% 目标是：使总公交成本最低。 《运筹学》实验指导手册 19 2、做敏感性分析 敏感性报告如下，可利用敏感性报告得到影子价格，从而可对每一学校是否 租用简易教室进行决策。 3、用从敏感性报告中得到各学校可以增加的最大容量，用百分百原则分析 多个参数变化对于最优方案的影响。 《运筹学》实验指导手册 20 I The bussing costs increase 1% from area 6 to all the schools, then: Percentage of allowable increase for school 1 used=($505–$500)/$33.33 = 15%. Percentage of allowable increase for school 2 used = ($303 – $300) / ∞ = 0%. Percentage of allowable increase for school 3 used = ($0 – $0) / $166.67 = 0%. Sum = 15%. Therefore, the bussing costs from area 6 can increase uniformly by (100%/15%)(1%) = 6.67% before 100% will be reached. Beyond that, the solution might change. 4、对于有几所学校需要租用简易教室,确定在影子价 格有效的前提下,每个学校租用教室的数目的组合.然后用影子价格确定其中哪种 组合的公交和租费总成本最小.使用规划求解器找到该问题对应的最优解。 The following combinations do not violate the 100% rule: 《运筹学》实验指导手册 21 实验六：用 EXCEL 电子表格解决物流中的网络配送问题 一、实验目的 1、了解网络配送问题的基本结构； 2、掌握网络配送问题中不能使用固定供应地和需求地的组合的处理技巧； 3、掌握网络配送问题中单位配送成本的获取； 4、掌握大型网络配送问题的电子表格编制技巧。 二、实验内容 北 北方飞机制造公司为全世界的航空公司生产各种商务飞机。制造过程最后一 步是生产喷气发动机并把它们安装到已经完成的飞机框架之中去（非常快的一个 操作）。按照公司的一些定单合同，不久要交付使用相当多数量的飞机。 问题: 在这四个月内每个月应该生产多少发动机,可使得四个月的总费用(包 括存储费和生产费最小)?三、实验步骤 1、对问题进行细致的分析。这个问题本身是一个生产安排问题，如何将其 转化为一个网络配送问题。这里可以将生产的月份 1、2、3、4看成是供应地， 交货的月份 1、2、3、4看成是需求地，从而转化为一个网络配送问题。 2、单位配送成本的获得。这里的成本包括生产成本、存储成本。成本按如 下方式计算： 《运筹学》实验指导手册 22 3、编制该问题的电子表格模型，并用规划求解求出最优方案。 《运筹学》实验指导手册 23 实验七：用 EXCEL 电子表格解决物流中的网络配送问题 （续） 一、实验目的 1、了解网络配送问题的基本结构； 2、掌握网络配送问题中不能使用固定供应地和需求地的组合的处理技巧； 3、掌握网络配送问题中单位配送成本的获取； 4、掌握大型网络配送问题的电子表格编制技巧。 二、实验内容 Texago 公司是美国本土的大型一体化石油公司，公司大部分石油在自己的 油田中生产，其余从中东进口。公司拥有大型配送网络，把石油从油田运送到炼 油厂，然后将石油产品从炼油厂运送到公司的产品配送中心。公司为了增加其主 要产品的市场占有率，管理层决定新建一个炼油厂来增加公司的产量，并增加从 中东的石油进口量。 《运筹学》实验指导手册 24 《运筹学》实验指导手册 25 问题：需要根据以上调查信息，确定新炼油厂的地址该选在洛杉矶、加尔维 斯敦，还是圣路易斯？ 三、实验步骤 1、首先对问题进行透彻的分析。该问题是六个简单网络配送问题的综合应 用。 分析:运输问题的六个应用 选择新地址需要考虑的成本: (1)每一个新炼油厂建造地点选择带来的总原油运输成本; (2)每一个新炼油厂建造地点选择带来的总石油制品运输成本; 《运筹学》实验指导手册 26 2、建立由油田到炼油厂的电子表格基模型: 然后将三个备选地点代入基模型，分别求得从油田到炼油厂运输费用 1。 3、建立由炼油厂到配送中心的电子表格基模型: 然后将三个备选地点代入基模型，分别求得从炼油厂到配送中心的运输费用 2。 4、将运输费用 1、运输费用 2 及备选点的运营费进行综合考虑，取三笔费 用之和最小的一个为最终新炼油厂的选址。 《运筹学》实验指导手册 27 实验八：用 EXCEL 电子表格解决最优指派问题 一、实验目的 1、了解指派问题的基本结构； 2、掌握建立指派问题的电子表格模型的程序； 3、掌握指派问题中分派的人和任务不相等、不能采用固定的人和任务搭配 的组合等问题的处理技巧； 4、掌握大型指派问题的电子表格编制技巧。 二、实验内容 1、Better Products 公司决定利用三个有剩余生产能力的工厂生产四种新产 品，有关信息如下。并且规定一类产品不可以在不同的工厂生产。 问题：求出使成本最小的最优生产方案。 2、Middletown 镇学区开办了三所高中，需要为每所高中重新划分服务区域。 整个城市被分成 9 个人口数量大致相等的区域,每所学校都给定了所能安排的最 小和最大的学生数，学区管理者认为划分入学区域界限的适当是要使学生到 学校的平均路程最短。如果除了学校的招生数的限制外，还要考虑同一区域的学 生应该被分配在同一学校就读，每所学校需要接收 3 个地区的学生就读。 《运筹学》实验指导手册 28 问题：每个区域有多少学生被分配到每所学校？三、实验步骤 1、细致分析指派问题与网络配送问题的区别，同一个问题，什么时候应该 看成网络配送问题，什么时候应该看成指派问题，关键之处在于其任务是否可以 分割。 2、建立 Better Products 公司最优生产的电子表格模型。注意其中指派问题 的单位费用的计算，是将某一类产品放在某个工厂生产的整体成本，如产品 1 共 20 件在第 1 个工厂生产的成本为$41×20=$820 3、建立 Middletown 镇学区的电子表格模型： 《运筹学》实验指导手册 29 《运筹学》实验指导手册 30 实验九：用 EXCEL 电子表格求解网络优化问题 一、实验目的 1、了解最小费用流问题、最短路问题、最大流问题的模型结构； 2、掌握最小费用流问题电子表格求解程序； 3、掌握指最短路问题的电子表格求解程序； 4、掌握最大流问题的电子表格求解程序。 二、实验内容 （1）用电子表格解决最小费用流问题：无限配送公司有两家工厂生产一种 产品，并且要将产品运送到两个仓库： 工厂１的产量为 80 units. 工厂２的产量为 70 units. 仓库１需要 60 units. 仓库 2 需要 90 units. 工厂１和仓库１之间，工厂２和仓库２之间有铁路相连出此之外，卡车还可 将产品运送至配送中心后在运往两个仓库 问题：各条线路应该各运送多少单位产品？ （2）用电子表格解决最大流问题：BMZ 公司是欧洲一家生产豪华汽车的制 造商，其产品出口到美国对公司特别重要。BMZ 汽车在加利福尼亚特别受欢迎， 因此保证洛杉矶中心的良好的供应就显得特别重要，以便能及时提供维修汽车的 配件。BMZ 公司需要一个运输计划，从德国斯图加特运输配件至洛杉矶中心的 《运筹学》实验指导手册 31 配件尽可能多，以便补充该中心正在减少的库存。运送多少配件到洛杉矶中心的 关键在于该公司配送网络的容量。 问题：从斯图加特到洛杉矶的各条运输路线要运送多少单位的配件？ （3）用电子表格解决最短路问题：奎克公司获悉它的一个竞争者计划把一 种很有销售潜力的新产品投放市场，奎克公司也一直在研制一种类似的产品，并 计划 20 个月后投放市场。现在公司的管理者希望迅速推出该产品参与市场竞争， 但是研制工作还有 4 个没有时间重叠的阶段没有完成，这 4 个可以从正常的实 施水平提高为优先水平或应急水平，公司为提前推出产品拨款 3000 万美元。 问题：4 个阶段的那些阶段应该加快速度？是优先水平，还是应急水平？ 二、实验步骤 《运筹学》实验指导手册 32 1、在 EXCEL 中查阅 SUMIF 函数，了解其使用方法 The SUMIF formula can be used to simplify the node flow constraints. =SUMIF(Range A, x, Range B)For each quantity in (Range A) that equals x, SUMIF sums the corresponding entries in (Range B). The net outflow (flow out – flow in) from node x is then =SUMIF(“From labels”, x, “Flow”) – SUMIF(“To labels”, x, “Flow”)2、建立 无限配送公司的电子表格模型 （3）建立 BMZ 公司的最大流电子表格模型 （4）对奎克公司的问题进行分析，化为网络模型。 《运筹学》实验指导手册 33 （5）建立奎克公司的电子表格模型，并用规划求解求出最优安排方案。 《运筹学》实验指导手册 34 实验十：用 EXCEL 电子表格求解非线性规划问题 一、实验目的 1、了解非线性函数的 EXCEL 表示方法； 2、了解非线性规划模型的一般结构； 3、了解非线性规划的局部最优和整体最优的概念； 4、掌握非线性规划的 EXCEL 求解程序。 二、实验内容 管理大量证券投资组合的职业经理习惯于用基于非线性规划的计算机模型 帮助他们工作。投资者同时关心着期望回报和风险。如何追求收益和风险的平 衡？非线性规划经常被用于确定证券投资组合，该组合在一定的假设下，获得收 益和风险之间的最优平衡 目标: 风险极小化 约束：期望回报≥ 可接受的回报的最小水平 考虑 3 支股票的一个投资组合，三支股票的期望回报，风险数据如下， 问题：在期望回报至少要达到 18%的前提下，怎样的投资组合使风险最小？ 三、实验步骤 1、对该问题进行细致的分析，建立其理论模型 设 Si 为投资于股票 i 的投资比率，i=1，2，3Minimize Risk = (0.25S1) 2 +(0.45S2) 2 +(0.05S3) 2 +2(0.04)S1S2+2(–0.005)S1S3+2(–0.01)S2S3 subject to (21%)S1 + (30%)S2 + (8%)S3 ≥18% S1+S2+S3=100% 《运筹学》实验指导手册 35 and S1≥ 0, S2≥ 0, S3≥0. 2、建立该问题的电子表格模型，注意非线性目标函数的表达。并用规划求 解求出最优组合投资方案，此时由于是非线性规划模型，在用规划求解时，不要 选择线性模型，而是选择迭代时间和迭代次数。 3、使用 Solver Table 作期望回报和风险的平衡分析 《运筹学》实验指导手册 36 实验十一：用可分离规划逼近非线性规划问题 一、实验目的 1、了解可分离规划的特点； 2、了解应用可分离规划逼近非线性规划的技巧； 3、掌握应用可分离规划求解线性规划的程序。 二、实验内容 组合投资优化问题：莉迪亚从证券市场上了解到有关股票的信息， BB 股票将会在明年从现在的每股 50 美元上涨到 72 美元； LOP 股票会今年每股 127 美元的基础上上涨 42%； ILI 股票会从现在的每股 4 美元涨到 8 美元； HEAL 会从现在的每股 50 美元增加到每股 75 美元； QUI 会在现在的每股 150 美元的基础上上涨 46%； AUA 会从现在的每股 20 美元涨到 26 美元； 五 种 股 票 的 方 差 数 据 如 下 ： （1）莉迪亚希望忽视所有投资的风险。在这种情况下，她最优的投资组合 决策是什么，也就是她在六种不同股票上分别投资多少？应该投资组合的风险是 多少？ （2）莉迪亚认为不能在一种股票上投入超过总额的 40%的资金，在不考虑 风险并加入这一限制条件下，她的最优组合投资是什么，该投资组合的总风险又 是多少？ 《运筹学》实验指导手册 37 （3）现在莉迪亚要将投资风险考虑在内。为了在下面几个问题中使用，建 立一个二次规划模型，使她的风险最小，同时保证她的收益不低于她选择的最低 可接受水平。（4）莉迪亚希望获得至少 35%的预期收益，同时她又要保持最小 的投资风险，在这种情况下，最优的投资组合是什么？ （5）如果莉迪亚要获得至少 25%的预期收益，最小的投资风险是多少？至 少 40%的预期收益呢，情况又如何？ 三、实验步骤 1、对六种股票的投资风险进行比较，可得出莉迪亚希望忽视所有投资的风 险情况下的投资方案。 ILydia wants to ignore the risk of her investment she should invest all her money into the stock that promises the highest expected return. According to the predictions of the investment advisors the expected returns equal 20% for Bigbell, 42% for Lotsofplace, 100% for Internetlife, 50% for Healthtomorrow, 46% for Quicky, and 30% for Automobile Alliance. Therefore, she should invest 100% of her money into Internetlife. The risk (variance) of this portfolio equals 0.333. 2、在莉迪亚不考虑风险的情况下，建立如下的电子表格模型，并求解可获 得最优投资方案。 3、在莉迪亚考虑风险的情况下，建立如下的电子表格模型，并求解可获得 最优投资方案。 《运筹学》实验指导手册 38 《运筹学》实验指导手册 39 实验十二：用 EXCEL 电子表格求解风险决策问题 一、实验目的 1、了解风险决策的基本原理； 2、掌握应用 TREEPLAN 宏模型画决策树； 3、掌握应用 TREEPLAN 进行决策分析的程序。 二、实验内容 The Goferbroke Company 在未经证实的地区钻探石油.一位地质学家报告 说,这块地有 1/4 的概率有石油。在这块地上钻探险石油需要大约$100,000 的投 资.如果这块地是干涸的(没有石油),整个投资将会化为乌有。如果这块地蕴含石 油,则估计可以得到大约$800,000 的净收入，另一个公司听说有可能有石油,决定 出价$90,000 来购买这块土地。并且公司可以可通过花费 3 万美元进行细致的地 质勘探来进一步核实这块土地有石油的概率。勘探的可能结果有: FSS:好的地震勘探回波,很可能有石油 USS:不好的地震勘探回波,很可能没有石油 Question: Goferbroke 是应该钻探石油还是应该出售土地呢? 三、实验步骤 1、TreePlan 的安装:与 Solver 软件的安装类似 (1)首先从学习光盘中找到 TreePlan 程序模块; (2)将 TreePlan 程序模块拷贝到 Program files\Microsoft office\ Office\library\ 目录下; (3)在 Excel 工具菜单中选择加载宏; (4)在加载宏对话框中选中 TreePlan 程序; (5)回到 Excel 工具菜单中查看,是否有 Decision Tree 菜单条,如果有,则安装成 功. 2、应用 TreePlan 程序模块画出没有经过进一步的地质勘探的初步决策结果。 《运筹学》实验指导手册 40 3 、 应 用 EXCEL 电 子 表 格 计 算 后 验 概 率 。 4、应用修正的后验概率进行更准确的决策分析。 《运筹学》实验指导手册 41 《运筹学》实验指导手册 42 实验十三：风险决策问题的敏感性分析 一、实验目的 1、了解风险决策问题敏感性分析的基本原理； 2、掌握用 EXCEL 中的数据模拟表进行敏感性分析； 3、掌握应用 SENSIT 宏模块进行敏感性分析。 二、实验内容 （1）在 The Goferbroke Company 问题中，对土地有石油的概率进行敏感 性分析，如有石油的概率为 0.15~0.35，应用数据模拟表进行敏感性分析。 （2）分别用 SensIt 中单因素图(Plot)、蛛网图(Spider)、旋风图(Tornado) 对土地有石油的概率、钻探石油所得收益、钻探成本进行敏感性分析。 三、实验步骤 1、首先在电子表格中创建一张表,列出数据单元格的各个尝试值. 2、数据表的第一行,输入公式使之与相关的输出单元格相关联. 3、选择整个数据表,然后选择数据菜单中的模拟运算表菜单条. 4、SensIt 的安装:与 TreePlan 软件的安装类似 (1)首先从学习光盘中找到 SensIt 程序模块; (2)将 程序模块 SensIt 拷贝到 Program files\Microsoft office\ Office\library\目 录下; 《运筹学》实验指导手册 43 (3)在 Excel 工具菜单中选择加载宏; (4)在加载宏对话框中选中 SensIt 程序; (5)回到 Excel 工具菜单中查看,是否有 SensIt 菜单条,如果有,则安装成功. 5、SensIt 进行各种敏感性分析 （1）单因素图(Plot) （2）蛛网图(Spider) （3）旋风图 《运筹学》实验指导手册 44 (Tornado) 《运筹学》实验指导手册 45 实验十四：用 EXCEL 中的相关分析做回归预测 一、实验目的 1、了解回归预测的基本原理； 2、掌握用 EXCEL 插入图表画散点图的方法； 3、掌握在 EXCEL 图表中添加趋势线和显示回归方程的基本方法。 4、掌握应用 EXCEL 中的相关分析做多元回归分析。 二、实验内容 （1）计算机俱乐部仓库通过与顾客电话下单确定价格的方式销售各种计算 机产品.其产品包括台式计算机、笔记本电脑、外围设备、附属硬件、备用品、 软件及电脑相关的家具。 CCW 呼叫中心从不关闭。它总是被接收处理顾客订单的代理人挤满。 呼叫中心为接入电话提供了大量的电话线路。如果在电话到来时代理人正 忙，电话会进入等待队列。如果所有的线路都在使用，电话会响起忙音。 尽管一些遇到忙音或等待时间过长而挂断电话的用户会再次打来电话，但 是许多客户并不会这样做。因此，拥有足够的值班代理人来使这种情况出现的次 数最少是很重要的。另一方面，由于代理人的劳动成本很高，CCW 试图避免过 多的代理人值班工作，造成他们有大量的空闲时间。于是，获得代理人需求的预 测成了公司的当务之急。 问题：根据前 3 年的呼叫量来预测销售量。 （2）若某个公司的企业销售及有关变量数据如下表所示： 《运筹学》实验指导手册 46 需要建立销售额与广告支出、扩张支出、竞争对手的销售额之间的加归方程， 并对销售额进行预测。 三、实验步骤 1、选择画散点图的数据区域； 2、在插入菜单中选择插入“图表”3、在图表对话框中选择散点图类型，并在 图表栏中选添加趋势线，显示回归方程。 4、加 载 EXCEL 中的数据分析宏模块，在工具栏中选择数据分析，选择回归分析。 《运筹学》实验指导手册 47 得到回归方程：