Excel 最优化模型

三、非线性规划
wenku.baidu.com
（2）EXCEL模型
2 3 4 5 6 7 8 9 10 11 12 13 14 B
工时 用电量 原材料 产量 a b 单价 收益 单位变动成本 变动成本 总固定成本 总利润
C
产品1 3 4 9 1.00 3000 -50 2950.00 2950.00 528.00 528.00 10000.00 -4738.00
垄断商品利润随单价的变化图形 单价=30元时，利润=2226元 利润 8000 7000 6000 5000 4000 3000 2000 1000 0 -1000 10 30 50 70 90 110 130 150 170 2226.00 单价 190 6821.02 单位变动成本=10元时，最优单价=106.27元
某移动通讯公司准备在某一城市建立发射塔，该城 有4个地区，现有4个建塔位置，每个位置对各地区的 覆盖情况和费用见下表：该公司怎样选择建塔位置， 既能覆盖所有地区，又使总费用最小。
覆盖 地区A 地区B 地区C 地区D 费用 位置1 1 1 1 1 350 1 400 300 380 位置2 位置3 1 位置4 1
2.最优化问题分类
（1）根据有无约束条件 无约束条件的最优化问题 有约束条件的最优化问题 （2）根据决策变量在目标函数与约束条件中出现的形式 线性规划问题 非线性规划问题
（3）根据决策变量是否要求取整数
整数规划问题（0-1规划问题 ） 任意规划问题
一、最优化问题概述
3.最优化问题的数学模型
Max : y f x1 , x2 ,, xn St : s1 x1 , x2 ,, x n 0 s2 x1 , x2 ,, x n 0
50 25
10
20
30
40
50
0
三、非线性规划 非线性规划模型的一般形式
Max : y f x1 , x2 ,, xn St : s1 x1 , x2 ,, x n 0 s2 x1 , x2 ,, x n 0
sm x1 , x2 ,, x n 0
三、非线性规划
案例：在上例的基础上，当原料用量>=300
公斤时，供应商提供的原料价格从170元 降为150元。该公司怎样安排两种产品的 生产量，所获得的利润最大。（主要考虑 多极值情况）。
三、非线性规划
（5）初值与最优解
初值 产品1 1.00 30.00 25.00 30.00 产品2 1.00 15.00 25.00 10.00 终值 产品1 13.92 26.82 23.34 28.45 产品2 15.25 14.67 22.50 11.00 总利润 18293.3 15951.8 15660.9 12297.4
运量 仓库1 仓库2 仓库3 仓库4 仓库5 总计 提供量 工厂A 1 1 1 1 1 5 310 工厂B 1 1 1 1 1 5 260 工厂C 1 1 1 1 1 5 280 总计 3 3 3 3 3 需求量 180 80 200 160 220 总运费 83
四、常见规划问题 （2）规划求解
四、常见规划问题 2.选址问题
二、线性规划
（1）数学模型
Max : y 200x1 210x2 St : 3x1 7 x2 300 4 x1 5x2 250
9 x1 4 x2 420
x1 , x2 0
二、线性规划
（2）EXCEL模型
产品1 工时 用电量 原材料 单位利润 产量 总利润 3 4 9 200.00 1.00 410.00 产品2 7 5 4 210.00 1.00 需要量 可提供量 10.00 300.00 9.00 250.00 13.00 420.00
二、线性规划
（3）用规划求解工具求解
二、线性规划
（4）制作利润随产量变化的三维曲面图和俯视图
50 45 40 35 30 25 20 15 10 5 0 0 10 20 30 40 50
工时 用电量 原材料 利润
0
22000 20000 18000 16000 14000 12000 10000 8000 6000 4000 2000 0
E
F
G
H
期初现金 到期本金 到期利息 现金需要额 一年期存款 二年期存款 三年期存款 期末现金
1000.00 1000.00 1000.00 7000.00
第3年 5025.00 2000.00 80.03 1000.00 1000.00 1000.00 1000.00 3105.03
第4年 3105.03 3000.00 170.59 -5000.00 1000.00 1000.00 9275.62
三、非线性规划
（1）数学模型
Max : y 3000 50x1 x1 3250 80x 2 x 2 528x1 330x2 10000
2 2472x1 50x12 2622x2 80x2 10000
st :
3x1 7 x2 300 4 x1 5 x2 250 9 x1 4 x2 420 x1 , x2 0
4.最优化问题的求解方法
（2）用规划求解工具计算最优解
操作简单，求解最多200个决 策变量的规划问题，可以达到 很高的精度，对于线性规划问 题可以找到全局最优解。当模 型中其他参数发生变化时，规 划求解工具不能自动计算出新 的最优解。
一、最优化问题概述
4.最优化问题的求解方法
（3）采用查表法求解
求解2个决策变量的规划问题，可 以达到较高的精度，查表法与图表相 结合有助于找到全局最优解，当模型 中其他参数发生变化时，可以直接把 新的最优解计算出来。
二、线性规划
线性规划的一般形式
Max : y a1 x1 a2 x2 an xn b
St : a11 x1 a12 x2 a1n xn b1 0 a21 x1 a22 x2 a2n xn b2 0
……
am1 x1 am2 x2 amn xn bn 0
sm x1 , x2 ,, x n 0
……
一、最优化问题概述 4.最优化问题的求解方法 公式法 用规划求解工具求解 用查表法求解
一、最优化问题概述
4.最优化问题的求解方法
案例：某公司生产和销售一种垄断产
品，固定成本F=500元，单位变动成 本v=10元，销量Q与单价p之间的关系 为Q=160-0.79p，那么公司怎样定价 才能获得最大的利润。
四、常见规划问题 （1）建立Excel模型
B 2 3 4 5 6 7 8 9
覆盖 地区A 地区B 地区C 地区D 费用 选择 总费用
C
位置1 1
D
位置2 1
E
位置3 1
F
位置4
G
覆盖次数 1 3 3 1 3 3 380 1
1 350 1 1430
1 400 1
300 1
四、常见规划问题 （2）规划求解
一、最优化问题概述
4.最优化问题的求解方法
（1）利用公式法计算最优解
R C pQ F vQ
bp2 a bv p F va P opt bv a / 2b
max bv a / 4b F av
2
一、最优化问题概述
……
三、非线性规划 案例：某公司生产和销售两种产品，两种产
品各生产1单位需要工时3小时和7小时，用 电量4千瓦和5千瓦，需要原材料9公斤和4公 斤。公司可提供的工时为300小时，可提供 的用电量为250千瓦，可提供的原料为420公 斤。两种产品的单价与销量之间存在负线性 关系，分别为p1=3000-50q1,p2=3250-80q2。 工时、用电量和原材料的单位成本分别是10、 12和50，总固定成本是10000。该公司怎样 安排生产，所获利润最大。
第5年 9275.62 3000.00 170.59 2000.00 1000.00
第6年 9446.21 3000.00 170.59
9446.21
12616.80
四、常见规划问题 （2）规划求解
四、常见规划问题 4.生产管理问题
已知某公司生产的产品在不同月份的需求量、单位生产成本与 生产能力不同（见下表），每月的储存成本等于单位储存成本与月 平均库存量（月初库存量与月末库存量的平均值）的乘积，而每月 的单位储存成本等于当月单位生产成本的1.5%。公司要求每月的生 产量既不超过当月生产能力又不低于当月生产能力的一半，另外， 为防备急需，管理人员还要求每月月末库存量不少于1500件（安全 库存量），仓库容量为6000件，当前库存量为2750件。试确定一种 生产安排使得既能满足每个月的需求量同时又使作为生产成本与储 存成本之和的总成本达到极小。
四、常见规划问题 3.资金管理问题
现有10000元准备存入银行，可以选择一年 期、二年期和三年期存款。三种存款的年利率 分别为2.5%、2.7%和2.9%。第3年初和第5年初 需要使用现金1000元和2000元，第4年初有5000 元的现金收入可以存入银行。问如何分配每年 的各种存款额才能使第6年末的现金余额最大？
四、常见规划问题 （1）建立Excel模型
B 2 3 4 5 6 7 8 9 10 11 12 13 14 15
一年期 二年期 三年期
C
年利率 2.5% 2.7% 2.9% 第1年 10000.00
D
到期总利率 2.5% 5.5% 9.1% 第2年 7000.00 1000.00 25.00 1000.00 1000.00 1000.00 5025.00
三、非线性规划
（6）变化后总利润的三维曲面图形和俯视图形
四、常见规划问题 1.运输问题
某公司有3个工厂，生产的产品运到5个仓库，3个 工厂的生产能力为310，260和280，每个仓库的需求量 为180、80、200、160和220。从工厂运到各仓库的运 费如下表。该公司怎样安排，所花费的总运费最小。
第六章 最优化模型
主要内容：
一、最优化问题概述
二、线性规划
三、非线性规划
四、常见规划问题
五、多目标规划问题
六、最优投资组合模型
一、最优化问题概述 1.最优化问题定义 最优化问题就是在给定条件下寻找 最佳方案的问题。 即在资源给定时寻找最好的目标， 或在目标确定下使用最少的资源。
一、最优化问题概述
一、最优化问题概述
4.最优化问题的求解方法
（3）采用查表法求解
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 B 固定成本 单位变动成本 单价截距 (a) 单价斜率 (b) 单价 销售数量 总成本 销售收益 利润 最优单价 利润极大值 初始最优单价 步长 C 500 10 160 -0.79 30 136.3 1863 4089 2226 110 6810 100 10 D E F 0 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 200 G 2226 -2100 -500 942 2226 3352 4320 5130 5782 6276 6612 6790 6810 6672 6376 5922 5310 4540 3612 2526 1282 -120
D
产品2
E
F
G
需要量 可提供量 单位成本 7 10.00 300 10 5 9.00 250 12 4 13.00 420 50 1.00 3250 -80 3170.00 3170.00 330.00 330.00
三、非线性规划
（3）用规划求解工具求解
三、非线性规划
（4）绘制总利润的三维曲面图形和俯视图形
二、线性规划
案例：某公司生产和销售两种产品，两种
产品各生产一个单位需要工时3小时和7小 时，用电量4千瓦和5千瓦，需要原材料9 公斤和4公斤。公司可提供的工时为300小 时，可提供的用电量为250千瓦，可提供 的原材料为420公斤。两种产品的单位利 润分别为200元和210元。该公司怎样安排 两种产品的生产量，所获得的利润最大。
运费 工厂A 工厂B 工厂C 仓库1 仓库2 仓库3 仓库4 仓库5 10 8 6 5 4 6 5 4 3 6 3 4 5 5 9
四、常见规划问题 （1）建立Excel模型
B 2 3 4 5 6 7 8 9 10 11 12 13
运费 工厂A 工厂B 工厂C
C
D
E
F
G
H
I
仓库1 仓库2 仓库3 仓库4 仓库5 10 8 6 5 4 6 5 4 3 6 3 4 5 5 9