Excel最优化模型解析课件.pptx

1、第六章第六章 最优化模型最优化模型主要内容：主要内容：一、最优化问题概述一、最优化问题概述二、线性规划二、线性规划三、非线性规划三、非线性规划四、常见规划问题四、常见规划问题五、多目标规划问题五、多目标规划问题六、最优投资组合模型六、最优投资组合模型一、最优化问题概述一、最优化问题概述1.1.最优化问题定义最优化问题定义最优化问题就是在给定条件下寻找最优化问题就是在给定条件下寻找最佳方案的问题。最佳方案的问题。即在资源给定时寻找最好的目标，即在资源给定时寻找最好的目标，或在目标确定下使用最少的资源。或在目标确定下使用最少的资源。一、最优化问题概述一、最优化问题概述2.2.最优化问题分类最优化问

2、题分类 （1 1）根据有无约束条件）根据有无约束条件 无约束条件的最优化问题无约束条件的最优化问题 有约束条件的最优化问题有约束条件的最优化问题 （2 2）根据决策变量在目标函数与约束条件中出现的形式）根据决策变量在目标函数与约束条件中出现的形式 线性规划问题线性规划问题 非线性规划问题非线性规划问题（3 3）根据决策变量是否要求取整数）根据决策变量是否要求取整数 整数规划问题（整数规划问题（0-10-1规划问题规划问题 ） 任意规划问题任意规划问题一、最优化问题概述一、最优化问题概述3.3.最优化问题的数学模型最优化问题的数学模型nxxxfyMax,:210,:211nxxxsSt0,212

3、nxxxs0,21nmxxxs一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法最优化问题的求解方法 公式法公式法 用规划求解工具求解用规划求解工具求解 用查表法求解用查表法求解 一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法最优化问题的求解方法 案例：案例：某公司生产和销售一种垄断产某公司生产和销售一种垄断产品，固定成本品，固定成本F=500F=500元，单位变动成元，单位变动成本本v=10v=10元，销量元，销量Q Q与单价与单价p p之间的关系之间的关系为为Q=160-0.79pQ=160-0.79p，那么公司怎样定价，那么公司怎样定价才能获得最大的利润。

4、才能获得最大的利润。一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法最优化问题的求解方法 （1 1）利用公式法计算最优解）利用公式法计算最优解avFbabvbabvPvaFpbvabpvQFpQCRopt4/2/2max2一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法最优化问题的求解方法 （2 2）用规划求解工具计算最优解）用规划求解工具计算最优解操作简单，求解最多操作简单，求解最多200个决策个决策变量的规划问题，可以达到很变量的规划问题，可以达到很高的精度，对于线性规划问题高的精度，对于线性规划问题可以找到全局最优解。当模型可以找到全局最优解。当模型中其他

5、参数发生变化时，规划中其他参数发生变化时，规划求解工具不能自动计算出新的求解工具不能自动计算出新的最优解。最优解。一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法最优化问题的求解方法 （3 3）采用查表法求解）采用查表法求解 求解求解2 2个决策变量的规划问题，可个决策变量的规划问题，可以达到较高的精度，查表法与图表相以达到较高的精度，查表法与图表相结合有助于找到全局最优解，当模型结合有助于找到全局最优解，当模型中其他参数发生变化时，可以直接把中其他参数发生变化时，可以直接把新的最优解计算出来。新的最优解计算出来。一、最优化问题概述一、最优化问题概述4.4.最优化问题的求解方法

6、最优化问题的求解方法 （3 3）采用查表法求解）采用查表法求解 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 A B C D E F G 2226固定成本5000-2100单位变动成本1010-500单价截距 (a)16020942单价斜率 (b)-0.79302226单价30403352销售数量136.3504320总成本1863605130销售收益4089705782利润2226806276906612最优单价1101006790利润极大值681011068101206672初始最优单价1001306376步长101405

7、92215053101604540170361218025261901282200-120垄断商品利润随单价的变化图形2226.006821.02-10000100020003000400050006000700080001030507090110130150170190单价利润单价=30元时，利润=2226元单位变动成本=10元时，最优单价=106.27元二、线性规划二、线性规划线性规划的一般形式线性规划的一般形式bxaxaxayMaxnn2211:0:11212111bxaxaxaStnn022222121bxaxaxann02211nnmnmmbxaxaxa二、线性规划二、线性规划案例：

