Transcript Spreadsheet Modeling & Decision Analysis:

Chapter 8
Nonlinear Programming &
Evolutionary Optimization
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-1
Introduction to
Nonlinear Programming (NLP)
An NLP problem has a nonlinear objective
function and/or one or more nonlinear
constraints.
 NLP problems are formulated and
implemented in virtually the same way as
linear problems.
 The mathematics involved in solving NLPs is
quite different than for LPs.
 Solver tends to mask this difference but it is
important to understand the difficulties that
may be encountered when solving NLPs.

Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-2
Possible Optimal Solutions to NLPs
(not occurring at corner points)
objective function
level curve
objective function
level curve
optimal solution
optimal solution
Feasible
Region
Feasible
Region
linear objective,
nonlinear constraints
nonlinear objective,
linear constraints
objective function
level curve
objective function
level curves
optimal solution
Feasible
Region
nonlinear objective,
nonlinear constraints
optimal solution
Feasible
Region
nonlinear objective,
linear constraints
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-3
Local vs. Global Optimal Solutions
X2
Local optimal solution
C
E
Feasible Region
B
F
Local and global
optimal solution
G
A
D
X1
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-4
Comments About NLP Algorithms
 It
is not always best to move in the
direction producing the fastest rate of
improvement in the objective.
 NLP algorithms can terminate a local
optimal solutions.
 The starting point influences the local
optimal solution obtained.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-5
Comments About Starting Points
 The
null starting point should be avoided.
 When possible, it is best to use starting
values of approximately the same
magnitude as the expected optimal values.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-6
Location Problems



Many decision problems involve determining optimal
locations for facilities or service centers. For example,
– Manufacturing plants
– Warehouse
– Fire stations
– Ambulance centers
These problems usually involve distance measures in the
objective and/or constraints.
The straight line (Euclidean) distance between two
points (X1, Y1) and (X2, Y2) is:
Distance 
X
1
 X2   Y1  Y2 
2
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
2
8-7
A Location Problem:
Rappaport Communications

Rappaport Communications provides cellular
phone service in several mid-western states.

The want to expand to provide inter-city
service between four cities in northern Ohio.

A new communications tower must be built to
handle these inter-city calls.

The tower will have a 40 mile transmission
radius.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-8
Graph of the Tower Location Problem
Y
50
Cleveland
x=5, y=45
40
30
Youngstown
Akron
x=12, y=21
20
x=52, y=21
10
Canton
x=17, y=5
0
0
10
20
30
40
50
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
60
X
8-9
Defining the Decision Variables
X1 = location of the new tower with
respect to the X-axis
Y1 = location of the new tower with
respect to the Y-axis
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-10
Defining the Objective Function
 Minimize
the total distance from the new
tower to the existing towers

MIN:


 
2
5-X
1
17 - X
 
2
1
 45  Y
1
 5 Y
1
 
2

 
2

12 - X
 
2
1
 
2
52 - X
1
 21  Y
1
 21  Y

1
2

Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
2
8-11
Defining the Constraints
 Cleveland

 Akron
 
2
5-X

1
1

1

 21  Y
 
2
17 - X
1
 
2
12 - X
 Canton
 45  Y
1
 5 Y
1
2


2
2
 40
 40
 40
 Youngstown

 
2
52 - X
1
 21  Y
1

2
 40
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-12
Implementing the Model
See file Fig8-10.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-13
Analyzing the Solution
The optimal location of the “new tower” is in
virtually the same location as the existing Akron
tower.
 Maybe they should just upgrade the Akron
tower.
 The maximum distance is 39.8 miles to
Youngstown.
 This is pressing the 40 mile transmission
radius.
 Where should we locate the new tower if we
want the maximum distance to the existing
towers to be minimized?

Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-14
Implementing the Model
See file Fig8-13.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-15
Comments on Location Problems
 The
optimal solution to a location problem
may not work:
– The land may not be for sale.
– The land may not be zoned properly.
– The “land” may be a lake.
 In
such cases, the optimal solution is a good
starting point in the search for suitable
property.
 Constraints may be added to location
problems to eliminate infeasible areas from
consideration.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-16
Optimizing Existing Financial Models

It is not necessary to always write out the
algebraic formulation of an optimization
problem, although doing so ensures a
thorough understanding of the problem.

Solver can be used to optimize a host of preexisting spreadsheet models which are
inherently nonlinear.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-17
A Life Insurance Funding Problem



Thom Pearman owns a whole life policy with
surrender value of $6,000 and death benefit of
$40,000.
He’d like to cash in his whole life policy and use
interest on the surrender value to pay premiums on a
a term life policy with a death benefit of $350,000.
The premiums on the new policy for the next 10
years are:
Year
1
2
3
4
5
6
7
8
9
10
Premium $423 $457 $489 $516 $530 $558 $595 $618 $660 $716


Thom’s marginal tax rate is 28%.
What rate of return will be required on his $6,000
investment?
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-18
Implementing the Model
See file Fig8-22.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-19
The Portfolio Optimization Problem

A financial planner wants to create the least risky
portfolio with at least a 12% expected return using
the following stocks.
Annual Return
Year
IBC
NMC
NBS
1 11.2%
8.0% 10.9%
2 10.8%
9.2% 22.0%
3 11.6%
6.6% 37.9%
4
-1.6% 18.5% -11.8%
5
-4.1%
7.4% 12.9%
6
8.6% 13.0% -7.5%
7
6.8% 22.0% 9.3%
8 11.9% 14.0% 48.7%
9 12.0% 20.5% -1.9%
10
8.3% 14.0% 19.1%
11
6.0% 19.0% -3.4%
12 10.2%
9.0% 43.0%
Avg 7.64% 13.43% 14.93%
IBC
NMC
NBS
Covariance Matrix
IBC
NMC
NBS
0.00258 -0.00025 0.00440
-0.00025 0.00276 -0.00542
0.00440 -0.00542 0.03677
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-20
Defining the Decision Variables
p1 = proportion of funds invested in IBC
p2 = proportion of funds invested in NMC
p3 = proportion of funds invested in NBS
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-21
Defining the Objective
Minimize the portfolio variance (risk).
n 1
n
MIN:

i =1
2 2
p
i i
2
n

i 1 j i 1
pp
ij i
j
 i2  the variance on investment i
 ij   ji = the covariance between investments i and j
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-22
Defining the Constraints
 Expected
return
0.0764 p1 + 0.1343 p2 + 0.1493 p3 >= 0.12
 Proportions
p1 + p2 + p3 = 1
p1, p2, p3 >= 0
p1, p2, p3 <= 1
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-23
Implementing the Model
See file Fig8-26.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-24
The Efficient Frontier
Portfolio Variance
0.04000
0.03500
0.03000
0.02500
0.02000
Efficient Frontier
0.01500
0.01000
0.00500
0.00000
10.00%
10.50%
11.00%
11.50%
12.00%
12.50%
13.00%
13.50%
14.00%
14.50%
15.00%
Portfolio Return
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
8-25