Excel-最优化模型

第六章最优化模型主要内容：一、最优化问题概述二、线性规划三、非线性规划四、常见规划问题五、多目标规划问题六、最优投资组合模型一、最优化问题概述1.最优化问题定义最优化问题就是在给定条件下寻找最佳方案的问题。即在资源给定时寻找最好的目标，或在目标确定下使用最少的资源。一、最优化问题概述2.最优化问题分类（1）根据有无约束条件无约束条件的最优化问题有约束条件的最优化问题（2）根据决策变量在目标函数与约束条件中出现的形式线性规划问题非线性规划问题（3）根据决策变量是否要求取整数整数规划问题（0-1规划问题）任意规划问题一、最优化问题概述3.最优化问题的数学模型nxxxfyMax,,,:210,,,:211nxxxsSt0,,,212nxxxs0,,,21nmxxxs……一、最优化问题概述4.最优化问题的求解方法公式法用规划求解工具求解用查表法求解一、最优化问题概述4.最优化问题的求解方法案例：某公司生产和销售一种垄断产品，固定成本F=500元，单位变动成本v=10元，销量Q与单价p之间的关系为Q=160-0.79p，那么公司怎样定价才能获得最大的利润。一、最优化问题概述4.最优化问题的求解方法（1）利用公式法计算最优解avFbabvbabvPvaFpbvabpvQFpQCRopt4/2/2max2一、最优化问题概述4.最优化问题的求解方法（2）用规划求解工具计算最优解操作简单，求解最多200个决策变量的规划问题，可以达到很高的精度，对于线性规划问题可以找到全局最优解。当模型中其他参数发生变化时，规划求解工具不能自动计算出新的最优解。一、最优化问题概述4.最优化问题的求解方法（3）采用查表法求解求解2个决策变量的规划问题，可以达到较高的精度，查表法与图表相结合有助于找到全局最优解，当模型中其他参数发生变化时，可以直接把新的最优解计算出来。一、最优化问题概述4.最优化问题的求解方法（3）采用查表法求解12345678910111213141516171819202122ABCDEFG2226固定成本5000-2100单位变动成本1010-500单价截距(a)16020942单价斜率(b)-0.79302226单价30403352销售数量136.3504320总成本1863605130销售收益4089705782利润2226806276906612最优单价1101006790利润极大值681011068101206672初始最优单价1001306376步长10140592215053101604540170361218025261901282200-120垄断商品利润随单价的变化图形2226.006821.02-10000100020003000400050006000700080001030507090110130150170190单价利润单价=30元时，利润=2226元单位变动成本=10元时，最优单价=106.27元二、线性规划线性规划的一般形式bxaxaxayMaxnn2211:0:11212111bxaxaxaStnn022222121bxaxaxann02211nnmnmmbxaxaxa……二、线性规划案例：某公司生产和销售两种产品，两种产品各生产一个单位需要工时3小时和7小时，用电量4千瓦和5千瓦，需要原材料9公斤和4公斤。公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的原材料为420公斤。两种产品的单位利润分别为200元和210元。该公司怎样安排两种产品的生产量，所获得的利润最大。二、线性规划（1）数学模型21210200:xxyMax30073:21xxSt2505421xx4204921xx0,21xx二、线性规划（2）EXCEL模型产品1产品2需要量可提供量工时3710.00300.00用电量459.00250.00原材料9413.00420.00单位利润200.00210.00产量1.001.00总利润410.00二、线性规划（3）用规划求解工具求解二、线性规划（4）制作利润随产量变化的三维曲面图和俯视图0510152025303540455001020304050工时用电量原材料利润01020304050025500200040006000800010000120001400016000180002000022000三、非线性规划非线性规划模型的一般形式nxxxfyMax,,,:210,,,:211nxxxsSt0,,,212nxxxs0,,,21nmxxxs……三、非线性规划案例：某公司生产和销售两种产品，两种产品各生产1单位需要工时3小时和7小时，用电量4千瓦和5千瓦，需要原材料9公斤和4公斤。公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的原料为420公斤。两种产品的单价与销量之间存在负线性关系，分别为p1=3000-50q1,p2=3250-80q2。工时、用电量和原材料的单位成本分别是10、12和50，总固定成本是10000。该公司怎样安排生产，所获利润最大。三、非线性规划（1）数学模型0,420492505430073:1000080262250247210000330528803250503000:21212121222211212211xxxxxxxxstxxxxxxxxxxyMax三、非线性规划（2）EXCEL模型234567891011121314BCDEFG产品1产品2需要量可提供量单位成本工时3710.0030010用电量459.0025012原材料9413.0042050产量1.001.00a30003250b-50-80单价2950.003170.00收益2950.003170.00单位变动成本528.00330.00变动成本528.00330.00总固定成本10000.00总利润-4738.00三、非线性规划（3）用规划求解工具求解三、非线性规划（4）绘制总利润的三维曲面图形和俯视图形三、非线性规划案例：在上例的基础上，当原料用量=300公斤时，供应商提供的原料价格从170元降为150元。该公司怎样安排两种产品的生产量，所获得的利润最大。（主要考虑多极值情况）。三、非线性规划（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）变化后总利润的三维曲面图形和俯视图形四、常见规划问题1.运输问题某公司有3个工厂，生产的产品运到5个仓库，3个工厂的生产能力为310，260和280，每个仓库的需求量为180、80、200、160和220。从工厂运到各仓库的运费如下表。该公司怎样安排，所花费的总运费最小。运费仓库1仓库2仓库3仓库4仓库5工厂A108654工厂B65436工厂C34559四、常见规划问题（1）建立Excel模型2345678910111213BCDEFGHI运费仓库1仓库2仓库3仓库4仓库5工厂A108654工厂B65436工厂C34559运量仓库1仓库2仓库3仓库4仓库5总计提供量工厂A111115310工厂B111115260工厂C111115280总计33333需求量18080200160220总运费83四、常见规划问题（2）规划求解四、常见规划问题2.选址问题某移动通讯公司准备在某一城市建立发射塔，该城有4个地区，现有4个建塔位置，每个位置对各地区的覆盖情况和费用见下表：该公司怎样选择建塔位置，既能覆盖所有地区，又使总费用最小。覆盖位置1位置2位置3位置4地区A111地区B1地区C1地区D11费用350400300380四、常见规划问题（1）建立Excel模型23456789BCDEFG覆盖位置1位置2位置3位置4覆盖次数地区A1113地区B13地区C13地区D113费用350400300380选择1111总费用1430四、常见规划问题（2）规划求解四、常见规划问题3.资金管理问题现有10000元准备存入银行，可以选择一年期、二年期和三年期存款。三种存款的年利率分别为2.5%、2.7%和2.9%。第3年初和第5年初需要使用现金1000元和2000元，第4年初有5000元的现金收入可以存入银行。问如何分配每年的各种存款额才能使第6年末的现金余额最大？四、常见规划问题（1）建立Excel模型23456789101112131415BCDEFGH年利率到期总利率一年期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.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）规划求解四、常见规划问题4.生产管理问题已知某公司生产的产品在不同月份的需求量、单位生产成本与生产能力不同（见下表），每月的储存成本等于单位储存成本与月平均库存量（月初库存量与月末库存量的平均值）的乘积，而每月的单位储存成本等于当月单位生产成本的1.5%。公司要求每月的生产量既不超过当月生产能力又不低于当月生产能力的一半，另外，为防备急需，管理人员还要求每月月末库存量不少于1500件（安全库存量），仓库容量为6000件，当前库存量为2750件。试确定一种生产安排使得既能满足每个月的需求量同时又使作为生产成本与储存成本之和的总成本达到极小。1月2月3月4月5月6月需求量100045006000550035004000生产量上限400035004000450040003500单位生产成本240250265285280260四、常见规划问题（1）建立Excel模型234567891011121314151617BCDEFGH仓库容量6000安全库存量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.9754.2754.23.9总成本-208366.08四、常见规划问题（2）规划求解四、常见规划问题5.方程组求解问题现有1个3元1次方程组，求该方程组的解。664012880221830100162520zyxzyxzyx四、常见规划问题（1）建立Excel模型234567BCDEFGxyz等式左边等式右边方程120251661.00100方程230182270.0080方程38124060.0066自变量1.00001.00001.0000左右差值55.0000四、常见规划问题