8、案例：某公司生产和销售两种产品，两种某公司生产和销售两种产品，两种产品各生产一个单位需要工时产品各生产一个单位需要工时3 3小时和小时和7 7小小时，用电量时，用电量4 4千瓦和千瓦和5 5千瓦，需要原材料千瓦，需要原材料9 9公斤和公斤和4 4公斤。公司可提供的工时为公斤。公司可提供的工时为300300小小时，可提供的用电量为时，可提供的用电量为250250千瓦，可提供千瓦，可提供的原材料为的原材料为420420公斤。两种产品的单位利公斤。两种产品的单位利润分别为润分别为200200元和元和210210元。该公司怎样安排元。该公司怎样安排两种产品的生产量，所获得的利润最大。两种产品的生产量，

9、所获得的利润最大。 二、线性规划二、线性规划（1 1）数学模型）数学模型21210200:xxyMax30073:21 xxSt2505421 xx4204921 xx0,21xx二、线性规划二、线性规划（2 2）EXCELEXCEL模型模型产品1产品2需要量可提供量工时3710.00300.00用电量459.00250.00原材料9413.00420.00单位利润200.00210.00产量1.001.00总利润410.00二、线性规划二、线性规划（3 3）用规划求解工具求解）用规划求解工具求解二、线性规划二、线性规划（4 4）制作利润随产量变化的三维曲面图和俯视图）制作利润随产量变化的三维

10、曲面图和俯视图0510152025303540455001020304050工时用电量原材料利润01020304050025500200040006000800010000120001400016000180002000022000三、非线性规划三、非线性规划非线性规划模型的一般形式非线性规划模型的一般形式 nxxxfyMax,:210,:211nxxxsSt0,212nxxxs0,21nmxxxs三、非线性规划三、非线性规划案例：案例：某公司生产和销售两种产品，两种产某公司生产和销售两种产品，两种产品各生产品各生产1 1单位需要工时单位需要工时3 3小时和小时和7 7小时，用小时，用电量电量

11、4 4千瓦和千瓦和5 5千瓦，需要原材料千瓦，需要原材料9 9公斤和公斤和4 4公公斤。公司可提供的工时为斤。公司可提供的工时为300300小时，可提供小时，可提供的用电量为的用电量为250250千瓦，可提供的原料为千瓦，可提供的原料为420420公公斤。两种产品的单价与销量之间存在负线性斤。两种产品的单价与销量之间存在负线性关系，分别为关系，分别为p p1 1=3000-50q=3000-50q1 1,p,p2 2=3250-80q=3250-80q2 2。工时、用电量和原材料的单位成本分别是工时、用电量和原材料的单位成本分别是1010、1212和和5050，总固定成本是，总固定成本是100

12、0010000。该公司怎样。该公司怎样安排生产，所获利润最大。安排生产，所获利润最大。三、非线性规划三、非线性规划（1 1）数学模型）数学模型0,420492505430073:1000080262250247210000330528803250503000:21212121222211212211xxxxxxxxstxxxxxxxxxxyMax三、非线性规划三、非线性规划（2 2）EXCELEXCEL模型模型2 3 4 5 6 7 8 9 10 11 12 13 14 B C D E F G 产品1产品2需要量可提供量 单位成本工时3710.0030010用电量459.0025012原材料9

13、413.0042050产量1.001.00a30003250b-50-80单价2950.003170.00收益2950.003170.00单位变动成本528.00330.00变动成本528.00330.00总固定成本10000.00总利润-4738.00三、非线性规划三、非线性规划（3 3）用规划求解工具求解）用规划求解工具求解三、非线性规划三、非线性规划（4 4）绘制总利润的三维曲面图形和俯视图形）绘制总利润的三维曲面图形和俯视图形 三、非线性规划三、非线性规划案例：案例：在上例的基础上，当原料用量在上例的基础上，当原料用量=300=300公斤时，供应商提供的原料价格从公斤时，供应商提供的原

14、料价格从170170元元降为降为150150元。该公司怎样安排两种产品的元。该公司怎样安排两种产品的生产量，所获得的利润最大。（主要考虑生产量，所获得的利润最大。（主要考虑多极值情况）。多极值情况）。三、非线性规划三、非线性规划（5 5）初值与最优解）初值与最优解 总利润产品1产品2产品1产品21.001.0013.9215.2518293.330.0015.0026.8214.6715951.825.0025.0023.3422.5015660.930.0010.0028.4511.0012297.4初值终值三、非线性规划三、非线性规划（6 6）变化后总利润的三维曲面图形和俯视图形）变化后总

