Optimisation Tools
Image from:
http://electronicpackaging.asmedigitalcollection.asme.org/article.aspx?articleid=1410258/
SCOPE
• EXCEL
– GOALSEEK
– SOLVER
• MATLAB
– linprog
– fmincon
EXCEL GOALSEEK
• Found under DATA -> WHATIF ANALYSIS
• Used to find the value of a function that will meet a
stated target, e.g.
– Find the value of x that will set f(x) = 1000
• Useful for unconstrained optimisation with only one
decision variable
Using EXCEL GOALSEEK
• Example (ideal gas equation) – find the temperature
at which the pressure will be 300,000 Pa
• Set up spreadsheet
• Enter equation in the cell which will contain the a
target value
Using EXCEL GOALSEEK
• Click on DATA->WHAT-IF-ANALYSIS->GOALSEEK
Aside: using “names” in EXCEL
• Makes equations clearer
• Use NAME MANAGER – found under FORMULA tab
• A number of other options available (explore …)
GOALSEEK Exercise
• You have just won $1M in the lottery. To maintain a
comfortable lifestyle, you estimate that you would require a
pre-tax income of $5000 per month. The following expression
shows the income based on amount and years invested as
well as interest rate
1
1 + R
−1
3
M = monthly income ($)
M = P.
400
−4.Y P = amount of fixed investment ($)
1 − 1 + R R = fixed rate of interest (%)
400 Y = number of years before the investment runs out
a) If you invested the $1M at an interest rate of 2%, how many
years would you be able to get an income of $5000 per
month.
b) What must the interest rate be if you want to have $5000
per month for at least 40 years?
Pros and Cons of GOALSEEK
• Pros
– easy to use
– can be used for both linear and non-linear objective
functions
• Cons
– can only change 1 decision variable at a time
– can’t include constraints
Example
PRISON BUILDING
Exercise
compound
Fencing
Objective is to build a fenced off exercise compound
of maximum area, using 500m of fencing material.
Example
PRISON BUILDING
Area of exercise
Y compound (A)
Fencing
X
max A, s.t. X+2Y<=500
Solution using EXCEL SOLVER
• Can use SOLVER to obtain answer
EXCEL SOLVER
• Can be used to solve general constrained
optimisation problems
• Needs to be enabled
– FILE->OPTIONS->CUSTOMIZE RIBBON
– Select DEVELOPER on the right-hand panel and
– Click OK
– Goto DEVELOPER -> ADD-INS
– Select ANALYSIS TOOLPAK and SOLVER ADD-IN
• SOLVER should now be available under the DATA tab
• Useful Resource: https://www.solver.com/
Using EXCEL SOLVER
• Can accommodate linear and non-linear optimisation
objective functions
• Can consider up to 200 decision variables
• Apart from equality and inequality constraints, can
additionally accommodate decision variables that are
– strictly integer values
– binary values
• In addition to “maximising” and “minimising”, it can
be used to discover conditions for setting the
objective function to a specific value
Using EXCEL SOLVER
• Terminology
– Target Cell – the cell containing the formula of the
objective function
– Changing Cells – the locations of the decision variables
that can be varied
• Set up spreadsheet to contain all the decision
variables and their values and identify the changing
cells
• Specify the constraints
• Enter objective function formula in the target cell
EXCEL SOLVER
• Capable of solving a number of optimisation
problems using different approaches
EXCEL SOLVER Algortihms
• For linear objective functions and constraints, select
SIMPLEX LP
• For non-linear objective functions, select either:
GRG Non-linear
or Evolutionary
• The “GRG” (Generalised Reduced Gradient) algorithm is
based on the use of gradients
• The “Evolutionary” algorithm is based on the theory of
evolution and survival of the fittest
• GRG is faster than Evolutionary
• Evolutionary may be better at finding a global optimum
MATLAB tools
• MATLAB has an “OPTIMIZATION TOOLBOX”
containing many scripts and functions to help solve
different kinds of optimisation problems
• The ones that we are interested in is “linprog” and
“fmincon”
• “linprog” is used to solve LP problems
• “fmincon” is used to solve constrained NLP problems
Which tool to use?
• As the module progresses, you will learn how to use
the appropriate tool(s) to tackle the different types
of optimisation problems
• Bring in your laptops for all lessons