15、利润的三维曲面图形和俯视图形 四、常见规划问题四、常见规划问题1.1.运输问题运输问题 某公司有某公司有3 3个工厂，生产的产品运到个工厂，生产的产品运到5 5个仓库，个仓库，3 3个个工厂的生产能力为工厂的生产能力为310310，260260和和280280，每个仓库的需求量，每个仓库的需求量为为180180、8080、200200、160160和和220220。从工厂运到各仓库的运。从工厂运到各仓库的运费如下表。该公司怎样安排，所花费的总运费最小。费如下表。该公司怎样安排，所花费的总运费最小。运费仓库1仓库2仓库3仓库4仓库5工厂A108654工厂B65436工厂C34559四、常见规划问

16、题四、常见规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 8 9 10 11 12 13 B C D E F G H I 运费仓库1仓库2仓库3仓库4仓库5工厂A108654工厂B65436工厂C34559运量仓库1仓库2仓库3仓库4仓库5总计提供量工厂A111115310工厂B111115260工厂C111115280总计33333需求量18080200160220总运费83四、常见规划问题四、常见规划问题（2 2）规划求解）规划求解四、常见规划问题四、常见规划问题2.2.选址问题选址问题 某移动通讯公司准备在某一城市建立发射塔，该城某移动通讯公司准备在某一

17、城市建立发射塔，该城有有4 4个地区，现有个地区，现有4 4个建塔位置，每个位置对各地区的个建塔位置，每个位置对各地区的覆盖情况和费用见下表：该公司怎样选择建塔位置，覆盖情况和费用见下表：该公司怎样选择建塔位置，既能覆盖所有地区，又使总费用最小。既能覆盖所有地区，又使总费用最小。 四、常见规划问题四、常见规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 8 9 B C D E F G 覆盖位置1位置2位置3位置4覆盖次数地区A1113地区B13地区C13地区D113费用350400300380选择1111总费用1430四、常见规划问题四、常见规划问题（2 2）规

18、划求解）规划求解四、常见规划问题四、常见规划问题3.3.资金管理问题资金管理问题 现有现有1000010000元准备存入银行，可以选择一年元准备存入银行，可以选择一年期、二年期和三年期存款。三种存款的年利率期、二年期和三年期存款。三种存款的年利率分别为分别为2.5%2.5%、2.7%2.7%和和2.9%2.9%。第。第3 3年初和第年初和第5 5年初年初需要使用现金需要使用现金10001000元和元和20002000元，第元，第4 4年初有年初有50005000元的现金收入可以存入银行。问如何分配每年元的现金收入可以存入银行。问如何分配每年的各种存款额才能使第的各种存款额才能使第6 6年末的现

19、金余额最大？年末的现金余额最大？ 四、常见规划问题四、常见规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F G H 年利率到期总利率一年期2.5%2.5%二年期2.7%5.5%三年期2.9%9.1%第1年第2年第3年第4年第5年第6年期初现金10000.007000.005025.003105.039275.629446.21到期本金1000.002000.003000.003000.003000.00到期利息25.0080.03170.59170.59170.59现金需要额1000.00-5000

20、.002000.00一年期存款1000.001000.001000.001000.001000.00二年期存款1000.001000.001000.001000.00三年期存款1000.001000.001000.00期末现金7000.005025.003105.039275.629446.2112616.80四、常见规划问题四、常见规划问题（2 2）规划求解）规划求解四、常见规划问题四、常见规划问题4.4.生产管理问题生产管理问题 已知某公司生产的产品在不同月份的需求量、单位生产成本与已知某公司生产的产品在不同月份的需求量、单位生产成本与生产能力不同（见下表），每月的储存成本等于单位储存成本

21、与月平生产能力不同（见下表），每月的储存成本等于单位储存成本与月平均库存量（月初库存量与月末库存量的平均值）的乘积，而每月的单均库存量（月初库存量与月末库存量的平均值）的乘积，而每月的单位储存成本等于当月单位生产成本的位储存成本等于当月单位生产成本的1.5%。公司要求每月的生产量。公司要求每月的生产量既不超过当月生产能力又不低于当月生产能力的一半，另外，为防备既不超过当月生产能力又不低于当月生产能力的一半，另外，为防备急需，管理人员还要求每月月末库存量不少于急需，管理人员还要求每月月末库存量不少于1500件（安全库存量件（安全库存量），仓库容量为），仓库容量为6000件，当前库存量为件，当前库

22、存量为2750件。试确定一种生产安件。试确定一种生产安排使得既能满足每个月的需求量同时又使作为生产成本与储存成本之排使得既能满足每个月的需求量同时又使作为生产成本与储存成本之和的总成本达到极小。和的总成本达到极小。 1月2月3月4月5月6月需求量100045006000550035004000生产量上限400035004000450040003500单位生产成本240250265285280260四、常见规划问题四、常见规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B C D E F G H 仓库容量6

23、000安全库存量1500储存成本系数0.015当前库存量27501月2月3月4月5月6月需求量100045006000550035004000月初库存量27501751-2748-8747-14246-17745生产量111111生产量上限400035004000450040003500生产量下限200017502000225020001750月末库存量1751-2748-8747-14246-17745-21744月平均库存量2250.5-498.5-5747.5-11496.5-15995.5-19744.5单位生产成本240250265285280260单位储存成本3.63.753.97

24、54.2754.23.9总成本-208366.08四、常见规划问题四、常见规划问题（2 2）规划求解）规划求解四、常见规划问题四、常见规划问题5.5.方程组求解问题方程组求解问题现有现有1 1个个3 3元元1 1次方程组，求该方程组的解。次方程组，求该方程组的解。 664012880221830100162520zyxzyxzyx四、常见规划问题四、常见规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 B C D E F G xyz等式左边 等式右边方程120251661.00100方程230182270.0080方程38124060.0066自变量1.0000

25、1.00001.0000左右差值 55.0000四、常见规划问题四、常见规划问题（2 2）规划求解）规划求解五、多目标规划问题五、多目标规划问题1.1.内涵内涵 具有多个目标的决策就是多目标决策具有多个目标的决策就是多目标决策 2.2.多目标决策主要方法多目标决策主要方法 化多为少法化多为少法 直接求非劣解法直接求非劣解法 目标规划法目标规划法 多属性效用法多属性效用法 层次分析法层次分析法 重排序法重排序法 多目标群决策和多目标模糊决策多目标群决策和多目标模糊决策五、多目标规划问题五、多目标规划问题案例：案例：某公司生产和销售两种产品，两种产品各某公司生产和销售两种产品，两种产品各生产一个单

26、位需要生产一个单位需要3 3工时和工时和7 7工时，用电量工时，用电量4 4千千瓦和瓦和5 5千瓦，需要原材料千瓦，需要原材料9 9公斤和公斤和4 4公斤。公司公斤。公司可提供的工时为可提供的工时为300300，可提供的用电量为，可提供的用电量为250250千千瓦，可提供的原材料为瓦，可提供的原材料为420420公斤。两种产品的公斤。两种产品的单位利润分别为单位利润分别为1212元和元和1515元。假设两种产品各元。假设两种产品各生产生产1010个单位，试在个单位，试在ExcelExcel中建立产品组合线中建立产品组合线性规划模型，用规划求解工具求解两种产品的性规划模型，用规划求解工具求解两种

27、产品的最优生产量，使总利润最大，总工时最少；把最优生产量，使总利润最大，总工时最少；把规划求解参数保存在单元格中。规划求解参数保存在单元格中。 五、多目标规划问题五、多目标规划问题（1 1）建立）建立ExcelExcel模型模型 2 3 4 5 6 7 8 B C D E F 产品1产品2需要量提供量工时3710300用电量459250原材料9413420单位利润1215产量1.001.00总利润27（2 2）规划求解）规划求解 第一步：求解总利润最大第一步：求解总利润最大五、多目标规划问题五、多目标规划问题（2 2）规划求解）规划求解 第二步：在保持总利润最大的同时，第二步：在保持总利润最大

28、的同时，求解最少的总工时求解最少的总工时五、多目标规划问题五、多目标规划问题六、最优投资组合模型六、最优投资组合模型1.1.投资组合概念投资组合概念投资要考虑：收益和风险投资要考虑：收益和风险 如何进行投资组合实现收益增大，风如何进行投资组合实现收益增大，风险降低？险降低？2.2.求解方法求解方法（1 1）目标规划法）目标规划法 假设有假设有n n个项目可以投资，各项目的平均收益个项目可以投资，各项目的平均收益率分别为率分别为 ，各项目间的协方差为，各项目间的协方差为 。若各项目。若各项目的投资比例为的投资比例为 。则预期的回报率。则预期的回报率 ，方差方差 。则最优投资组合可表示为：。则最优

29、投资组合可表示为： iRijiWn1iiiRWRn1in1jjiij2W六、最优投资组合模型六、最优投资组合模型（1 1）目标规划法）目标规划法n1in1jjiij2WMin:n1iiiRW R:Maxpn1iiiRRW St.n1in1jpjiijWSt2.1n1iiW 1n1iiW 六、最优投资组合模型六、最优投资组合模型（2 2）化多为少法）化多为少法 若风险厌恶度等于若风险厌恶度等于D D，预期收益率为，预期收益率为R R，预期，预期方差为方差为 , ,则综合风险收益率为则综合风险收益率为 22)1 (DDRR2)1 (:DDRRMAX1W .n1iiSt六、最优投资组合模型六、最优投

30、资组合模型案例：案例：现有一笔资金，准现有一笔资金，准备购买备购买IBCIBC、NMCNMC和和NBSNBS三三个公司的股票。各公司在个公司的股票。各公司在过去过去1212年的收益率见下表：年的收益率见下表：问：在保证收益率不低于问：在保证收益率不低于12%12%的前提下，怎样组合的前提下，怎样组合可以使风险最小？在方差可以使风险最小？在方差不大于不大于0.010.01的前提下，怎的前提下，怎样组合可以使收益率最大？样组合可以使收益率最大？若风险厌恶度等于若风险厌恶度等于0.50.5，则最优证券组合是什么？则最优证券组合是什么？此时的收益率和方差各为此时的收益率和方差各为多少？多少？（1 1）

31、建立）建立ExcelExcel模型模型 六、最优投资组合模型六、最优投资组合模型2 3 4 5 6 7 8 9 10 11 12 13 14 15 C D E F G H I J K L 年份IBCNMCNBSIBCNMCNBS 111.20%8.00%10.90%IBC0.00258-0.000250.00440210.80%9.20%22.00%NMC-0.000250.00276-0.00542311.60%6.60%37.90%NBS0.00440-0.005420.036774-1.60%18.50%-11.80%5-4.10%7.40%12.90%IBCNMCNBS 68.60%1

32、3.00%-7.50%平均收益率7.64%13.43%14.93%76.80%22.00%9.30%投资比例0.00%42.51%57.49%811.90%14.00%48.70%912.00%20.50%-1.90%投资比例合计100.00%108.30%14.00%19.10%收益率0.14296116.00%19.00%-3.40%风险0.010001210.20%9.00%43.00%每年收益率收益率协方差（2 2）规划求解）规划求解 收益固定，风险最小收益固定，风险最小六、最优投资组合模型六、最优投资组合模型（2 2）规划求解）规划求解 风险固定，收益最大风险固定，收益最大六、最优投

33、资组合模型六、最优投资组合模型（3 3）当风险厌恶度等于）当风险厌恶度等于0.50.5时，建立时，建立ExcelExcel模型模型 六、最优投资组合模型六、最优投资组合模型年份IBCNMCNBSIBCNMCNBS 111.20%8.00%10.90%IBC0.00258-0.000250.00440210.80%9.20%22.00%NMC-0.000250.00276-0.00542311.60%6.60%37.90%NBS0.00440-0.005420.036774-1.60%18.50%-11.80%5-4.10%7.40%12.90%IBCNMCNBS 68.60%13.00%-7.50%平均收益率7.64%13.43%14.93%76.80%22.00%9.30%投资比例0.00%68.87%31.13%811.90%14.00%48.70%912.00%20.50%-1.90%投资比例合计100.00%108.30%14.00%19.10%收益率0.13900116.00%19.00%-3.40%风险0.002551210.20%9.00%43.00%风险厌恶度0.5收益率-风险0.06823每年收益率收益率协方差（4 4）规划求解）规划求解 风险厌恶度固定，收益最大风险厌恶度固定，收益最大六、最优投资组合模型六、最优投资组合模型